Importing data from a network file via a SQL stored procedure

Question

question

evadarryl asked Oct 21, '13

Importing data from a network file via a SQL stored procedure

I have a stored procedure that imports data to a SQL table from a file stored on a network drive. The procedure runs great if I execute it from within SQL Server Management Studio. I need to have this procedure run in the middle of the night via a SQLScheduler job. This process is not working since the procedure can not locate the network drive because I'm not logged into server. Is there a way to create a network mapping within a stored procedure? If not, any suggestions on how I can make this work? I'm using SQLExpress. Thanks!

import

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Oct 25, 2013 at 10:47 AM

This site runs on votes. Please mark all helpful answers by clicking on the thumbs up. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.

0 ·

Answer 1 · 2013-10-21T16:46:31Z

KenJ answered Oct 21, '13

Instead of using a mapped network drive, say the F: drive, that is mapped to the UNC path `\\remoteserver\networkshare`, use the UNC path directly within your TSQL script `\\removeserver\networkshare\filename.ext` If I recall correctly, SQL Express does not have the SQL Server Agent for scheduling jobs, so you'll have to schedule your process via windows scheduler (maybe via sqlcmd), a 3rd party scheduling tool, or from a remote server that has SQL Server Agent. You'll also have to ensure that the account your scheduler uses to execute the stored procedure has permission to both the database and the network share/file

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2013-10-21T22:18:17Z

sqlaj 1 answered Oct 21, '13

What about using PowerShell? Create a domain account with only the permissions needed to complete the task. Then create a Windows Scheduled task with that user executing the scripts.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

evadarryl commented · Oct 21, 2013 at 10:51 PM

I'm not sure how to use PowerShell and I'm not sure if our security will allow me to have a domain acct created.

0 ·

KenJ commented · Oct 22, 2013 at 01:40 PM

They will only allow you to create a new domain account if they want the file imported automatically :)

0 ·

sqlaj 1 commented · Oct 22, 2013 at 04:27 PM

PowerShell can be complicated but not for something like what you want to do. As for the domain account and security. That is an internal process you would need to work. There should be a way to present valid reasoning for such service accounts. (In my opinion)

0 ·

question