SQL Bulk Insert

I'm trying to run a bulk insert script and have SQL Server separate to Web Server. Running the script directly from the SQL Server works ok, where the script is located on a network share to the Web Server.
When I run the same script via a store procedure via a web page (connection uses a SQL sysadmin account) is doesn't work??

What I am trying to do is to get a User to upload a .csv file to web server. This works ok. Once the file is there then they click the Import button which fires the script. The Script Bulk Inserts the .csv file into a SQL table, again works well when executed from local SQL Server, but not from Web page.

I know the problem is permission related but I can't get my head around it. Any help is appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> When I run the same script via a store procedure via a web page

Include the WITH EXECUTE AS OWNER clause in your stored procedure to make this work.

Stored procedure would have been created by some user with admin privileges and you try to call them with some other user privileges.

Kindly recompile the procedure with the above clause and this should help you out.
WebAlAuthor Commented:
I now get the error:
Msg 4834, Level 16, State 4, Line 9
You do not have permission to use the bulk load statement.

For testing I have setup up a remote connection to SQL Server from SQL Management Studio on Web Server PC. I use sa login to create connection
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> You do not have permission to use the bulk load statement.

The user which you try to execute that procedure needs to have bulkadmin privileges to do bulk load operation.
Make sure this applies to both users running the stored procedure as well as the user who tries to connect from your web server.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

WebAlAuthor Commented:
Hi rrjegan17,
I appreciate, especially from the huge amount of posts made about this subject there is a lot of things to consider when it comes to permissions (Windows, SQL).
My SQL Login did have all of neccessary Bulk Load permissions. After spending the entire day on it I have finally managed to get it going, with the actually issue being permissions to the mapped network drive (Z:\) using a SQL login account. To get around this I've mapped and disconnected the drive in the script.

(I will award some points if there is a better solution than the method shown below)

--map drive to web server
EXEC master..xp_cmdshell 'net use t: \\<domain>\inetpub /user:<domain\user> <password>'

--import file to temp table
set @a=N'
BULK INSERT #tempImport
    FROM ''' + @file + '''
            FIRSTROW = 2,
        FIELDTERMINATOR = '','',
        ROWTERMINATOR = ''\n''
exec sp_executesql @a

-- disconnect mapped drive
EXEC master..xp_cmdshell 'net use /d t:'

Thank you very much for your replies. This is a good community.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> To get around this I've mapped and disconnected the drive in the script.

What's the need to map and disconnect it each and every time.
As you are going to repeat this several times, you can just map the network drive once and use it later on right.

Haven't thought about mapped drives as you have never mentioned it earlier in your question.
Since you are just mapping a shared network drive, map it once and use it later on from that path itself in the stored procedure.

Hope this helps.
WebAlAuthor Commented:
I had a mapped drive using Explore in the first place. It had exactly the same credentials in the SQL statement, although I still had the problems. Buggers me why it didn't work and now works when mapped drive is setup within SP. I'll do some more testing tonight now that I know the cause is to do with the mapped drive. You are right, there shouldn't be any need to map and disconnect each and every time. Thanks.
WebAlAuthor Commented:
If I use SQL to map the drive the script works. If I map the drive using Explore using the same credentials it doesn't work.

In this case I don't mind the script mapping the drive and disconnecting it because it is a low use script.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Even I would recommend to map the drive using SQL as it will tell any errors for credentials used.

I suggested you to map the drive once using your SQL scripts and later on use it forever and not to create it through Windows explorer.
WebAlAuthor Commented:
I did try that. Runnning script with mapped drive info in it. Then commenting out the mapped drive section and the script still worked. Just as you say. I did read somewhere yesterday that the SQL mapped drive is mapped only as long as the xp_cmdshell objects exists. Do you know what this exactly means? What happens if SQL Server is restarted, would I lose the mapped drive? If this is the case I would have to check to see if the SQL mapped drive exists, if not create it.

Coming back to my original question... which was me having issues with Bulk Insert using a separate SQL Server and Web Server. It seems that it does now work through the setup of a SQL mapped drive, which I had discovered. Your suggestion to further improve this is to SQL map the drive only once, which I agree, my script requires.
I would be happy to award full points for this question to you to if you are able to provide a separate script I can use that checks if a SQL map drive exists, if not create one. This will complete my question and provide an answer if I lose the SQL map drive should SQL Server is restarted.
I look forward to your reply.
Cheers. Al.
WebAlAuthor Commented:
Some TESTING: I had the SQL mapped drive connected. Script works good. Re-started SQL Server and the scripts doesn't work. Obviously, I need to re-create the SQL mapped drive for it to work. A script seems to be required that checks the SQL mapped drive for a connection, if not then create it, to avoid my problem occuring after a SQL Server restarts. Al.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> It seems that it does now work through the setup of a SQL mapped drive, which I had discovered.

Hope you would have already aware of that one and hence haven't mentioned that one to you.

The drawback of using net use command is that once the SQL Server service is restarted, the mapped drive will no longer be visible because it will be unmapped. If you want to persist the mapped drive information then you need to create a startup procedure for executing the script:

EXEC master..xp_cmdshell 'net use t: \\<domain>\inetpub /user:<domain\user> <password>'

More info on Startup procedures below:

If you create a startup procedure, which creates the mapped drive once SQL Server was started / restarted then I hope it solves your objective right. When you restart the server, then this mapped drive is definitely lost and you have to recreate it.

Kindly revert if I need to clarify more.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WebAlAuthor Commented:
perfect answer rrjegan17. Thanks. Al.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome and glad to help you out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.