xp_sendmail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

xp_sendmail

Author

Topic

jausen
Starting Member

4 Posts

Posted - 2004-06-07 : 15:42:09

I am using xp_sendmail to automatically email the results of a query that I have written, but am getting the following message when trying to execute it.

Server: Msg 103, Level 15, State 7, Line 7
The identifier that starts with 'Select Document_tb.ReferenceData as 'Marten Trip#',
Document_tb.DocumentName as 'Cust ShipId',
Document_tb.PartnerKEY as 'P' is too long. Maximum length is 128.

The query I am using is listed below and I would greatly appreciate any help on this.

DECLARE @message VARCHAR(8000)
DECLARE @query VARCHAR(8000)

SET @message = '210 Report'

EXEC master.dbo.xp_sendmail @recipients = 'edihelp@marten.com', @subject = 'test',
@message = @message, @query =
"Select Document_tb.ReferenceData as 'Marten Trip#',
Document_tb.DocumentName as 'Cust ShipId',
Document_tb.PartnerKEY as 'Partner',
Document_tb.TransactionSetID as 'Set ID',
'GENTRAN Processed Status' =
Case Interchange_tb.ProcessedStatus
When 1 Then 'Received'
When 2 Then 'Sent'
When 3 Then 'Ready to Send'
When 4 Then 'Queued'
When 5 Then 'Hold'
When 6 Then 'Overdue'
End,
'ISA Network Status'=
Case Interchange_tb.NetworkStatus
When 0 Then 'Not Updated'
When 1 Then 'Received OK'
When 2 Then 'Network Warning'
When 3 Then 'Network Error'
When 4 Then 'Picked Up'
When 5 Then 'Sent to 3rdParty Network'
END,
'Doc Compliance Status'=
Case Document_tb.ComplianceStatus
When 0 Then 'Incomplete'
When 1 Then 'NonCompliant'
When 2 Then 'OK'
When 3 Then 'Document Queued'
When 4 Then 'Sent'
When 5 Then 'Net Received'
When 6 Then 'Net Delivered'
When 7 Then 'FA Received'
When 8 Then 'Waiting for Ack'
When 9 Then 'Over Due'
When 10 Then 'Net Warning'
When 11 Then 'Net Error'
When 12 Then 'Ack with Errors'
When 13 Then 'Partially Ack'
When 14 Then 'Ack Rejected'
When 15 Then 'Net Picked Up'
When 16 Then 'Duplicate'
When 17 Then 'Ready to Send'
When 18 Then 'Send Failed'
End,
'GS AckStatus'=
Case Group_tb.AckStatus
When 0 Then 'Ack Not Required'
When 1 Then 'Waiting for ACK'
When 2 Then 'OK'
When 3 Then 'Ack With Errors'
When 4 Then 'Partially Ack'
When 5 Then 'Rejected'
When 6 Then 'Ack Overdue'
END,
Interchange_tb.ControlNumber as 'ISA Cntrl#',
Group_tb.ControlNumber as 'GS Cntrl#',
Document_tb.ControlNumber as 'ST Cntrl#',
'Time Sent' = convert(char,dateadd(second,(Interchange_tb.TimeSent-14400),'1/1/1970'),9),
'Time Network Received' =
Case Interchange_tb.NetworkTime
When 0 Then 'Not Updated'
Else convert(char,dateadd(second,(Interchange_tb.NetworkTime-14400),'1/1/1970'),9)
End

From GENTRANDatabase.dbo.Document_tb,
GENTRANDatabase.dbo.Interchange_tb,
GENTRANDatabase.dbo.Group_tb,
GENTRANDatabase.dbo.Track_tb

where Document_tb.DocumentKEY = Track_tb.DocumentKEY and
Group_tb.GroupKEY = Track_tb.GroupKEY and
Interchange_tb.InterchangeKEY = Track_tb.InterchangeKEY and
Document_tb.Direction = 1 and --Selects outbound document only
Document_tb.TransactionSetID = '210' and
convert(char,dateadd(second,(Interchange_tb.TimeSent 14400),'1/1/1970'),9) > DATEADD(Hour, -24, GETDATE())

Order by Interchange_tb.TimeSent DESC,Document_tb.PartnerKEY, Document_tb.TransactionSetID"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 15:45:25

Put the query in a stored procedure or view. Have xp_sendmail execute the stored procedure or select from the view.

Tara

jausen
Starting Member

4 Posts

Posted - 2004-06-08 : 08:46:12

Thanks for the help it worked great!

jausen
Starting Member

4 Posts

Posted - 2004-06-08 : 09:05:30

One other question is there a way to format the output of the query to be in a readable format like in columns. I get the query results and the data is all over the place. It is not readable, so I was wondering if there is anything I can do. Thanks Jody.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-08 : 12:09:32

No, there isn't much you can do. If you want a more readable format, you'd have to use a different e-mail solution.

Tara

jausen
Starting Member

4 Posts

Posted - 2004-06-08 : 12:29:16

Thanks. Jody

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources