Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 generate excel from stored procedure

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
Yes




CODO ERGO SUM
Go to Top of Page

sardinka
Posting Yak Master

142 Posts

Posted - 2006-11-13 : 14:14:09
Can you give me an example how to do this?
Go to Top of Page

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.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-11-13 : 14:53:27
You need to look up COM automation

Personally, I'd rather drive an ice pick into my temple



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

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



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

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

CODO ERGO SUM
Go to Top of Page

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?
Go to Top of Page

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 file
declare @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_output


If 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"
Go to Top of Page

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 8
Invalid object name 'csm_filelocations'.
Did I misunderstood???
Go to Top of Page

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"
Go to Top of Page

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'

Go to Top of Page

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!
Raju
http://www.trickylife-trickylife.blogspot.com/
Go to Top of Page
   

- Advertisement -