Write to a text file in stored proc without xp_cmdshell
-
Hi,
I am wondering if there is a way to write to a text file in a stored procedure without using xp_cmdshell. We are running into security issues in a production web app that's calling a procedure with xp_cmdshell.
I'm trying to avoid rewriting an entire process (i.e. moving the file write to DTS or .NET code).
Any help would be great! Thanks.
-
You can always transfer the write part to a job.
Raise an alert in the proc.
The alert makes the job start.
Shouldn't have any security issue.
-
You can create a linked server connection to a text file using Jet. That's one way to do it without resorting to xp_cmdshell.
Jet OLE DB Provider for Linked Server
Look for the section titled "To set up a linked server against a formatted text file:" (near the bottom).
K. Brian Kelley
@kbriankelley -
And you can do inserts/updates/deletes just like a normal table???
-
It's been a long time since I tested (2003, I think), but I believe the answer is yes on the inserts. I don't think I tried updates and deletes. I was testing for a log file situation (trigger writes change to a log file).
K. Brian Kelley
@kbriankelley -
Sure would be nice... Gonna have to play with that tomorrow.
-
Saw an article once (and can't remember if it was on this site or not - gotta love senior moments
) about using the sp_oacreate and the filesystemobject.Went something like this:
DECLARE @fsoToken Int
DECLARE @error Int
DECLARE @tsToken Int
DECLARE @fToken Int
DECLARE @fileContents VarChar(2000)
DECLARE @src VarChar(500)
DECLARE @desc VarChar(500)
--first create the file manipulation object
EXEC @error = sp_oaCreate 'scripting.filesystemobject', @fsoToken OUT
-- a non 0 result in @error indicates failure
IF @error <>0
BEGIN
EXEC sp_oaGetErrorInfo @fsoToken, @src OUT, @desc OUT
PRINT 'Error creating fileSystemObject token'
SELECT Error=Convert(VarBinary(4),@error), Source = @src,Description = @desc RETURN
END
--next create the object to write the file
EXEC @error = sp_oaMethod @fsoToken, 'CreateTextFile', @tsToken OUT, 'fully qualified path to text file', 'TRUE'
-
I recreated the test and was able to write successfully. Basically I outputted the contents of the Orders table from Northwind to a text file. Was able to INSERT and SELECT just fine. Didn't get UPDATE and DELETE working due to a "Bookmark is invalid" error which I didn't have time to do any searching on. The key is to get the schema.ini file correct.
Basically you're setting up a directory to hold the files. The text file serves as the table. This works much like the way FoxPro did. BTW, I did need to create the file itself as a zero byte length file.
K. Brian Kelley
@kbriankelley -
I put together a small test using sp_oacreate and the filesystemobject and it worked well. I haven't incorporated it yet into the specific stored proc yet...hopefully this will go fine. If not, I'll try the other suggestions.
Thanks very much!
-
The only caution on sp_oacreate is it is an extended stored procedure and it carries the ramifications of such. Also, the sp_oa* stored procedures typically require sysadmin rights to execute. If this needs to be executed by an end user (not a DBA or administration with such rights), you may need to think about the implementation on this one.
K. Brian Kelley
@kbriankelley -
Brian is correct, and there are some issues with overall performance (and consistency) using the filesystemobject this way. But it is always an option when all else doesn't seem to solve the problem at hand.
-
Very good points. I will keep an eye on performance (provided rights isn't an issue). I'm hoping to do more extensive tests next week.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply