SQL Server BCP utility to copy a table in and out of SQL Server to a CVS file
fniles
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
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.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"fniles" <fni...@pfmail.com> wrote in message
news:u4oP9OpP...@TK2MSFTNGP06.phx.gbl...
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, esq...@sommarskog.se
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
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).
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9A073FF4...@127.0.0.1...
Tom Cooper
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
"Bob Milton" <DocBo...@newsgroup.nospam> wrote in message
news:ORjoDArP...@TK2MSFTNGP03.phx.gbl...
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.
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.
fniles
On the 2nd problem:
> 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.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9A073FF4...@127.0.0.1...
Erland Sommarskog
> On the 2nd problem:
>> 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)".
fniles
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.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9A0A50A2...@127.0.0.1...
Erland Sommarskog
>> 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 ?
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/details.aspx?FamilyID=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&DisplayLang=en
It says that Win2000 is supported.