query results, written directly to a TXT file

[ruupy]

Hello screenwatchers,

Can anybody tell how i go about, writen the result of a query, directly to a TXT file.
I know about the use of xp_cmdshell to write self composed lines of data to files but how could i write the result of een simple 'select * from tbl01' directly to a TXT file?


ThenX 4 your time
RuupY

 

[rajeevnandanmishra]

Hi there,
This problem is discussed very much in this forum. And terry had provided very good solutions.
Check Thread183-93959 for some ideas.

 

[Bernadette]

Hi There

The following is taken form

http://www.sywnk.com.

It should help you. There is no way of doing this directly from Query Analyser but there are a few ways you can get around it.


1. Use xp_cmdshell and the ECHO command. Use the > or >> redirection symbols to either create or append to a file.
xp_cmdshell "@ECHO test message >> C:\file.fil"

2. Put the information you want into a table (note this can't be an ordinary temporary table, but it can be a global temporary table) and then bcp it out to a file via xp_cmdshell.
xp_cmdshell &quot;bcp <dbname>..<tablename> out c:\file.fil -Usa -P<password> -c&quot;

3. Write your own extended stored procedure. As this is a C program it can use standard file access commands to achieve whatever you want.

4. Run the select through ISQL via xp_cmdshell and use the -o parameter to output the results to a file. This example uses the -E parameter to avoild hard-coding a userid.

declare @str varchar(255)
select @str = 'isql -Q&quot;select * from <tablename>&quot; -E -oc:\file.fil'
exec master..xp_cmdshell @str



Hope This Helps :-> Bernadette

 

[ruupy]

aha!

...the 'temp-table >> bcp option',...i checked it out and it worx!...not as direct as i had hoped but it gets the job done.

I need it to create some simple report-like files, generated using some tabledata.

ThenX a lot for the info!

RuupY out

 

[ADW]

Another way, perhaps a slightly longer though is to use the DTS (data transformation services) and select a text file as your destination. You can then open the file in Excel or Acces to produce your report

P.S use export and not import

 

[ruupy]

...DTS's....i try to avoid them when possible.
There rather instable and cumbersome. I prefer putting a SP in a job.

But anyway, i think the temp-table >> bcp optie is, i think, the shortest option, i'll stick with that 1.

ByTheWay,
About DTS's ... is it possible to use a process-task to unzip a ZIP-file using WinZip8.0(32Bit)????

The server from which i get my SQL data is so uptight that Gozilla and GetRight are cut off when trying to autodownload a file. We're now trying the same thing using an FTP server, unzipping files is a bit more differcult so i thought, maybe SQL can make me happy.....


RuupY out