August 18, 2009 at 4:38 am
Hi guys and gals,
I have an import job on my database server I'm having a spot of bother with.
At the moment, an XML file is uploaded to a website on an hourly basis. It's about 20MB in size.
I have a VBS script scheduled which takes the file path and calls a stored procedure that imports it like this:
INSERT INTO dbo.tbl_myXMLData(xml_feed)
SELECT xmlData FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @filePath + @fileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA);
This script works ok if I log into the server and manually run it but fails if scheduled.
What I want to know is, can I cut out the "middle man" and just tell SQL Server to go get the file and import it.
The file name is always the same so I was thinking could this be done with a linked server? I've only ever used this with Excel spreadsheets.
One thing I don't want to do is enable the xp_cmdshell type procedures. I don't like the security risks they potentially open us up to.
Thanks in advance for any help.
August 18, 2009 at 9:51 am
I would still try the import with the openrowset function. Make sure that the account that is used by the SQL Server agent has permissions to read data on the directory that the file exists. Also make sure that if you are using mapped drive, that the same mapping exists for the user that run the SQL Agent. You can try to logon as that user and run the procedure manualy and see the error that is caused.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply