bcp.exe installation

(This was originally in the sqlserver.programming group but I moved it to a
more appropriate place)
I'm using the bcp utility to create a text file from a view. The text file
is used by a 3rd party program for other processing.
bcp installs on the server machine with sql 2k (or in my case MSDE) just
fine but of course it's not automatically on any of the client machines. But
that's where I need it because those are the users that create and make use
of this text file.
My qustion is how to install bcp on the client machines without installing
MSDE. I thought it would work to just copy the file to an appropriate
location but apparently it needs to be installed. How do I go about that?
Thanks,
Keith
Never mind. I found a very nice inexpensive 3rd party control for use with
Delphi that will export datasources to all sorts of output formats. This is
a better solution since now I have no need at all for bcp.
Keith

BCP, ignore errors problem

Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:\data.txt -b1 -m100 -c -t, \n -Sdatabase -Uuser -Ppassword

here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors

please help.

thanks
madhuWhy not use BULK INSERT where it accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause, respectively, which allows the user to specify whether constraints are checked during a bulk load.

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_...ptimization.htm
"ChrisR" wrote:

> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
>
>

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm
"ChrisR" wrote:
> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> > One of my upcoming projects is going to be to move some data & its
> > related heavy processing off an production server off to a newer
> > server, do the processing, and then move the data back. There will be
> > large amounts of data going out & going in. My question deals with
> > efficiency of this data movement. I read in BOL that for exporting,
> > bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> > this data was coming out of SQL Server to a flat text file or bcp'ing
> > over to another SQL instance. So I'm not sure if I should bcp out to
> > text, then bcp in to my other server...or just bcp directly to the
> > other server. Also, I suppose I should not even consider DTS, since
> > bcp is just as fast, and there won't be any significant transformations
> > on the data? Opinions?
> >
>
>

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?
BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegro ups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>
|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?
|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegr oups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>
|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_c...timization.htm
"ChrisR" wrote:

> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegro ups.com...
>
>
sql

BCP utility replaces German with junk characters

Hi All,
I am using command line bcp utility of SQL Server to import data from a text
file to database.

I have some german words in the text file and after
import the German characters are lost.

see eg below.

Input : Khner, Klaus -> Text file value
OutPut: Khner, Klaus -> Table data, after import.

I am using unicode in the commad line.
The collation setting is LATIN

can anyone show some light.

Thanks in advance

Manoj.Manoj (mcmanoj_2000@.yahoo.com) writes:
> I am using command line bcp utility of SQL Server to import data from a
> text file to database.
> I have some german words in the text file and after
> import the German characters are lost.
> see eg below.
> Input : Khner, Klaus -> Text file value
> OutPut: K?hner, Klaus -> Table data, after import.
> I am using unicode in the commad line.
> The collation setting is LATIN

Since BCP is a command-line utility it's defaul code page is the OEM
code page. Therefore, if your server uses an ANSI page, and this is
the normal, there is an automatic conversion from OEM to ANSI. Problem
is if the file is in ANSI already - which it often is.

BCP offers the -C option to control this. The simplest is to use
-C RAW to turn off conversion.

See the description of BCP in Books Online for more details.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP utility help

I want to bcp out the record set in a flat file. I am unable to write the
correct script to do this.
What I am trying to do is build a text comma delimited file by running a
stored procedure say procTest. This bcp command will be executed in a nightl
y
job.
Please help.David (David@.discussions.microsoft.com) writes:
> I want to bcp out the record set in a flat file. I am unable to write the
> correct script to do this.
> What I am trying to do is build a text comma delimited file by running a
> stored procedure say procTest. This bcp command will be executed in a
> nightly job.
BCP db.dbo.tbl out tblout.bcp -T -c -t,
This is a command-line operation. To run it from a stored procedure,
you would have to call xp_cmdshell to spawn out to command-line level.
Now, when you say comma-delimited, do you in fact mean something like:
"value",2,"other value",98
then it gets trickier, particularly if the first column needs a quote.
If the first column needs a quote, you can use a formar file. If the
first column needs a quote, you will need to use the queryout option, or
define a view or possibly use a global temp table. Queryout appears to
give people headache, so I would stay away from that one.
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|||The line to tell BCP to import a comma separated file to SQL table is:
-t,
It's so tricky.
*** Sent via Developersdex http://www.examnotes.net ***

BCP utility

In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \\indiadb\ftproot\CLIENT_BRANCH_UPLOAD\branchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r \n -c -t "," -q'It doesn't...did you look at BOL?

You can however "fake it out"

It would require a view or using QUERYOUT...

Something like

SELECT 'HEADING1','HEADING2',ect
UNION ALL
SELECT Col1, Col2, ect
FROM yourTable

Just make sure you CONVERT everyhting to varchar...

bcp utilities

Hi
When I used the bcp utilities to output a query to a text file, the date
field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can I
correct this?
Thanks!
ChrisYou could use :-
bcp with the queryout option and specifiy a select statement that
converts the date to your format
or
create a view that returns the data as you require and then bcp out throught
the view
or
specifiy a format file for the bcp file to use
--
HTH
Ryan Waight, MCDBA, MCSE
"ChrisM" <cma1@.mail.com> wrote in message
news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> Hi
> When I used the bcp utilities to output a query to a text file, the date
> field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can
I
> correct this?
> Thanks!
> Chris
>|||Ryan
Thanks for your replied. Could you give me some example on how the queryout
option converts the date to the format I need? Below is the bcp command I
use.
bcp pubs..titles out "C:\bcp_test
Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Thanks!
Chris
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> You could use :-
> bcp with the queryout option and specifiy a select statement that
> converts the date to your format
> or
> create a view that returns the data as you require and then bcp out
throught
> the view
> or
> specifiy a format file for the bcp file to use
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "ChrisM" <cma1@.mail.com> wrote in message
> news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > When I used the bcp utilities to output a query to a text file, the date
> > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
can
> I
> > correct this?
> >
> > Thanks!
> >
> > Chris
> >
> >
>|||You would have to use the CONVERT statement which converts the dates into a
string, and with which you can define a format for the date (the 3:rd
parameter to the function).
bcp "SELECT CONVERT(char(10), ord_date, 101) + ' ' + CONVERT(char(8),
ord_date, 108) FROM pubs..sales" out
"C:\bcp_test\Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Alternatively, you can create a view with the query and CONVERTS and then
export from the view.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ChrisM" <cma1@.mail.com> wrote in message
news:esqf0M%23pDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Ryan
> Thanks for your replied. Could you give me some example on how the
queryout
> option converts the date to the format I need? Below is the bcp command I
> use.
> bcp pubs..titles out "C:\bcp_test
> Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
> Thanks!
> Chris
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You could use :-
> > bcp with the queryout option and specifiy a select statement that
> > converts the date to your format
> >
> > or
> >
> > create a view that returns the data as you require and then bcp out
> throught
> > the view
> >
> > or
> >
> > specifiy a format file for the bcp file to use
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "ChrisM" <cma1@.mail.com> wrote in message
> > news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > When I used the bcp utilities to output a query to a text file, the
date
> > > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
> can
> > I
> > > correct this?
> > >
> > > Thanks!
> > >
> > > Chris
> > >
> > >
> >
> >
>

BCP to comma separated Quote surround text file.

