February 18, 2009 at 7:08 am
We have an automated job scheduled at clients’s databases on Sql Server 2005. The job runs some validation scripts and sends results of the scripts via email. For one client the job is running perfectly but on another client, it is giving following error:
Msg 22050, Level 16, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter
Below is the script which runs as scheduled job:
declare @mail_list varchar(100)
set @mail_list = 'abc@xyz.com'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email_ProfileName',
@recipients = 'abc@xyz.org',
@copy_recipients = @mail_list,
@query = 'EXEC TPRO.dbo.Data_Check_Notification_Procedure_SP',
@subject = 'Subject',
@body = 'Body Text',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Validation_Results.txt' ;
I tried running the following script:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'EMAIL_ProfileName',
@recipients='abc@xyz.com',
--@query = 'select getdate()',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Subject'
If I comment @query parameter in the query above, it runs fine. But running the query with @query parameter gives the same error.
We have tried to run the job using both Windows authentication and SQL authentication
Is there any permissin related problem?
Any help on this topic will be highly appreciated
February 18, 2009 at 7:17 am
Are both the servers at the same patch/service pack level?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 7:21 am
Can you able to execute the individual procedure TPRO.dbo.Data_Check_Notification_Procedure_SP successfully?
--Ramesh
February 18, 2009 at 7:33 am
Yes both servers are using service pack 2
February 18, 2009 at 7:34 am
Yes we can independently run the stored procedure
February 18, 2009 at 7:42 am
rahul170 (2/18/2009)
Yes both servers are using service pack 2
What about CU's?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 7:48 am
What version your SQL Server 2005 is? Is it 32 or 64 bit? Database Mail is only supported on 32 bit versions of EE, DE, WE & SE editions.
Edit:
See this link for more information on supported features
http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx
--Ramesh
February 18, 2009 at 7:52 am
I dont know how to check CU but running @@version gives the following result:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
February 18, 2009 at 7:55 am
I ran the query - SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Below is the result:
9.00.3042.00SP2Standard Edition
February 18, 2009 at 7:59 am
Run this on the server, and see what parameters the procedure SP_SEND_DBMAIL is using:
USE MSDB
GO
SP_HELPTEXT SP_SEND_DBMAIL
GO
February 18, 2009 at 7:59 am
Ramesh (2/18/2009)
What version your SQL Server 2005 is? Is it 32 or 64 bit? Database Mail is only supported on 32 bit versions of EE, DE, WE & SE editions.Edit:
See this link for more information on supported features
http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx
Actually this was changed in SP1, see this blog post - http://wiseman-wiseguy.blogspot.com/2008/03/64-bit-database-mail-sql-server-agent.html
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 8:03 am
rahul170 (2/18/2009)
I ran the query - SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')Below is the result:
9.00.3042.00SP2Standard Edition
Is it the same on both servers? That is the base SP2 install.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 18, 2009 at 8:05 am
There is no problem related to parameter as the same code is running fine in our local server and at another client's server
February 18, 2009 at 8:15 am
Was it running fine before and started giving errors now?
if you have configured dbmail recently, have you recycled the services yet?
February 18, 2009 at 8:17 am
Jack Corbett (2/18/2009)
Actually this was changed in SP1, see this blog post - http://wiseman-wiseguy.blogspot.com/2008/03/64-bit-database-mail-sql-server-agent.html
...I was actually correcting my statement in a new reply but you beat me on it:D.
Now for the OP, can you cross check the parameters for the procedure sysmail_delete_profile_sp? It should have an additional parameter @force_delete which added in SP2 release. And if you don't find have such parameter, then there are chances that the SP2 has not correctly applied. Check Logs to see if there's something failed during install.
--Ramesh
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply