Need to send result set to a file
-
In SQL2000, how can I redirect the output of a query to a file with the data being ; delimited?
-
Something like...
Exec ('master.dbo.xp_cmdshell ''BCP ' + db_name(db_id()) + '..' + @TBName + ' OUT "' + @OutPutFile + '" -c -S"' + @@Servername + '" -a32768 -T -t"' + @Delimiter + '"''')
Also look at QUERYOUT keyword
Once you understand the BITs, all the pieces come together
-
Thankd you.
-
Use DTS. Set up an output file and use either a table or SQL statment to create the output. If you set it up dynamically you can change the source and/or the output filename so that the DTS can be reused.
Matt,
Peace Out!
Regards,
Matt -
Another option...
Look at isql and osql.
"Keep Your Stick On the Ice" ..Red Green -
DTS export to text and specify delimiter as ;.
-Isaiah -
I am having the same problem and will appreciate any ideas.
I notice that the BCP works only for a query or SP that conatins a single select statement. That is, it returns only one result set and will ignore the rest.
The DTS will do the same as it insists on a transformation being specified for the columns.
The osql is a good one excepts that you have to clean the file of headers or blank lines if you suppress headers. The real problem I have with osql is that it insists on giving out the columns as fixed width. This means columns are padded with spaces and distorts the output.
Thanks.
-
Hi Eletuw,
Copy data to temp table before do BCP.
-
My select statements have different number of columns. Copying the data to a temp table is likely to be a pain.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply