Export Results from an SP to an Excel File

  • 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.

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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.

  • 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.

  • 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