BULK INSERT ERROR

Question

Post reply

BULK INSERT ERROR

yoothasak1

SSChasing Mays

Points: 604
More actions
April 20, 2009 at 10:11 pm

#155586

Dear All,
Please help, I try to insert datas from the file "\\ServerName\Sharefolder\FileName.Dat" into a table :
Code :
DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET
(
BULK '\\ServerName\Sharefolder\FileName.Dat', SINGLE_BLOB
) X
INSERT INTO dbo.TestLog (Code,Time,Target,Machine,Elapsed)
SELECT
C.value('@code', 'varchar(50)') as [Code],
C.value('@time', 'datetime') as [Time],
C.value('@target', 'varchar(max)') as [Target],
C.value('@machine', 'varchar(50)') as [Machine],
C.value('@elapsed', 'varchar(50)') as [Elapsed]
FROM @xml.nodes('/Msg') X(C)
I alway get this error :
Msg 4861, Level 16, State 1, Line 4
Cannot bulk load because the file "\\ServerName\Sharefolder\FileName.Dat" could not be opened. Operating system error code 3(error not found).

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

This error is probably happening because the account that service being used to run SQL Server

does not have access to the share.

The query you are using runs from the (SQL Server) service (check Control Panel...Services to find which account it is using). If you are running SQL Server as a domain account, simply grant it access to the share. If you are using something like "NETWORK SERVICE" I think you will need to grant the \\yourdomain\NameOfSQLServer$ (the dollar is important) access to the share.

yoothasak1 SSChasing Mays Points: 604 More actions · Answer 2

Hi,

First, Very thanks for you kind.

When I change the "Log on As" of SQL service to be Windows Authentication User then the problem is solved.

I don't have domain system.

And if am using something like "NETWORK SERVICE". How can I grant the \\yourdomain\NameOfSQLServer$ to access to the share?

Thanks.

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

I am not able to give you a solution to this one but try the following:

Grant access to an account on the SQL Server computer...might need you to create a new account and change the SQL Server service to use that account. There probably is a way to use the "NETWORK SERVICE" account but I don't know it and I don't have a network to test it on.

An alternative that I did get working for one customer was to map a drive to the share using the Windows command NET USE. You can specify the credentials of a user on the other server (the one with the share) as part of the NET USE command. If you do this you also need to undo the NET USE command (NET USE /DELETE).

You should also be aware that the drive letter you use cannot be used for any other drive on the SQL Server host (even if the drive is only available to another user). This is Windows limitation.

Hope this helps - someone else may have more info to add. I certainly will be interested in any other solutions.

yoothasak1 SSChasing Mays Points: 604 More actions · Answer 4

Thank you very much for you answer my friend,

I followed your first suggestion : I have changed the "Log on as" user to be Windows Authentication user, so I don't use "Network Service" at current time.

Now It is working.

Thank you,

Yoothasak.