bcp_bind

Hi experts,
I created a program loading data from diferent sources to SQL Server
database by using bcp_... bulk copy functions with some variables.(not from a
file)
My problem is: Now, a table, "Person", has 10 columns. I bound 10 variables
to the 10 columns, and load data is fine. But if in the future the table
needs increase 1 column to 11 columns and run the existing program, it will
get the error "HY000--
Not enough columns bound.". That means "bcp_bind" function has to bind to
all the columns of the table. otherwise the binding will fail. If load data
from a file, I can use "bcp_columns" function to specify column number. But I
insert data from variables, how can I bind to only partial columns of a table?
Thank you.
MCH
I have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
|||always bind to a view of a table, not directly to a table, so you can control
the columns you want to bind.
MCH
"Yevheniy" wrote:

> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>
sql

bcp_bind

Hi experts,
I created a program loading data from diferent sources to SQL Server
database by using bcp_... bulk copy functions with some variables.(not from a
file)
My problem is: Now, a table, "Person", has 10 columns. I bound 10 variables
to the 10 columns, and load data is fine. But if in the future the table
needs increase 1 column to 11 columns and run the existing program, it will
get the error "HY000--
Not enough columns bound.". That means "bcp_bind" function has to bind to
all the columns of the table. otherwise the binding will fail. If load data
from a file, I can use "bcp_columns" function to specify column number. But I
insert data from variables, how can I bind to only partial columns of a table?
Thank you.
MCHI have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com|||always bind to a view of a table, not directly to a table, so you can control
the columns you want to bind.
MCH
"Yevheniy" wrote:
> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>

BCP Queryout export to XML Question

Hey All,
Thought I'd take a break from Scalar Functions for a while and give Omnibuzz
a break from answering them.
I've searched thru the BOL, and this forum, as well as the help files in
SQL2005. I've gotten to the point where I am exporting the data
correctly..however I am looking for a format..
Here's what I'm attempting.. I need to take certain grabs of data using a
stored procedure.. dump it to an xml file / soap file, and then publish to
another service. for that to happen, i have a specific file format that the
y
have to be in.
So, here's my data...
strike price nominalDate
1000 0.000 2006-04-01
10000 2.850 2006-04-01
10050 0.000 2006-04-01
10150 0.000 2006-04-01
10200 0.000 2006-04-01
Here's the optionformat.xml
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="10"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="10"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="nominalDate" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="price" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="strike" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
and here's the real format it needs to be in.
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
<CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Of course, this doesn't include the headers at all, but I'm thinking I can
just add that to the file using the copy command to join the 3 files (copy
txt1.txt + optionformat.xml + txt2.txt uploadfile.xml)
The hardest part is getting it in that format...
Now to have the answer plunked right in front of me would be nice, but I
really need to learn how to do this, and understand the process... The only
other option that i have is to write a small vb program that calls from the
database, formats the data using the FileSystemObject.
If you know of a good resource that explains exporting into formatted xml,
that would be great.
If I'm totally going down the wrong road on this solution, let me know as
well.
Thanks!
~Dan Regalia
--
www.krushradio.com - Internet Radio for the rest of usDaniel Regalia (DanielRegalia@.discussions.microsoft.com) writes:
> Here's what I'm attempting.. I need to take certain grabs of data using
> a stored procedure.. dump it to an xml file / soap file, and then
> publish to another service. for that to happen, i have a specific file
> format that they have to be in.
>...
> Here's the optionformat.xml
><?xml version="1.0"?>
><BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
>...
> and here's the real format it needs to be in.
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="0.75"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="1.0"/>
><CurvePoint nominalDate="2006-06-01" price="0.0010" strike="3.0"/>
Ehum, you cannot use a BCP format file to specify an XML format for
the output.
You should probably look into using FOR XML EXPLICIT instead.
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

BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS

BCP IN column limit

I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005? Stated differently, what is the maximum number of columns I can import with bcp? Are there other maximums I should be investigating? My incoming record length is about 2K.

Hi,

I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue? I'd appreciate if you'd share any findings.

Cheers

RichardS71

|||

No success so far. The project has been benched for a while. My current suspect is there is a buffer limit I'm encountering somewhere. but haven't heard or read about one yet. Are you using a straight bcp (bcp with format file), or using the C entry points? We're using the C method right now, and would like to test a straight bcp and see if that eliminates the problem.

Do you have any other ideas?

|||

I'm doing a straight BCP without a format file (using the -c switch to denote all the fields are characters) although I've tried it with a format file too and I'm getting the same problem. For a full description of prob see post with title BCP fails with 134 columns .

I'm also getting the feeling that it's an internal buffer issue. I passed the problem onto a collegue and he did his own example of bcp with 134 fields, and his example worked. His table name and column names were shorter. Think I may be getting closer to the answer. I'll post here if I do.

Regards

RichardS

|||

Ok, I've cracked my problem at least.

Whilst SQL Server allows you to create tables with column names that begin with a number, BCP version 8.00.XXX won't let you BCP to the table. You wouldn't believe how long and hard I've been trying to crack this one.

I hope this helps. BTW I've scoured the net and I've not found anything todo with a limitation on number of columns. My inclimation would be to look in another direction, like what you've called your columns , how long the identifiers are, do they start with numbers...that sort of thing.

Tell me how you get on....and good luck.

Regards

RichardS

BCP DB Library functions

Hi,
We are using the DB-Library functions in a C++ program to do a BCP out of
SQL Server and are having a problem getting it to output character data.
When using the batch BCP utility there is the -c option which outputs the
information as character data. Is it possible to set this option with the
available DB-Library functions, or some other way? bcp_init does not seem
to have the full set of options that are available in the batch utility.
Thanks in advance for any help.
Wayne AntinoreWayne Antinore (wantinore@.veramark.com) writes:
> We are using the DB-Library functions in a C++ program to do a BCP out of
> SQL Server and are having a problem getting it to output character data.
> When using the batch BCP utility there is the -c option which outputs the
> information as character data. Is it possible to set this option with the
> available DB-Library functions, or some other way? bcp_init does not seem
> to have the full set of options that are available in the batch utility.
You have access to all features that the command-line interface provides,
they are just more difficult to use. When using the API, you need to think
in terms of format files, no matter you if specify data format as such,
or if you define columns programmatically with bcp_colfmt. -c and -n are
just shortcuts for special cases format files.
The bcp functions are described in Books Online, but the documentation
is a bit obscure in places, not the least the one bcp_colfmt. I know,
I had a hard time to understand it myself. I have a Perl module for DB-Lib
which include the DB-Library routines, and even if you use C++, you may find
my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
dblib.html#bcp_routines.
By the way, why DB-Library? In my opinion, DB-Library is a very good
client library, but alas Microsoft does not agree with me and has
deprecated it, and has not added support for the new datatypes in SQL7
and later. You should probably use ODBC or OLE DB instead. (Which I'm
told have a very similar bulk-copy interface to DB-Library.)
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Thanks Erland,
Since we were only putting out one column we formatted for the column and
all works well. We were still using DB-Library because this was an addition
to a component that already was using it so we were sticking with what we
knew (or thought we did!) .
Thanks, again.
Wayne
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AA27345CCYazorman@.127.0.0.1...
> Wayne Antinore (wantinore@.veramark.com) writes:
> > We are using the DB-Library functions in a C++ program to do a BCP out
of
> > SQL Server and are having a problem getting it to output character data.
> > When using the batch BCP utility there is the -c option which outputs
the
> > information as character data. Is it possible to set this option with
the
> > available DB-Library functions, or some other way? bcp_init does not
seem
> > to have the full set of options that are available in the batch utility.
> You have access to all features that the command-line interface provides,
> they are just more difficult to use. When using the API, you need to think
> in terms of format files, no matter you if specify data format as such,
> or if you define columns programmatically with bcp_colfmt. -c and -n are
> just shortcuts for special cases format files.
> The bcp functions are described in Books Online, but the documentation
> is a bit obscure in places, not the least the one bcp_colfmt. I know,
> I had a hard time to understand it myself. I have a Perl module for DB-Lib
> which include the DB-Library routines, and even if you use C++, you may
find
> my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
> dblib.html#bcp_routines.
> By the way, why DB-Library? In my opinion, DB-Library is a very good
> client library, but alas Microsoft does not agree with me and has
> deprecated it, and has not added support for the new datatypes in SQL7
> and later. You should probably use ODBC or OLE DB instead. (Which I'm
> told have a very similar bulk-copy interface to DB-Library.)
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

BCP DB Library functions

Hi,
We are using the DB-Library functions in a C++ program to do a BCP out of
SQL Server and are having a problem getting it to output character data.
When using the batch BCP utility there is the -c option which outputs the
information as character data. Is it possible to set this option with the
available DB-Library functions, or some other way? bcp_init does not seem
to have the full set of options that are available in the batch utility.
Thanks in advance for any help.
Wayne AntinoreWayne Antinore (wantinore@.veramark.com) writes:
> We are using the DB-Library functions in a C++ program to do a BCP out of
> SQL Server and are having a problem getting it to output character data.
> When using the batch BCP utility there is the -c option which outputs the
> information as character data. Is it possible to set this option with the
> available DB-Library functions, or some other way? bcp_init does not seem
> to have the full set of options that are available in the batch utility.
You have access to all features that the command-line interface provides,
they are just more difficult to use. When using the API, you need to think
in terms of format files, no matter you if specify data format as such,
or if you define columns programmatically with bcp_colfmt. -c and -n are
just shortcuts for special cases format files.
The bcp functions are described in Books Online, but the documentation
is a bit obscure in places, not the least the one bcp_colfmt. I know,
I had a hard time to understand it myself. I have a PERL module for DB-Lib
which include the DB-Library routines, and even if you use C++, you may find
my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
dblib.html#bcp_routines.
By the way, why DB-Library? In my opinion, DB-Library is a very good
client library, but alas Microsoft does not agree with me and has
deprecated it, and has not added support for the new datatypes in SQL7
and later. You should probably use ODBC or OLE DB instead. (Which I'm
told have a very similar bulk-copy interface to DB-Library.)
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Since we were only putting out one column we formatted for the column and
all works well. We were still using DB-Library because this was an addition
to a component that already was using it so we were sticking with what we
knew (or thought we did!) .
Thanks, again.
Wayne
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns948AA27345CCYazorman@.127.0.0.1...
> Wayne Antinore (wantinore@.veramark.com) writes:
of
the
the
seem
> You have access to all features that the command-line interface provides,
> they are just more difficult to use. When using the API, you need to think
> in terms of format files, no matter you if specify data format as such,
> or if you define columns programmatically with bcp_colfmt. -c and -n are
> just shortcuts for special cases format files.
> The bcp functions are described in Books Online, but the documentation
> is a bit obscure in places, not the least the one bcp_colfmt. I know,
> I had a hard time to understand it myself. I have a PERL module for DB-Lib
> which include the DB-Library routines, and even if you use C++, you may
find
> my documentaion helpful, see http://www.sommarskog.se/mssqlperl/mssql-
> dblib.html#bcp_routines.
> By the way, why DB-Library? In my opinion, DB-Library is a very good
> client library, but alas Microsoft does not agree with me and has
> deprecated it, and has not added support for the new datatypes in SQL7
> and later. You should probably use ODBC or OLE DB instead. (Which I'm
> told have a very similar bulk-copy interface to DB-Library.)
> --
> 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 API

I would like to execute bulk copy functionality from a C++ app. Does
an API exist? I have found Bulk-Copy functions (bcp_init,
bcp_exe...) in the ODBC API, is this what I should use? And if yes,
will/is it support by SQL Server version after SQL Server 2000?

Thanks!"elizabeth" <ezelasky@.hotmail.com> wrote in message
news:78393913.0410080650.16eb7a77@.posting.google.c om...
>I would like to execute bulk copy functionality from a C++ app. Does
> an API exist? I have found Bulk-Copy functions (bcp_init,
> bcp_exe...) in the ODBC API, is this what I should use? And if yes,
> will/is it support by SQL Server version after SQL Server 2000?
> Thanks!

Yes - see "Performing Bulk Copy Operations" in Books Online. ODBC is
supported in MSSQL 2005, and the same documentation appears in the latest
available version of the MSSQL 2005 BOL:

http://www.microsoft.com/downloads/...&DisplayLang=en

Simon|||With unmanaged C++ code, you might also consider OLE DB IRowsetFastLoad.
This is what DTS uses.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"elizabeth" <ezelasky@.hotmail.com> wrote in message
news:78393913.0410080650.16eb7a77@.posting.google.c om...
>I would like to execute bulk copy functionality from a C++ app. Does
> an API exist? I have found Bulk-Copy functions (bcp_init,
> bcp_exe...) in the ODBC API, is this what I should use? And if yes,
> will/is it support by SQL Server version after SQL Server 2000?
> Thanks!

Batch execute of SQL script from ADO.Net

I have a VB.Net application that needs to create about 5 stored
procedures and a couple of functions on SQL Server 2005 Express.
Currently I can execute all of them in one window of Sql Server
Management Studio, just separate each of them with a "GO" statement. Is
there a way I can accomplish this "one shot" approach via ADO.Net in my
application? If so, I can just put all my ddl SQL code in a text file
as an embedded resource, and then execute it in a couple of lines of
code. However, I suspect that I need to execute each ddl statement
separately and thus will need to parse the text file to break it up, or
break the sql code into multiple files, one for each stored proc.
Thanks for your thoughts,
Marcus[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
Marcus (holysmokes99@.hotmail.com) writes:
> I have a VB.Net application that needs to create about 5 stored
> procedures and a couple of functions on SQL Server 2005 Express.
> Currently I can execute all of them in one window of Sql Server
> Management Studio, just separate each of them with a "GO" statement. Is
> there a way I can accomplish this "one shot" approach via ADO.Net in my
> application? If so, I can just put all my ddl SQL code in a text file
> as an embedded resource, and then execute it in a couple of lines of
> code. However, I suspect that I need to execute each ddl statement
> separately and thus will need to parse the text file to break it up, or
> break the sql code into multiple files, one for each stored proc.
Yes, if you read this file from your own application, you will need to
parse the file for "go" and send down batch by batch with ExcecuteNonQuery.
Parsing the file for "go" is a trivial matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx