November 19, 2002 at 12:03 pm
I have a stored procedure that selects data from various tables. I would like to export the results into and excel file. Is there a way to do this on the fly?
Thanks in advance.
November 19, 2002 at 1:09 pm
Insert the records into the temp table from the Sp and than BCP out the data from the temp table to the csv file.I think you can build this functionality into one stored procedure.
November 19, 2002 at 1:17 pm
You also could open excel and use the "Get External Data" function to import SQL Server data into excel, but of course this was not what you where asking.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 1:53 pm
I don't believe you can utilize bcp in a stored procedure. This is specifically to be utilized in batch files or at the command prompt. If I am completely wrong I would really appreciate a little schooling on how to accomplish this as BOL is not very forthcoming with this matter.
Can anyone please help? 🙁
Thanks in advance
November 19, 2002 at 2:10 pm
You might try using xp_cmdshell to execute a OSQL command. The OSQL command can execute your SP and redirect the output to a file.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 4:01 pm
I like Greg's solution(the last one). Make the file csv.
Least load on the server as I see it.
Edited by - Scorpion_66 on 11/19/2002 4:02:01 PM
November 20, 2002 at 6:46 am
I appreciate the help, but 1) I can't seem to get it to work, i'm still somewhat novice with some of the tools and 2) Am I doing the optimal thing here.
All I want to be able to do is have a SP run every day that pushes out usually more than 25 records out to an excel spreadheet.
Any words of advice.
Thanks
November 20, 2002 at 8:44 am
That SP should look something like this:
create procedure usp_test as
declare @cmd varchar(4000)
exec master.dbo.xp_cmdshell 'OSQL -E -h-1 -s "," -Q"set nocount on ; select * from sysobjects " -o c:/temp/usp_test.out'
Might need to do some other special formating of output in the select statement. But hopefully this will get you started.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 20, 2002 at 10:43 am
DTS is probably the neatest solution.
You can run them using DTSRUN from the command prompt or as a scheduled job
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 22, 2002 at 3:47 pm
I just got this to work on my server. I used the following code in an SP.
EXEC master..xp_cmdshell 'bcp "SELECT * FROM db.dbo.tablename" queryout C:\Directorypath\filename.csv -c -t , -U sa -P sa'
This works perfectly for me. It will overwrite the file everytime somebody executes the SP.
November 22, 2002 at 3:52 pm
I forgot to mention that this file comes out without column headings. I had to use an insert statement to put them in with the data. Ugly. But, functional.
November 22, 2002 at 10:17 pm
Try this example using the pubs database
insert into
OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\temp\wow.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
select * from authors
Note: The spreadsheet must exist, and must have the same number of columns used as your resultset.
September 19, 2006 at 2:27 pm
Is there a way to include the field names with the data?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply