March 30, 2004 at 1:56 pm
Hi
I would like to know if anyone has used the sp_oacreate stored procedure for writing to a text file from T-sql in sql server.
March 31, 2004 at 10:50 pm
What do you mean "for writing to a text file". Generally sp_oaXXX methods operate on COM objects, which can do practically anything. I suppose you have a COM object "for writing to a text file", but still have no idea what your question is?
Specify it in more details.
April 6, 2004 at 10:14 am
I am using the Filesystem object to write to the text file .Here is a sample code .
Declare @object int
Declare @status int
Declare @source varchar(255)
Declare @description varchar(255)
Declare @output varchar(255)
Declare @hr int
Declare @objFile int
--EXEC @status = sp_OACreate 'Scripting.FileSystemObject', @oSQL OUT
EXEC @status = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @status <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @status
RETURN
END
Exec @status = sp_OAMethod @object, 'CreateTextFile',@objFile,'C:\esps\test1.txt','true'
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
RETURN
END
April 8, 2004 at 12:09 am
First of all next time at least describe errors or problems you encountered. For lack of them I had to to execute your code and ...
th following line
Exec @status = sp_OAMethod @object, 'CreateTextFile',@objFile,'C:\esps\test1.txt','true'
has to be corrected like this:
Exec @status = sp_OAMethod @object, 'CreateTextFile',@objFile OUT,'C:\esps\test1.txt',1
Remember:
a) CreateTextFile method returns an interface which has to be passed back to @objFile (that is why OUT specifier)
b) esps directory must exists on drive c: on your sql server's computer where test1.txt file will be created (as far as I know sp_OAxxx in SQL server does not support remote out-of-process servers yet)
So finally everything works fine.
January 19, 2006 at 9:43 am
Have an issue in similar context
sp_OA will create the txt file on the SQL machine,but will fail to create on a shared network location.
Any comments appreciatted.
Thank you.
January 19, 2006 at 10:10 am
Does the sql server account have permissions to write on the share?
February 3, 2006 at 5:01 am
I am being told that it has ,but I would like to check it mysefl;
What test would be concludent ?
Thank you for your information.
February 3, 2006 at 10:34 am
logging on to the network with the account sql server/sql server agent runs under and try to put a file on the share
February 3, 2006 at 11:30 am
That sorted out the issue.
Thanks for the help.
February 3, 2006 at 12:10 pm
Glad you got it sorted out
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply