create text file from query analyzer
-
Hi
I want to create a text file through a sproc. Here are some scenarios and pros and cons.
Can you suggest me alternatives within each scenarios? Thanks.
1. I use bcp and export the rows into a text file. It does not put column names as header, how do I achieve that?
2. I can use OSQL utility to query out directly to text file. It puts dashes under each column and number of rows affected. How should I get rid of it?
3. Last option : Use sp_OACreate and create scripting.FileSystemObject. Works but involves a lot of programming for error reporting.
Any other methods I can use? I want to use bcp, but I dont know how to get the column as header. Any ideas?
Thanks for your time and patience.
-
How much data are you exporting? If it's not a huge number of rows you could quite easily use DTS to output to a text destination. BCP is faster on text output, but if the row count is small, it doesn't make that much difference.
From personal experience I'd choose the sp_OA* option as a last resort. They have proven to be a bit flaky under load.
--------------------
Colt 45 - the original point and click interface
-
This is a bummer, isn't it--that a product this mature has such poor text file output options (still the most common form of data transport).
Running a DTS package from a stored procedure is painful.
I have had success with adding the column headers as a row to a temporary table then using bcp to output the table or doing a union (between the table and a header row) in the bcp query. Of course, you have to add some kind of non-output column to sort on first if the query is being sorted; otherwise the header row might not come out on top. Also, the output table column data types have to accommodate the width of each column heading.
Maybe someone will think this is so laughable that they offer a real solution. Otherwise, I know it works.
-
this is from a discussion post i had saved in the past: it used bcp to send to a dynamically named file based on the @id field; hope this helps.
DECLARE @id int,
@bcpJob varchar(2000),
@FileName varchar(2000)
DECLARE the_cursor CURSOR
FOR
SELECT id FROM table WHERE data_long IS NOT NULL
OPEN the_cursor
FETCH NEXT FROM the_cursor
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = REPLACE('C:\' + CONVERT(varchar(20),@id,1) + '.txt','/','-')
SET @bcpJob = 'bcp "select data_long from database..table where id = "' + CONVERT(varchar(20),@id,1) + ' queryout "' + @FileName + '" -c'
EXEC master..xp_cmdshell @bcpJob
FETCH NEXT FROM the_cursor
INTO @id
END
CLOSE the_cursor
DEALLOCATE the_cursor
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Funny, there's no pain involved when I run a DTS package from a stored procedure. Then again, I've always thought my system was magical. It does seem to easily do things other people have trouble doing.
--------------------
Colt 45 - the original point and click interface -
Magic aside, when I tried executing a (very simple) DTS package with xp_cmdshell dtsrun... it locks up the session. Nor did I have any success with someone's stored proc using the sp_OA* system proc. Anyway it still seems like an awful lot of work just to get a text file with column headings.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply