• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 974
  • Last Modified:

BULK INSERT SQL SERVER 2005

BULK INSERT SQL SERVER 2005:
I'm trying to do the following :

BULK INSERT [HTG_DB].[dbo].[STRATEGY_GROUP]
FROM 'C:\TEMP\tt.csv'
WITH (FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n');

and am getting the error that the file cannot be found... its there.

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "C:\Temp\tt.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).

I remember having this problem a while back but don't remember how to fix it.

0
bboo
Asked:
bboo
  • 5
  • 4
  • 3
  • +1
1 Solution
 
CCongdonCommented:
NTFS permission issue on c:\temp\tt.csv?
0
 
CCongdonCommented:
Also, is the file on the machine you are executing the SQL on or on the SQL server? I believe that command is expecting the file to be on the SQL machine, no matter where you are executing the SQL from.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the account with which the sql server service starts with , must have the permission to read that file
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
SQL_SERVER_DBACommented:
The source file that the Bulk Insert task loads can be on the same server as the SQL Server database into which data is inserted, or on a remote server. If the file is on a remote server, you must specify the file name using the Universal Naming Convention (UNC) name in the path.
0
 
CCongdonCommented:
Annnnd, if for some reason you can't get the file up to the SQL server, try a UNC style filename to a place where you can place the file that the SQL server can see over the network.
0
 
bbooAuthor Commented:
OK .. I moved the file to my Share0 and it still cannot find it :


BULK INSERT [HTG_DB].[dbo].[STRATEGY_GROUP]
FROM '\\Somehostname\SomeDir\tt.csv'
WITH (FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n');
0
 
SQL_SERVER_DBACommented:
The server must have permission to access both the file and the destination database.
0
 
SQL_SERVER_DBACommented:
check your domain rights
0
 
bbooAuthor Commented:
The databse is the same database I am executing it from so it should have permission to execute on that database. How to I find out if the database server has permission to access the file?

Also, I have no problem importing the file using Management-> Import data.
0
 
CCongdonCommented:
Probably because your management utility is running on the same system that you are storing the file, correct? That means 'you' are accessing the file, even though you are passing it onto the SQL server. The problem is that through the query, you are telling the SQL server service (as a user) to go fetch the file.

In the end, your easiest bet would be to put the file on a drive that is local to the SQL service.
0
 
bbooAuthor Commented:
My SQL Server is not local its at another location ... they don;t want us using that server. I remember doing this at another job , where I just put in the share drive as the directory path and it always worked just fine. It wasn't SQL Srever 2005 though ... would that make a difference ?
0
 
CCongdonCommented:
If the server is in a different location than your workstation, \\machinename in the UNC might not work. You might try \\YOUR.IP.ADD.RESS\sharename\filename in the query...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now