CODEWITHSUNDEEP
sql-server
M_M
M_M

Reputation: 501

Failed to initialize sqlcmd library with error number -2147024809

I created a new email profile with the super admin and I configured the smtp.

Then when I execute this query:

   exec msdb.dbo.sp_send_dbmail
   @profile_name ='admin_mail',
   @recipients = '[email protected]',
   @execute_query_database = 'DashboardPowerBi',
   @query = 'select top 20 from Client_1',
   @subject= 'Liste des clients',
   @body_format ='HTML',
   @attach_query_result_as_file = 1;

I get this error:

Failed to initialize sqlcmd library with error number -2147024809

Upvotes: 2

Views: 17390

Answers (6)

smoore4
smoore4

Reputation: 4866

I found that you could not have brackets around your FQDN, so change this

SET @sql = 'SELECT field1, field2, etc FROM [DBNAME].[dbo].[Table1]'

to this instead.

SET @sql = 'SELECT field1, field2, etc FROM DBNAME.dbo.Table1'

Upvotes: 0

Arun J
Arun J

Reputation: 11

The Problem is with the directly passing query in @qry.

Try to insert the record to be attached in Global TempTable (##Temp) and try to use that select script with specified column names. Like Below

DECLARE @qry varchar(8000) = 'SET NOCOUNT ON
SELECT 
Column1,
Column2
From ##TEMPEMAIL
    SET NOCOUNT OFF'

Upvotes: 1

Xmex
Xmex

Reputation: 91

Recently i had an issue related with this, the solution here was to fully qualified the DATABASE + SCHEMA_NAME + TABLE_NAME ('select * from sales..sales_month'), even if you're in the same database.

Regards!

Upvotes: 9

Jeremy Hodge
Jeremy Hodge

Reputation: 662

In my case, After I had tested the query and encapsulated in a string, I had forgotten to update...

'' AS Blank

to

'''' AS Blank

Upvotes: 1

sgordon
sgordon

Reputation: 53

I also had this error message. It ended up being I didn't have the database tables fully qualified in the query. database.schema.tablename I only had the tablename.

While searching, others had this error because the file attachment was too large. You can change that in SSMS - Management - Database Mail

Upvotes: 2

Robert Sievers
Robert Sievers

Reputation: 1355

Well, the query is incorrect. You need to specify which columns you are selecting

@query = 'select top 20 * from Client_1',

But rather than using '*', actually list the columns you want.

Upvotes: 3

Related Questions