Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.








Sponsored Links:

Related Messages:
Email Body Was Truncated In Tcp Layer In Sp_send_dbmail
 

I sent email from sp_send_dbmail in html format.
when i checked it in outlook, each piece was corrupted at 1000 bytes, the totalbytes in my email is 2600 bytes,it was corrupted twice. it looks like below:
<table>
<tr><td>Customer</
John Smith<td></tr>
</table>
 
I believe Sql server SMTP mail server sent the email body in 1000 bytes each TCP conversion.
 
Is any way I can change the TCP setting for SQL server SMTP mail engine.
 
Thanks,
 
 

View Replies !   View Related
255 Character Limit
Hi,
I€™m having a problem with a storedprocedure in SQL Server 2005 Developer Edition: I have declared a varchar(max) variable, but when a try to assign a large string it gets truncated to 255 characters, I get the same result if a declare the variable with varchar(8000), żcan someone help me fix this?, I don€™t need the full 8000 characters, but I need more that just 255.
Thanks.

View Replies !   View Related
Excel 255 Character Limit :(
I am using VB.NET / SQL to extract data from an Excel spreadsheet and insert it (via a dataset) into an SQL database.

My SQL select to form the dataset is:
Select 0, '01/01/1905', '00:00' , 'Description: ' + [DESCRIPTION] + '<br/><br/>Type: ' + [Field5] + '<br/> Thankyou' As Description From [Primary Schools$]

I am using Jet for the connection, the connection string is:

sExcelConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Path ofile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

My problem is that, although Description and Field5 themselves are not above 255 characters, the combination of the two, plus my own text do come out as more than 255 characters and as such get truncated.

I have tried using cast and nested selects, but to no avail.

Any body got any ideas ?

View Replies !   View Related
Datatype Character Limit
I am trying to store long text so I gave the field in question the TEXT datatype. But it only lets me store 1023 characters. I was expecting (but not quite needing) 2,147,483,647 characters when using the TEXT datatype. Can some one point out what I am missing.

Great forum, my first post, ahhh....

I will continue to search the forum for my answer. Thank you for taking the time to read this.

John

...uh, something like that...

View Replies !   View Related
The 4000 Character Limit For Expressions
I am building rather large SQL statements using expressions and I am about to hit the 4000 character limit. First, what were the reasons for setting such a low limit? With it in place you are forced to come up with other solutions, but perhaps that was the intention.

Speaking of limits, what are the limits for storing a value in a string that is defined in a package configuration file? What are the limits when creating a string within a script task and then use it to set a string variable in the package? What are the limits when using a string value as an input parameter to an Execute SQL Task using OLE DB? Do these differ depending on the connection type?

I guess I'm getting a bit paranoid about limits, but I don't want to hit any others late in the design.

Regards,
Lars

View Replies !   View Related
Support Asian Character And Connect Limit
Do MS SQL Express support Asian character set?

If I want to input Traditional Chinese character, what collation I should use?

 

Is there any limitation on the no of connect to MS SQL Express?

View Replies !   View Related
Character Limit For Variables Inside A Stocked Procedure
I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.I am looking for a "How to" to put this limit to 8000.Thank you! 

View Replies !   View Related
Need Workaround For 4000-character Limit On CLR Sproc Parameters
I've written a managed (C#) stored procedure with the following signature:


[Microsoft.SqlServer.Server.SqlProcedure]
public static void Sproc(string startDate, string endDate, string idList)...



Sometimes when I call this sproc, my comma-separated list of IDs exceeds 4000 characters.  How can I get around this problem? 

I guess I need something equivalent to NVarchar(MAX), but for CLR sprocs instead of TSQL.

Any thoughts?

View Replies !   View Related
When Changing To Type Text From Nvarchar, The 255 Character Limit Is Maintained?
Hi - I am changing a field from type nvarchar to type text, given thatI need to store strings longer than 255 characters. To do this Ichange the data type in SQL Server, then I change the parameter code inthe calling procedure, as per below:cmd.Parameters.Append(cmd.CreateParameter("@title", adVarWChar,adParamInput, 255, title));becomes:cmd.Parameters.Append(cmd.CreateParameter("@title", adLongVarWChar,adParamInput, 1073741823, title));However, when I do this, for some reason, the field is still limited to255 characters - when I try to update the field with 256 characters,the error 'Application uses a value of the wrong type for the currentoperation.' occurs.Why is this? I've checked that the correct data is contained in theparameter. When I look at the data in the database, the column inquestion shows the content, whereas the next column, which has alwaysbeen of type text, shows '<LongText>' - does this mean anything? Do Ineed to do something special to convert the column from nvarchar totext?Many thanks,Iain

View Replies !   View Related
Select Part Of Character String Based On A Character
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).

How do I select everything to the right of the dash when the number of characters to the left of the dash varies?

View Replies !   View Related
XML Parsing: Line 26, Character 5, Illegal Xml Character
 

I have a Table with 5 million rows and a column that is "Text" and some of the column lengths are 690,000 characters. Most of the data in this one column is XML data and it is taking a long time to find and replace values in the XML nodes.  In another post it was recommended that we just change the column to XML and then do an update to the values.
 
When I try this in a test environment with a small sample of data it works fine and I change it from Text to XML, update the values, and then change it to VARCHAR(MAX) and then back to Text (to avoid having to mess with code releases, etc.)
 
In a production environment the switch from Text to XML fails after 14 hours saying that "XML parsing: line 26, character 5, illegal xml character" which I have to assume that there is data in at least one of the rows that is not a valid XML character.
 
I think I read that when changing the column type to XML it has to go through all of the rows and validate that there is valid XML in the column.
 
Is there a way to turn this validation off?  (like NOCHECK or something)
Is there an easy way to search through these column values for the illegal characters?
Does anyone know which specific characters are illegal?
 
Keep in mind that this is a very wide column in a big table where just this one table makes up about 400GB or more of this DB size.
 
Cursoring through the rows and leaving the column as text, the script below takes 3 seconds per row to grab the data for 1 row, convert it to xml, replace 7 of the values and then convert it back.  (I tried a "while loop" with no performace gain as well)
 
3 seconds times 5 million rows would take about 600 hours in a production environment where I would probably not be able to run this during the day.
 
ANY help would be much appreciated!!!
 


  Select @snapShot = CONVERT(xml, SnapshotText) from Audit_ProductInstances where Audit_ProductInstanceID = @AuditProdID

  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoLarge]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoExtraSmall]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoMedium]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoMediumLarge]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[LogoSmall]"]/Value/text())[1]
        with ""')
  Set @snapShot.modify('replace value of (/WorksheetData/MergeFields/MergeFieldBase[Name="[Seal]"]/Value/text())[1]
        with ""')
  Update Audit_ProductInstances set SnapShotText = Convert(varchar(max), @snapShot), FixedFlag = 1, SQLUser = 'FixedSnapshot'
  where Audit_ProductInstanceID = @AuditProdID
 

Thanks,
Brian

View Replies !   View Related
Need Help W/ Sp_send_dbmail !
SQL Server 2005Help. I have a problem:On a 2000 box, I am calling the sp_send_dbmail stored procedure on a2005 box (they are linked servers).This is how I'm calling the proc (from the 2000 box), which usessp_send_dbmail to send the mail.EXEC My2005Server.DatabaseName.dbo.SendNotification@recipients = 'me@yahoo.com',@subject = 'SENDING @TOTAL,@body = @text1 + @text2 + @text3 + @text4 + @text5@text1 - @text5 are varchar(8000).As you can see I'm trying to take advantage of the varchar(max) that@body allows.However, I get this error:Server: Msg 170, Level 15, State 1, Line 22Line 22: Incorrect syntax near '+'.I need to be able to concatenate these somehow... as I cannot put theminto one large variable from the 2000 server side.Can you see the problem here?Help.Thanks

View Replies !   View Related
Sp_send_dbmail
When using "sp_send_dbmail", messages are queued through Service Broker. Other than the system views, "dbo.sysmail_...", in MSDB, is there another way to know if an email was sent successfully?

Also, in 2000 you did not need to supply the whole email address in order for xp_sendmail to work. For example, I could use my name, "RGioia" as the recipient and it would send email to rgioia@<my current domain>. Does anyone know of a work-around for this in 2005 or do you just have to ensure that full email addresses are used?

Thanks in advance!!

View Replies !   View Related
SP_SEND_DBMAIL
I wrote a trigger as follows:

CREATE TRIGGER TR_ABC
ON TABLE_A
AFTER INSERT
AS
DECLARE @E_MAIL VARCHAR(255),
@MESSAGE VARCHAR(255)
SET @MESSAGE = 'A new call request has been logged'

SELECT @E_MAIL = E_MAIL
FROM TABLE_A AS A
LEFT OUTER JOIN TABLE_B AS B
ON B.SYSADMIN = A.SYSADMIN
WHERE SYS_ADMIN = 1

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SCINFO',
@recipients = @E_MAIL,
@body = @MESSAGE,
@subject = 'SCI Service Request';


The problem is everytime I insert data into the table I get an error stating that-:
'At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Mail queued.'

Please help.

View Replies !   View Related
Sp_send_dbmail
Hello all,

I'm new to this forum and have the same question on SQLTeam.com, where I'm a frequent forum user.

I'm trying to send the results of a table to a group of people by email, see code used below:

exec msdb.dbo.sp_send_dbmail    @profile_name =  'DT',    @recipients =  'isantos@foo.net;',    @subject =  'QA Results',    @body =  'Some Text',    @query =  'select * from [db].[dbo].[table]',    @execute_query_database =  'db',    @exclude_query_output =  1,    @append_query_error =  1;


Error message I get by email:

Msg 15404, Level 16, State 19, Server TAKKARA, Line 1 Could not obtain information about Windows NT group/user 'WECLICKisantos', error code 0x5.

If I remove the @query, @exclude_query_output and @append_query_error options, I can send the email without a problem, just whenever I try to add a query to it that it gives me this error message.

I'm a sysadmin, I can run that query, I have access to the DatabaseMailUserRole under msdb and the profile that I'm using is public... I'm kind of stuck, let me know if you can help.

View Replies !   View Related
Read Chinese Character From SQL(SQL Server 2005) Database Table Column And Display Chinese Character
Hi!

I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
 LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using  UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
       
        int codePage =  950;
        StringBuilder message = new StringBuilder();
        Encoding targetEncoding = Encoding.GetEncoding(codePage);
        byte[] encodedChars= targetEncoding.GetBytes(str);
        .
        message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
        for (int i = 0; i < encodedChars.Length; i++)
        {
            message.Append("Byte " + i + ": " + encodedChars);
        }
      
        message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
        Console.Writeline(message.ToString());

View Replies !   View Related
Sp_send_dbmail Question
I'm running the following test

declare @dbccdate varchar(60)
set @dbccDate='dbcc east' + convert(varchar(60),getdate(),10)
exec msdb.dbo.sp_send_dbmail
@recipeints='human@gmail.com',
@body = 'This is a test'
@subject= @dbccdate ,
@query='dbcc checkdb (msdb)',
@attach_query_result_as_file=1,
@query_attachment_filename='Dbcc_MSDB_Results.txt'

This works fine but, I'd really like the subject to contain the line
"Checkdb found 0 errors..." So mgmt doesn't have to open the txt file to view the results.

I've tried dumping the results to a txt file first. Then trying to read the text file for the line inquestion. I wrote a for loop to parse the results command line, but can't figure how to add that value to the subject.

The script is this:

for /f %i in ('findstr /B /I "CHECKDB" y:dbcclogmsdblog.txt') do echo %i

Is there anyway to do this?

View Replies !   View Related
Sp_send_dbmail Problem
I am trying to use the sp_send_dbmail sproc but I get the following error:

Msg 22051, Level 16, State 1, Line 0
Attachment file C: est.txt is invalid.

My code:


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Server Mail Profile blah blah',
@recipients = 'myEmail@somewhere.com,
@body = 'Hello World',
@subject = 'Hello World',
@file_attachments = N'C: est.txt'

why would that be? The test.txt file does exist on my drive!

Please advise.

View Replies !   View Related
Possible Issues With SP2 And Sp_send_dbmail?
Have a funny one that I've not yet resolved, and was wondering if I am the only one.

SQL2005 SP1 server, Ent Ed, 64 bit, clustered machine.
SP2a installed over Easter weekend.

about 12 days after apparently succesful upgrade, the cluster was failed over. After that fail-over, an existing job started failing, with the following message:
quote:Error executing extended stored procedure: Invalid Parameter [SQLSTATE 42000] (Error 22050). The step failed.

All the job does is build a SQL query, then supply the query to sp_send_dbmail for execution:

DECLARE
@RetCodebit,
@Tovarchar(200),
@Subjectvarchar(100),
@CCvarchar(200),
@Queryvarchar(max),
@Debugsmallint

--cut section that has the query , but these are straight SQL
--queries - no SP or XP usage whatsoever

EXEC@RetCode = msdb.dbo.sp_send_dbmail
@profile_name= 'Email',
@recipients= @To,
@subject= @Subject,
@copy_recipients= @CC,
@query_result_header=0,
@Query=@Query

One 'funny' I noticed is that up until the failover, SSMS seemed to erport the version of the server as still the SP1 (2157). After failover, it now lists (3042). This may simply be a symptom of SSMS not auto-refreshing though. We have (I believe) failed back again since then, still no joy.

Any Ideas?

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

View Replies !   View Related
Sp_send_dbmail &&amp; COM Errors.
I just started getting these errors on a few (not all) servers the other day.  These servers have been running fine for a few months now...



Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: Error initializing COM

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.



I can only find one thread regarding this error, specifically with dbmail and I am doing the same thing (executing a query and attaching the results).

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=248650

This thread points to a KB article http://support.microsoft.com/kb/910416 which is close except for the fact that these were not upgrades, but clean installs.  I'm leary of applying the hotfix to my servers.

Is this a known issue internal to MS and is the referenced hotfix appropriate to install?

View Replies !   View Related
Problem With Sp_send_dbmail
 

Hello,
 

I have a problem when send a mail with sp_send_dbmail.
the mail server adds a legal warning to the sent message but this he visualizes with strange characters
because can be?
 
Thanks.

View Replies !   View Related
Sp_send_dbmail Without Using A Cursor?
I have a table which contains a list of addresses to send an email to.  I'd like to be able to do something like:
 
 
update emaillist

set sendcount = SendMyMessage( emailaddress )
where sendcount = 0
 
 
In this case SendMyMessage is a user defined function which calls the sp_send_dbmail stored procedure.
 
However, I am receiving an error saying "Only functions and extended stored procedures can be executed from within a function"

 
From what I've seen, it sounds like I am forced to use a cursor to go through my email list table.  Is that the only way?
 
Under SQL 2000, using a user defined function to call CDONTS was a no brainer....
 
 
 
Thanks,
Chris.

View Replies !   View Related
Anyone Else Get No Body To Msgs After 10/28?
Anyone else get no body to msgs after 10/28?
Why admin peoples?

View Replies !   View Related
Gap Between Header And Body
Hello every body,

I am facing a smal formatting issue. the issue i have 7 text boxes attached with each other in the header to show the week days ,i.e Sunday,Monday,Tuesday,Wednesday,Thursay,Friday and Saturday

In Body,i have a matrix which will extend to have 7 columns for the weekdays.

Every thing is fine,Except a small gap between header and footer where i want the 7 text boxes to act as column header names.

I tried with various options like removing the header and placing the text boxes in Body followed by the matrix. 2) placing the seven column headers in a dummy table and attaching that with matrix,

 

But i am unable to remove the gap between them. if any one of you had experienced the same problem or knows how to solve.it's really a great help

 

Thank you,

 

View Replies !   View Related
Sp_send_dbmail Possible Security Issue
I have a stored procedure that sends an mail using sp_send_dbmail. My problem is, if I execute the stored procedure via my ASP.NET 2005 application, the email will not send. I know the stored procedure is being called and works because:
1. The stored procedure does 2 things, sets a status, then sends the email. The status is being changed, but the email is not being sent.
2. When I investigate with Sql Server Profiler, I see the stored proc being called and the values of the parameters are set as they should be.
What I don't understand is, I can copy the call in Sql Server Profler, and paste it in a Query Analyzer window and execute that same call, and everything works fine, status is changed, and the email is sent (to myself for testing and I receive it just fine).
Is there a reason why the email won't send via code? Am I suppose to setup some security that will allow my application to send emails? Any info would be GREATLY appreciated.

View Replies !   View Related
Sql 2005, Sp_send_dbmail, @profile_name
Hi everyone,
I'm trying to usie "sp_send_dbmail" and i'm required to provide value to: "@profile_name" argument. The trouble is i have no idea what it means..
How do i get to know what is my "profile_name" ?
Thanks.

View Replies !   View Related
Problem With Sp_send_dbmail With Attachment
Hi,

We have a DTS package in the old SS2000 that we are still using in SS2005. We change the old xp_sendmail to use the new sp_send_dbmail. This runs fine without attachment. But with attachment, I'm encountering an error when running the package.

The task reported failure on execution.
The client connection security context could not be impersonated. Attaching file requires an integrated client login.
Syntax error or access violation.

I cannot find much topic on the net about this error.

Thanks very much for any help.

Regards

View Replies !   View Related
Trigger Problem With Sp_send_dbmail
Hallo
 
I want to write a trigger that sends me an email, when new records are added into a table.
 
For testing I have created one table called location1, which has simple data from AdventureWorks.Production.Location. And another table is called new_location, which stores the newly added records.
 
Here is the trigger:********************************
use [AdventureWorks]
if object_id ('location1') is not null
    drop table location1
go
create table location1(LocationID int, LName varchar(50))

if object_id ('new_location') is not null
    drop table new_location
go
create table new_location
    (LocationID int, LName varchar(50))

if object_id ('change_ID', 'TR') is not null
    drop trigger change_ID
go
create trigger change_ID on location1
for insert
as
    delete new_location
    insert into new_location
        select * from inserted

    exec sp_send_dbmail
         @profile_name = 'Profile',
         @recipients = 'email',
         @subject = 'New Record(s) added.',
         @query = 'select * from new_location order by LocationID';
go
********************************

After I insert statement, Management Studio runs the executing throughout.
insert into location1
    select LocationID, Name
    from Production.Location
    order by LocationID

Can somebody help?









 

View Replies !   View Related
Msdb.dbo.sp_send_dbmail Error
 I make PROCEDURE  to send email useing  db msdb and this PROCEDURE    dbo.sp_send_dbmail   like this

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'saly',

@recipients = @email,

@subject = @subject,

@body = @body;

Go

And when EXEC  it gives me mail qeue

and mail don't arrive where it goes i don't know please tell me what error

thankx very much

View Replies !   View Related
Sp_send_dbmail Erroring Out On Line 476?!
 

Hello all -
 
I'm using SQL Server 2005.  I'm trying to send an email with query results attached.
I've enabled database mail (surface config, config wizard, profile set, accounts loaded).
I know database mail itself works as I'm able to send plain text messages as well as messages with HTML embedded.
However, when I try to format a query to send attachments with mail, as shown in BOL; I always get this error message -
<snip>

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476

Query execution failed: Error initializing COM

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.
<snip>
 
Here is a sample of the query...

DECLARE @WHO VARCHAR(255)

SET @WHO = '<name would go here>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'sqlmail'

,@recipients = 'my.name@wouldgohere.com'

,@subject = 'testing attachment'

,@query = N'SELECT email_body

FROM WFS_non_stock_email_sendit_history

WHERE who_entered = @WHO

ORDER BY date_created DESC

OPTION(MAXDOP 1)'

,@execute_query_database = 'whaley'

,@attach_query_result_as_file = 1

,@query_attachment_filename = 'TESTATTACH.txt'

,@query_result_separator = ';'
 
If I were to run this query outside of this dbmail code, the query works fine.
 
If I can believe the go to line editor, line 476 in the sp_send_dbmail sproc is...

DELETE sysmail_attachments_transfer WHERE uid = @temp_table_uid

 
My problem here is this is a system sproc that MSFT built.  I don't understand why I would get this error message instead of something more definitive and indicative of the problem.
 
Has anyone else seen this problem?  I really need to be able to send attachments.
 
Thanks
 
Randyvol 

View Replies !   View Related
Sp_send_dbmail. How Can I Use A Query For @recipients
Hi everyone,
i'd like to assign "@recipients" for sp_send_dbmail derived from a query as follows:
 


Code SnippetDECLARE @mailist VARCHAR(2000)
SET @mailist=
'select email
from
server.db.dbo.table
where
lastname=' +''''+'aaa'+''''
exec msdb.dbo.sp_send_dbmail
@profile_name='myprofile',
@recipients=@mailist,
@subject='email address through a query',
@...
 
 

when i run it i dont get any error message but mails to dot arrive.
any suggestion why ?
Thanks a lot

View Replies !   View Related
Sp_send_dbmail Doesn't Work
hi all,
i made a stored procedure that uses the sp_send_dbmail to send mails. SQL server dislays the message "mail queued" but nothing is recieved
 
here is the code of the stored procedure i made
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Exams',
@recipients = 'me@domain.com',
@Body_format = 'HTML' ,
@subject = 'Room Preparation' ,
@body='hi there';
so can anyone help with this issue
thanks in advance
 

View Replies !   View Related
Sp_send_dbmail Result Header
I am trying to use sp_send_dbmail to exec stored procedure results to an email as an attachement.  This works very well, except for one problem, it is not giving the result headers for the queries in the stored procedure even with the

@query_result_header = 1

 

I have also tried this with a VERY simple query, just incase it was the multiple result sets causing the problem, also not putting the results as an attachment and just straight into the body. 

 

Does anybody know how to solve this problem?

 

Thanks in advance,

 

Chris 

 

View Replies !   View Related
Sp_send_dbmail Query Option
Hello,
I'm using sp_send_dbmail with query option.  Is there a way to not have "1 rows returned" in the email?
Thanks!

View Replies !   View Related
Can Any Body Tell Me What Wrong In My Sql Statement
Hi,
can any body tell me what is wrong in my sql statement
 SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
it gives me this error
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
 
but when i use somthing like that i will works fine
SELECT
Budget


i am using sql server 2000
 

View Replies !   View Related
Can Any Body Tell My Why This Page Does&#180;t Uppdate Into Sql
can any body tell my why this page does´t uppdate into my sql
i get no error but it does´t uppdate the records :(


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Updating a Row of Data with Validation</title>
<style type="text/css">
#editPanel {
width:90%; padding:10; background:khaki;
border:thin outset}
#posted {
background:lightgreen; border:thin inset}
</style>

<script language="C#" runat="server">
/* *********** Data base config ************ */

string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password


/* *********** define DataBase Sql/Access data ************ */

string TheID;

protected void Page_Load ( object src, EventArgs e )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password


SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ("SELECT * FROM cars ORDER BY MessageDate desc",myConn);
myCmd.Connection = myConn;
TheID = Request.QueryString [ "TheID" ] ;
if ( !IsPostBack )
{
if ( TheID == null )
{
bindGrid ( );
loadPanel.Visible = true;
editPanel.Visible = false;
postPanel.Visible = false;
}
else
{
getMessage ( Request.QueryString [ "TheID" ] );
loadPanel.Visible = false;
editPanel.Visible = true;
postPanel.Visible = false;
}
}
else
{
loadPanel.Visible = false;
editPanel.Visible = false;
postPanel.Visible = true;
}
}

public void bindGrid ( )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ("SELECT TOP 10 * FROM cars",myConn);
SqlDataReader rdr;

myCmd.CommandText = "SELECT TOP 10 * FROM cars";

myConn.Open();

rdr = myCmd.ExecuteReader ( );
myGrid.DataSource = rdr;
myGrid.DataBind ( );
rdr.Close();
myConn.Close();

/*
myCmd.CommandText = "SELECT * FROM cars";
myConn.Open();

myGrid.DataSource=myCmd.ExecuteReader ( );
myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
myGrid.DataBind ( );
*/
}

public void getMessage ( String TheID )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );
SqlDataAdapter myAdapter = new SqlDataAdapter ("select * from cars where ID=" + TheID, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ] ;

msgFastnumer.Value = myRow [ "Fastnumer" ].ToString ( );
msgTegund.Value = myRow [ "Tegund" ].ToString ( );
editPanel.DataBind ( );
}

public void updateMessage ( object src, EventArgs e )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );

myCmd.Connection = myConn;

SqlDataAdapter myAdapter = new SqlDataAdapter ("select * from cars where ID=" + TheID, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ] ;

SqlDataReader rdr;

if ( Page.IsValid )
{
myCmd.CommandText = "UPDATE cars SET Fastnumer=@Fastnumer, Tegund=@Tegund where id=" + TheID;

msgFastnumer.Value = myRow [ "Fastnumer" ].ToString ( );
msgTegund.Value = myRow [ "Tegund" ].ToString ( );

myCmd.Parameters.Add ( "@Fastnumer", SqlDbType.NVarChar ).Value = msgFastnumer.Value;
myCmd.Parameters.Add ( "@Tegund", SqlDbType.NVarChar ).Value = msgTegund.Value;

myConn.Open ( );
rdr = myCmd.ExecuteReader ( );
myGrid.DataSource = rdr;
myGrid.DataBind ( );
rdr.Close();
myConn.Close ( );

}
bindPostPanel ( TheID );
}

public void bindPostPanel ( String TheID )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password

SqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );
SqlDataReader rdr;
myCmd.CommandText = "SELECT * FROM cars WHERE Id='" + TheID + "'";

myCmd.Connection = myConn;

myConn.Open();

rdr = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataSource = rdr;
listDetails.DataBind ( );
rdr.Close();
myConn.Close();

/*
myConn.Open ( );
listDetails.DataSource = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataBind ( );
myConn.Dispose ( );
*/




}
</script>
</head>
<body>
<div class="header"><h3>ADO.NET Primer:
<span class="hilite">Editing an Existing Record</span></h3>
</div>
<hr size="1" width="90%">
<br>
<center>
<asp:panel id="loadPanel" runat="server">
<h5>Select a Record to Edit</h5>
<asp:datagrid id="myGrid" runat="server" width="90%" cellpadding="5" gridlines="vertical" bordercolor="black"
borderwidth="1" font-size="8pt" backcolor="ghostwhite" alternatingitemstyle-backcolor="lightgray"
autogeneratecolumns="false">
<headerstyle backcolor="darkslategray" forecolor="khaki" height="25" font-bold />
<columns>
<asp:boundcolumn headertext="Date" datafield="Innsett" dataformatstring="{0:d}" />
<asp:hyperlinkcolumn headertext="Fastnumer" datanavigateurlfield="id" datanavigateurlformatstring="aspxtest.aspx?TheID={0}"
datatextfield="Fastnumer" />
<asp:boundcolumn headertext="Tegund" datafield="Tegund" />
</columns>
</asp:datagrid>
<p align="center">Back to top</p>
</asp:panel>







<asp:panel id="editPanel" runat="server">
<h5>To save changes to the database, press Update.</h5>
<form runat="server">
<input type="hidden" id='msgDate' runat="server">
<table width="85%" cellspacing="1" cellpadding="3" border="0">
<col width="40%" align="right">
<col width="60%">
<tr>
<td>Fastnumer:</td>
<td><input id="msgFastnumer" runat="server">
<asp:requiredfieldvalidator runat="server" controltovalidate="msgFastnumer" errormessage="Sender's name cannot be blank. "
display="none" /></td>
</tr>
<tr>
<td>Your email address:</td>
<td><input id="msgEmail" runat="server">
<asp:regularexpressionvalidator runat="server" controltovalidate="msgEmail" validationexpression="^[w-]+@[w-]+.(com|net|org|edu|mil)$"
errormessage="Please enter a valid e-mail address. " display="none" /></td>
</tr>
<tr>
<td>Tegund:</td>
<td><input id="msgTegund" runat="server"></td>
</tr>
<tr>
<td>Your message:</td>
<td><textarea id="msgBody" rows="5" cols="35" runat="server"></textarea></td>
</tr>
</table>
<p>
<input type="submit" value="Update" runat="server" onServerClick="updateMessage">
<input type="reset" value="Cancel" onClick="self.location.replace ( 'aspxtest.aspx' ) "></p>
<asp:validationsummary runat="server" displaymode="SingleParagraph" showmessagebox="true" showsummary="false" />
</form>
</asp:panel>
<asp:panel id="postPanel" runat="server">
<h5>Thank you for editing your comments. This record has been updated.</h5>
<asp:datalist id="listDetails" width="85%" runat="server">
<itemtemplate>
<table id="posted" width="100%" cellspacing="1" cellpadding="5" border="0">
<col width="35%" align="right">
<tr>
<td>Date:</td>
<td><%# DataBinder.Eval ( Container.DataItem, "Innsett", "{0:d}" ) %></td>
</tr>
<tr>
<td>Name:</td>
<td><%# ( ( IDataRecord ) Container.DataItem ) [ "Fastnumer" ] %></td>
</tr>
<tr>
<td>Tegund:</td>
<td><%# ( ( IDataRecord ) Container.DataItem ) [ "Tegund" ] %></td>
</tr>
</table>
</itemtemplate>
</asp:datalist>
<p>
Back to Edit View
Select Another Record
</p>
</asp:panel>
</center>
<br>
<hr size="1" width="90%">
</body>
</html>

View Replies !   View Related
Body Of E-mail Cut When Using CDONTS From SQL
The pretty much same code sends right e-mail from ASP page, but
this, if sent from SQL server, cuts Body for the message.
I did verify that SQL sends right query (to MTS?) using SQL Profiler.

Please help!


Declare
@From [VARCHAR](8000) ,
@To [VARCHAR](8000) ,
@Subject [VARCHAR](8000),
@Body [VARCHAR](8000),
@Importance int ,
@MailFormat [BIT] ,
@BodyFormat [BIT]
Select @From ='Server', @To ='somebody@domain.com,
@Body= 'Dear Valuable Customer,<BR>Good Afternoon!<BR>Your Request for<i>The Best Candies in the World</i> has been received on the WEB.<BR> You can access it by logging in <a href=http://www.domain.com/>http://www.domain.com/</a> to review your orders. If you see a <FONT color=red>missing</FONT> designation or the reports indicate the previous run date - please hit the refresh key on your browser. If the problem persists, please call Customer Service at the number you've been given immediately for prompt assistance. ',
@Importance =3,
@MailFormat =0,
@BodyFormat =0



EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
EXEC @hr = sp_OASetProperty @object, 'From', @From
EXEC @hr = sp_OASetProperty @object, 'To', @To
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @object, 'MailFormat', @MailFormat
EXEC @hr = sp_OASetProperty @object, 'BodyFormat', @BodyFormat
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
exec @hr = sp_OASetProperty @object, 'Importance', @Importance
EXEC @hr = sp_OAMethod @object, 'Send'
EXEC @hr = sp_OADestroy @object

View Replies !   View Related
Using Variable Value In Email Body
Hello,

I have declared a variable in my SSIS package and got record count from myTable. Now I want to use this variable in email subject and email body.

Whereas when i use variable in email body then it result in variable value only ... i am unable to insert other text in it.

Please give some idea how to do it?

View Replies !   View Related
Page Number In Body
how to get page number in body of report

View Replies !   View Related
Body Values In Header And Header Values In Body
Hi,

 

I want to display a value  from db in the header section. I have read a couple of information that I should place the value in a in-visible text field an reference it in the header with the ReportItems. This works great with the first page but on the second page the header information are empty (I think because the Textfield is on Page 1 not Page 2)... So is there a way to accomplish that for all pages? Not only the first page....

 

My second one, hope you don't mind that I post it in the same thread, is:

 

My Report needs to display the total size of pages in the body. I did not realy found anything useful where I can retrieve from the total size of pages of my reports....

 

thanks for any help

 

f.

View Replies !   View Related
The Body Of The Report Disappeared
 
Hello,
I was modifying some standard reports of one planning program, by downloading .rdl file from report server and creating a new report project. I have done so many times.
Last weak in one of those new reports only header of the report was seen in the report server as I thought I had made some kind of mistake tried to look for it, and after some time the problem fixed itself and I blamed everything on one mixed up connection between the tables in the report, because after removing it the problem fixed itself, but now I think that was just a coincidence.
This weak after working fine for about a weak, 6 reports appeared as only headers and footers, they worked fine yesterday and no changes were made to the reports, but in the morning only the header is seen.
The reports that are affected are the 4 new ones, and 2 original ones, they are all dealing with the same date just different grouping and sorting.
Does anyone know how to fix this problem?
 
Darius

View Replies !   View Related
Sql Server 2005 How Do I Turn On Sp_send_dbmail
Hi everyone.
I've just installed sql server 2005 (evaluation) and i need to turn the option of using sp_send_dbmail on. I tried books on line but didnt manage to understand how it is done.
Thanks

View Replies !   View Related
Sp_send_dbmail-Mail Not Sent - Remote Certificate
When i use sp_send_dbmail stored procedure inside a Execute SQL task,the task is executed successfully.

But when i look in to 'Database Mail Log', it says

"The mail could not be sent to the recepient because of the mail server failure. Exception message: cannot send mails to mail server(The remote certificate is invalid according to the validation procedure)

Please let me know what can be the issue.

View Replies !   View Related
Attach Query Results As Xml With SP_SEND_DBMAIL
I'm trying to get sp_send_dbmail to attach the @query result set as an xml file.

The email is being sent and the attachment is present, but rather than ascii xml, it contains a chunk of nonsense like this
0x440352004F005700440470006B00650079004409660069007200730074006E0061006D00650044086C006100730074006E0061006D0065004410700068

I've tested the query with the first bit of code and sent the email with the second bit. I think I can jimmyjack it to write the xml to a temp file on the server and send that file using the @file_attachments parameter, but I'm hoping someone out there knows how to get this one to work.

Thanks in advance for any help you can give me.

Here's my code:


-- test that xml output is being generated
DECLARE @OUTPUT xml
SET @OUTPUT = (SELECT * FROM database..table FOR XML PATH('ROW'), root('ROOT'))
SELECT @OUTPUT

-- run the email proc
EXEC msdb.dbo.sp_send_dbmail
@QUERY= 'SET NOCOUNT ON SELECT * FROM database..table FOR XML PATH(''ROW''), root(''ROOT'')'
,@RECIPIENTS= 'userid@companyname.COM'
,@subject= 'Subject'
,@attach_query_result_as_file= 1
,@query_attachment_filename= 'ATTACHMENT.xml'
,@query_result_width= 32767
,@query_no_truncate= 1
,@append_query_error= 1
,@query_result_header= 1

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved