Sending Excel from SQL

Hi,

BAsed an online search, here is my code to get the result from a query to an Excel. This Excel will be attached to an email. I am sure the email part works well. Here is the code and the error.

https://stackoverflow.com/questions/39976000/how-do-you-email-a-query-result-as-a-csv-using-sp-send-dbmail-stored-procedure-w

I know my code has some missing parts, but I am trying to get the email out first then worry about the format. thanks
declare @qry varchar(8000)
declare @column1name varchar(90)

-- Create the column name with the instrucation in a variable
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'CMID]'
 
-- Create the query, concatenating the column name as an alias
select @qry='set nocount on;select ID ,name from table1'
 
-- Send the e-mail with the query results in attach
exec msdb.dbo.sp_send_dbmail 
@recipients="myemail@abc.com",

@body_format='HTML',
@profile_name = 'PDN',
@query=@qry,
@subject = 'Daily Report',
@body = 'Hello, how are you?',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'result.csv',
@query_result_separator=',',@query_result_width =32767,
@query_result_no_padding=1

Open in new window

Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

thanks
mcrmgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
You have to include your database name in the @query parameter of sp_send_dbmail because it runs on msdb by default.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
OK, let's take a step back...

try changing the select @qry line:
select @qry='select ''A'' as COLA, ''Description'' as COLB '

Open in new window


then run the query.

Have you been able to send any messages via dbmail (even a simple 'hello world') ?

Then we can add in 'use [yourdb]; set nocount on; ' as the first part of @qry and try again with the same select in @qry
ste5anSenior DeveloperCommented:
As a 'Daily Report' it should run as SQL Agent job. Thus create a stored procedure with your batch. When it works, then create either a SQL Agent job directly or use a maintainance plan to schedule it.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

mcrmgAuthor Commented:
@Mark Wills,

Yes, I was able to receive an email. Please see attached file. thanks
result.csv
Mark WillsTopic AdvisorCommented:
Very good.

We can now try the subsequent test, using the query itself. Now, if there is anything wrong with the query, db_mail is likely to have a dummy spit and complain.

So, always test your query first.

As Máté Farkas said in his post above, you need to be in the right place to execute the query. We can do that in one or two ways. Either set your DB location, or, specify it in the select.

Either:
select @qry='set nocount on;select ID ,name from mydb..table1'

Open in new window

Or :
select @qry='use [mydb]; set nocount on;select ID ,name from table1'

Open in new window

where "mydb" is the database which has table1. And it assumes a default schema of "dbo"
ie select ID ,name from mydb.dbo.table1
mcrmgAuthor Commented:
Thank you very much..working now..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.