I have a need to export (regularly) a large table. I need to export to a
comma delimited file with quote surrounds around text fields (or all fields
for all that matters).
I can export the data with no problem, I can't figure out how to get the
quote surrounds however.
Here is what my bcp statement looks like:
bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
***** -S VNET-SQL /c /t , > e:\export\out.txt
I used the bol and it looked like I should be able to do something with
the -t switch but that seems to be having no impact what-so-ever.
thanks.Quote surrounds can be made by specifying in a format file the delimiters
for each field, rather than using the -t operator. Read about format files,
they are not really that hard but many people choke on them too quickly. If
I remember correctly, you can define a column 0 that terminates with " if
you need a quote on the first column.
Think of it as a regular expression problem.
RLF
PS - Of course, it leaves me wondering what you are getting with /t. (FWIW,
I don't think it matters, but you are using /t and the doc is for -t.)
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>I have a need to export (regularly) a large table. I need to export to a
>comma delimited file with quote surrounds around text fields (or all fields
>for all that matters).
> I can export the data with no problem, I can't figure out how to get the
> quote surrounds however.
> Here is what my bcp statement looks like:
> bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
> ***** -S VNET-SQL /c /t , > e:\export\out.txt
> I used the bol and it looked like I should be able to do something with
> the -t switch but that seems to be having no impact what-so-ever.
> thanks.
>|||the /t is just one of the many versions I was trying out, I think it was
meant to be: -t \t (tab delimited)
in the end I managed to get the -t to work except for the very first record
on the very first row.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ubZrw8DQFHA.2000@.TK2MSFTNGP15.phx.gbl...
> Quote surrounds can be made by specifying in a format file the delimiters
> for each field, rather than using the -t operator. Read about format
> files, they are not really that hard but many people choke on them too
> quickly. If I remember correctly, you can define a column 0 that
> terminates with " if you need a quote on the first column.
> Think of it as a regular expression problem.
> RLF
> PS - Of course, it leaves me wondering what you are getting with /t.
> (FWIW, I don't think it matters, but you are using /t and the doc is
> for -t.)
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>

BCP template using quoted text qualifiers

I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?I'd say the other table does not match your structure of the first table

Where's the bcp command and the DDL of the tables.|||The tables are exactly the same. They are duplicates of one another just in different databases.
When I tried to manually import the file that that was exported using BCP I got the error stating that I was trying to put a string into a numeric field. When I exported the table manually with quotes, I had no problem importing it in to the other table.
Do you know how to export a table with the quotes around the text in the file using bcp?|||You can either:
- create a view or;
- create a procedure.

Within either you can embed any character to be part of the data you're trying to export. If it's a view, - use OUT, else - QUERYOUT.|||So there is no switch with the bcp command to put in quotes?sql

BCP Syntax

I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith

BCP support in SQL Server 2005 - Doubts

Hi friends,

I am currently using SQL Server 2000 server. I have run some BCP command to import data to SQL from CSV and Text files with the help of FORMAT files through Windows Scheduler.

My doubt is, if I swich to SQL Server 2005, whether the same BCP commands will supported? If no, What are the things I need to do to run the BCP commands in 2005 as in 2000?

I heard that the BCP utility is no longer support in 2005 and this can be done thru SSIS utility. Is it right...?

Is SQL Server 2005 support to use the BCP commands without going to SSIS utility?

Please help. Thanks in advance.

Regards,

Sethu.

BCP should still work for you -it is a backwards compatibility issue.

The SQL 2005 utility is SQLCommand.exe. It has almost the identical command set as BCP. Refer to Books Online, Topic: 'SQLCommand utility'

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 question

Hi,
I have just finished a BCP ,with 35 columns, it extracts data from a SQL
SERVER Database to a text file, using a format file. The next step I will ad
d
more 58 in it, but for my surprise when I run this BCP (with 35 columns) it
appears the message "Query hints exceed maximum command buffer size of 1023
bytes (1229 bytes input). : No error"
So, the question is : What can I do to supply my necessity ?
ThanksAdalberto Andrade (Adalberto Andrade@.discussions.microsoft.com) writes:
> I have just finished a BCP ,with 35 columns, it extracts data from a
> SQL SERVER Database to a text file, using a format file. The next step I
> will add more 58 in it, but for my surprise when I run this BCP (with 35
> columns) it appears the message "Query hints exceed maximum command
> buffer size of 1023 bytes (1229 bytes input). : No error"
That was indeed surprising. Can you post:
o CREATE TABLE statement
o The format file.
o And the BCP command.
Without a repro it's difficult to say much.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||A Google search turns up this potential workaround:
[url]http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1116[/
url]
<quote>
Hi all. Fixed my problem.
I now have to create a dynamic temporary stored procedure which selects
the result set I want exporting. I then ask bcp to execute this and
export its contents
A bit long winded though...
</quote>
and this reply (from Erland, whose recommendation to post more detail is
well-taken)
<quote>
One idea would be to create a global temporary stored procedure that
returns the result set you're after and then only pass call to that
procedure to BCP.
Something like this:
EXEC ('CREATE PROCEDURE ##temp AS ' + @.sql_str)
DECLARE @.bcpCommand varchar(2000)
SET @.bcpCommand = 'bcp "EXEC ##temp" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U U2ADJC1 -P xxxxxx -c'
SET QUOTED_IDENTIFIER OFF
EXEC master..xp_cmdshell @.bcpCommand
DROP PROCEDURE ##temp
Erland Sommarskog, SQL Server MVP
</quote>
Steve Kass
Drew University
Adalberto Andrade wrote:

>Hi,
> I have just finished a BCP ,with 35 columns, it extracts data from a SQ
L
>SERVER Database to a text file, using a format file. The next step I will a
dd
>more 58 in it, but for my surprise when I run this BCP (with 35 columns) it
>appears the message "Query hints exceed maximum command buffer size of 1023
>bytes (1229 bytes input). : No error"
>So, the question is : What can I do to supply my necessity ?
>
> Thanks
>
>|||Hi Steve,
I tried what you suggested me, but unfortunately the error message was
"Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"
And my script is :
DECLARE @.consulta varchar (4000)
SET @.consulta = 'SELECT i2017_insc_munic from DB002.dbo.ds2017 WHERE
i2017_data_pgto_y2>20050601'
EXEC ('CREATE PROCEDURE ##TEMP AS ' + @.consulta)
DECLARE @.bcpcomando varchar(4500)
SET @.bcpcomando = 'bcp "EXEC ##TEMP" queryout c:\dts\cis.txt -f
d:\users\sql\bcp\cis\formato.fmt -SPRODUCTIONDB -Umyuser -Pmypassword'
PRINT @.bcpcomando
SET QUOTED_IDENTIFIER OFF
exec master..xp_cmdshell @.bcpcomando
DROP PROCEDURE ##temp
Note : I can run the same BCP in the command line and it really work OK. I
think the program xp_cmdshell isn't seeing the bcp program. Isn't it ?
What is my mistake ? and What is wrong ?
Thanks a
lot
Adalberto Andrade
"Steve Kass" wrote:

> A Google search turns up this potential workaround:
> http://www.sql-server-performance.c...ID=1116

> <quote>
> Hi all. Fixed my problem.
> I now have to create a dynamic temporary stored procedure which selects
> the result set I want exporting. I then ask bcp to execute this and
> export its contents
> A bit long winded though...
> </quote>
> and this reply (from Erland, whose recommendation to post more detail is
> well-taken)
> <quote>
> One idea would be to create a global temporary stored procedure that
> returns the result set you're after and then only pass call to that
> procedure to BCP.
> Something like this:
> EXEC ('CREATE PROCEDURE ##temp AS ' + @.sql_str)
> DECLARE @.bcpCommand varchar(2000)
> SET @.bcpCommand = 'bcp "EXEC ##temp" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U U2ADJC1 -P xxxxxx -c'
> SET QUOTED_IDENTIFIER OFF
> EXEC master..xp_cmdshell @.bcpCommand
> DROP PROCEDURE ##temp
> --
> Erland Sommarskog, SQL Server MVP
> </quote>
> Steve Kass
> Drew University
> Adalberto Andrade wrote:
>
>|||Adalberto,
Is the D: drive a network share? If so, the SQL Server account may not
know about it.
Drive letter network mappings are defined for users, and you could try
referring to
the file by its UNC name (with slashes and a server name - the name you
specify
when you set up a network mapping).
SK
Adalberto Andrade wrote:
>Hi Steve,
>I tried what you suggested me, but unfortunately the error message was
>"Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"
>And my script is :
>DECLARE @.consulta varchar (4000)
>SET @.consulta = 'SELECT i2017_insc_munic from DB002.dbo.ds2017 WHERE
>i2017_data_pgto_y2>20050601'
>EXEC ('CREATE PROCEDURE ##TEMP AS ' + @.consulta)
>DECLARE @.bcpcomando varchar(4500)
>SET @.bcpcomando = 'bcp "EXEC ##TEMP" queryout c:\dts\cis.txt -f
>d:\users\sql\bcp\cis\formato.fmt -SPRODUCTIONDB -Umyuser -Pmypassword'
>PRINT @.bcpcomando
>SET QUOTED_IDENTIFIER OFF
>exec master..xp_cmdshell @.bcpcomando
>DROP PROCEDURE ##temp
>Note : I can run the same BCP in the command line and it really work OK. I
>think the program xp_cmdshell isn't seeing the bcp program. Isn't it ?
>What is my mistake ? and What is wrong ?
>
> Thanks
a
>lot
>Adalberto Andrade
>
>"Steve Kass" wrote:
>
>|||Hi Steve,
D is a drive of my machine because I was running in a client. So I
think SQL SERVER didn't have visibility and don't know what or where it.
I decided running this script in the SERVER machine to avaid problem and
substituted D by C (with the same directory of the format file) and
everything worked OK.
Thanks more
one time,
Adalberto
Andrade
"Steve Kass" wrote:

> Adalberto,
> Is the D: drive a network share? If so, the SQL Server account may not
> know about it.
> Drive letter network mappings are defined for users, and you could try
> referring to
> the file by its UNC name (with slashes and a server name - the name you
> specify
> when you set up a network mapping).
> SK
>
> Adalberto Andrade wrote:
>
>

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
Dylan
Have you checked the event log to verify you are not getting Disk I/O
errors?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>
|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>
|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>
sql

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BINeen">
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BINeen">
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BINeen">
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BINeen">
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BINeen">
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created[vbcol=seagreen]
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...
if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> > Hi Guys
> >
> > I have a client who was having issues importing from a text file
(Created
> by
> > there mainframe ) into SQL server, using DTS it would only import a
> certain
> > number of the rows (like 10 000 out of a million) we hare now trying to
> use
> > BCP and see the following problem below:
> >
> >
> >
> > C:\>bcp CARD..AVAAF_ABSA_Employee in
> > c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
> >
> >
> >
> > netjie -Usa -Ppassword
> >
> >
> >
> > SQLState = S1000, NativeError = 0
> >
> >
> >
> > Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> > format file
> >
> >
> >
> > This is the format of the clients format file:
> >
> >
> >
> > 8.0
> >
> > 25
> >
> > 1 SQLCHAR 2 2 "\0" 0 num1
> > SQL_Latin1_General_Cp437_BIN
> >
> > 2 SQLCHAR 2 7 "\0" 10 Employee_no
> > SQL_Latin1_General_Cp437_BIN
> >
> > 3 SQLCHAR 2 7 "\0" 1 AB_Number
> > SQL_Latin1_General_Cp437_BIN
> >
> > 4 SQLCHAR 2 30 "\0" 3 Surname
> > SQL_Latin1_General_Cp437_BIN
> >
> > 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> >
> > 6 SQLCHAR 2 10 "\0" 2 Initials
> > SQL_Latin1_General_Cp437_BIN
> >
> > 7 SQLCHAR 2 20 "\0" 0 num2
> > SQL_Latin1_General_Cp437_BIN
> >
> > 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> > SQL_Latin1_General_Cp437_BIN
> >
> > 9 SQLCHAR 2 12 "\0" 0 num3
> > SQL_Latin1_General_Cp437_BIN
> >
> > 10 SQLCHAR 2 10 "\0" 0 num4
> > SQL_Latin1_General_Cp437_BIN
> >
> > 11 SQLCHAR 2 40 "\0" 0 num5
> > SQL_Latin1_General_Cp437_BIN
> >
> > 12 SQLCHAR 2 4 "\0" 0 num6
> > SQL_Latin1_General_Cp437_BIN
> >
> > 13 SQLCHAR 2 40 "\0" 0 num7
> > SQL_Latin1_General_Cp437_BIN
> >
> > 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 15 SQLCHAR 2 20 "\0" 7 FaxNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 16 SQLCHAR 2 12 "\0" 6 CellularNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 17 SQLCHAR 2 56 "\0" 0 num8
> > SQL_Latin1_General_Cp437_BIN
> >
> > 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
> SQL_Latin1_General_Cp437_BIN
> >
> > 19 SQLCHAR 2 40 "\0" 0 num9
> > SQL_Latin1_General_Cp437_BIN
> >
> > 20 SQLCHAR 2 10 "\0" 0 num10
> > SQL_Latin1_General_Cp437_BIN
> >
> > 21 SQLCHAR 2 4 "\0" 0 num11
> > SQL_Latin1_General_Cp437_BIN
> >
> > 22 SQLCHAR 2 40 "\0" 0 num12
> > SQL_Latin1_General_Cp437_BIN
> >
> > 23 SQLCHAR 2 40 "\0" 0 num13
> > SQL_Latin1_General_Cp437_BIN
> >
> > 24 SQLCHAR 2 2 "\0" 0 num14
> > SQL_Latin1_General_Cp437_BIN
> >
> > 25 SQLCHAR 2 16 "\r\n" 15 Province
> SQL_Latin1_General_Cp437_BIN
> >
> >
> >
> >
> >
> > I cannot find any info on this error message , has anyone got any ideas,
> > this is SQL server 2000 SP3
> >
> >
> >
> > Thanks
> >
> > Dylan
> >
> >
>

BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql