Outputting results to a text file

Outputting results to a text file

Post by Tibor Karasz » Fri, 18 Feb 2000 04:00:00



Larry,

Execute xp_cmdshell and run ISQL from there with your query and /o. There's
a proc called sp_dbm_query_to_file on www.dbmaint.com which does that.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


Quote:> All,

> Is there any way to send a result set to a text file directly from
> T-SQL? I know that you can from osql pretty easily, but what about from
> QA? or for that matter from a sproc/xsproc? Of course if I knew how to
> put results into a variable, then we could use xp_cmdshell, right?

> This kind of sounds simple, but I haven't figured it out yet.

> Any ideas?

> Thanks!

> -Larry

 
 
 

Outputting results to a text file

Post by Neil Pik » Fri, 18 Feb 2000 04:00:00


Larry - not directly.

Quote:> Is there any way to send a result set to a text file directly from
> T-SQL? I know that you can from osql pretty easily, but what about from
> QA? or for that matter from a sproc/xsproc? Of course if I knew how to
> put results into a variable, then we could use xp_cmdshell, right?

Q.     How can I read/write to a flat file from inside a SQL Server TSQL
script/stored-procedure/trigger?
(v1.4 1999.12.09)

A. SQL Server doesn't have a handy SPOOL command like Oracle does for writing
to files, but there are a number of ways of doing what you want.

1.  For reading, use xp_cmdshell and the ECHO command.  Use the > or >>
redirection symbols to either create or append to a file.

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 "bcp <dbname>..<tablename> out c:\file.fil -Usa -P<password> -c"

3.  BCP or BULK INSERT (SQL 7 only) can also be used to read in a flat file
into a table, from where it can be processed.

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

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




6.  There is a free XP - xp_query_to_file - at  www.dbmaint.com

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (412 entries) see

http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.sql-server.co.uk

 
 
 

1. How to output results onto a TEXT file?

Hi,

I have a script:  myscript.sql

that contains a simple command: select * from mytable;

How can I modify the script to output the results onto a text file?  

For example: Oracle uses the command: SPOOL C:\OUTPUT.LOG

Is there a similar way for SQLServer?

I have tried using the BCP utility as follows:

bcp mydb.user1.mytable out c:\output.log -i myscript.sql  -S myserver -T

But the result is:
Invalid prefix length. Valid prefix-lengths are 0, 1, 2, or 4.
Try again:

So I am looking at an alternative way to initiate the output from my sql
script rather than from the bcp command utility.

I would appreciate your help.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. US-NJ-Somerset System Administrator, HP/UNIX, Oracle DB, C, Shell Programming (1308-1742)

3. Outputting results to a text file

4. Formatting data in pick format for printing

5. output query results to a text file

6. Script to Review DBCC output

7. Output result into text file

8. MDAC

9. Output Query Result to Text File with BCP

10. Output query result to a text file?

11. Output Query Results to Text File

12. result of SQL statements, should be output it to a text file

13. Oracle8i stored procedures - output result to text file