Author |
Topic |
sardinka
Posting Yak Master
142 Posts |
Posted - 2006-11-13 : 13:41:25
|
is it posible to generate excel from stored procedure? |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-13 : 13:48:37
|
YesCODO ERGO SUM |
|
|
sardinka
Posting Yak Master
142 Posts |
Posted - 2006-11-13 : 14:14:09
|
Can you give me an example how to do this? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-13 : 14:23:26
|
Can I ask why you would want to do this? If you have SSIS or DTS, it would be better to call the stored procedure and use that to write to the spreadsheet. You could also use VBA to call the stored procedure from Excel and populate into the spreadsheet. These would be the preferrable methods. Use the SP for data layer. Use the applications for application layer. If you use DTS or SSIS, it's pretty much just drag and drop. If you need a cut and paste solution for DTS or SSIS, go to www.sqldts.com or www.sqlis.com and get a code example. You can also find them, including additional VBA code, by going to the MSDN website and doing a simple search.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
sardinka
Posting Yak Master
142 Posts |
Posted - 2006-11-13 : 14:43:46
|
I know how to create a DTS and VB. I just wanted to do something diferent I would like to try to do the same from TSQL. |
|
|
X002548
Not Just a Number
15586 Posts |
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-13 : 14:58:53
|
Okay. Are you using SQL Server 2000 or 2005? I can appreciate someone wanting to do something stupid just to prove it can be done and learn. lol MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-13 : 15:10:55
|
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926CODO ERGO SUM |
|
|
sardinka
Posting Yak Master
142 Posts |
Posted - 2006-11-13 : 15:26:05
|
I am using SQL2000.I was able to generate the file using this:Exec Master..xp_cmdshell 'bcp "Select * from db..tablename" queryout "\\Servername\testing.xls" -c'How do I generate the columns names as well? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2006-11-13 : 15:39:33
|
You do realize if you BCP out into a CSV file it will open right up in Excel. You can just change the extension to .xls but leave the delimiter as CSV.--Generate the error report filedeclare @file varchar(100)declare @cmd_text varchar(8000)declare @filename varchar(300)declare @path varchar(200)select @file=convert(varchar(20),getdate()-1,101)select @filename = replace(replace(@file,'.xls',''),'/','')+ 'ExportERRORS' + '.xls'select @path='"' + error_file_path + @filename + '"' from csm_filelocations where servername=@@servername and dbname=db_name()SET @cmd_text = 'bcp "exec dbname.dbo.p_csm_CreateExport_ErrorFile" queryout ' + @path + ' -T -c"'exec master.dbo.xp_cmdshell @cmd_text, no_outputIf you want column names you could always union a select statement on there and do an order by so your columns appear at the top. Not pretty but answers your question.Mike"oh, that monkey is going to pay" |
|
|
sardinka
Posting Yak Master
142 Posts |
Posted - 2006-11-13 : 16:02:39
|
I try to run your statement and I got the following error:Server: Msg 208, Level 16, State 1, Line 8Invalid object name 'csm_filelocations'.Did I misunderstood??? |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2006-11-13 : 16:12:59
|
You need to change this to work with your table structure. We have a table that lists the server & database names and the locations for various files. You can just hard code the path if you want to. And thanks you just gave me my 1000th post!Mike"oh, that monkey is going to pay" |
|
|
sardinka
Posting Yak Master
142 Posts |
Posted - 2006-11-13 : 16:30:47
|
I guess I am confused by this:exec dbname.dbo.p_csm_CreateExport_ErrorFile ???Where do I get the errors lines since I am running my bcp:Exec Master..xp_cmdshell 'bcp "Select * from db..tableName" queryout "\\serverName\Test\testing.xls" -C' |
|
|
vgr.raju
Yak Posting Veteran
95 Posts |
Posted - 2006-11-13 : 18:10:05
|
You can use the SQLCMD Utility. SQLCMD Output contains the column names.Thanks!Rajuhttp://www.trickylife-trickylife.blogspot.com/ |
|
|
|