October 14, 2004 at 4:41 am
I have a vb client program uploading a large (between 1k to 3Mb) file to SQL server via a parameter in a stored proc executed via ADO. It works a treat.
I want to write the data into a file on disk (I know I can put it in the db, but the nature of the data and the quantity of it makes this option unrealistic - I am really just using ADO as the pipe and do not want to store the blob, although I do need to update SQL tables with other info about the upload so I can find the file again).
I looked at textcopy, bcp and custom COM via sp_OACreate etc.
I do not want to move away from the ADO upload process as it works faultlessly and quickly and requires no end user overhead.
Textcopy and bcp require xp_cmdshell execute, and sp_OACreate requires SA permissions. I want to run this from a stored proc accessed by a non-DBA level user - I do not want DBA level connection info in the public domain and I have to tell the customers the database connection info so that they can upload the file.
Question is: How can I get the data out of the stored proc and into the file system without needing to grant the SQL user DBA rights or execute on xp_cmdshell?
Any answers ?
October 14, 2004 at 12:05 pm
The only thing you can do with a parameter of datatype text is to put it in a table.
I recommend putting the data to a table temporarily. Create a sql job that runs in the background and moves the data from the table to the file system. Faster for the user, and no worries about permissions. Just have that delay between upload and the file creation.
You can also use xp_cmdshell or the sp_OAxxx and FSO to create the file immediately. You still have to put the data to a table temporarily. As long as your stored procedure has the same owner as xp_cmdshell/sp_OAxxx, your users can execute your procedure without having direct access to xp_cmdshell/sp_OAxxx. Since the stored procedure owners are probably dbo, the databases must have the same owner. Where I work, all objects are dbo and all databases are owned by sa. Check out discussions on the chain of ownership in BOL and online.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply