Here is my problem: I don't have permissions to the 'master' db, thusly I don't have permissions to 'xp_cmdshell'. However, I need to output several small tables that I have created from one large table into individual text files.
Now, from the documentation I have read this is easily possible with the use of dts packets. However, my knowledge of dts packets is non-existent. So, I have created a dts packet that will take the data from a table and export it to a text file.
Here is what I need to know how to do: I would like all the small tables to go to their individual text files based on the file name I provide from within tsql. I would also like this to be done with the use of local tables (i.e. #tablename).
I am trying to alleviate human interface and the resulting errors from the sql statements we have now. We have an elaborate db system with tables and permissions all over the place, and thusly I want this done and removed with the use of local tables. Is this something that anyone could assist me with?
To execute a DTS package from a stored procedure or other t-sql code, you have to use the sp_OACreate and sp_OAMethod system procedures.
The problem, if you don't have permission to the master db, you won't be able to execute these either.
My recommendation to you would be to create SQL Server Agent job(s) to execute the DTS Package and allow the package to execute on a schedule. The SQL Agent execution account should have access to run the DTS Package.
The only other option is to be granted sufficient permission to the master db.
sql