asked on

Cannot bulk load because the file could not be opened

Hi,

I have two SQL2005 servers, one for delevopment the other live, when I try to bulk upload using the development server with the following code, it works fine:

DECLARE @sql VARCHAR(8000)
SET @sql =
'BULK
INSERT CONTACTTEMP
FROM ''\\hugo\wwwroot\damovo_demo\contacts\2100\contacts.csv''
WITH (FORMATFILE = ''C:\contacts.fmt'')'
EXEC (@sql)

However when I try to run the same code on the live server I get

Cannot bulk load because the file "\\hugo\wwwroot\damovo_demo\contacts\2100\contacts.csv" could not be opened. Operating system error code 1326(error not found)

I looked up this error and it suggests a login problem, but what I don't understand is that both servers access the remote machine and file in the same way, why does the live server have an issue?

Thanks for any help in advance

edit: sorry noticed I added to wrong section (VBscript)

JestersGrind

If you are logging into the SQL server with a SQL account, not a Windows account, bulk import will use the security context of the SQL Server process. What account is the SQL Server service running as?

Greg

udaya kumar laligondla

this may be due to the logins used by the different servers.
map the network path to a drive and test the same
for example map \\hugo\wwwroot\damovo_demo\contacts\ to Z:
Z:\2100\contacts.csv''
this will help to verify if there is any issue in accessing the file also.

trojan_uk

ASKER

Thanks guys,

The server is running as administrator, I tried the mapped drive but got the same error except the error code was 3.

I'm not a DBA so I don't have much knowledge of the security structure of SQL 2005, so I'm not sure if this makes a difference but I did notice that on the development machine (that works) that under Server Roles I have a Public server role, but I don't have that listed in the Live server list.

If I look under the permissions option of the server properties on the live server Public is listed there? But like I said not inn the server roles list Not sure if this is right or have an effect.

JestersGrind

Are you manually running the script above? If so, are you logging into SQL with a Windows authentication account or a SQL account? This sounds like a NTFS permissions issue of the file that you are trying to bulk insert, not a SQL permissions issue.

Greg

trojan_uk

ASKER

Thanks for the reply Greg,

in this case I am loging in using a SQL account and running the script in a query window.

I copied the exact same piece of code and ran it in the same manner on both live and development servers, the development ran fine but the live failed, yet both were calling the same file from the same remote server, so that is why I think it must be a SQL problem

ASKER CERTIFIED SOLUTION

JestersGrind

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

mastoo

I like Greg's answer, but also let me suggest you could accomplish the load from a command prompt using the bcp utility. This avoids having to loosen the server security.

trojan_uk

ASKER

Thanks again guys,

Greg spot on, on the development server the service was running domain/administrator on the live server it was ./administrator, changed it to domain/administrator and all is well.

Thanks for you time and patience