SQL Server BCP utility to copy a table in and out of SQL Server to a CVS file

[fniles]

I would like to copy a table(s) from SQL Server 2005 to a CVS file and vice
versa.
I was thinking to use the BCP command line utility, but I have a few
questions:

1. The machine where I am going to run BCP utility does not have SQL Server
installed, so BCP.exe and its underlying DLLs are not there.
I copied BCP.EXE, then it tells me that it expects sqlncli.dll. After I
copied that DLL, it now says "Unable to load BCP resource DLL. BCP cannot
continue."
How can I run BCP on a machine where SQL Server is not installed ? What
files do I need to copy ?

2. When I run BCP on my machine accessing a SQL Server on the server (say
myServer), I can copy a table to a cvs file, but when I try to copy from CVS
file back to SQL Server (after truncating the table on the SQL Server), it
gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'desc'

Another error that I got on another table:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'open'

The command I use to copy from SQL Server to CVS file:
bcp deskmz.dbo.tableA out c:\tableA.csv /Umyuser /Pmypassword /SmyServer /c
/t,

The command I use to copy from CVS file to SQL Server:
bcp deskmz.dbo.tableA in c:\tableA.csv /Umyuser /Pmypassword /SmyServer /c
/t,

When I do the same commands (without /SmyServer) on the server where SQL
Server is located, I can run both commands successfully.
Can I copy from the CVS file back to SQL Server on a machine other than the
SQL Server machine ?

Thank you.

 

[TheSQLGuru]

I would imagine that it is a licencing violation to use bcp on a machine
that doesn't contain a licensed copy of sql server.

Perhaps the file has embedded double quotes and that is where the import
errors are coming from. Not sure though.

 

[Erland Sommarskog]

1. The machine where I am going to run BCP utility does not have SQL
Server installed, so BCP.exe and its underlying DLLs are not there. I
copied BCP.EXE, then it tells me that it expects sqlncli.dll. After I
copied that DLL, it now says "Unable to load BCP resource DLL. BCP
cannot continue."
How can I run BCP on a machine where SQL Server is not installed ? What
files do I need to copy ?

As Kevin says, you are probably in for a license violation here.
Then again, BCP is just a command-line wrapper on the bulk-copy API
which is in the ODBC part of SQL Native Client, which is free
redistributable, so it's quite a mild violation.

You also need a BCP.RLL which is in a resource directory somewhere. If that
is the last file you need, I don't know.

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

Strange. While error messages from BCP often are obscure, this one beats
me entirely. It's as if BCP would generate illegal SQL syntax. But I
can't see why BCP would include DESC in a command. Or OPEN for that
matter.

The best advice is to use the Profiler to see what commands BCP produces.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Bob Milton]

I don't know if 2005 is different, but older versions of the client tools
contained bcp. That allowed one to use bcp from a client rather than the
server. It should still work, though I haven't tried it. (It is not a
license problem, since you have to have a client license to access the
server in any fashion).

1. The machine where I am going to run BCP utility does not have SQL
Server installed, so BCP.exe and its underlying DLLs are not there. I
copied BCP.EXE, then it tells me that it expects sqlncli.dll. After I
copied that DLL, it now says "Unable to load BCP resource DLL. BCP
cannot continue."
How can I run BCP on a machine where SQL Server is not installed ? What
files do I need to copy ?

As Kevin says, you are probably in for a license violation here.
Then again, BCP is just a command-line wrapper on the bulk-copy API
which is in the ODBC part of SQL Native Client, which is free
redistributable, so it's quite a mild violation.

You also need a BCP.RLL which is in a resource directory somewhere. If
that
is the last file you need, I don't know.

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

Strange. While error messages from BCP often are obscure, this one beats
me entirely. It's as if BCP would generate illegal SQL syntax. But I
can't see why BCP would include DESC in a command. Or OPEN for that
matter.

The best advice is to use the Profiler to see what commands BCP produces.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Tom Cooper]

I also thought that just installing the client tools installed bcp. In any
case, bcp comes with the Express edition (see
http://msdn2.microsoft.com/en-us/library/ms345154.aspx
and search on bcp. So, since the Express edition is free and freely
distributable, getting a licensed version of bcp is not a problem.

In fact, I would suggest you do an install rather than trying to copy
various files. It can be very difficult to get all the right files in all
the right places with all the right registry entries. Why not let the
install program do that work for you?

I, too, don't know why you are getting those errors, it seems very strange.
The first thing is install bcp correctly. Then I would make sure you don't
have some other program named bcp (that name has been used by more than one
database vendor). If you do, make sure you run the correct one. Then, if
the error is still present, do as Erland suggested, and run Profiler to see
what bcp is doing.

Tom

I don't know if 2005 is different, but older versions of the client tools
contained bcp. That allowed one to use bcp from a client rather than the
server. It should still work, though I haven't tried it. (It is not a
license problem, since you have to have a client license to access the
server in any fashion).

1. The machine where I am going to run BCP utility does not have SQL
Server installed, so BCP.exe and its underlying DLLs are not there. I
copied BCP.EXE, then it tells me that it expects sqlncli.dll. After I
copied that DLL, it now says "Unable to load BCP resource DLL. BCP
cannot continue."
How can I run BCP on a machine where SQL Server is not installed ? What
files do I need to copy ?

As Kevin says, you are probably in for a license violation here.
Then again, BCP is just a command-line wrapper on the bulk-copy API
which is in the ODBC part of SQL Native Client, which is free
redistributable, so it's quite a mild violation.

You also need a BCP.RLL which is in a resource directory somewhere. If
that
is the last file you need, I don't know.

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

Strange. While error messages from BCP often are obscure, this one beats
me entirely. It's as if BCP would generate illegal SQL syntax. But I
can't see why BCP would include DESC in a command. Or OPEN for that
matter.

The best advice is to use the Profiler to see what commands BCP produces.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Erland Sommarskog]

I also thought that just installing the client tools installed bcp. In
any case, bcp comes with the Express edition (see
http://msdn2.microsoft.com/en-us/library/ms345154.aspx and search on
bcp. So, since the Express edition is free and freely distributable,
getting a licensed version of bcp is not a problem.

One possible problem with this is that you may not want to install a full
instance of Express, or even the full set of query tools on a machine. As
far as I can recall there is no such option when you install one of
the paid-for editions.

But if there is a tools-only option for Express, that should do the trick,
since Express does not come with very many tools. Well, you still get
the SQL Configuration Manager. Then again, you may have a need for it.
--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[fniles]

Thank you.

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

I noticed that the table I tried to import back into SQL Server has a column
named "desc", and "open" in the other table.
I noticed the SQL Server ODBC Driver on the server (SQLSRV32.DLL) is version
2000.86.1830.00, and on my machine (that does not work) it is version
2000.85.1117.00.
Even after I install the latest MDAC 2.8 that I just downloaded from
Microsoft website, my SQLSRV32.DLL version is still 2000.85.1117.00.
Could the difference version in the SQL Server ODBC Driver make it work on
the server and not work on my machine ?
Where can I get SQLSRV32.DLL version 2000.86.1830.00 ?

Thank you very much.

1. The machine where I am going to run BCP utility does not have SQL
Server installed, so BCP.exe and its underlying DLLs are not there. I
copied BCP.EXE, then it tells me that it expects sqlncli.dll. After I
copied that DLL, it now says "Unable to load BCP resource DLL. BCP
cannot continue."
How can I run BCP on a machine where SQL Server is not installed ? What
files do I need to copy ?

As Kevin says, you are probably in for a license violation here.
Then again, BCP is just a command-line wrapper on the bulk-copy API
which is in the ODBC part of SQL Native Client, which is free
redistributable, so it's quite a mild violation.

You also need a BCP.RLL which is in a resource directory somewhere. If
that
is the last file you need, I don't know.

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

Strange. While error messages from BCP often are obscure, this one beats
me entirely. It's as if BCP would generate illegal SQL syntax. But I
can't see why BCP would include DESC in a command. Or OPEN for that
matter.

The best advice is to use the Profiler to see what commands BCP produces.



--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Erland Sommarskog]

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

I noticed that the table I tried to import back into SQL Server has a
column named "desc", and "open" in the other table.

Aha! BCP fails to quote the table columns properly.

I noticed the SQL Server ODBC Driver on the server (SQLSRV32.DLL) is
version 2000.86.1830.00, and on my machine (that does not work) it is
version 2000.85.1117.00. Even after I install the latest MDAC 2.8 that I
just downloaded from Microsoft website, my SQLSRV32.DLL version is still
2000.85.1117.00.
Could the difference version in the SQL Server ODBC Driver make it work on
the server and not work on my machine ?
Where can I get SQLSRV32.DLL version 2000.86.1830.00 ?

Was that MDAC 2.8 SP1? I know there is an SP1, maybe even an SP2.

On one machine here I have 2000.86.3959.0, and under File Version I ass
"(srv03_sp2_rtm.070216-1710)".




--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[fniles]

Was that MDAC 2.8 SP1? I know there is an SP1, maybe even an SP2.
Probably, I am not sure.
On the microsoft page http://support.microsoft.com/kb/884103, it says
"To install MDAC 2.8 SP1, you must install Windows XP Service Pack 2 (SP2).
"

"To install MDAC 2.8 SP2, you must install Windows Server 2003 SP1."

So, to install SP1, you need Win XP and to install SP2 you need Win 2003 ?
How about Win 2000 ?

Thanks.

2. When I run BCP on my machine accessing a SQL Server on the server
(say myServer), I can copy a table to a cvs file, but when I try to copy
from CVS file back to SQL Server (after truncating the table on the SQL
Server), it gave me an error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near the keyword 'desc'

I noticed that the table I tried to import back into SQL Server has a
column named "desc", and "open" in the other table.

Aha! BCP fails to quote the table columns properly.

I noticed the SQL Server ODBC Driver on the server (SQLSRV32.DLL) is
version 2000.86.1830.00, and on my machine (that does not work) it is
version 2000.85.1117.00. Even after I install the latest MDAC 2.8 that I
just downloaded from Microsoft website, my SQLSRV32.DLL version is still
2000.85.1117.00.
Could the difference version in the SQL Server ODBC Driver make it work
on
the server and not work on my machine ?
Where can I get SQLSRV32.DLL version 2000.86.1830.00 ?

Was that MDAC 2.8 SP1? I know there is an SP1, maybe even an SP2.

On one machine here I have 2000.86.3959.0, and under File Version I ass
"(srv03_sp2_rtm.070216-1710)".




--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Erland Sommarskog]

Probably, I am not sure.
On the microsoft page http://support.microsoft.com/kb/884103, it says
"To install MDAC 2.8 SP1, you must install Windows XP Service Pack 2 >(SP2).
"

"To install MDAC 2.8 SP2, you must install Windows Server 2003 SP1."

So, to install SP1, you need Win XP and to install SP2 you need Win 2003 ?
How about Win 2000 ?

I followed the link for the SP1 fixlist, and from that page I get the
clear impression that MDAC 2.8 SP1 is included in Windows XP2.

I found the download for MDAC 2.8 SP1 (couldn't find one for SP2):
http://www.microsoft.com/downloads/...95-efc2-4f8e-a9e0-3a1afbd5922e&DisplayLang=en
It says that Win2000 is supported.

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx