Solved

SQL Bulk Insert

Posted on 2009-07-12
13
808 Views
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.
Thanks.
Al
0
Comment
Question by:WebAl
  • 7
  • 6
13 Comments
 
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.
0
 

Author Comment

by:WebAl
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
0
 
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.
0
 

Author Comment

by:WebAl
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 + '''
    WITH
    (
            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.
Al
0
 
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.
0
 

Author Comment

by:WebAl
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.
Al
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:WebAl
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.
0
 
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.
0
 

Author Comment

by:WebAl
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.
0
 

Author Comment

by:WebAl
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.
0
 
LVL 57

Accepted Solution

by:
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:
http://msdn.microsoft.com/en-us/library/ms191129(SQL.90).aspx

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.
0
 

Author Closing Comment

by:WebAl
ID: 31602685
perfect answer rrjegan17. Thanks. Al.
0
 
LVL 57

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now