sp_send_dbmail - Error when passing @query parameter.
-
Hi All,
When I send mail using "sp_send_dbmail" it works fine.
But it fails when I set "@query" paramerter, fails with below error.
"Failed to initialize sqlcmd library with error number -2147024809.
Can someone please help on this ?.
Thanks in advance.
San.
-
The query executing successfully in Query window?
If yes... then Security permission issues are there ..
The problem with the SQL Agent is that it runs under an account which does not have the same privileges than the user using the SQL Server Management Studio (usually a Windows Administrator or a user with higher privileges).
You can change the User account of the SQL Server Using the SQL Server Configuration Manager and you can use proxies to run jobs with more privileges.
You can use this link to learn more abouth SQL Server Proxies
Below is link where i got info...
Thanks,
SP
-
Pulivarthi Sasidhar (11/27/2014)
The query executing successfully in Query window?If yes... then Security permission issues are there ..
The problem with the SQL Agent is that it runs under an account which does not have the same privileges than the user using the SQL Server Management Studio (usually a Windows Administrator or a user with higher privileges).
You can change the User account of the SQL Server Using the SQL Server Configuration Manager and you can use proxies to run jobs with more privileges.
You can use this link to learn more abouth SQL Server Proxies
Below is link where i got info...
Thanks,
SP
Thanks.
Query is fine and service account is my own id with administrator rights.
When I run the script manually also it doesn't work.
If I comment "@query=", then everything is working fine.
-
Anyone faced this issue ? Please advise.
-
What is the exact code you are running?
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
Have you checked that you have the correct Access to the objects you Need?
-
I ran into this once as well. In my case, I was executing msdb.dbo.sp_send_dbmail from one database and was querying a table in another. If this is what you are finding as well, either include the @execute_query_database parameter:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'email@domain.com',
@execute_query_database = 'MyDatabase',
@query = 'select Column1, Column2 from MyTable',
@subject= 'send_dbmail test';
Or fully qualify the table in your @query parameter:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'email@domain.com',
@query = 'select Column1, Column2 from MyDatabase.dbo.MyTable',
@subject= 'send_dbmail test';
-
LSAdvantage (12/1/2014)
I ran into this once as well. In my case, I was executing msdb.dbo.sp_send_dbmail from one database and was querying a table in another. If this is what you are finding as well, either include the @execute_query_database parameter:EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'email@domain.com',
@execute_query_database = 'MyDatabase',
@query = 'select Column1, Column2 from MyTable',
@subject= 'send_dbmail test';
Or fully qualify the table in your @query parameter:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile',
@recipients = 'email@domain.com',
@query = 'select Column1, Column2 from MyDatabase.dbo.MyTable',
@subject= 'send_dbmail test';
Thanks.
Even if I put "Select getdate()" also it fails. If I don't put any query and keep it empty string then its working fine too.
-
I had the same problem when trying to call a stored procedure and send the results in an email. The problem was that the correct database was not being referenced. I resolved it by qualifying the database name such as [database].[schema].[stored procedure]
-
We had similar issue and observed that it is caused by some specific combinations of parameters. More info:
http://sqldot.com/failed-to-initialize-sqlcmd-library-with-error-number-2147024809
-
check the account running your SQL Server Service. change from Local Service to Local System may solve the issue.
-
Apologies for reviving a pretty old thread, but there is a severe lack of solutions online for this and this forum is one of the few that references the exact issue I'm seeing and remains yet unanswered.
I'm seeing the same error in SQL 2016 Std. (13.0.4001.0):
Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.It seems to be directly related to the @query parameter. The query I'm passing does not need any context since it is merely selecting the number 1. I am running this as a sysadmin and both the SQL Service and SQL Agent service accounts are domain accounts. The server has access to the mail relay because I'm able to send mail when I don't use the @query parameter.
This works:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile'
,@recipients = 'myemail@mycompany.com'
,@subject = 'test'
,@Body = 'test message'This does not:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MyProfile'
,@recipients = 'myemail@mycompany.com'
,@subject = 'test'
,@Body = 'test message'
,@query= 'SELECT 1'I've been fighting this for months now and I can't find a single working answer online. I've tried about every single suggestion to no avail. It works on some servers and fails on others, so it's got to be some sort of server configuration or envirnomental variable or bug that exists in particular versions.
-
can you check to see if the .Net 3.5 library was installed? unless you hand edit a config file, you could install say, SQL2014 or 2016, and db mail does not work, because by default it uses .NET 3.5 , which may not be installed
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you for the quick suggestion. It is not installed actually... however .NET Framework 4.5 Features (2 of 7) are. I'll make that suggestion to the sysadmin and see if they are willing to install it to see if that is the remedy.
I also noticed a potentially related odd behavior. When I run EXEC xp_logininfo 'Domain\Myself'; I get the following error:
Could not obtain information about Windows NT group/user 'Domain\Myself', error code 0x5.
Could it be related to Active Directory?
-
Hi,
I got the same error, couldn't find an answer anywhere. Whilst looking at a different solution using a stored procedure I ran the query and got an error telling me the query was wrong.
I had two 'FROM' statements in my query and didn't spot the duplication.
Parsing the command in the Job Step did not pick up the mistake either.
I fixed the query and the error was gone.
Check your query works.
Hope this helps.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply