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_de
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_dem
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)
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_de
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_dem
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)
VB ScriptMicrosoft SQL Server 2005
Last Comment
trojan_uk
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
Z:\2100\contacts.csv''
this will help to verify if there is any issue in accessing the file also.
map the network path to a drive and test the same
for example map \\hugo\wwwroot\damovo_demo
Z:\2100\contacts.csv''
this will help to verify if there is any issue in accessing the file also.
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.
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.
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
Greg
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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
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
Greg