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.tabl eA out c:\tableA.csv /Umyuser /Pmypassword /SmyServer /c
/t,
The command I use to copy from CVS file to SQL Server:
bcp deskmz.dbo.tabl eA 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.
9  15131 
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" <fn****@pfmail. comwrote in message
news:u4******** ******@TK2MSFTN GP06.phx.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.tabl eA out c:\tableA.csv /Umyuser /Pmypassword /SmyServer
/c /t,
The command I use to copy from CVS file to SQL Server:
bcp deskmz.dbo.tabl eA 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.
fniles (fn****@pfmail. com) 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
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" <es****@sommars kog.sewrote in message
news:Xn******** *************@1 27.0.0.1...
fniles (fn****@pfmail. com) 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
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
"Bob Milton" <Do********@new sgroup.nospamwr ote in message
news:OR******** ******@TK2MSFTN GP03.phx.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" <es****@sommars kog.sewrote in message
news:Xn******** *************@1 27.0.0.1...
>fniles (fn****@pfmail. com) 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
redistributabl e, 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Tom Cooper (to*******@comc ast.no.spam.ple ase.net) writes:
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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thank you.
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" <es****@sommars kog.sewrote in message
news:Xn******** *************@1 27.0.0.1...
fniles (fn****@pfmail. com) 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
fniles (fn****@pfmail. com) writes:
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)".
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
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.
"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** ************@12 7.0.0.1...
fniles (fn****@pfmail. com) writes:
>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.0 0.
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, es****@sommarsk og.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
fniles (fn****@pfmail. com) writes:
>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 ?
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/d...DisplayLang=en
It says that Win2000 is supported.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sven |
last post by:
Hello,
I am receiving a text file that is produced from a mainframe that is
out of my control. I am attempting to find a (hopefully clean) way to
import it into a SQL Server database in an automated fashion. I am
not really concerned about how many tables it requires or what the
schema looks like as long as the data remains related and ends up in
its respective fields (I will probably use scratch tables for this).
The data is given...
|
by: alanknipmeyer |
last post by:
Hi,
I`m in the process of migrating a Access 2002 (Run in 2000 mode) from
Windows 98 to Win2K Server. It is a shared resource via a file share on
the 98 Server. Client systems are Win98 with the shared drive mounted
and the application run via the shared drive.
I have tried once before, but came across some file locking issues. I
thought i had addressed these file locking issues, but it came apparent
I hadn't when data started to get...
|
by: Andrew Chanter |
last post by:
I am in the process of developing an ADP client for a SQL Server database.
The IT Manager has requested that the adp file be launched centrally by each
user from a file on the file server (as opposed to installing a copy of the
adp on each client PC). I am aware of issues with this methodology when
using an mdb file as client, but I'm not sure whether adps are better or
worse in this regard. Does anyone have any firsthand experience,...
|
by: Jobs |
last post by:
Download the JAVA , .NET and SQL Server interview with answers
Download the JAVA , .NET and SQL Server interview sheet and rate
yourself. This will help you judge yourself are you really worth of
attending interviews. If you own a company best way to judge if the
candidate is worth of it.
http://www.questpond.com/InterviewRatingSheet.zip
|
by: huyuhui |
last post by:
The following is a question of LOAD utility.
Question:
How does the DB2 enforce table check constraints for data added to
table with the LOAD utility?
A. With the BUILD phase of LOAD
B. With the SET INTEGRITY statement
C. With the DELETE phase of the LOAD
D. With the UPDATE CONSTRAINTS statement
Answer is A
| | |
by: Connie |
last post by:
We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated....
|
by: Max Vit |
last post by:
I have deployed few Access apps splitting it in Front End and Back
End. Our environment uses Win XP SP2 for clients, Win 2k3 for servers
and Access 2003. The max. number of clients is about 50 (concurrent
users is estimated around 10).
Whilst the Back End always lives on a server, I am not quite clear
where the Front End should live.
I have searched the web and find contradicting views.
|
by: Andy_Khosravi |
last post by:
I just recently changed my database that I'm running from a monolithic
DB to a split FE/BE. The front end resides on the client machine and
the BE resides on a network drive. I'm experimenting with a utility
developed by Tony Toews to handle the distribution and subsequent
updates of the software. I'm having some trouble with the overall
upgrade process I've implemented, and I'm hoping one of you may have
an idea how to go about fixing...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
