March 30, 2004 at 2:47 pm
Hi
Please pass on some examples if anyone has used the sp_oacreate stored procedure to create and write to a text file in sql server from T-sql .
Here is a sample code I am using
Declare @object int
Declare @status int
Declare @source varchar(255)
Declare @description varchar(255)
Declare @output varchar(255)
Declare @hr int
Declare @FS int
Declare @file varchar(100)
Declare @test-2 varchar(100)
Declare @val varchar(50)
Declare @textname varchar(100)
set @textname = 'CreateTextFile'
SET @file = "c:\esps\s.txt"
SET @test-2 = 'Hello'
set @val ='true'
EXEC @status = sp_OACreate 'Scripting.FileSystemObject', @object OUT
select @status
select @object
IF @status <> 0
BEGIN
Exec @hr = sp_OaGetErrorInfo @object, @source OUT,@description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
END
EXECUTE @status = sp_OAMethod @object, @textname,@fs out,@file,@val
select @status
IF @status <> 0
BEGIN
Exec @hr = sp_OaGetErrorInfo @object, @source out,@description out
SELECT convert(varbinary(4),@hr) as hr, @source as Source, @Description as Description
select 'Create text file'
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
END
thx
March 31, 2004 at 6:18 pm
See post by powilliams for a neat example:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=17145#bm82859
April 6, 2004 at 10:33 am
Thanks for the Reply .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply