Write to a text file from a 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

General SQL Server Forums

New to SQL Server Programming

Write to a text file from a stored procedure

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-10-19 : 08:08:35

Hi,
I have a sp which saves the necessary information
regarding the status of action(whether success or failure, rows affected etc) to a log table
say(StatusLog)After this, I was sending a database mail
with information taken from the log table via sp_send_dbmail. Now I would like to
write the status information to a 'txt' file instead of sending via mail.
How can I write to a text file from a stored procedure in ms sql server 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 08:17:19

have a look at bcp

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-10-20 : 07:49:58

Thanks.

I tried something like below.

DECLARE @cmd varchar(256), @var varchar(50)
SET @var = 'Hello world!' +CHAR(13)+CHAR(10)+ 'This is second line'
print @var
SET @cmd = 'echo>d:\myfile.txt ' + @var
EXEC master..xp_cmdshell @cmd, no_output

But it is not saving the second line in the file.
How can I manage the line breaks/new lines?

quote:
Originally posted by visakh16

have a look at bcp

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-20 : 08:13:17

Refer following nice article
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

lock
Starting Member

1 Post

Posted - 2015-02-14 : 05:32:10

??????? 19???? ????

http://goo.gl/x2TTfF

??????? 19????

??? ??????? 19????, ??????? 19????, ??????? 19???? ???, ??????? 19???? ??, ??????? 19???? ??, fc2 ??????? 19????

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-17 : 07:31:48

quote:
Originally posted by lock

??????? 19???? ????

http://goo.gl/x2TTfF

??????? 19????

??? ??????? 19????, ??????? 19????, ??????? 19???? ???, ??????? 19???? ??, ??????? 19???? ??, fc2 ??????? 19????

Plz behave like professional...Admin plz block this type of forum members...

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-17 : 08:45:10

DECLARE @cmd varchar(256), @var varchar(50),@var1 varchar(50)
SET @var = 'Hello world!'
set @var1 = 'This is second line'
SET @cmd = '(echo '+@var+' && echo '+@var1+') >d:\SprintTask.txt'
EXEC master..xp_cmdshell @cmd, no_output

quote:
Originally posted by razeena

Hi,
I have a sp which saves the necessary information
regarding the status of action(whether success or failure, rows affected etc) to a log table
say(StatusLog)After this, I was sending a database mail
with information taken from the log table via sp_send_dbmail. Now I would like to
write the status information to a 'txt' file instead of sending via mail.
How can I write to a text file from a stored procedure in ms sql server 2005?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources