Could not bulk insert because file...error 1

[GHolden]

Hi

I have some sql that bulk inserts from a text file to a table using a format file.

USE FENSAOLD

DELETE FROM tblImportTest

BULK INSERT tblImportTest
FROM 'F:\SQLDATA\MSSQL\DATA\TEST.TXT'
WITH
(
FORMATFILE = 'F:\SQLDATA\MSSQL\DATA\bcp.fmt'
)

This works fine while the text file and format file are on the sql server.

When I move the the text file to another server and change the sql to...

USE FENSAOLD

DELETE FROM tblImportTest

BULK INSERT tblImportTest
FROM '\\CHATS-FS1\ENERGY\FENSA\SQL\TEST.TXT'
WITH
(
FORMATFILE = '\\CHATS-FS1\ENERGY\FENSA\SQL\bcp.fmt'
)


I get the error message....

Could not bulk insert because file '\\CHATS-FS1\ENERGY\FENSA\SQL\bcp.fmt' could not be opened. Operating system error code 5(error not found).

I assume this is some kind of permissions problem.

The SQL Server is logged into an admin account in the same domain as CHATS-FS1 and has full control to all folders and files.

If anyone can shed any light on this?

Thanks.

There are two ways to write error-free programs; only the third one works.

 

[JamesLean]

You are right - this is a permissions problem.

On your comment about the SQL Server account, are you talking about the account that is logged into Windows which SQL is running on, or the account that the SQL Service is configured to use? The SQL Service must be using a domain account with the required permissions for this to work.

Also, make sure that the SQL Agent Service is using the same account if you are running this as part of a scheduled job. --James

 

[GHolden]

Thanks for the quick response, works like a dream.

There are two ways to write error-free programs; only the third one works.