Create File In Procedure without XP_CMDSHELL?

[osjohnm]

Hi all

Would have use the keyword search but it is still down.

Is there away to generate a txt file from within a stored procedure without using xp_cmdshell?

Currently we use xp_cmdshell dtsrun, this is being called within about 20 procedures. We are executing it manually on an adhoc basis but once it is automated the dts package call will fail due to (sysadmin)permissions on xp_cmdshell.

I found away around this by granting permissions on the sql user and setting the proxy account for SQLServerAgent. The problem is we aren't sure if our client's DBAs will go for it.

Therefore I need away to create a txt file or call a DTS package without xp_cmdshell. The txt file contains transactions for the General Ledger and hence it has to be accurate and formatted.

Any suggestions, ideas or web links would be appreciated.
Microsoft's site wasn't all that helpful and Google hasn't picked up much either.

John

 

[JamesLean]

If you already have the DTS packages setup it would make sense to utilise these. You can use the sp_OA set of stored procs to access COM objects, including DTS. This method is more flexible than using xp_cmdshell (although involves slightly more complicated code) plus you don't have the permissions issues.

This article should give you some good ideas:

http://www.databasejournal.com/features/mssql/article.php/1459181

Also, have a search in

http://www.sqldts.com

for more information.

--James