Best practice for text file creation from SP?

[jsteph]

Hi all,
I'm using sql-2005 on win 2k3 64-bit. I have a process that needs to write a csv file to a network share from a stored procedure.

I've used the sp_OAMethod to write simple text log files, so I have a few questions.

sp_OAMethod does fine when writing to a disk local to the server but does not seem to want to write to a unc share--is this something that can be overcome syntactically or via permissions?

Is sp_OAMethod the best way to write the file or are there newer possibly cleaner methods? (by 'cleaner' I just mean that sp_OAMethod seems klunky--but maybe it's the best way--that's why I'm asking).

Is there a 'pre-built' function to output a set of data in csv format? I have a test procudure working where I create the csv 'by hand', but as I was writing it I kept thinking there must be something already existing for this.

Thanks very much for any help and advice,
--Jim

 

[gmmastros]

sp_OAMethod does fine when writing to a disk local to the server but does not seem to want to write to a unc share--is this something that can be overcome syntactically or via permissions?

This is probably a permissions issue.

When SQL server interacts with the "computer" it uses the account used to start the SQL Server service. This account can be anything you want it to be. By default, it is set to "Local System" which has full access to the local computer (the server), but no access to any other computer on your network.

Do this on the server:

Click Start -> Run
Type services.msc
click OK
Scroll down to the "SQL SERVER" service.
Right click -> Properties
click the "Log On" tab.

Notice the log on information. If it's set to "Local system account", you will not be able to access any folders on any other computer.

If you decide to change the log on account, please be very careful. In cases like this, I usually make an active directory account specifically for SQL Server. Make sure you give this account a strong password, and make sure the password never expires (or you'll be scratching your head the next time you reboot the server after the password expiration date).


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jsteph]

George,
Thanks, that makes sense. I'll probably keep the account the same and just have the share on the server and give the process that requires this file permissions to that share.

Do you know anything about the viability or alternatives to sp_OAMethod? If that's the 'best practice' way then I'll continue to use it, but I was just thinking there might be other methods that might, for example, take a table-variable and a disk-path as inputs and output a csv file to that path. Again--I'm ok doing that manually but I don't want to re-invent the wheel here.
--Jim

 

[gmmastros]

I don't really know because I don't have a use for this in my application.

There are a couple ideas here:

http://www.nigelrivett.net/SQLTsql/WriteTextFile.html

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom