bcp easy question - 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

Import/Export (DTS) and Replication (2000)

bcp easy question

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2004-12-01 : 13:24:09

Hi, I am reading a book in this moment and I need to do some examples that it have.
Right now I want to execute the next line:

bcp northwind.dbo.shippers in c:\SQLServer\Downloads\book\4486\ch21\newship.txt -c -T

I am doing this in the query analyzer, but it shows me the next error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

Is here where I have to run the bcp instruction?, is something wrong with the user ( I'm using sa)?
Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 14:00:55

As a trusted connection (-T) it's using your id, and that id needs the correct permissions to do a bcp, and read from the location of the c drive.

And, where are you doing this?

If it's from a command line, then it's your c drive

If you're trying to do this from QA, you need to be using xp_cmdshell, and the file would then need to be on the server, or a servers mapped drive.

Here's an xp_cmdshell example


DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.Orders out c:\Orders.dat -SNJROS1D151\NJROS1D151DEV -Usa -P -c'
EXEC master..xp_cmdshell @cmd
GO

Brett

8-)

shifis
Posting Yak Master

157 Posts

Posted - 2004-12-01 : 14:06:40

Thanks a lot!!

shifis
Posting Yak Master

157 Posts

Posted - 2004-12-01 : 15:18:04

Another question:
I uses your code:
DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.Orders out c:\Orders.dat -SS-WFHTJLUJ0CWW8 -Usa -P -c'
EXEC master..xp_cmdshell @cmd
GO
Then I created a new table Ordenes ( using the Orders script)

CREATE TABLE [Ordenes] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL DEFAULT (0),
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

) ON [PRIMARY]
GO
Then I used this code to copy the information to Ordenes:
DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.Ordenes in c:\Orders.dat -SS-WFHTJLUJ0CWW8 -Usa -P -c'
EXEC master..xp_cmdshell @cmd
GO

and I got the next error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

Any Idea about that I am doing wrong?

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 16:48:18

I would say the file is not there

Do

master..xp_cmdshell 'DIR C:\*.dat'

What does it say?

Brett

8-)

shifis
Posting Yak Master

157 Posts

Posted - 2004-12-01 : 17:09:10

Mmm you so right, what happen is that I connect to another server to run the instruction, I put the file in a network drive abd it runs.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources