Post reply

Exporting to a text file from a stored procedure

  • September 18, 2003 at 5:28 am

    #82257

    I want to output the results of a query to a text file. I know I can do this using DTS, but is there a way to do it from a stored procedure?

  • September 18, 2003 at 7:07 am

    #474859

    Depending on your definition of 'query' you could use bcp

    exec master..xp_cmdshell 'bcp "select ... from datbase..table" 

    queryout outputfilename 

    -c -Sserver -Uuser -Ppassword'

    or

    exec master..xp_cmdshell 'bcp "exec datebase..procname" 

    queryout outputfilename 

    -c -Sserver -Uuser -Ppassword'

    to use the output of another procedure

    Edited by - davidburrows on 09/18/2003 07:07:28 AM

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 18, 2003 at 10:27 am

    #474908

    Thanks David, that was just the job!

    However, after I get the sp working, I tried to be clever (always dangerous) and wanted to put my rather long query into a variable to make the sp easier to read/maintain as follows:

    DECLARE @cmd varchar(1000)

    , @sql varchar(1000)

    SET @sql = 'SELECT Field1

    , Field2

    FROM Database.dbo.Table'

    SET @cmd = 'bcp "' + @sql + '" queryout test.csv -c -t, -T'

    EXEC master..xp_cmdshell @cmd, no_output

    I wasted ages trying to figure out why this was failing. It turned out to be my splitting the sql statement into more than 1 line! The following code worked:

    SET @sql = 'SELECT Field1, Field2 FROM Database.dbo.Table'

    Am I missing some wider issue with concatenating strings? Or is it to do with the xp_cmdshell sp?

    Thanks,

    Barry

  • September 18, 2003 at 10:36 am

    #474909

    Barry,

    It's to do with concatenation. When you put text between quotes on separate lines sql will include CR/LF in the text. Sometimes it is useful sometimes not.

    SET @sql = 'SELECT Field1' +

    ', Field2 ' +

    'FROM Database.dbo.Table'

    and

    SET @sql = 'SELECT Field1, Field2 FROM Database.dbo.Table'

    will give you the same text in the variable.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • September 19, 2003 at 1:58 am

    #474989

    Thank you David for both your expertise and the speed of your responses.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply