How to you use dbmail with BCP

Question

How to you use dbmail with BCP

nitinuniyal

SSC Eights!

Points: 997
More actions
April 2, 2012 at 7:36 am

#143292

Hi All,
I am tryig to run the below query but its failing while running. I am trying to send the query results in excel via dbmail.
[font="Courier New"]EXEC msdb.dbo.sp_send_dbmail
@recipients=N'nuniyal@shb.com.sa',
@body= 'Hi All
Please find the attached ATM status report as per today',
@query = Exec master..xp_cmdshell
'bcp "exec postcard..RPT_CardStats" queryout G:\Data\BSHB\SHB_Card_Stats_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%.xls -c -t, -T',
@subject = 'Test Email Subject',
@profile_name = 'SHBMailProfile'
,@attach_query_result_as_file = 1
,@query_attachment_filename ='Results.txt'[/font]
Error :- Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Exec'.
Could you please le me know how to use bcp with dbmail and let me know what would be the correct syntax for above query.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

nitinuniyal (4/2/2012)
Hi All,
I am tryig to run the below query but its failing while running. I am trying to send the query results in excel via dbmail.
[font="Courier New"]EXEC msdb.dbo.sp_send_dbmail
@recipients=N'nuniyal@shb.com.sa',
@body= 'Hi All
Please find the attached ATM status report as per today',
@query = Exec master..xp_cmdshell
'bcp "exec postcard..RPT_CardStats" queryout G:\Data\BSHB\SHB_Card_Stats_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%.xls -c -t, -T',
@subject = 'Test Email Subject',
@profile_name = 'SHBMailProfile'
,@attach_query_result_as_file = 1
,@query_attachment_filename ='Results.txt'[/font]
Error :- Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'Exec'.
Could you please le me know how to use bcp with dbmail and let me know what would be the correct syntax for above query.

The query execution functionality in Database Mail allows you to include the results of a query in the email content. Are you meaning to email the results of the call to xp_CmdShell?

Or did you mean to simply include the results of a call to postcard..RPT_CardStats?

EXEC msdb.dbo.sp_send_dbmail

@recipients = N'nuniyal@shb.com.sa',

@body = 'Hi All

Please find the attached ATM status report as per today',

@query = 'Exec postcard..RPT_CardStats',

@subject = 'Test Email Subject',

@profile_name = 'SHBMailProfile',

@attach_query_result_as_file = 1,

@query_attachment_filename = 'Results.txt'

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

nitinuniyal SSC Eights! Points: 997 More actions · Answer 2

[font="Tahoma"]Thanks for the Reply..

But what i want is to mail the results in Excel format, if I only define the SP in query then it will only give me the results in text and that too unformatted.

That’s why I was looking for a way by which I can send the excel sheet using DBMAIL.[/font]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

nitinuniyal (4/3/2012)
[font="Tahoma"]Thanks for the Reply..
But what i want is to mail the results in Excel format, if I only define the SP in query then it will only give me the results in text and that too unformatted.
That’s why I was looking for a way by which I can send the excel sheet using DBMAIL.[/font]

You cannot export data to an Excel file using bcp. If you truly want an Excel file, and you are not just faking it by exporting CSV-data to a text file with an xls extension which is not the same thing at all, then you will need to use a tool like SSIS or OPENDATASOURCE. I look to leave OPENDATASOURCE disabled on my instances for security reasons so prefer to do these types of things using SSIS.

Option 1, create an SSIS package that can:

1. export data to an Excel file

2. email the Excel file using Send Mail Task

Option 2, use a blend of SSIS and T-SQL:

1. create an SSIS package that can export data to an Excel file.

2. call msdb.dbo.sp_send_dbmail using the @file_attachments parameter to email your Excel file.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Stuart Davies SSCoach Points: 19505 More actions · Answer 4

It's a bit cumbersome but I also seen a view along the lines of:-

SELECT

Field1 + ',' +

Field2 + ',' +

Field3 + ',' +

...

Fieldn AS TheOutput

FROM table

Then bcp the view out (it ends up as a csv) and have it as one of your attachments.

This is an approached used in one of the systems I look after - but it is a swine to sort out any errors

-------------------------------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

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 5

SELECT

'"'+Field1+'"' + ',' +

'"'+Field2+'"' + ',' +

'"'+Field3+'"' + ',' +

...

Fieldn AS TheOutput

FROM table

Wrap them in double quotes in case anything has a comma in it.

Cheers

Stuart Davies SSCoach Points: 19505 More actions · Answer 6

jfogel (4/5/2012)
SELECT
'"'+Field1+'"' + ',' +
'"'+Field2+'"' + ',' +
'"'+Field3+'"' + ',' +
...
Fieldn AS TheOutput
FROM table
Wrap them in double quotes in case anything has a comma in it.

Good catch

-------------------------------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

jfogel-1131029 SSC Veteran Points: 235 More actions · Answer 7

jfogel-1131029

SSC Veteran

Points: 235

April 5, 2012 at 8:54 am

#1469591

I try!

nitinuniyal SSC Eights! Points: 997 More actions · Answer 8

Thanks Mate,

I think i will try SSIS package, of not then old good way of csv:-D

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

nitinuniyal (4/3/2012)
[font="Tahoma"]Thanks for the Reply..
But what i want is to mail the results in Excel format, if I only define the SP in query then it will only give me the results in text and that too unformatted.
That’s why I was looking for a way by which I can send the excel sheet using DBMAIL.[/font]

Can you attach a "picture" of your desired output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nitinuniyal SSC Eights! Points: 997 More actions · Answer 10

Thanks Jef for looking into my query, I have attached the xlsx file for your refrence.

nitinuniyal SSC Eights! Points: 997 More actions · Answer 11

Thanks Jeff for looking into my query, I have attached the xlsx file which i need as a attachment in my mail to users.

All i want to do is to use bcp in dbmail.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

First, a disclaimer. I've done a lot of things similar to the following but I've not actually included the output as an attached file to an email. I also normally use CDOSYS instead of sp_send_dbmail and I normally embed the output of the query in the body of the email instead of an attachment. In fact, I don't even have sp_send_dbmail setup on my Development/QA servers at work (long story as to why). The only place I have sp_send_dbmail setup is on my production servers and I'm just not going to test code there. So, longer story made shorter, although I've tested all of the code that makes the report, formats it as HTML, and have verified the HTML does, in fact, easily open as an Excel file, I've not tested the code to actually send the email.

Ok… back to business. To restate the problem and using your code as an example, you have a stored procedure called "postcard..RPT_CardStats" and you'd like to email the results of that stored procedure as an Excel Spreadsheet. I'll reword that part a bit and say that you'd like to email the result as something "pretty" that can be opened using Excel and has columns as you would expect an Excel spreadsheet to have.

Just so other folks can "play along", let's create a report using the AdventureWorks database (from SQL Server 2005) to produce an example report. Of course, we'll develop the report as a stored procedure. Here's that stored procedure.

CREATE PROCEDURE dbo.RPT_ExpectedRevenue

--===== Define the parameters of the SP

@pReportDate DATETIME = NULL

AS

--===== Declare some obviously named variables

DECLARE @ReportStart DATETIME, --Inclusive Date

@ReportEnd DATETIME --Exclusive Date

;

--===== Create and populate the date boundaries (2 days)

-- of the report based on the @pDueDate parameter,

-- If no date was provided, use today.

SELECT @pReportDate = ISNULL(@pReportDate,GETDATE()),

@ReportStart = DATEADD(dd,DATEDIFF(dd,0,@pReportDate),0),

@ReportEnd = DATEADD(dd,2,@ReportStart)

;

--===== Produce the report for items according to the

-- previously established date boundaries

SELECT wo.WorkOrderID,

p.ProductID,

p.Name,

wo.OrderQty,

wo.DueDate,

ExpectedRevenue = (p.ListPrice - p.StandardCost) * wo.OrderQty

FROM AdventureWorks.Production.WorkOrder AS wo

JOIN AdventureWorks.Production.Product AS p

ON wo.ProductID = p.ProductID

WHERE DueDate >= @ReportStart

AND DueDate < @ReportEnd

ORDER BY DueDate ASC,

ExpectedRevenue DESC

;

To execute the stored procedure and return some data, we'd issue the follow command in SQL (it's an older database so need to use an older date).

EXEC dbo.RPT_ExpectedRevenue '2004-05-01';

So far, nothing new.

The goal, however, is to return the output of the EXEC as something "pretty" and to attach it as a file in an email. Yes, we could use SSIS and all different manner of tools but, being the hardcore data-troll that I am, I'll use some hardcore T-SQL and a little XML magic, instead.

To pull this all off, we need to format the output of the original stored procedure as something that Excel will still recognize. We have the additional "complexity" of attaching it as a file that someone could open using a simple double-click in the email to open. I put the word "complexity" in quotes because it's not complex to do such a thing. We'll trick Excel simply by giving the filename a ".xls" extension even though it's not an actual Excel file.

The formatting will be done by some very simple "HTML". Since HTML and XML are very similar in structure, doing the formatting is comparatively easy, as well.

We'll also take the extra step of not having to modify the original stored procedure.

With all of that in mind, here's a stored procedure that takes the results of the original stored procedure and formats it as an "HTML" table that Excel can actually open and treat as a formatted spreadsheet.

CREATE PROCEDURE dbo.RPT_ExpectedRevenue_Mail

--===== Define the parameters of the SP.

-- These should be identical to the original stored

-- procedure that we'll call from within this one.

@pReportDate DATETIME = NULL

AS

--===== Create a place to store the results of the original

-- stored procedure.

CREATE TABLE #Results

(

WorkOrderID INT,

ProductID INT,

Name VARCHAR(50),

OrderQty INT,

DueDate DATETIME,

ExpectedRevenue DECIMAL(9,2)

)

;

--===== Populate the temp table with the results of the

-- original stored procedure.

INSERT INTO #Results

(WorkOrderID,ProductID,Name,OrderQty,DueDate,ExpectedRevenue)

EXEC dbo.RPT_ExpectedRevenue @pReportDate

;

--===== Create a variable to hold the HTML.

-- We could probably get along without this and

-- simply return the HTML using a SELECT, but I

-- want to ensure that the datatype of the output

-- is correct as well as not taking any chances on

-- truncation.

DECLARE @HTML NVARCHAR(MAX)

;

--===== Build the required HTML to return the result set

-- as a nicely formatted "table" that Excel is capable

-- of opening as is.

SET @HTML = '

<H1>Work Order Report</H1>

<table border="1">

<tr>

<th>Work Order ID</th>

<th>Product ID</th>

<th>Name</th>

<th>Order Qty</th>

<th>Due Date</th>

<th>Expected Revenue</th>

</tr>

'

+ CAST(

( --=== This creates the body of the report with

-- all the correct HTML flags to build a table

SELECT td = WorkOrderID,'',

td = ProductID,'',

td = Name,'',

td = OrderQty,'',

td = CONVERT(CHAR(10),DueDate,101),'',

td = ExpectedRevenue,''

FROM #Results

ORDER BY DueDate ASC,

ExpectedRevenue DESC

FOR XML PATH('tr'), TYPE

)

AS NVARCHAR(MAX))

+ '

</table>'

;

SELECT HTML = @HTML

;

I've tested the HTML output of that stored procedure by saving it as a file with a ".xls" extension and opened it simply by double-clicking on it from Window Explorer. It very nicely opens the file in Excel and the formatting is fine and should continue to do so as an attachment to an email (although we won't need to make a separate file by the time we get done here). Here's what the file looks like when opened in Excel.

This is the part I've not actually tested. You should be able to actually send such a file as an attachment without pre-saving a file using the following code.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SHBMailProfile',

@recipients = N'nuniyal@shb.com.sa',

@body = N'Hi All

Please find the attached ATM status report as per today',

@query = 'EXEC dbo.RPT_ExpectedRevenue_Mail ''2004-05-01'';',

@subject = 'Test Email Subject',

@attach_query_result_as_file = 1,

@query_attachment_filename = 'Results.xls'

;

This isn't a complete solution. For example, the date in @query is hardcoded. You could just change that to GETDATE(). You could also make all of this rather generic with OPENROWSET and some dynamic SQL but I figured we'd peel the first potato here instead of trying to do the whole bag at once. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nitinuniyal SSC Eights! Points: 997 More actions · Answer 13

Hi Jeff,

INDEED a solution and i think this will help to peel all patatos and serve them as fries 😀 .

Thanks again for the solution and now i am going to add HTML\XML bit to my SP and lets see how it goes ... Business ppl are tough nut to crack but i think this tip of yours will help me.

You are doing the Gr8 job by helping other Kudos !!!!.

Many Thanks

Nitin Uniyal

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

Thanks for the feedback, Nitin. Let me know how it all works out. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)