SQL Bulk Insert

Posted on 2009-07-12
Last Modified: 2012-06-21
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.
Question by:WebAl
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24836852
>> 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.

Author Comment

ID: 24836911
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
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24837363
>> 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.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 24837413
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.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24837550
>> 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.

Author Comment

ID: 24837591
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.

Author Comment

ID: 24845412
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.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24845960
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.

Author Comment

ID: 24846056
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.

Author Comment

ID: 24846121
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.
LVL 57

Accepted Solution

Raja Jegan R earned 500 total points
ID: 24847206
>> 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.

Author Closing Comment

ID: 31602685
perfect answer rrjegan17. Thanks. Al.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24849726
Welcome and glad to help you out.

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question