?
Solved

Email query results

Posted on 2008-11-07
7
Medium Priority
?
1,595 Views
Last Modified: 2012-05-05
i want to email the results of the query attached in the body of the email.  I have configured SQL mail what is the code i need?

Thanks

SELECT T1.[SlpName], T2.CardName
, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date
, sum(T0.[Quantity]) Total_fixes
, sum(T0.LineTotal) Invoicing_total
, sum(T3.[U_IIS_TIME]) TaskTime_Total
, T0.[u_iis_INS2], T0.[u_IIS_INS3]
FROM DLN1 T0  
INNER JOIN OSLP T1
  ON T0.SlpCode = T1.SlpCode
INNER JOIN ODLN T2
  ON T0.DocEntry = T2.DocEntry
left outer join OSCN T3
  on t3.ItemCode = t0.ItemCode
 and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
WHERE T2.[U_IIS_JTYPE] = '6'
  and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
  and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]

Open in new window

0
Comment
Question by:KeirMcCann
  • 3
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 22904068
Hello KeirMcCann,

http://www.sqlteam.com/article/sqlmail-in-depth


GOD BLESS,

Aneesh R.
0
 

Author Comment

by:KeirMcCann
ID: 22904112
xp_sendmail
    @recipients='email',
    @subject='REPORT: Completions per engineer',
    @query='SELECT T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date, sum(T0.[Quantity]) Total_fixes, sum(T0.LineTotal) Invoicing_total, sum(T3.[U_IIS_TIME]) TaskTime_Total, T0.[u_iis_INS2], T0.[u_IIS_INS3] FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8) WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1) and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]'

When i execute this it opens up a dialog box asking me to save a report file then gives an error


0
 
LVL 6

Accepted Solution

by:
Jerryuk007 earned 1500 total points
ID: 22904231
Try something like this (replacing the Emails with the correct Email Address):


declare @myQuery varchar(8000), @eSubject varchar(254)
Set @myQuery='SELECT T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date, sum(T0.[Quantity]) Total_fixes,
 sum(T0.LineTotal) Invoicing_total, sum(T3.[U_IIS_TIME]) TaskTime_Total, T0.[u_iis_INS2], T0.[u_IIS_INS3]
 FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
 INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry
 left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
 WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
 and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]'

Set @eSubject='REPORT: Completions per engineer'

            SET @StrSQL = 'Select Body from TmpEmail'
            Create Table TmpEmail (Body varchar(254))
            Insert into TmpEmail Values('Hi All')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Please find attached the latest reports generated by the SQL Server.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Thanks.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('MSSQL Database Server')
            exec master..xp_sendmail @recipients='myEmail@whoknows.com',
            @query=@myQuery, @no_header='TRUE',
            @copy_recipients='mySecondEmail?whoknows.com',
            @subject=@eSubject,
            @dbuse='master'

      Drop Table TmpEmail

Hope this helps.

Jerry
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:KeirMcCann
ID: 22904386
thanks for your help jerry i added my mails and got this error unfortuantely i suck at this!

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '6'.
Msg 137, Level 15, State 1, Line 13
Must declare the scalar variable "@StrSQL".

declare @myQuery varchar(8000), @eSubject varchar(254)
Set @myQuery='SELECT T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date, sum(T0.[Quantity]) Total_fixes,
 sum(T0.LineTotal) Invoicing_total, sum(T3.[U_IIS_TIME]) TaskTime_Total, T0.[u_iis_INS2], T0.[u_IIS_INS3]
 FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
 INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry
 left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
 WHERE T2.[U_IIS_JTYPE] = '6' and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
 and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]'
 
Set @eSubject='REPORT: Completions per engineer'
 
            SET @StrSQL = 'Select Body from TmpEmail'
            Create Table TmpEmail (Body varchar(254))
            Insert into TmpEmail Values('Hi All')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Please find attached the latest Invoice reports generated by the SQL Server.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Thanks.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('MSSQL Database Server')
            exec master..xp_sendmail @recipients='mail.co.uk',
            @query=@myQuery, @no_header='TRUE',
            @copy_recipients='mySecondEmail?whoknows.com',
            @subject=@eSubject,
            @dbuse='advanced'
 
      Drop Table TmpEmail

Open in new window

0
 
LVL 6

Expert Comment

by:Jerryuk007
ID: 22906149
Oops !
Replace set @StrSQL=... by set @myQuery=...

Jerry
0
 

Author Comment

by:KeirMcCann
ID: 22906191
i am still getting

 Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '6'.

Is it something to do with the date field?

Thanks
Keir
0
 
LVL 6

Expert Comment

by:Jerryuk007
ID: 22911633
every single quotes in the SQL Query Command needs to be "doubled"...
like: WHERE T2.[U_IIS_JTYPE] = ''6'' (2 single quotes on each side of the 6... Avoid double quotes of course)

Jerry
declare @myQuery varchar(8000), @eSubject varchar(254)
Set @myQuery='SELECT T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120) Date, sum(T0.[Quantity]) Total_fixes,
 sum(T0.LineTotal) Invoicing_total, sum(T3.[U_IIS_TIME]) TaskTime_Total, T0.[u_iis_INS2], T0.[u_IIS_INS3]
 FROM DLN1 T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
 INNER JOIN ODLN T2 ON T0.DocEntry = T2.DocEntry
 left outer join OSCN T3 on t3.ItemCode = t0.ItemCode and t3.Cardcode = SUBSTRING(t2.CardCode, 1, 8)
 WHERE T2.[U_IIS_JTYPE] = ''6'' and T0.[U_iis_ddate] >= (CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)) - 1)
 and T0.[U_iis_ddate] < CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))
 group by T1.[SlpName], T2.CardName, CONVERT(varchar(10), T0.U_iis_ddate, 120), T0.[u_iis_INS2], T0.[u_IIS_INS3]'
 
Set @eSubject='REPORT: Completions per engineer'
 
            SET @myQuery= 'Select Body from TmpEmail'
            Create Table TmpEmail (Body varchar(254))
            Insert into TmpEmail Values('Hi All')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Please find attached the latest Invoice reports generated by the SQL Server.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('Thanks.')
            Insert into TmpEmail Values('')
            Insert into TmpEmail Values('MSSQL Database Server')
            exec master..xp_sendmail @recipients='mail.co.uk',
            @query=@myQuery, @no_header='TRUE',
            @copy_recipients='mySecondEmail?whoknows.com',
            @subject=@eSubject,
            @dbuse='advanced'
 
      Drop Table TmpEmail

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question