appending to text file

Hi all,

I am using SQL Server 2005 SP2. I have a need to add / append text (one line
each time) to an o/p text file in c: (say c:\test\logdate.txt) from within a
stored proc. This adding happens frequently like a log file.

Is there is any way for doing this? We do not have permission to use
xp_cmdshell.

--
Regards

WT

Princy (noname-at-discussion.mc.com) writes:
> I am using SQL Server 2005 SP2. I have a need to add / append text (one
> line each time) to an o/p text file in c: (say c:\test\logdate.txt) from
> within a stored proc. This adding happens frequently like a log file.
>
> Is there is any way for doing this? We do not have permission to use
> xp_cmdshell.

And nor should you. You should write a stored procedure in the CLR for the
task. The assembly would need the external_access permission and to create
such an assembly requires the database to be marked as trustworthy.

It can be questioned whether it's a good idea to write to a file from a
stored procedure if it's going to happen often. It does not sound like a
good recipe for scalability.


--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Princy
Not ideally, but see if that helps you
Note, you should be a member of sysadmin server role

create procedure file_write_text(@file_name varchar(1000),
@text varchar(8000),
@append bit = 0)
as
/*
exec file_write_text 'c:\foo.txt','hello'
exec file_write_text 'c:\foo.txt',' world',1
*/

declare @fso int
declare @ts int
declare @rv int

exec @rv = sp_oacreate "scripting.filesystemobject", @fso OUTPUT, 1
if @rv <> 0 goto eh
if @append = 1
begin
--open the text stream for append, will fail if the file doesn't exist
exec @rv = sp_oamethod @fso,"opentextfile", @ts OUTPUT, @file_name, 8
if @rv <> 0 goto eh
end
else
begin
--create a new text file, overwriing if necessary
exec @rv = sp_oamethod @fso,"createtextfile", @ts OUTPUT, @file_name, -1
if @rv <> 0 goto eh
end
exec @rv = sp_oamethod @ts,"write",null ,@text
if @rv <> 0 goto eh
exec @rv = sp_oamethod @ts,"close"
if @rv <> 0 goto eh

exec sp_oadestroy @ts
exec sp_oadestroy @fso

return 0

eh:
declare @es varchar(512)
declare @ed varchar(512)
exec sp_oageterrorinfo null, @es OUTPUT, @ed OUTPUT
raiserror(@ed,16,1)
exec sp_oadestroy @ts
exec sp_oadestroy @fso
return 1






"Princy" wrote in message
news:1794BCA2-FB7F-43A9-A90A-E887BE18ED98-at-microsoft.com...
> Hi all,
>
> I am using SQL Server 2005 SP2. I have a need to add / append text (one
> line
> each time) to an o/p text file in c: (say c:\test\logdate.txt) from within
> a
> stored proc. This adding happens frequently like a log file.
>
> Is there is any way for doing this? We do not have permission to use
> xp_cmdshell.
>
> --
> Regards
>
> WT

My first choice would be to create a scheduled SSIS package or job that
queries the data and inserts it into the text file rather than doing this
from a stored procedure or trigger.
Below is a T-SQL solution that uses openrowset and the OLEDB provider for
text files. In this example, the file sysobjects.csv should pre-exist in a
folder on the database server or a network folder that is accessible by the
SQL service account. The first row should contain the header, in this case
"A,B". Also, to use openrowset, you need to have the server option 'Ad Hoc
Distributed Queries' enabled.

insert into openrowset
(
'Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\TEMP\;HDR=Yes;',
'select A, B from sysobjects.csv'
)
select object_id, schema_id
from sys.objects


"Princy" wrote:

> Hi all,
>
> I am using SQL Server 2005 SP2. I have a need to add / append text (one line
> each time) to an o/p text file in c: (say c:\test\logdate.txt) from within a
> stored proc. This adding happens frequently like a log file.
>
> Is there is any way for doing this? We do not have permission to use
> xp_cmdshell.
>
> --
> Regards
>
> WT

I do not have sysadmin fixed role to execute this procedure. Any alternative

--
Regards

WT


"Uri Dimant" wrote:

> Princy
> Not ideally, but see if that helps you
> Note, you should be a member of sysadmin server role
>
> create procedure file_write_text(@file_name varchar(1000),
> @text varchar(8000),
> @append bit = 0)
> as
> /*
> exec file_write_text 'c:\foo.txt','hello'
> exec file_write_text 'c:\foo.txt',' world',1
> */
>
> declare @fso int
> declare @ts int
> declare @rv int
>
> exec @rv = sp_oacreate "scripting.filesystemobject", @fso OUTPUT, 1
> if @rv <> 0 goto eh
> if @append = 1
> begin
> --open the text stream for append, will fail if the file doesn't exist
> exec @rv = sp_oamethod @fso,"opentextfile", @ts OUTPUT, @file_name, 8
> if @rv <> 0 goto eh
> end
> else
> begin
> --create a new text file, overwriing if necessary
> exec @rv = sp_oamethod @fso,"createtextfile", @ts OUTPUT, @file_name, -1
> if @rv <> 0 goto eh
> end
> exec @rv = sp_oamethod @ts,"write",null ,@text
> if @rv <> 0 goto eh
> exec @rv = sp_oamethod @ts,"close"
> if @rv <> 0 goto eh
>
> exec sp_oadestroy @ts
> exec sp_oadestroy @fso
>
> return 0
>
> eh:
> declare @es varchar(512)
> declare @ed varchar(512)
> exec sp_oageterrorinfo null, @es OUTPUT, @ed OUTPUT
> raiserror(@ed,16,1)
> exec sp_oadestroy @ts
> exec sp_oadestroy @fso
> return 1
>
>
>
>
>
>
> "Princy" wrote in message
> news:1794BCA2-FB7F-43A9-A90A-E887BE18ED98-at-microsoft.com...
> > Hi all,
> >
> > I am using SQL Server 2005 SP2. I have a need to add / append text (one
> > line
> > each time) to an o/p text file in c: (say c:\test\logdate.txt) from within
> > a
> > stored proc. This adding happens frequently like a log file.
> >
> > Is there is any way for doing this? We do not have permission to use
> > xp_cmdshell.
> >
> > --
> > Regards
> >
> > WT
>
>
>

Princy (noname-at-discussion.mc.com) writes:
> I do not have sysadmin fixed role to execute this procedure. Any
> alternative

Did you consider to implement this in the CLR? Or use SSIS which I think
was suggested in another post?

--
Erland Sommarskog, SQL Server MVP, esquel-at-sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx