Using bcp to import data error message

Question

Post reply

Using bcp to import data error message

mzzzshay

Old Hand

Points: 385
More actions
September 8, 2011 at 6:07 pm

#243519

I have to use bcp to import a text file from my computer. I am getting the above error and not sure why. Here is my code and any help is appreciated.
bcp tempdb.dbo.DimProducts in
"c:\Users\Shay\Documents\DimProducts.txt" -T -c

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

mrdenny SSCrazy Points: 2898 More actions · Answer 1

There's no error message listed. What's the error?

mzzzshay Old Hand Points: 385 More actions · Answer 2

I apologize here is the error message.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '.'.

- Win. SSCertifiable Points: 6790 More actions · Answer 3

Hope this helps :

http://msdn.microsoft.com/en-us/library/ms162802.aspx

Cheers,
- Win.

" Have a great day "

mrdenny SSCrazy Points: 2898 More actions · Answer 4

The syntax should be:

bcp "tempdb.dbo.DimProducts" in "c:\Users\Shay\Documents\DimProducts.txt" -T -c

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

The following article has additional information on the BCP Utility:

http://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

- Win. SSCertifiable Points: 6790 More actions · Answer 6

bcp [tempdb].[dbo].[DimProducts] in

"c:\Users\Shay\Documents\DimProducts.txt" -c -t

You don't use BCP from Query Analyzer. You run it from a command-line window.

you should use -c and not -C. The spacing should be OK, though.

I have not tested this, but still a hope..

Cheers,
- Win.

" Have a great day "

- Win. SSCertifiable Points: 6790 More actions · Answer 7

Denny,

if we use this from analyzer it wil throw error as its a command not sql syntax.

bcp "tempdb.dbo.DimProducts" in "c:\Users\Shay\Documents\DimProducts.txt" -T -c

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'in'.

Cheers,
- Win.

" Have a great day "

mrdenny SSCrazy Points: 2898 More actions · Answer 8

As stated this shouldn't be run from Query Analyzer. It should be run from a command prompt on the machine's console which has the file on it. If the file is on a network share you'll probably want to specify the network path instead of a local drive.

mzzzshay Old Hand Points: 385 More actions · Answer 9

I can not get this to work for nothing. I tried putting the code in the command prompt and it just keeps opening up the data in notepad.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

What is the syntax?

Could you include the DDL and the Data for the file so that I can test?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mrdenny SSCrazy Points: 2898 More actions · Answer 11

mzzzshay (9/12/2011)
I can not get this to work for nothing. I tried putting the code in the command prompt and it just keeps opening up the data in notepad.

Please post the exact syntax you are running in the command window.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

I gave you an example early on that was based on the Adventureworks2008 Database.

If I'm not familiar with something and I'm getting errors, I have tried running the example.

Often this points you in the right direction.

Did you take the time to read the article that I posted?

Just stating I can't get it to work does not provide any insight.

http://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mzzzshay Old Hand Points: 385 More actions · Answer 13

MrDenny, thanks for continuing to try to help me.

This is my exact code that I am copying to the dos command prompt

bcp DimProducts in Documents\DimProducts.txt -T -c

This is the error message I am now getting:

SQLState = $0002, NativeError = 208

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server] Invalid obeject name 'DimProducts'.

mrdenny SSCrazy Points: 2898 More actions · Answer 14

bcp DimProducts in Documents\DimProducts.txt -T -c
This is the error message I am now getting:
SQLState = $0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server] Invalid obeject name 'DimProducts'.

OK, this is because BCP doesn't know which database to find your table in. You've got two options (I'll show you both below). The first is to include the database own schema name (usually dbo) in the first parameter after BCP. Assuming that the database is called MyDatabase and the schema is dbo it would look like this.

bcp MyDatabase.dbo.DimProducts in Documents\DimProducts.txt -T -c

The second option is to use the -d parameter. Again assuming that the database is named MyDatabase and the owner is dbo.

bcp dbo.DimProducts in Documents\DimProducts.txt -T -c -d MyDatabase

Either option will work. Personally I prefer the first one.