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.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net


"fniles" <> wrote in message
news:.gbl...
>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.
>

Erland Sommarskog

fniles () writes:
> 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,

Books Online for SQL Server 2005 at

Books Online for SQL Server 2000 at

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).

"Erland Sommarskog" <> wrote in message
news:Xns9A073FF4C080Yazorman@127.0.0.1...
> fniles () writes:
>> 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,
>
> Books Online for SQL Server 2005 at
>
> Books Online for SQL Server 2000 at
>

Tom Cooper

I also thought that just installing the client tools installed bcp. In any
case, bcp comes with the Express edition (see

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" <DocBob1945@newsgroup.nospam> wrote in message
news:.gbl...
>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).
>
> "Erland Sommarskog" <> wrote in message
> news:Xns9A073FF4C080Yazorman@127.0.0.1...
>> fniles () writes:
>>> 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,
>>
>> Books Online for SQL Server 2005 at
>>
>> Books Online for SQL Server 2000 at
>>
>
>