BCP server not found failure

Hi..im trying to use BCP to transfer data from text file to a SQL table and
give me an error. I have access to the db table with the 'sa' user and both
input and format files are in the directory. This was working well, but
suddenly stop working.
This is the complete command and the error at the prompt:
--COmmand--
C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
-fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
--error--
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Connect()).
Any idea..?
CARLOS BREA
PUERTO RICO
You have NOT specified the "-S server_name[\instance_name]" option.
This specifies the instance of SQL Server to connect to. If no server is
specified, bcp connects to the default instance of SQL Server on the local
computer. If you do not have a default instance and instead have a NAMED
instance, then this option is required. Also, this option is required when
executing bcp from a remote computer on the network.
Reference:
Books Online topic: bcp Utility
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||I have several named instances...the issue is that this bcp command was
working before setup of the DNS entries. Is any relation of this with the
failure.//?
How can I specified a specific default named instances in my SQL server, to
not have to use the -S flag at the command? I have a program that generate
the bcp command and didnt specify the -s flag. What I have to do to use the
bcp withut the -s flag?
Thanks.,..
"Fany Vargas [MSFT]" wrote:

> You have NOT specified the "-S server_name[\instance_name]" option.
> This specifies the instance of SQL Server to connect to. If no server is
> specified, bcp connects to the default instance of SQL Server on the local
> computer. If you do not have a default instance and instead have a NAMED
> instance, then this option is required. Also, this option is required when
> executing bcp from a remote computer on the network.
>
> Reference:
> Books Online topic: bcp Utility
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||You have to specify the -S flag to connect to a named instace. If you have
a default instance which is running (service is not stopped) then excluding
the -S specifies to connect to this default instance. The only possible way
(it may not even work) I can see where you can get the BCP command to work
without the -S command and force it to connect to a named instance is to
create a client alias with name "." and point it your named instance. So
for example:
start->run->cliconfg.exe->alias tab->Add
aliasname = .
servername=sqlservername\instancename
you can also try alias:
aliasname = (local)
servername=sqlservername\instancename
but keep in mind that would force any other applications on this machine
which try to connect using "." notation to now connect to the named
instance you pointed it too instead of the default instance. So the best
option would be to have your application create the bcp command correctly.
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
sql

BCP server not found failure

Hi..im trying to use BCP to transfer data from text file to a SQL table and
give me an error. I have access to the db table with the 'sa' user and both
input and format files are in the directory. This was working well, but
suddenly stop working.
This is the complete command and the error at the prompt:
--COmmand--
C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
-fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
--error--
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Se
rver does
not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(Connect()).
Any idea..?
CARLOS BREA
PUERTO RICOYou have NOT specified the "-S server_name[\instance_name]" option.
This specifies the instance of SQL Server to connect to. If no server is
specified, bcp connects to the default instance of SQL Server on the local
computer. If you do not have a default instance and instead have a NAMED
instance, then this option is required. Also, this option is required when
executing bcp from a remote computer on the network.
Reference:
Books Online topic: bcp Utility
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx|||I have several named instances...the issue is that this bcp command was
working before setup of the DNS entries. Is any relation of this with the
failure.//?
How can I specified a specific default named instances in my SQL server, to
not have to use the -S flag at the command? I have a program that generate
the bcp command and didnt specify the -s flag. What I have to do to use the
bcp withut the -s flag?
Thanks.,..
"Fany Vargas [MSFT]" wrote:

> You have NOT specified the "-S server_name[\instance_name]" option.
> This specifies the instance of SQL Server to connect to. If no server is
> specified, bcp connects to the default instance of SQL Server on the local
> computer. If you do not have a default instance and instead have a NAMED
> instance, then this option is required. Also, this option is required when
> executing bcp from a remote computer on the network.
>
> Reference:
> Books Online topic: bcp Utility
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>|||You have to specify the -S flag to connect to a named instace. If you have
a default instance which is running (service is not stopped) then excluding
the -S specifies to connect to this default instance. The only possible way
(it may not even work) I can see where you can get the BCP command to work
without the -S command and force it to connect to a named instance is to
create a client alias with name "." and point it your named instance. So
for example:
start->run->cliconfg.exe->alias tab->Add
aliasname = .
servername=sqlservername\instancename
you can also try alias:
aliasname = (local)
servername=sqlservername\instancename
but keep in mind that would force any other applications on this machine
which try to connect using "." notation to now connect to the named
instance you pointed it too instead of the default instance. So the best
option would be to have your application create the bcp command correctly.
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP help, or alternative

I need some help on this.
I currently use BCP to transfer my tab delimted text file into my SQL
2000 database. I use a format file. This has been working well, but I am
currently receiving an error message about some data. I am not concerned
about that at this time.
I'd prefer to get some feedback from you all. What other options do I
have besides BCP? The data file I have has 108 columns, and about 8000
rows so it's alot of data. I only NEED to import about 40 of those
columns. I know BCP allows this, hence why I ahve been using it.
I have a shared SQL server and do not believe I can create a DTS on it.
What other options do I have? Currently a PERL files runs all my
commands for BCP, so maybe I can use something else in the PERL file,
besides BCP.
I also have my own server, so there are no limitations there. Just with
the shared SQL box.
*** Sent via Developersdex http://www.examnotes.net ***C# is great for whipping up a quick parsing tool that will process the file
and output something a little more palatable to SQL Server. Ideally, you'd
have an output file with only the data you need, and one that can be
imported into the database using something as simple as BULK INSERT.
"Joey Martin" <joey@.kytechs.com> wrote in message
news:O2TfqxGRGHA.2532@.TK2MSFTNGP10.phx.gbl...
>
> I need some help on this.
> I currently use BCP to transfer my tab delimted text file into my SQL
> 2000 database. I use a format file. This has been working well, but I am
> currently receiving an error message about some data. I am not concerned
> about that at this time.
> I'd prefer to get some feedback from you all. What other options do I
> have besides BCP? The data file I have has 108 columns, and about 8000
> rows so it's alot of data. I only NEED to import about 40 of those
> columns. I know BCP allows this, hence why I ahve been using it.
> I have a shared SQL server and do not believe I can create a DTS on it.
> What other options do I have? Currently a PERL files runs all my
> commands for BCP, so maybe I can use something else in the PERL file,
> besides BCP.
> I also have my own server, so there are no limitations there. Just with
> the shared SQL box.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Yeah BULK INSERT is supposed to be faster than bcp too. It has similar
arguments and can be run from a SQL script instead of a batch file. You can
even use your format file with it ( I think!).
I'm a big fan of both bcp and BULK INSERT, not so much DTS.
Damien
"Aaron Bertrand [SQL Server MVP]" wrote:

> C# is great for whipping up a quick parsing tool that will process the fil
e
> and output something a little more palatable to SQL Server. Ideally, you'
d
> have an output file with only the data you need, and one that can be
> imported into the database using something as simple as BULK INSERT.
>
>
> "Joey Martin" <joey@.kytechs.com> wrote in message
> news:O2TfqxGRGHA.2532@.TK2MSFTNGP10.phx.gbl...
>
>|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> C# is great for whipping up a quick parsing tool that will process the
> file and output something a little more palatable to SQL Server.
> Ideally, you'd have an output file with only the data you need, and one
> that can be imported into the database using something as simple as BULK
> INSERT.
Tsssk! Joey is already using Perl, and what better tool is there to
read and parse a file than Perl?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Tsssk! Joey is already using Perl, and what better tool is there to
> read and parse a file than Perl?
Perl, C#, whatever. My point was that *any* application language parsing,
trimming and re-formatting the file is going to do more for you in the long
run than messing with the BCP format file and leaving the input files
untouched. My experience is with C#, not with Perl.

BCP Help

I'm tryping to use BCP to transfer some data to a file. In an effort ot learn about it, I'm trying some simple stuff with the pubs database and some examples from BOL. However, none of them are working.

I'm trying this command:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword

Of course i'm changing servername, sa, and password to the appropriate stuff..

I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'queryout'.

Any ideas why this example from BOL isn't working for me?

Thanks in advance!

MarkThat looks right. If you are doing this from within SQL (constructing a statement and executing xp_cmdshell, are you sure this is the statement being formed?|||Originally posted by bpdWork
That looks right. If you are doing this from within SQL (constructing a statement and executing xp_cmdshell, are you sure this is the statement being formed?

I'm actually using Query Analyzer and getting that error. Would that make any difference?|||Ah, yes.

BCP is a command line utility, not a SQL command. To run it from QueryAnalyser, you need to shell the command to the operating system with xp_cmdshell.

Try running it froma Command Prompt.

-b|||Originally posted by bpdWork
Ah, yes.

BCP is a command line utility, not a SQL command. To run it from QueryAnalyser, you need to shell the command to the operating system with xp_cmdshell.

Try running it froma Command Prompt.

-b

Awesome. Thanks so much.. Worked great. I feel like an idiot now.

Thanks again|||You mean, you feel smarter now.|||Originally posted by bpdWork
You mean, you feel smarter now.

Something like that. Maybe one day i'll beable to get a job as a DBA.|||DBA is boring. Programming is where it's at man! (Let the hell-fire begin!)|||DBA is boring. Programming is where it's at man! (Let the hell-fire begin!)

Am having a different kinda
discussion (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31111) right now.

BTW ... there is a difference between a support DBA and a development DBA.

BCP Format File

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?Rathtap (amcniw@.yahoo.com) writes:
> A format file provides a way to bulk copy data selectively from a data
> file to an instance of SQL Server. This allows the transfer of data to
> a table when there is a mismatch between fields in the data file and
> columns in the table.
> I take it this assumes the number of fields in the data file will
> always be constant. What if it is not?
> My table has two columns but my datafile may have 2 to 4 columns and I
> want to always select only the first two. Is there a way to set up the
> format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is the table:
CREATE TABLE [dbo].[CT_RATE_TEMP] (
[CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODERATE] [smallmoney] NULL ,
[CODERATE2] [smallmoney] NULL ,
[CODERATE3] [smallmoney] NULL ,
[CODERATE4] [smallmoney] NULL
) ON [PRIMARY]
GO

And here is the import file:
3641525.00
5275924.50
5276012.77
5277524.03
7428516.88
744367.84
800037.58
800048.00
800058.92
800068.94
800079.32
800089.66
800099.90
800109.90
8001110.08
8001210.29
Here I would like Col1 of the file to map to the Code field and Col2
to CodeRate. CODERATE2,CODERATE3 and CODERATE4 should be null.

On the other hand there may be the following file to import where all
fields match, then there is no problem:
5275924.5024.5024.50
5276012.7712.7712.77
5277524.0324.0324.03
7428516.8816.8816.88
744367.847.847.84
800037.587.587.58
800048.008.008.00
800058.928.928.92
800068.948.948.94
800079.329.329.32
800089.669.669.66
800099.909.909.90
800109.909.909.90
8001110.0810.0810.08
8001210.2910.2910.29
8001612.0512.0512.05
8001812.1412.1412.14
8001912.6112.6112.61
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93AEDF7A33AYazorman@.127.0.0.1>...
> Rathtap (amcniw@.yahoo.com) writes:
> > A format file provides a way to bulk copy data selectively from a data
> > file to an instance of SQL Server. This allows the transfer of data to
> > a table when there is a mismatch between fields in the data file and
> > columns in the table.
> > I take it this assumes the number of fields in the data file will
> > always be constant. What if it is not?
> > My table has two columns but my datafile may have 2 to 4 columns and I
> > want to always select only the first two. Is there a way to set up the
> > format file to accomplish that?
> Don't know off hand. Can you post a sample of the input file, and a CREATE
> TABLE statement for your table?|||try adding default values to the null fields

bcp error server not found

Hi..im trying to use BCP to transfer data from text file to a SQL table and
give me an error. I have access to the db table with the 'sa' user and both
input and format files are in the directory. This was working well, but
suddenly stop working.
This is the complete command and the error at the prompt:
--COmmand--
C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
-fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
--error--
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Connect()).
Any idea..?
CARLOS BREA
PUERTO RICOI think you may be missing the /S parameter which contains the
server/instance name of SQL Server.
"Pirulo_y2k" wrote:
> Hi..im trying to use BCP to transfer data from text file to a SQL table and
> give me an error. I have access to the db table with the 'sa' user and both
> input and format files are in the directory. This was working well, but
> suddenly stop working.
> This is the complete command and the error at the prompt:
> --COmmand--
> C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
> -fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
> --error--
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
> not exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Connect()).
> Any idea..?
> CARLOS BREA
> PUERTO RICO|||Thanks DrRides...I used with the -S and works great! The issue is that it
was working without the -S swith before.
I just cut and paste the bcp command from an error log that generate an
application. Is there a way that I can run the bcp command without specify
the named instances? or..where I can setup a default named instances for
the bcp command..'
"DrRides" wrote:
> I think you may be missing the /S parameter which contains the
> server/instance name of SQL Server.
> "Pirulo_y2k" wrote:
> > Hi..im trying to use BCP to transfer data from text file to a SQL table and
> > give me an error. I have access to the db table with the 'sa' user and both
> > input and format files are in the directory. This was working well, but
> > suddenly stop working.
> >
> > This is the complete command and the error at the prompt:
> >
> > --COmmand--
> > C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
> > -fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
> >
> > --error--
> > SQLState = 08001, NativeError = 17
> > Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
> > not exist or access denied.
> > SQLState = 01000, NativeError = 2
> > Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> > (Connect()).
> >
> > Any idea..?
> > CARLOS BREA
> > PUERTO RICO

bcp error server not found

Hi..im trying to use BCP to transfer data from text file to a SQL table and
give me an error. I have access to the db table with the 'sa' user and both
input and format files are in the directory. This was working well, but
suddenly stop working.
This is the complete command and the error at the prompt:
--COmmand--
C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
-fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
--error--
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Connect()).
Any idea..?
CARLOS BREA
PUERTO RICO
I think you may be missing the /S parameter which contains the
server/instance name of SQL Server.
"Pirulo_y2k" wrote:

> Hi..im trying to use BCP to transfer data from text file to a SQL table and
> give me an error. I have access to the db table with the 'sa' user and both
> input and format files are in the directory. This was working well, but
> suddenly stop working.
> This is the complete command and the error at the prompt:
> --COmmand--
> C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
> -fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
> --error--
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
> not exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Connect()).
> Any idea..?
> CARLOS BREA
> PUERTO RICO
|||Thanks DrRides...I used with the -S and works great! The issue is that it
was working without the -S swith before.
I just cut and paste the bcp command from an error log that generate an
application. Is there a way that I can run the bcp command without specify
the named instances? or..where I can setup a default named instances for
the bcp command..?
"DrRides" wrote:
[vbcol=seagreen]
> I think you may be missing the /S parameter which contains the
> server/instance name of SQL Server.
> "Pirulo_y2k" wrote:

bcp error server not found

Hi..im trying to use BCP to transfer data from text file to a SQL table and
give me an error. I have access to the db table with the 'sa' user and both
input and format files are in the directory. This was working well, but
suddenly stop working.
This is the complete command and the error at the prompt:
--COmmand--
C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
-fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
--error--
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Se
rver does
not exist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(Connect()).
Any idea..?
CARLOS BREA
PUERTO RICOI think you may be missing the /S parameter which contains the
server/instance name of SQL Server.
"Pirulo_y2k" wrote:

> Hi..im trying to use BCP to transfer data from text file to a SQL table an
d
> give me an error. I have access to the db table with the 'sa' user and bot
h
> input and format files are in the directory. This was working well, but
> suddenly stop working.
> This is the complete command and the error at the prompt:
> --COmmand--
> C:\>bcp dt..hc990021 in e:\dtracker\dtdata30\33600001032002990.or1
> -fe:\dtracker\dtdata30\HC990021.fmt -b5000 -F1 -Usa -P -h
> --error--
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does
> not exist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Co
nnectionOpen
> (Connect()).
> Any idea..?
> CARLOS BREA
> PUERTO RICO|||Thanks DrRides...I used with the -S and works great! The issue is that it
was working without the -S swith before.
I just cut and paste the bcp command from an error log that generate an
application. Is there a way that I can run the bcp command without specify
the named instances? or..where I can setup a default named instances for
the bcp command..'
"DrRides" wrote:
[vbcol=seagreen]
> I think you may be missing the /S parameter which contains the
> server/instance name of SQL Server.
> "Pirulo_y2k" wrote:
>

bcp error handling

Hello
I am using bcp.exe to transfer data between remote servers and need a way to
do the error handling. What I need is simple - just to know whether the copy
was successful or not, because it needs to be in transaction with other
operations. There are situations when the return value of the exe is always
0 and nothing gets written into the error log ( for example copying from
file to table that violates a constraint ). All that came to me was to parse
the log file ( option -o ) for strings like 'error' and 'failed' but this
doesn't look like a great idea. And yes, I am using bcp in both directions,
so bulk insert will not do.
Any ideas of how to find whether the copy was successful?
(sql server 2000 sp3, in case it matters)
Thanks
Plamen
"Plamen Doykov" <p_doykov@.code.bg> schrieb im Newsbeitrag
news:uoZ1ear1EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Hello
> I am using bcp.exe to transfer data between remote servers and need a
way to
> do the error handling. What I need is simple - just to know whether the
copy
> was successful or not, because it needs to be in transaction with other
> operations. There are situations when the return value of the exe is
always
> 0 and nothing gets written into the error log ( for example copying from
> file to table that violates a constraint ). All that came to me was to
parse
> the log file ( option -o ) for strings like 'error' and 'failed' but
this
> doesn't look like a great idea. And yes, I am using bcp in both
directions,
> so bulk insert will not do.
> Any ideas of how to find whether the copy was successful?
> (sql server 2000 sp3, in case it matters)
We settled with the output parsing also. That's what we use in Java:
private static final Pattern ERROR_PATTERN =
Pattern.compile(
"^\\s*(?:SQLState\\s*=\\s*([^,]+?)\\s*,\\s*NativeError\\s*=\\s*([+-]?\\d+)
\\s*|" +
"Error\\s+=\\s*+(.*?))$", Pattern.MULTILINE );
/**
* Extract error BCP messages from the given process output.
*
* @.param bcpOutput the output to extract the messages from.
* @.return a List of {@.link String} with the messages.
*/
protected static List extractErrorMessages( String bcpOutput ) {
List messages = new LinkedList();
Matcher m = ERROR_PATTERN.matcher( bcpOutput );
SqlServerError err = null;
while ( m.find() ) {
String message = m.group( 3 );
if ( message == null ) {
/*
* no message => first line:
* SQLState = 23000, NativeError = 3604
*/
err = new SqlServerError();
err.setSqlState( m.group( 1 ) );
err.setNativeError( StringParsing.string2int( m.group(
2 ) ) );
}
else {
/*
* second line:
* Error = [Microsoft][ODBC SQL Server Driver][SQL
Server]Doppelter Schl?ssel wurde ignoriert.
*/
err.setMessage( message );
messages.add( err );
err = null;
}
}
return messages;
}
Kind regards
robert