Bulk insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk insert

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-14 : 15:20:18

Hi all,
sorry, this may seem an abvious question!

I have sysadmin privileges, and connected to a server (SQLSERVER) through SQL server 2008. Trying to run a bulk insert:

BULK INSERT dbo.audit 
    FROM 'C:\Users\USERXX\Documents\FOLDER\filecsv.csv' 
    WITH 
    ( 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )
Go

ERROR:

Cannot bulk load because the file "C:\Users\USERXX\Documents\FOLDER\filecsv.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).

The file Path is local in my computer, this means that I'm reading a file from my computer.

How can this be done please?
Thanks,

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 15:32:07

is sql server installed in same computer? if not you need to replace absolute path with UNC path. Otherwise it will look for path in machine where sql server is installed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-14 : 16:09:48

SQL SERVER is installed on my Machine:


============  < Connected Thru SQLServer       >  ==========
|SQL server|  <================================> |MyMachine|
============                                      ==========

============                        ==========
|DataBase  |  ===================  |CSVFile  |
============                        ==========

I need to run a .sql from the cmd line of my machine
Part of this script is the bulk I showed earlier.
So, I'm assuming that I have to specify the \\server on cmd line when I execute it from my machine, however, I do still have the path of the bulk insert within the script that refers to my machine!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 16:54:48

sorry your diagram shows as if you're connecting to remote sql server. can you confirm the database engine is in your machine itself. By SQL Server i mean the engine rather than the SSMS client tool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-15 : 08:36:13

Sorry Visakh! I'm confused

.....WEll
1.SSMS is installed in my computer.
-I go to 'File>Connect Object Explorer' to Connect to the server
-Example of connect to server window:
Server Type: Database Engine
Server Name: RemoteServer
Authentication: Windows Authentication
2.The data (tables) I'm accessing is in an SQL Server Machine that's remote.

I want to run .sql file that is saved in my computer against some tables in that Server.
I ued

sqlcmd -ifile.sql

when I run the script against the (local) database engine.
How do you think this can be done.
Thanks a lot.

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 10:10:48

ok..then as i suggested the path should be UNC path as you're accesing SQLServer running in a remote machine and trying to BULK INSERT from it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-15 : 10:28:56

Cool...thanks...this moved me from one error to another :)

I'm logging into this SQL Server with a Windows Authentication.

MyMachine(cmdline executing.sql that's saved locally)====> RemoteSQLSRV

within that .sql script I have :
BULK INSERT dbo.table
FROM '\\MyMchineNAME\Foilder\CSVFile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Go
To me, this above statement will try to bulk insert from my local machine.

I get this error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\MyMchineNAME\Foilder\CSVFile.csv" could not be opened. Operating system error code 5(Access is denied.).

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 11:38:55

are you sure path is correct? looks like typo in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-15 : 11:56:56

Yeah, it is correct.
I think this error has something to do with Access of Windows Authenticated accounts.

Operating system error code 5(Access is denied.)

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 12:03:03

is it mapped to correct role in sql server? also can i assume your login has access to the path?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-15 : 12:29:53

Well, I don't want to assume and say yes.
according to some posts, this is a very common issue of the following case:
----------------------------------------------------
Client <=======> SQL Server <=========> File Server
----------------------------------------------------
In my case, the File server is the same as the client.
Now, to resolve this problem, you need to Either Log-in using an "SQL Authentication" or configure a Delegation to trust the Request of the server to bulk insert from a remote machine.
Would this remind you of alternative solution?

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-15 : 13:02:42

nope..in your case you dont need to do it as they both are same machine so only thing is login used should be a trusted one with access to required paths

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-15 : 14:01:22

then, how can I map the it? and how can I find out that lo-gin has access the path?
Thanks Visakh.

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources