Creating a Text File in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating a Text File in Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-19 : 09:05:36

Krishnan writes "Hi,

We are using Back office 4.5 SQL Server 7 and NT 4 SP 6.

We would like to write to a text file from Stored procedure.

Would be obliged if we get a work around.

Thanks

Krishnan
"

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-08-19 : 09:27:37

U can use the EXECUTE xp_cmdshell 'echo >> log.txt'
I AM NOT SURE OF THE SYNTAX..CHECK Books Online.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-19 : 10:45:57

-- Writing to a text file from a stored procedure

The are several methods of creating text files from sql server.
osql
bcp
redirection commands
sp_MakeWebTask
Com object calls

Remember that in all cases the path of the output file will be relative to the server - not to the client on which the application is running.

osql.
This example will just output master..sysobjects to the file c:\osqloutput.txt.

declare @cmd varchar(1000)
select @cmd = 'osql -U -P -S -Q"select * from master..sysobjects" -o"c:\osqloutput.txt" -w500'
exec master..xp_cmdshell @cmd

bcp
bcp very fast and easy to use for just dumping a table out - can be used against a view too.

master..xp_cmdshell 'bcp master..sysobjects out c:\file.bcp -S -U -P -c '

redirection commands
You will need to create the data to be output as in dynamic sql statements
The first command here creates or overwrites the file - the rest append to the file.
exec master..xp_cmdshell 'echo hello > c:\file.txt'
exec master..xp_cmdshell 'echo appended data >> c:\file.txt'
exec master..xp_cmdshell 'echo more data >> c:\file.txt'

will generate a file containing
hello
appended data
more data

sp_MakeWebTask
This is for creating html code from a query

Com object calls
Using sp_oa... system stored procedures and creating com objects or existing applications you can probably do whatevr you wish - but you should probably ask whether sql server is the right place to control this.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

majnoon
Starting Member

26 Posts

Posted - 2003-07-15 : 06:30:32

To nr:

can you use redirectional commands or bcp where the output folder is on a mapped drive.

I have tried this but without success

and by the way:

Sometimes Cursors are the only option
Same with DTS, and in the UK
Beer is always cold and definately fizzy

Wishing you a peaceful journey

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-15 : 08:20:43

quote:
Sometimes Cursors are the only option

Wrong.

quote:
Same with DTS, and in the UK

Wrong.

quote:
Beer is always cold and definately fizzy

Utterly, absolutely, marvelously RIGHT!!!

quote:
Wishing you a peaceful journey

Wishing you a good cold one.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-15 : 11:46:34

>> can you use redirectional commands or bcp where the output folder is on a mapped drive.
Think so - but it will run as the sql server service user probably so that user has to have the drive mapped and permission on it.
Easier to use unc notation.
Start off with giving everyone full control totest then narrow down the permissions.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

keg
Starting Member

2 Posts

Posted - 2003-07-23 : 17:33:06

Hi,

I have a Problem outputting to a text file with Stored Procedure and BCP

I have tried using BCP to output to a file - I find that it works
if I am using a simple select query

e.g. exec master...cmdshell 'bcp "select * from sometable" queryout -c c:\somefile.txt -Uuser -Ppwd'

The output file is created.

However when I try using an sp as in:
exec master...cmdshell 'bcp "exec dbo.sp_test" queryout c:\somefile.txt --c Uuser -Ppwd'

I get a NULL output and no file created.

Can anyone help on this please?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-23 : 17:38:08

quote:

Hi,

I have a Problem outputting to a text file with Stored Procedure and BCP

I have tried using BCP to output to a file - I find that it works
if I am using a simple select query

e.g. exec master...cmdshell 'bcp "select * from sometable" queryout -c c:\somefile.txt -Uuser -Ppwd'

The output file is created.

However when I try using an sp as in:
exec master...cmdshell 'bcp "exec dbo.sp_test" queryout c:\somefile.txt --c Uuser -Ppwd'

I get a NULL output and no file created.

Can anyone help on this please?

Have you tried the -o option for bcp?

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-07-23 : 19:58:56

Your second bcp command line was a little goofed, it should read:

exec master...cmdshell 'bcp "exec dbo.sp_test" queryout c:\somefile.txt -c -Uuser -Ppwd'

Also, you should add SET NOCOUNT ON as the first line of your stored procedure:

CREATE PROCEDURE dbo.sp_test AS
SET NOCOUNT ON
--rest of code follows

This is especially true if you are performing anything other than a straight SELECT from a table or view. It prevents the "n row(s) affected" messages that appear in Query Analyzer and such. These messages can interfere with external data access API functions returning the proper results (ADO is especially prone to this)

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-05 : 08:40:01

exec master...cmdshell 'bcp "select * from sometable" queryout -c c:\somefile.txt -Uuser -Ppwd'

can I execute this command from DOS command prompt ......

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-05 : 08:43:08

quote:
Originally posted by qutesanju

exec master...cmdshell 'bcp "select * from sometable" queryout -c c:\somefile.txt -Uuser -Ppwd'

can I execute this command from DOS command prompt ......

BCP is a command line utility provided by sql server.
So you can execute from command prompt.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources