| Author |
Topic |
|
SQLSoaker
Posting Yak Master
169 Posts |
 Posted - 2009-01-05 : 15:45:12
|
| Hello,When I use: @query_attachment_filename = test.xlsUsing dbmail stored procedure,The results of the query are populated into an excel sheet. The problem is that the formatting is horrible! Is there any solution as to how to properly format the data when it is populating into excel?Thank you in advance |
|
|
revdnrdy
Posting Yak Master
220 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-05 : 17:09:41
|
| Great, thanks guys, I am working on this and will let you know how it goes! Appreciate it. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-06 : 14:56:37
|
| So I have a stored procedure that I need to bcp into excel (i guess using a .csv file).Can I write it as follows:?:'bcp "exec sp_mySP @dataareaid = ''xxx''" queryout "C:\test.csv" -c -t -S"' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-06 : 15:21:23
|
| sqlsoakerHere is one of my export routines using BCP.. tested and verified on sql2005 server instanceDECLARE @cmd VARCHAR(2048)SET @cmd = 'bcp '+ ' "SELECT Manufacturer,ManufacturerPartNumber,CAST(List as decimal(10,2)),'+ ' CAST(Cost as decimal(10,2)),CAST(Price as decimal(10,2)),CONVERT(varchar(12),CustomDate01,101) '+ ' FROM MyDataBaseName.dbo.Products Order by Manufacturer,ManufacturerPartNumber" queryout'+ ' D:\Excel_Exports\myfile.csv -c -t\, -r \n -T'EXEC master..xp_cmdshell @cmd |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-06 : 15:40:43
|
| Thank you,Can someone please help with the syntax, I'm getting rather confused over what arguments are needed, I have the "man" page for the bcp utility but still confused:'bcp "exec sp_mySP @dataareaid = ''xxx''" queryout 'C:\Excel\myfile.csv -c -t\, -r \n -T'Tara, you mentioned that I need to use -T but how? Like this? Same with -S what is the syntax to give it the server name???You all rock.SQLSoaker |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-06 : 16:02:44
|
| Used this:DECLARE @cmd VARCHAR(2048)SET @cmd = 'bcp '+ ' "exec sp_mySP @dataareaid = ''xxx''" queryout'+ ' C:\myfile.csv -c -t\, -r \n -T'EXEC master..xp_cmdshell @cmdGetting an error saying cmdshell is not turned on, so following these steps I tried to turn it on...http://www.tech-recipes.com/rx/2027/sql_server_2005_how_to_enable_xp_cmdshell/But got an additional error:An error has occured while establishing a connection to the server. When connecting to the SQL Server 2005, this failure may be cause by the fact thatt under the default settings SQL server does not allow remote connections.--UGH |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-06 : 16:06:28
|
| Is there another way to turn on cmdshell??? |
|
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-06 : 16:31:24
|
| EXEC sp_configure 'xp_cmdshell', 1GO |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
Posted - 2009-01-08 : 15:41:38
|
| Here is what I am trying to accomplish:Have a stored procedure for a report that needs to be automated to go to certain users:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Email_theSP] AS BEGIN SET NOCOUNT ON -- do some other actions DECLARE @body1 VARCHAR(1024) SET @body1 = 'Hi' This message was sent on:'+ CONVERT(VARCHAR, GETDATE()) EXEC msdb.dbo.sp_send_dbmail @recipients='me@me.com', @subject = 'SQL Test Mail - Testing Automation , @body= @body1, @execute_query_database = 'myDB', @query = 'sp_mySP @dataareaid = ''xxx''', @attach_query_result_as_file = 1, @query_result_width = 1500, @query_attachment_filename = 'testing.xls' ENDThe stored procedure sp_mySP:select .... from ... where .... join ....Can I use bcp with the @query parameter in dbmail???Something like this:@query = 'bcp sp_mySP @dataareaid = ''xxx'' queryout' C:\myfile.csv -c -t\, -r \n -T -S[myServerName]'I am struggling with the syntax so it is erroring out at the moment. Right now I am simply trying to call bcp in a script'bcp sp_mySP @dataareaid = ''xxx'' queryout' C:\myfile.csv -c -t\, -r \n -T -S[myServerName]'But keep getting these errors:Password: SQLState = 08001, NativeError = 53Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. SQLState = HYT00, NativeError = 0Error = [Microsoft][SQL Native Client]Login timeout expiredSQLState = 08001, NativeError = 53Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connectiNULLI am doing all of this so the data's formatting is clean in the report that needs to go to the users...Plz help with syntax!Regards. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLSoaker
Posting Yak Master
169 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
sp_send_dbmail