error while updating - 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

SQL Server 2000 Forums

SQL Server Development (2000)

error while updating

Author

Topic

yrkrish
Starting Member

6 Posts

Posted - 2007-11-09 : 15:04:20

Hi,
i have sql server 2005 and sql server management studio exp on my desktop.

BULK INSERT [dbo].[module]
FROM 'c:\data\MIL.csv'
WITH
(
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)

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

can anyone find the solution for this?

Thanks,
YRkrish.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 15:09:42

the file must be located on the server not on the client machine.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:11:10

Yes.
The path to the file is RELATIVE from your SQL Server's perspective, not your local machine.

E 12°55'05.25"
N 56°04'39.16"

yrkrish
Starting Member

6 Posts

Posted - 2007-11-09 : 15:11:47

Hi

do i need to give UNC name?

Thanks,
Yrkrish.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:12:52

quote:
Originally posted by spirit1

the file must be located on the server not on the client machine.

I don't think so. You can use UNC network shares, or even network drives.

E 12°55'05.25"
N 56°04'39.16"

yrkrish
Starting Member

6 Posts

Posted - 2007-11-09 : 15:16:36

Hi,

Is there anyother method to load the file?

Thanks,
Yrkrish.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:20:38

bulk insert is the fastest.

other than that; openrowset, opendatasource etc.

E 12°55'05.25"
N 56°04'39.16"

yrkrish
Starting Member

6 Posts

Posted - 2007-11-09 : 15:34:02

Hi,

If i use openrowset its givin the following error.

INSERT INTO dbo.module
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\data\mil.xls', 'SELECT * FROM [Sheet2$]')

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Thanks,
Yrkrish.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 15:34:31

you can also use dts or use a .net framework...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 15:35:28

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

yrkrish
Starting Member

6 Posts

Posted - 2007-11-09 : 15:41:55

Hi,

I think i need previlages on the server.

Thankyou very much guys for your valuable suggestions.

Thanks,
yrkrish.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 15:52:20

Or use the GUI "Surface Area Configuration".

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources