February 10, 2015 at 9:39 pm
Dear Expert
I am using below code to get query result in to excel format and mail to my email id
it's work fine but having problem with the excel format
i have attached here excel file with what SQL giving me and what my expected output
i am not expert in coding
DECLARE
@tab char(1) = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sqlmail', -- replace with your SQL Database Mail Profile
@recipients = 'atul.jadhav@netafim-india.com;', -- replace with your email address
@query= N'USE CreditControl
SELECT Region_Master_Final$.SBU, Region_Master_Final$.SUB_SBU, Region_Master_Final$.State, SUM(CollectionReport2015$.INR) AS INR
FROM Region_Master_Final$ INNER JOIN
Customer_Master$ ON Region_Master_Final$.RG = Customer_Master$.RG LEFT OUTER JOIN
CollectionReport2015$ ON Customer_Master$.Customer = CollectionReport2015$.Customer
GROUP BY Region_Master_Final$.SBU, Region_Master_Final$.SUB_SBU, Region_Master_Final$.State, CollectionReport2015$.Month
HAVING (CollectionReport2015$.Month = 2)
ORDER BY Region_Master_Final$.SBU, Region_Master_Final$.SUB_SBU, Region_Master_Final$.State, INR' ,
@subject = 'Collection Report',
@attach_query_result_as_file = 1,
@query_result_separator=@tab,
@query_attachment_filename = 'Memory Values.xls',
@query_result_no_padding=1
February 11, 2015 at 2:00 am
You may have a tough time removing everything you want to remove, as sp_send_dbmail doesn't seem to have that level of control.
You can probably remove the first line about switching database context by simply using that db name to produce a fully qualified (2 periods) table name.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2015 at 2:12 am
The following will remove the last row with the row count and I illustrated what I meant by the 3 part naming (assumes your schema is dbo).
atul.jadhav (2/10/2015)
DECLARE
@tab char(1) = CHAR(9)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sqlmail', -- replace with your SQL Database Mail Profile
@recipients = 'atul.jadhav@netafim-india.com;', -- replace with your email address
@query= N'set nocount on;
SELECT Region_Master_Final$.SBU, Region_Master_Final$.SUB_SBU, Region_Master_Final$.State, SUM(CollectionReport2015$.INR) AS INR
FROM [CreditControl].dbo.Region_Master_Final$ INNER JOIN
[CreditControl].dbo.Customer_Master$ ON Region_Master_Final$.RG = Customer_Master$.RG LEFT OUTER JOIN
[CreditControl].dbo.CollectionReport2015$ ON Customer_Master$.Customer = CollectionReport2015$.Customer
GROUP BY Region_Master_Final$.SBU, Region_Master_Final$.SUB_SBU, Region_Master_Final$.State, CollectionReport2015$.Month
HAVING (CollectionReport2015$.Month = 2)
ORDER BY Region_Master_Final$.SBU, Region_Master_Final$.SUB_SBU, Region_Master_Final$.State, INR' ,
@subject = 'Collection Report',
@attach_query_result_as_file = 1,
@query_result_separator=@tab,
@query_attachment_filename = 'Memory Values.xls',
@query_result_no_padding=1
Don't think I can help with those hyphens under the column titles though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2015 at 2:27 am
As it turns out there is this workaround for the hyphens.
Looks like crap to me but it will probably work.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply