| Author |
Topic |
|
SQLSoaker
Posting Yak Master
169 Posts |
 Posted - 2008-12-12 : 12:55:20
|
| Hello,I have this sp:CREATE PROCEDURE dbo.testEmailattachSP AS BEGIN SET NOCOUNT ON -- do some other actions DECLARE @body1 VARCHAR(1024) SET @body1 = 'SQL send mail test on this day:'+ CONVERT(VARCHAR, GETDATE()) EXEC msdb.dbo.sp_send_dbmail @recipients='myemail@myemail.com', @body= @body1, @execute_query_database = 'myDATABASE', @query = exec myDATABASE.myStoredProc, @subject = 'SQL Test Mail' END How do I attach a stored procedure from another DB and set this to the query?I also have to would like to use @attach_query_result_as_file to attach my sp to as a file.Right now @query = exec myDATABASE.myStoredProc is not working because it wants ''.How to attach an sp to @query?Thank you in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-12 : 13:53:43
|
| Thank you tkizer for your reply. This worked. Now, how do I pass the parameters of the SP within @query?I have this:CREATE PROCEDURE dbo.testEmailattachSP2 AS BEGIN SET NOCOUNT ON -- do some other actions DECLARE @body1 VARCHAR(1024) SET @body1 = 'SQL send mail test on this day:'+ CONVERT(VARCHAR, GETDATE()) EXEC msdb.dbo.sp_send_dbmail @recipients='myemail@myemail.com', @body= @body1, @execute_query_database = 'dbmine', @query = 'exec dbmine.mavCostLayer', @attach_query_result_as_file = 1, @subject = 'SQL Test Mail' END Without the parameters I get this error when I run this cmd:exec testEmailattachSP2Error:A severe error occurred on the current command. The results, if any, should be discarded.Thanks again.Regards. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-12 : 14:10:30
|
| Thank you tkizer once again.The procedure creates without errors:CREATE PROCEDURE dbo.testEmailattachSP4 AS BEGIN SET NOCOUNT ON -- do some other actions DECLARE @body1 VARCHAR(1024) SET @body1 = 'SQL send mail test on this day:'+ CONVERT(VARCHAR, GETDATE()) EXEC msdb.dbo.sp_send_dbmail @recipients='me@me.com', @body= @body1, @execute_query_database = 'myDB', @query = 'exec dbo.myDB.mavCustomerStatement_AgingBuckets @dataareaid = xxx, @accountnum = 100001', @attach_query_result_as_file = 1, @subject = 'SQL Test Mail' END But when I run the exec:exec testEmailattachSP4I am still getting this error:A severe error occurred on the current command. The results, if any, should be discarded.Any ideas as to why this won't execute???Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-12-12 : 14:17:23
|
| Because you didn't follow my example. Notice the single quotes with @var2 which represents a varchar/char parameter. This obviously doesn't work if you tried it in Management Studio, so what is missing?exec dbo.myDB.mavCustomerStatement_AgingBuckets @dataareaid = xxx, @accountnum = 100001Now you've got to add more single quotes to it as you are passing it to a parameter. Notice my example.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-12 : 14:33:23
|
Forgive me tkizer, I am fairly new to SQL.So tried this:@query = 'exec dbo.myDB.mavCustomerStatement_AgingBuckets @dataareaid = "xxx", @accountnum = "100001"'Did not work.So I tried this:@query = 'exec dbo.myDB.mavCustomerStatement_AgingBuckets @dataareaid = 'xxx', @accountnum = '100001''got this: Incorrect syntax near 'osg'.I need to send them both as strings, yes. What am I missing here!!!  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-12 : 15:05:45
|
quote:
Originally posted by tkizer
'exec dbo.myDB.mavCustomerStatement_AgingBuckets @dataareaid = ''xxx'', @accountnum = ''100001'''Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Thanks Tara, that worked (didn't realize they were single quotes).But when I execute the sp, I still get this error:A severe error occurred on the current command. The results, if any, should be discarded.It must have something else to do with the sp. Ugh.  |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2008-12-12 : 15:34:38
|
| I am trying to mess with the DB sources, I think this is where the problem lies. Thanks again for all the help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
sp_send_dbmail: attach sp in email