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

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
  • 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.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
spx for moving values to new table 5 74
SQL Backup skipping a few tables 7 51
convert null in sql server 12 46
SSRS: Why is Visual Studio stripping these properties? 2 22
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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