Error executing a stored procedure to do BULK INSERT

I have written a stored procedure to do bulk insert from a series of identically-formatted .csv files from an Agilent ICP-MS laboratory instrument. I am doing that because doing that in the Access application that needs the data, fails at times because MS Office 'helps' by interpreting a text data field as a date -- sometimes. The Bulk Insert works just fine on my development environment but fails at the customer site.
At my customer's site they get this error trying to run a stored procedure I wrote that does BULK INSERT.
-2147217900
[Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement.
upImportFromICPMSRaw '\\GSADC1\Company\InstrumentOutputFiles\ICPMS\New\185367.csv', tblFromICPMSRaw

The customer has SQL Server 2008 R2 Express installed
The stored procedure is called by a Microsoft Access 2010 application that has been working for years and accesses many tables, views and stored procedures.
The connection string to the database works on everything else and it is the standard sa account with password.
Connection string for the customer
Public Const strENCONNECT As String = "DRIVER={SQL Server};SERVER=GSADC1\SQLEXPRESS;UserID=sa;PWD=h20$tester;DATABASE=GSAData"
Connection string for my development environment
Public Const strENCONNECT As String = "DRIVER={SQL Server};SERVER=KRUNGTHEP;UID=sa;PWD=bkk6602;DATABASE=GSAData"

Here is the definition of the destination table, tblFromICPMSRaw
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblFromICPMSRaw](
[Sample Name] [nvarchar](255) NULL,
[Date And Time Acquired] [nvarchar](255) NULL,
[Element Full Name] [nvarchar](255) NULL,
[Concentration] [nvarchar](255) NULL,
[Units] [nvarchar](255) NULL
) ON [PRIMARY]
GO

Here is the stored procedure
CREATE PROCEDURE [dbo].[upImportFromICPMSRaw]
@FullFileName nvarchar(128)
, @DestinationTable nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
Exec('Delete from [GSAData].[dbo].[' + @DestinationTable + ']')
Exec('BULK INSERT [GSAData].[dbo].[' + @DestinationTable + ']
FROM  ''' + @FullFileName + '''
WITH ( Codepage = ''OEM'', Datafiletype = ''char'', Fieldterminator = '','', Firstrow = 2)')
END
GO

On my own development system with SQL Server 2008 R2 Standard, it works perfectly OK.
I have verified that the path to the database is shared with Full Control permissions and that the .csv file is not Read Only.
I have attached a .csv file on which the error did occur at the customer's site (but works in my environment.
Any suggestions gratefully received
185367.csv
Douglass MacLeanCTOAsked:
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.

Deepak ChauhanSQL Server DBACommented:
can you check the shared location once again, can you access it from the ssms.

exec XP_cmdhsell 'dir \\GSADC1\Company\InstrumentOutputFiles\ICPMS\New\'
0
Douglass MacLeanCTOAuthor Commented:
Good idea. I just checked it. See attached file, please
XP-cmdshell-Result.PNG
0
Deepak ChauhanSQL Server DBACommented:
Result is showing positive.
Normally [Microsoft ODBC SQL Server Driver][SQL Server] You do not have permission to use the bulk load statement. error occur when user does not have bulk admin permission on the server but in your case you are connecting with SA and SA has everything.


You can try this code to narrow down the issue. First use the shared location for @FullFileName if not success put the file '185367.csv'' to on local drive like D:\ or E:\ on the same server. use the same credentials what you are using in actual.

Declare @FullFileName nvarchar(128)
, @DestinationTable nvarchar(50)
set @FullFileName='\\GSADC1\Company\InstrumentOutputFiles\ICPMS\New\185367.csv'
set @DestinationTable ='tblFromICPMSRaw'
Exec('Delete from [GSAData].[dbo].[' + @DestinationTable + ']')
Exec('BULK INSERT [GSAData].[dbo].[' + @DestinationTable + ']
FROM  ''' + @FullFileName + '''
WITH ( Codepage = ''OEM'', Datafiletype = ''char'', Fieldterminator = '','', Firstrow = 2)')
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Douglass MacLeanCTOAuthor Commented:
Hi Deepak,

Good ideas. I'll try them out. May not get back to you right away, but I'll let you know as soon as I can.
0
Douglass MacLeanCTOAuthor Commented:
HI Deepak,

The folder structure is actually on the same server as SQL Server, which is convenient. I have tested using the real drive letter D:\ etc. and it works well in my development environment.

Right now, I'm having trouble getting to the customer's network and am working with their network support provider to solve it. As soon as I can run the test on the customer's network, I'll let you know and hopefully we will declare victory. :-)
0
Deepak ChauhanSQL Server DBACommented:
ok. Best of luck.
0
Douglass MacLeanCTOAuthor Commented:
Hi Deepak,

Still waiting for an opportunity to test the solution live on the customer's LAN.
0
Douglass MacLeanCTOAuthor Commented:
Hi Deepak,

I finally got a chance to test the solution of using the D:\Company\InstrumentOutputFiles\ICPMS\New\' path to the ICPMS to import. As with other solutions, it worked on my own development environment, but failed with a message that it could not find any files in the New folder, even though 185367.csv was there,

Attached is the result of running XP-cmdshell on the customer's server. The only think I find a bit perplexing is that it says it sees 2 DIRs. but the path has 4.

If you have any more ideas, please let me know. Otherwise I will close this with your solution accepted and continue to hunt for what may be going wrong -- or regroup and come up with a non-BULK INSERT solution.

Best Regards,
Doug
XP-cmdshell-Result0529.PNG
0
Deepak ChauhanSQL Server DBACommented:
Hi Doug,

Did you check permissions on the folder where file is placed. Add SQL server service account and comuter name, login name who is running the command in the security at least read permission or full control.
0
Douglass MacLeanCTOAuthor Commented:
Hi Deepak,

That makes sense. It is running under the SQL Server sa account, not a Windows Authentication account. So what SQL Server service account should I use?
0
Deepak ChauhanSQL Server DBACommented:
I am sorry but i dont understand "It is running under the SQL Server sa account, not a Windows Authentication account"

I mean to say
open services.msc and check sql server service log on account. You can find there service login account in the property of service then add this service account to the folder's security where csv file exists.

step1. check service account as explained above.
step2. Right click on Folder where csv file exists.
step3. go to property and add the service account with appropriate permissions.

In addition please check the security section for Bulk insert operation at below link.

https://msdn.microsoft.com/en-IN/library/ms188365.aspx
0
Deepak ChauhanSQL Server DBACommented:
Here is one more for security consideration bulk operation from remote share

https://msdn.microsoft.com/en-IN/library/ms175915.aspx
0

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
Deepak ChauhanSQL Server DBACommented:
And .... could you share the actual error number and msg.
0
Douglass MacLeanCTOAuthor Commented:
Hi Deepak,

Thanks for the long and valuable set of guidance you have me. We now have liftoff!!!
0
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 2008

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.