I have a stored proc that currently BCP 27 tables into a MS SQL 2000 DB. The original script was created to import FIXED-WIDTH. I have made the changes to TAB-DELIMITED. But the source db that outputs the TAB files is enclosing every field within double quotes("). I am getting a 'Invalid character value for cast specification' error on the first row which is an integer. Is there a parameter that I can set for bcp to strip out the "...".
Thanks,
JNunezNYCWhy don't you use DTS where you can specify the required settings for data.
2012年2月25日星期六
BCP derived fields
thanx in advance....
i have not tried this, but here's an idea:
Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?
eg: in the data file there are
Location UserLocId
USA 1
USA 2
IND 1
IND 2
I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'
If there is any way to upload this using BCP
( updating the usercode after loading will take more time)As you have posted a question in the articles section it is being moved to SQL Server Forum.
MODERATOR.|||
Quote:
Originally Posted by dipu vp
thanx in advance....
Pls suggest an idea to import derived fields from the data file to SQLServer2005 using the BCP utility?
eg: in the data file there are
Location UserLocId
USA 1
USA 2
IND 1
IND 2
I have to import Unique usercode like
'USA_1'
'USA_2'
'IND_1'
'IND_2'
If there is any way to upload this using BCP
( updating the usercode after loading will take more time)
i have not tried this, but here's an idea:
i think you can BCP into an existing table. so your FMT (format file) could be different from that of the existing table structure. can you try to have an format file based on Location, UserLocId structure and have an existing table with an extra calculated filed called UserCode with default value rtrim(Location) + '_' + ltrim(UserLocId )
i don't know if it will work but worth a shot.
BCP Deployment
Hi all,
I am doing a project that involved with the big volume of data to be
uploaded to the SQL server. Due to the limitation at my employer's
site, I couldn't have the bulk admin right. I am looking at the
command BCP and wonder if there is any copyright issues if I were to
use BCP and to deploy the command?
Thanx.Hi
The use of BCP and the other client tools are governed by the EULA. Without
knowing the exact details of what you are trying to do it would not be
possible to say if you are covered or not.
John
"CY Lim" <limcyang@.yahoo.com> wrote in message
news:ea2b1aa7.0310200251.3d96823a@.posting.google.c om...
> Hi all,
> I am doing a project that involved with the big volume of data to be
> uploaded to the SQL server. Due to the limitation at my employer's
> site, I couldn't have the bulk admin right. I am looking at the
> command BCP and wonder if there is any copyright issues if I were to
> use BCP and to deploy the command?
> Thanx.
BCP delivery the: character
Using the BCP utility ->
With a degree character in the flat data file, can anyone deliver this to a table without SQL Server changing it to stacked bar:
CREATE TABLE "dbo"."F_conv"
(
"col1" VARCHAR(21) NOT NULL
)
;
myformat.fmt
8.0
1
1 SQLCHAR 0 30 "\r\n" 1 col1 ""
mydata.dat file
1332 NS 4 Tall 32 oz
bcp command
bcp "MY_DB"."dbo"."F_conv" in "mydata.dat"
-q -S<server> -Usa -Psa -f"myformat.fmt"I don't think declaring the column as VARCHAR(...) will satisfy your requirements. I changed your sample to NVARCHAR(32) for the column and got a little closer (char(166) instead of char(176) for the degree sign) and then was able to perform a REPLACE on the column. I am pretty sure you also need to address collation of the column to ensure that your data gets imported correctly.|||Try taking BCP out of the picture, and use:CREATE TABLE dbo.tDstachon (
col1 CHAR(21) NOT NULL
)
GO
BULK INSERT dbo.tDstachon FROM 'c:\dstachon.txt'
BULK INSERT dbo.tDstachon FROM 'c:\dstachon.txt' WITH (CODEPAGE = 'ACP')
SELECT * FROM dbo.tDstachon-PatP|||thanks guys.
cheers,
d.
With a degree character in the flat data file, can anyone deliver this to a table without SQL Server changing it to stacked bar:
CREATE TABLE "dbo"."F_conv"
(
"col1" VARCHAR(21) NOT NULL
)
;
myformat.fmt
8.0
1
1 SQLCHAR 0 30 "\r\n" 1 col1 ""
mydata.dat file
1332 NS 4 Tall 32 oz
bcp command
bcp "MY_DB"."dbo"."F_conv" in "mydata.dat"
-q -S<server> -Usa -Psa -f"myformat.fmt"I don't think declaring the column as VARCHAR(...) will satisfy your requirements. I changed your sample to NVARCHAR(32) for the column and got a little closer (char(166) instead of char(176) for the degree sign) and then was able to perform a REPLACE on the column. I am pretty sure you also need to address collation of the column to ensure that your data gets imported correctly.|||Try taking BCP out of the picture, and use:CREATE TABLE dbo.tDstachon (
col1 CHAR(21) NOT NULL
)
GO
BULK INSERT dbo.tDstachon FROM 'c:\dstachon.txt'
BULK INSERT dbo.tDstachon FROM 'c:\dstachon.txt' WITH (CODEPAGE = 'ACP')
SELECT * FROM dbo.tDstachon-PatP|||thanks guys.
cheers,
d.
BCP delimiters
Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David Lozzi
Hi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John
|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John
|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David Lozzi
Hi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John
|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John
|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John
BCP delimiters
Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We nee
d
> to use another delimiter and I'm wondering if we can use multiple characte
rs
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We nee
d
> to use another delimiter and I'm wondering if we can use multiple characte
rs
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John
BCP delimiters
Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>> Howdy,
>> I have a fmt file used for importing data from a IBM database into SQL.
>> Currently we're using the pipe | as a delimiter and it works great. We
>> need
>> to use another delimiter and I'm wondering if we can use multiple
>> characters
>> like %$. If so, is it just as easy as changing the delimiter in the fmt
>> file?
>> Thanks,
>> David Lozzi
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> > Hi David
> >
> > "David Lozzi" wrote:
> >
> >> Howdy,
> >>
> >> I have a fmt file used for importing data from a IBM database into SQL.
> >> Currently we're using the pipe | as a delimiter and it works great. We
> >> need
> >> to use another delimiter and I'm wondering if we can use multiple
> >> characters
> >> like %$. If so, is it just as easy as changing the delimiter in the fmt
> >> file?
> >>
> >> Thanks,
> >>
> >> David Lozzi
> >>
> > The field terminator can be a string of up to 10 printable characters. See
> > http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> > http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> > file is a text file so it can be edited with any text editor.
> >
> > John
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>> Howdy,
>> I have a fmt file used for importing data from a IBM database into SQL.
>> Currently we're using the pipe | as a delimiter and it works great. We
>> need
>> to use another delimiter and I'm wondering if we can use multiple
>> characters
>> like %$. If so, is it just as easy as changing the delimiter in the fmt
>> file?
>> Thanks,
>> David Lozzi
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> > Hi David
> >
> > "David Lozzi" wrote:
> >
> >> Howdy,
> >>
> >> I have a fmt file used for importing data from a IBM database into SQL.
> >> Currently we're using the pipe | as a delimiter and it works great. We
> >> need
> >> to use another delimiter and I'm wondering if we can use multiple
> >> characters
> >> like %$. If so, is it just as easy as changing the delimiter in the fmt
> >> file?
> >>
> >> Thanks,
> >>
> >> David Lozzi
> >>
> > The field terminator can be a string of up to 10 printable characters. See
> > http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> > http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> > file is a text file so it can be edited with any text editor.
> >
> > John
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John
bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERV
Hello
bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERVERA
Since dir has a space (i.e name of the dir is 2 words with a space between
them)
C:\cnet reports
Tried C:\"cnet reports"\
c:\'cnet reports'\
gives error
How to specify a dir that has a space in it
works OK if dir does NOT have a space.
Thanks
Basheerbcp pubs.dbo.authors in "c:\cnet Reports\abc.txt -T -r \n -S SERV"
Quotes around the whole path.
"Basheer" <Basheer@.discussions.microsoft.com> wrote in message
news:9F87DA0B-1BC6-4A07-97C7-F0F99DB0EE63@.microsoft.com...
> Hello
> bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERVERA
> Since dir has a space (i.e name of the dir is 2 words with a space between
> them)
> C:\cnet reports
> Tried C:\"cnet reports"\
> c:\'cnet reports'\
> gives error
> How to specify a dir that has a space in it
> works OK if dir does NOT have a space.
> Thanks
> Basheer
>|||Hi,
Use the double quotes till the end of file name.
BCP MASTER..SYSOBJECTS OUT "C:\HARI
F1\HARI.TX" -Uuser -Ppassword -SServername -c
Thanks
Hari
SQL Server MVP
"Basheer" <Basheer@.discussions.microsoft.com> wrote in message
news:9F87DA0B-1BC6-4A07-97C7-F0F99DB0EE63@.microsoft.com...
> Hello
> bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERVERA
> Since dir has a space (i.e name of the dir is 2 words with a space between
> them)
> C:\cnet reports
> Tried C:\"cnet reports"\
> c:\'cnet reports'\
> gives error
> How to specify a dir that has a space in it
> works OK if dir does NOT have a space.
> Thanks
> Basheer
>
bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERVERA
Since dir has a space (i.e name of the dir is 2 words with a space between
them)
C:\cnet reports
Tried C:\"cnet reports"\
c:\'cnet reports'\
gives error
How to specify a dir that has a space in it
works OK if dir does NOT have a space.
Thanks
Basheerbcp pubs.dbo.authors in "c:\cnet Reports\abc.txt -T -r \n -S SERV"
Quotes around the whole path.
"Basheer" <Basheer@.discussions.microsoft.com> wrote in message
news:9F87DA0B-1BC6-4A07-97C7-F0F99DB0EE63@.microsoft.com...
> Hello
> bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERVERA
> Since dir has a space (i.e name of the dir is 2 words with a space between
> them)
> C:\cnet reports
> Tried C:\"cnet reports"\
> c:\'cnet reports'\
> gives error
> How to specify a dir that has a space in it
> works OK if dir does NOT have a space.
> Thanks
> Basheer
>|||Hi,
Use the double quotes till the end of file name.
BCP MASTER..SYSOBJECTS OUT "C:\HARI
F1\HARI.TX" -Uuser -Ppassword -SServername -c
Thanks
Hari
SQL Server MVP
"Basheer" <Basheer@.discussions.microsoft.com> wrote in message
news:9F87DA0B-1BC6-4A07-97C7-F0F99DB0EE63@.microsoft.com...
> Hello
> bcp dbname..testload in c:\"cnet Reports"\abc.txt -T -r \n -S SERVERA
> Since dir has a space (i.e name of the dir is 2 words with a space between
> them)
> C:\cnet reports
> Tried C:\"cnet reports"\
> c:\'cnet reports'\
> gives error
> How to specify a dir that has a space in it
> works OK if dir does NOT have a space.
> Thanks
> Basheer
>
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
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
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 data with quotes
I have a table with the following data
A_Id A_Name A_Desig
A324 Author1 Script Writer
T533 Tester Test cases
Now I want to export the data to text file with the following format
"A324" "Author1" "Script Writer"
"T533" "Tester" "Test cases"
Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
varchar(100)
set @.table='Author'
set @.FileName='C:\Author.txt'
set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
'+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
Exec(@.str)
But I get the result with first and last quotes missing
A324" "Author1" "Script Writer
T533" "Tester" "Test cases
How do I get the desired result?
MadhivananHi Madhivanan
You have a couple of choices. You could either specify a format file or add
the additional "" to your select statement. I've created a quick example
against the pubs database table employee for you ...
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -t, -Ssql-test -T
This result in...
"A-C71970F","Aria"
"A-R89858F","Annette"
"AMD15433F","Ann"
"ARD36773F","Anabela"
"CFH28514M","Carlos"
"CGS88322F","Carine"
"DBT39435M","Daniel"
"DWR65030M","Diego"
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138178304.808428.247350@.f14g2000cwb.googlegroups.com...
>I have a table with the following data
> A_Id A_Name A_Desig
> A324 Author1 Script Writer
> T533 Tester Test cases
> Now I want to export the data to text file with the following format
> "A324" "Author1" "Script Writer"
> "T533" "Tester" "Test cases"
> Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
> varchar(100)
> set @.table='Author'
> set @.FileName='C:\Author.txt'
> set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
> '+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
> Exec(@.str)
> But I get the result with first and last quotes missing
> A324" "Author1" "Script Writer
> T533" "Tester" "Test cases
>
> How do I get the desired result?
> Madhivanan
>|||Thanks, Ryan
When I run your query, I get yhis error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+emp_id+'.
Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Thanks, Ryan
> When I run your query, I get yhis error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '+emp_id+'.
A query? Ryan did not post a query. He posted something run in a command-
line window. (I tested it, and it works, once I had corrected the
table name, and removed the -S option to run on the local server.)
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|||Thanks Erland
I also made some changes to work
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -q
Madhivanan
A_Id A_Name A_Desig
A324 Author1 Script Writer
T533 Tester Test cases
Now I want to export the data to text file with the following format
"A324" "Author1" "Script Writer"
"T533" "Tester" "Test cases"
Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
varchar(100)
set @.table='Author'
set @.FileName='C:\Author.txt'
set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
'+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
Exec(@.str)
But I get the result with first and last quotes missing
A324" "Author1" "Script Writer
T533" "Tester" "Test cases
How do I get the desired result?
MadhivananHi Madhivanan
You have a couple of choices. You could either specify a format file or add
the additional "" to your select statement. I've created a quick example
against the pubs database table employee for you ...
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -t, -Ssql-test -T
This result in...
"A-C71970F","Aria"
"A-R89858F","Annette"
"AMD15433F","Ann"
"ARD36773F","Anabela"
"CFH28514M","Carlos"
"CGS88322F","Carine"
"DBT39435M","Daniel"
"DWR65030M","Diego"
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138178304.808428.247350@.f14g2000cwb.googlegroups.com...
>I have a table with the following data
> A_Id A_Name A_Desig
> A324 Author1 Script Writer
> T533 Tester Test cases
> Now I want to export the data to text file with the following format
> "A324" "Author1" "Script Writer"
> "T533" "Tester" "Test cases"
> Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
> varchar(100)
> set @.table='Author'
> set @.FileName='C:\Author.txt'
> set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
> '+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
> Exec(@.str)
> But I get the result with first and last quotes missing
> A324" "Author1" "Script Writer
> T533" "Tester" "Test cases
>
> How do I get the desired result?
> Madhivanan
>|||Thanks, Ryan
When I run your query, I get yhis error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+emp_id+'.
Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Thanks, Ryan
> When I run your query, I get yhis error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '+emp_id+'.
A query? Ryan did not post a query. He posted something run in a command-
line window. (I tested it, and it works, once I had corrected the
table name, and removed the -S option to run on the local server.)
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|||Thanks Erland
I also made some changes to work
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -q
Madhivanan
bcp data to a txt file
I am trying to export data to a text file using bcp. The table that I am
trying to export has an ntext column. I keep getting this error, and
have not been able to find a way to get around it. How do I specify to
another provider, or get it around it anyway? Thanks...
trying to export has an ntext column. I keep getting this error, and
have not been able to find a way to get around it. How do I specify to
another provider, or get it around it anyway? Thanks...
C:\Program Files\Microsoft SQL Server>bcp northwind.dbo.categories out
northwind
_categories.txt -n -T -Usa -PPassword-2003
Msg 4004, Level 16, State 1:
Server 'CORPLGROULTI1', Line 1:
Unicode data in a Unicode-only collation or ntext data cannot be sent
to clients using DB-Library (such as ISQL) or ODBC version 3.7 or
earlier.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it![posted and mailed, please reply in news]
tim groulx (timgru@.verizon.net) writes:
> I am trying to export data to a text file using bcp. The table that I am
> trying to export has an ntext column. I keep getting this error, and
> have not been able to find a way to get around it. How do I specify to
> another provider, or get it around it anyway? Thanks...
>
> C:\Program Files\Microsoft SQL Server>bcp northwind.dbo.categories out
> northwind
> _categories.txt -n -T -Usa -PPassword-2003
> Msg 4004, Level 16, State 1:
> Server 'CORPLGROULTI1', Line 1:
> Unicode data in a Unicode-only collation or ntext data cannot be sent
> to clients using DB-Library (such as ISQL) or ODBC version 3.7 or
> earlier.
Apparently you are not using a version of BCP that comes with the version
of SQL Server you are connecting to. Or you are, but for some reason
the ODBC drivers on the machine have been replaced by older ones.
The most likely reason, though, is that you are running BCP from a
machine that has the 6.5 tools installed, or you are using BCP from a
Sybase installation.
Use bcp -v to find out what you are using.
--
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 data importing error when using xml format file
Hello, I have a question.
I'm performing some early test for bulk import/export using the bcp(9.0) utility, but there is a problem when working with an xml FormatFile. I execute the following command:
>bcp TestDB.dbo.myNewTable1 in myDataFile.dat -fmyXMLFormatFile.xml -SmySever
-UmyAccount -PmyPassword
And get the following error:
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
0 rows copied.
Please help me understand and fix this problem.
Thanks a lot! Best regards,
Diego Valdez
For XML format you suppose to use -x switch...
Please check BOL for more info.
-xUsed with the format and -f format_file options, generates an XML-based format file instead of the default non-XML format file. The -x does not work when importing or exporting data. It generates an error if used without both format and -f format_file.
|||I don't think you understood my post...My problem is not in the generated xml format file. This file was created succesfully. The issue arises when I try to import data with my specified bcp command and seems that there is something wrong with the Data File, as the message I get says "Unexpected EOF encountered in BCP data-file"
Hope someone can help me get around this.|||There maybe a problem with the data file but you need to get BCP to read your format file first as it could be a spurious error thats getting thrown, right now your commandline is not valid.
BCP data import
Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.A
Hi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the options
> but still can't figure it out. I keep getting different error messages.
> T.I.A
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.A
Hi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the options
> but still can't figure it out. I keep getting different error messages.
> T.I.A
BCP data import
Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.AHi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
>|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
>|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the option
s
> but still can't figure it out. I keep getting different error messages.
> T.I.A
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.AHi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
>|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
>|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:
> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the option
s
> but still can't figure it out. I keep getting different error messages.
> T.I.A
bcp copying,but no file
DECLARE @.query NVARCHAR(2000)
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query
I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL
and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.
Could somebody please help meWhere are you looking for the file?
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query
I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL
and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.
Could somebody please help meWhere are you looking for the file?
BCP copy in failed...The SQL Server cannot obtain a LOCK resource
I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
Here is the error message:
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
Helen
On Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
Here is the error message:
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
Helen
On Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
BCP copy in failed...The SQL Server cannot obtain a LOCK resource
I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL S
erver
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL
Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL S
erver
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL
Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
BCP copy in failed...The SQL Server cannot obtain a LOCK resource
I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,
BCP copies only Integer data
I'm using BCP to copy data from a fixed length delimited file into a
SQL table, i'm using a format file, here is the format file:
8.0
14
1 SQLINT 0 4 "" 1 numero_llamada ""
2 SQLNCHAR 0 1 "" 2 operario
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 1 "" 3 cabina
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 21 "" 4 numero_marcado
SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 11 "" 5 destino
SQL_Latin1_General_CP1_CI_AS
6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
""
7 SQLNCHAR 0 1 "" 7 minuto
SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 1 "" 8 hora
SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 1 "" 9 centesima_segundo
SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 1 "" 10 segundos
SQL_Latin1_General_CP1_CI_AS
11 SQLINT 0 4 "" 11 duracion_segundos
""
12 SQLINT 0 4 "" 12 costo_centavos
""
13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 3 "" 14 fin
SQL_Latin1_General_CP1_CI_AS
now my problem is that the INT and SMALLINT get into the table where
they should be, but the character fields just show "NULL" and some
fields just show squares and the last character on that string, any
ideas?
Thank you(kibagami23@.gmail.com) writes:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
...
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
Is the file a binary file or a text file? Since the integers make it,
I assume that it is a binary file.
Really what a fixed-length delimitted file is I don't know. Judging
from your format file, your file is fixed-width only.
One possibility that the character column has length-specifiers. In
such case you should specify the length of these specifiers in the
third column in the BCP file.
For more substantial input, and less speculation, please post CREATE TABLE
statement for the table, and a sample input file. (preferably enclosed
in a zip archive as attachment.)
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|||Thank you for the reply, yes the file is Fixed width, and i am putting
the length of the specifiers in the third column of the format file,
the file is a binary file created from a thrid party software developed
in Borland C Builder , i'll post what you ask for later today, any
other ideas in the meantime?
Thank you|||I suggest a row terminator on your last column (column 14). Rather than "",
specify "\r\n".
Is bcp importing all the rows? (i.e. Input file has 1000 records and your
table has 1000 rows.)
Hope that helps,
Joe
"kibagami23@.gmail.com" wrote:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
> 2 SQLNCHAR 0 1 "" 2 operario
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 1 "" 3 cabina
> SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 21 "" 4 numero_marcado
> SQL_Latin1_General_CP1_CI_AS
> 5 SQLNCHAR 0 11 "" 5 destino
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
> ""
> 7 SQLNCHAR 0 1 "" 7 minuto
> SQL_Latin1_General_CP1_CI_AS
> 8 SQLNCHAR 0 1 "" 8 hora
> SQL_Latin1_General_CP1_CI_AS
> 9 SQLNCHAR 0 1 "" 9 centesima_segundo
> SQL_Latin1_General_CP1_CI_AS
> 10 SQLNCHAR 0 1 "" 10 segundos
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLINT 0 4 "" 11 duracion_segundos
> ""
> 12 SQLINT 0 4 "" 12 costo_centavos
> ""
> 13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
> SQL_Latin1_General_CP1_CI_AS
> 14 SQLNCHAR 0 3 "" 14 fin
> SQL_Latin1_General_CP1_CI_AS
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
> Thank you
>|||SSd2ZSBhbHJlYWR5IHRyaWVkIHB1dHRpbmcgdGhl
IHJvdyB0ZXJtaW5hdG9yIGFuZCBpdCBrZWVw
cyBkb2luZyB0aGUKc2FtZSwgYW5kIHllcyB0aGUg
YmNwIGltcG9ydHMgYWxsIG9mIHRoZSByb3dz
LCBidXQgaW4gdGhlIHNhbWUgd2F5IG9ubHkKdGhl
IGludGVnZXIgZmllbGRzIHNob3cgb24gdGhl
IHNxbCB0YWJsZSB0aGUgY2hhcmFjdGVyIGZpZWxk
cyBzaG93cwpOVUxMIGFuZCBzb21lIG90aGVy
cyBzaG93IHNxdWFyZXMgd2l0aCB0aGUgbGFzdCBj
aGFyYWN0ZXIgb2YgdGhhdApmaWVsZHMgc2hv
d2luZywgbGlrZSB0aGlzOgoKNDQ0NDIgTlVMTCBO
VUxMCuOQsOOYtOOQtuOkseOcs+OMsDUJ5JWD
5ZWM5IWMUgk0CU5VTEwJTlVMTAlOVUxMCU5VTEwJ
ODQJNzAwCU5VTEwJTlVMTAo0NDQ0MwlOVUxM
CU5VTEwJ44C246C545i1MQnkvYzkhYNMCTUJTlVM
TAlOVUxMCU5VTEwJTlVMTAkzNAkyMDAJTlVM
TAlOVUxMCjQ0NDQ0CU5VTEwJTlVMTAnjgLbjkLnj
hLEzCeS9jOSFg0wJNQlOVUxMCU5VTEwJTlVM
TAlOVUxMCTE0MTE3CTUxMjAwCU5VTEwJPwo0NDQ0
NQlOVUxMCU5VTEwJ46C245Sw45C3MgnkvYzk
hYNMCTUJTlVMTAlOVUxMCU5VTEwJTlVMTAkyNgky
MDAJTlVMTAlOVUxMCjQ0NDQ2CU5VTEwJTlVM
TAnjoLbjpLbjgLY1CeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTI0NTg2CTc2ODAwCU5V
TEwJPwo0NDQ0NwlOVUxMCU5VTEwJ46C245S246Sw
MwnkvYzkhYNMCTUJTlVMTAlOVUxMCU5VTEwJ
TlVMTAk4NjI4NgkxNzkyMDAJTlVMTAk/ CjQ0NDQ4CU5VTEwJTlVMTAnjiLbjpLLjhLM5CeS9
jOSF
g0wJNQlOVUxMCU5VTEwJTlVMTAlOVUxMCTg1MDcJ
NTEyMDAJTlVMTAk/CjQ0NDQ5CU5VTEwJTlVM
TAnjoLbjoLLjkLkxCeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTcyMDQJNTEyMDAJTlVM
TAk/ CjQ0NDUwCU5VTEwJTlVMTAnjkLDjmLTjkLbjoLLj
pLPjnLM5CeSVg+WVjOSFjFIJNAlOVUxM
CU5VTEwJTlVMTAlOVUxMCTU4OTYJMTAyNDAwCU5V
TEwJPwoKVGhhbmsgeW91Cg==|||I forgot to say that the last sample is the select from the destination
sql table|||For some reason I can't see your entire thread, but my first suggestion is
for you to view the file with a hex editor to see whether the file contains
what you expect it to.
A very odd thing is that your format file indicates that you have
1-character
SQLNCHAR columns called minuto, hora, segundos, and centesima_segundos.
How can these be represented with a single character? It seems much
more likely to me that these would be represented as unsigned 1-byte
integers
(SQLTINYINT) or other numeric data type.
Things to check with the hex editor are: is the string data actually stored
as Unicode? Is the string data terminated with char(0), or does it
contain non-printable characters (<= 001F Unicode)? (This could
cause problems, I think.)
Posting the CREATE TABLE statement for the destination table would
help.
Steve Kass
Drew University
Kibagami23 wrote:
>I forgot to say that the last sample is the select from the destination
>sql table
>
>
SQL table, i'm using a format file, here is the format file:
8.0
14
1 SQLINT 0 4 "" 1 numero_llamada ""
2 SQLNCHAR 0 1 "" 2 operario
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 1 "" 3 cabina
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 21 "" 4 numero_marcado
SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 11 "" 5 destino
SQL_Latin1_General_CP1_CI_AS
6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
""
7 SQLNCHAR 0 1 "" 7 minuto
SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 1 "" 8 hora
SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 1 "" 9 centesima_segundo
SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 1 "" 10 segundos
SQL_Latin1_General_CP1_CI_AS
11 SQLINT 0 4 "" 11 duracion_segundos
""
12 SQLINT 0 4 "" 12 costo_centavos
""
13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 3 "" 14 fin
SQL_Latin1_General_CP1_CI_AS
now my problem is that the INT and SMALLINT get into the table where
they should be, but the character fields just show "NULL" and some
fields just show squares and the last character on that string, any
ideas?
Thank you(kibagami23@.gmail.com) writes:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
...
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
Is the file a binary file or a text file? Since the integers make it,
I assume that it is a binary file.
Really what a fixed-length delimitted file is I don't know. Judging
from your format file, your file is fixed-width only.
One possibility that the character column has length-specifiers. In
such case you should specify the length of these specifiers in the
third column in the BCP file.
For more substantial input, and less speculation, please post CREATE TABLE
statement for the table, and a sample input file. (preferably enclosed
in a zip archive as attachment.)
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|||Thank you for the reply, yes the file is Fixed width, and i am putting
the length of the specifiers in the third column of the format file,
the file is a binary file created from a thrid party software developed
in Borland C Builder , i'll post what you ask for later today, any
other ideas in the meantime?
Thank you|||I suggest a row terminator on your last column (column 14). Rather than "",
specify "\r\n".
Is bcp importing all the rows? (i.e. Input file has 1000 records and your
table has 1000 rows.)
Hope that helps,
Joe
"kibagami23@.gmail.com" wrote:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
> 2 SQLNCHAR 0 1 "" 2 operario
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 1 "" 3 cabina
> SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 21 "" 4 numero_marcado
> SQL_Latin1_General_CP1_CI_AS
> 5 SQLNCHAR 0 11 "" 5 destino
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
> ""
> 7 SQLNCHAR 0 1 "" 7 minuto
> SQL_Latin1_General_CP1_CI_AS
> 8 SQLNCHAR 0 1 "" 8 hora
> SQL_Latin1_General_CP1_CI_AS
> 9 SQLNCHAR 0 1 "" 9 centesima_segundo
> SQL_Latin1_General_CP1_CI_AS
> 10 SQLNCHAR 0 1 "" 10 segundos
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLINT 0 4 "" 11 duracion_segundos
> ""
> 12 SQLINT 0 4 "" 12 costo_centavos
> ""
> 13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
> SQL_Latin1_General_CP1_CI_AS
> 14 SQLNCHAR 0 3 "" 14 fin
> SQL_Latin1_General_CP1_CI_AS
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
> Thank you
>|||SSd2ZSBhbHJlYWR5IHRyaWVkIHB1dHRpbmcgdGhl
IHJvdyB0ZXJtaW5hdG9yIGFuZCBpdCBrZWVw
cyBkb2luZyB0aGUKc2FtZSwgYW5kIHllcyB0aGUg
YmNwIGltcG9ydHMgYWxsIG9mIHRoZSByb3dz
LCBidXQgaW4gdGhlIHNhbWUgd2F5IG9ubHkKdGhl
IGludGVnZXIgZmllbGRzIHNob3cgb24gdGhl
IHNxbCB0YWJsZSB0aGUgY2hhcmFjdGVyIGZpZWxk
cyBzaG93cwpOVUxMIGFuZCBzb21lIG90aGVy
cyBzaG93IHNxdWFyZXMgd2l0aCB0aGUgbGFzdCBj
aGFyYWN0ZXIgb2YgdGhhdApmaWVsZHMgc2hv
d2luZywgbGlrZSB0aGlzOgoKNDQ0NDIgTlVMTCBO
VUxMCuOQsOOYtOOQtuOkseOcs+OMsDUJ5JWD
5ZWM5IWMUgk0CU5VTEwJTlVMTAlOVUxMCU5VTEwJ
ODQJNzAwCU5VTEwJTlVMTAo0NDQ0MwlOVUxM
CU5VTEwJ44C246C545i1MQnkvYzkhYNMCTUJTlVM
TAlOVUxMCU5VTEwJTlVMTAkzNAkyMDAJTlVM
TAlOVUxMCjQ0NDQ0CU5VTEwJTlVMTAnjgLbjkLnj
hLEzCeS9jOSFg0wJNQlOVUxMCU5VTEwJTlVM
TAlOVUxMCTE0MTE3CTUxMjAwCU5VTEwJPwo0NDQ0
NQlOVUxMCU5VTEwJ46C245Sw45C3MgnkvYzk
hYNMCTUJTlVMTAlOVUxMCU5VTEwJTlVMTAkyNgky
MDAJTlVMTAlOVUxMCjQ0NDQ2CU5VTEwJTlVM
TAnjoLbjpLbjgLY1CeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTI0NTg2CTc2ODAwCU5V
TEwJPwo0NDQ0NwlOVUxMCU5VTEwJ46C245S246Sw
MwnkvYzkhYNMCTUJTlVMTAlOVUxMCU5VTEwJ
TlVMTAk4NjI4NgkxNzkyMDAJTlVMTAk/ CjQ0NDQ4CU5VTEwJTlVMTAnjiLbjpLLjhLM5CeS9
jOSF
g0wJNQlOVUxMCU5VTEwJTlVMTAlOVUxMCTg1MDcJ
NTEyMDAJTlVMTAk/CjQ0NDQ5CU5VTEwJTlVM
TAnjoLbjoLLjkLkxCeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTcyMDQJNTEyMDAJTlVM
TAk/ CjQ0NDUwCU5VTEwJTlVMTAnjkLDjmLTjkLbjoLLj
pLPjnLM5CeSVg+WVjOSFjFIJNAlOVUxM
CU5VTEwJTlVMTAlOVUxMCTU4OTYJMTAyNDAwCU5V
TEwJPwoKVGhhbmsgeW91Cg==|||I forgot to say that the last sample is the select from the destination
sql table|||For some reason I can't see your entire thread, but my first suggestion is
for you to view the file with a hex editor to see whether the file contains
what you expect it to.
A very odd thing is that your format file indicates that you have
1-character
SQLNCHAR columns called minuto, hora, segundos, and centesima_segundos.
How can these be represented with a single character? It seems much
more likely to me that these would be represented as unsigned 1-byte
integers
(SQLTINYINT) or other numeric data type.
Things to check with the hex editor are: is the string data actually stored
as Unicode? Is the string data terminated with char(0), or does it
contain non-printable characters (<= 001F Unicode)? (This could
cause problems, I think.)
Posting the CREATE TABLE statement for the destination table would
help.
Steve Kass
Drew University
Kibagami23 wrote:
>I forgot to say that the last sample is the select from the destination
>sql table
>
>
BCP Converting character ° to ¦
A BCP process is converting a degree character (°) to a pipe character (¦).
This is the only conversion problem that has been identified. Is there a
method to correct this?Try adding -C RAW' to your BCP command. This will prevent code page
conversion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KingRo" <KingRo@.discussions.microsoft.com> wrote in message
news:A0D44DC3-915F-45B8-9BD1-EF16DCA4865D@.microsoft.com...
>A BCP process is converting a degree character (°) to a pipe character (¦).
> This is the only conversion problem that has been identified. Is there a
> method to correct this?
This is the only conversion problem that has been identified. Is there a
method to correct this?Try adding -C RAW' to your BCP command. This will prevent code page
conversion.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KingRo" <KingRo@.discussions.microsoft.com> wrote in message
news:A0D44DC3-915F-45B8-9BD1-EF16DCA4865D@.microsoft.com...
>A BCP process is converting a degree character (°) to a pipe character (¦).
> This is the only conversion problem that has been identified. Is there a
> method to correct this?
bcp connection problems
Well this one is puzzling me, and there is probably a very simple answer to this but coming from a linux/MySQL background to a windows/MSSQL system I can't figure it out.
Anyways I run bcp with my queryout string which is
bcp "SELECT * FROM edrdata.dbo.tblcomments WHERE siteid=-1" queryout "C:\DKD\" -c -U <username> -P <password> -S WELLDAQ\DBINFO
but I get an ODBC connection error, see below :
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()).
oh and SQL Server Service Manager shows the Server as WELLDAQ\DBINFO and it is running and I can connect to it through other means such as my visual basic applications I'm coding and through Visual Studio 2005 (remotely) so why can't bcp connect to it?
pls check your your surface area configuration if
|||1. the server is running in mixed mode
2. the server is configured to accept both local and remote connection
Make sure there are no spaces between the -U and the username. Same for -P and -S
Also, specify a filename as well as the directory path and put the -S parameter before the -U and -P
Hope this helps.
BCP connection problem
Hi,
I am trying to use BCP for bulk importing data into the SQL-server. I
created a system DSN that works fine with MS-Query, but bcp keeps returning
an error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Can anybody tell me what's wrong here (or offer an alternative for bulk
importing data)?
thanks in advance, Hans
Hans,
What is the full command that you are using to bcp in your data? There
may be a problem with the bcp syntax that you are using and not with the
DSN.
Thanks,
JD
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:B0315ACB-43DF-4663-B9C1-9460BA7208DE@.microsoft.com...
> Hi,
> I am trying to use BCP for bulk importing data into the SQL-server. I
> created a system DSN that works fine with MS-Query, but bcp keeps
returning
> an error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Can anybody tell me what's wrong here (or offer an alternative for bulk
> importing data)?
> thanks in advance, Hans
I am trying to use BCP for bulk importing data into the SQL-server. I
created a system DSN that works fine with MS-Query, but bcp keeps returning
an error:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or access denied.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Can anybody tell me what's wrong here (or offer an alternative for bulk
importing data)?
thanks in advance, Hans
Hans,
What is the full command that you are using to bcp in your data? There
may be a problem with the bcp syntax that you are using and not with the
DSN.
Thanks,
JD
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:B0315ACB-43DF-4663-B9C1-9460BA7208DE@.microsoft.com...
> Hi,
> I am trying to use BCP for bulk importing data into the SQL-server. I
> created a system DSN that works fine with MS-Query, but bcp keeps
returning
> an error:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
> exist or access denied.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Can anybody tell me what's wrong here (or offer an alternative for bulk
> importing data)?
> thanks in advance, Hans
BCP command with arguments having special chars
Hi all,
The command is as follows:
BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n
-Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
Here, since the first argument(database name) has a special char '+' in
it, the command gives a syntax error. How can i escape it? Double quotes"",
[] didnt work!! Pls help!
TIAYour thread in microsoft.public.sqlserver.programming on this subject is
still active. See my response.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sundar" <sundar.jammy@.gmail.com> wrote in message
news:b4d47b8e.0411040547.7c8ed064@.posting.google.c om...
> Hi all,
> The command is as follows:
> BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n
> -Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
> Here, since the first argument(database name) has a special char '+' in
> it, the command gives a syntax error. How can i escape it? Double
> quotes"",
> [] didnt work!! Pls help!
> TIA
bcp command to give colums
Can someonte tell me that if i
bcp bda..mytable out c:\discounts.xls -c -p , how can I put the first row as my column names since I get only dataIt won't...but there are other ways aroung it..you can use query out and supply a union like
SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99
Just need to make sure you're dfatatypes are converted to varchar
What about DTS to an EXCEL, or csv?|||Thanks very much it worked like magi
Originally posted by Brett Kaiser
It won't...but there are other ways aroung it..you can use query out and supply a union like
SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99
Just need to make sure you're dfatatypes are converted to varchar
What about DTS to an EXCEL, or csv?
bcp bda..mytable out c:\discounts.xls -c -p , how can I put the first row as my column names since I get only dataIt won't...but there are other ways aroung it..you can use query out and supply a union like
SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99
Just need to make sure you're dfatatypes are converted to varchar
What about DTS to an EXCEL, or csv?|||Thanks very much it worked like magi
Originally posted by Brett Kaiser
It won't...but there are other ways aroung it..you can use query out and supply a union like
SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99
Just need to make sure you're dfatatypes are converted to varchar
What about DTS to an EXCEL, or csv?
BCP command Issue
Hi all,
The command is as follows:
BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n
-Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
Here, since the first argument(database name) has a special char '+' in
it, the command gives a syntax error. How can i escape it? Double
quotes"",
[] didnt work!! Pls help!
TIA
Here is the way to go:
Run it via script file. I tried it and it worked.
Exec Master..xp_cmdshell "bcp test+3test.dbo.t1 out
E:\temp\t1.txt -t""|"" -r\n -SNYDAC1 -Unyloadtest -Pnyloadtest -eE:\Temp\err
or.txt"
Watch for double quotes embedded inside the double "".
"Sundar" <Sundar@.discussions.microsoft.com> wrote in message
news:8B60DCD7-C961-4FEC-9795-14DE1FC91C5B@.microsoft.com...
> Hi all,
> The command is as follows:
> BCP asdos+_reports..StageCONTACTINFO OUT
C:\16E8.tmp -w -b10000 -t"|" -r\n
> -Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
> Here, since the first argument(database name) has a special char '+' in
> it, the command gives a syntax error. How can i escape it? Double
> quotes"",
> [] didnt work!! Pls help!
> TIA
>
The command is as follows:
BCP asdos+_reports..StageCONTACTINFO OUT C:\16E8.tmp -w -b10000 -t"|" -r\n
-Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
Here, since the first argument(database name) has a special char '+' in
it, the command gives a syntax error. How can i escape it? Double
quotes"",
[] didnt work!! Pls help!
TIA
Here is the way to go:
Run it via script file. I tried it and it worked.
Exec Master..xp_cmdshell "bcp test+3test.dbo.t1 out
E:\temp\t1.txt -t""|"" -r\n -SNYDAC1 -Unyloadtest -Pnyloadtest -eE:\Temp\err
or.txt"
Watch for double quotes embedded inside the double "".
"Sundar" <Sundar@.discussions.microsoft.com> wrote in message
news:8B60DCD7-C961-4FEC-9795-14DE1FC91C5B@.microsoft.com...
> Hi all,
> The command is as follows:
> BCP asdos+_reports..StageCONTACTINFO OUT
C:\16E8.tmp -w -b10000 -t"|" -r\n
> -Sblrkec28791d\sqlserver -Uadmin -Padmin -eC:\ERRCONTACTINFO.txt
> Here, since the first argument(database name) has a special char '+' in
> it, the command gives a syntax error. How can i escape it? Double
> quotes"",
> [] didnt work!! Pls help!
> TIA
>
BCP Command - Login fails
Dear all,
I have a curious problem using BCP to import and export data. First of all I export data as XML file with the command:
bcp "SELECT * FROM [006]..hopibon FOR XML EXPLICIT" queryout c:\ortec\misc\data.xml -c -r "" -SMMOSSURPBM09 –T
Now this works fine. The table data is exported as data.xml. I use the exported data in an external tool and then the result of that is a csv file. This file needs to be imported again in the SQL database, so I can run some queries. I use the following commandline;
bcp "[606]..ORTECPlanning" IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T
When I use this command I get the following error;
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[606]’. Login fails.
I tested the following:
1) Run the same command on local PC (SQL Installed) and then no problem at all. So the command seems to be correct.
2) Then I tried to use the –U –P parameters instead of –T. Then the error message changes and states login failed…
3) Changed the command to bcp "[606]..ORTECPlanning" IN "c:\ortec\misc\planning.csv" -c -F1 -t; -S"MMOSSURPBM09" –T
4) Changed the command to bcp [MMOSSURPBM09].[606]..ORTECPlanning IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T
5) Remove –t; (suggestion) but this is causing the same error on the server and causing a new error unexpected EOF on the local machine
But no luck… I’m using the administrator to login and perform the command. Exactly the same user and situation as in the queryout command…. So then the question is what is wrong with the command;
bcp [606]..ORTECPlanning IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T
bcp "SELECT * FROM [006]..hopibon FOR XML EXPLICIT" queryout c:\ortec\misc\data.xml -c -r "" -SMMOSSURPBM09 –T
Now this works fine
I notice that the commands that fail seem to be hitting a different server ([606]) -NOT [006].
bcp "[606]..ORTECPlanning" IN c:\ortec\misc\planning.csv -c -F1 -t; -SMMOSSURPBM09 –T
When I use this command I get the following error;
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login ‘[606]’. Login fails.
Did I mis-read that?
bcp command
I am using the bcp command to export a bulk text file into the database,
bcp elearning.dbo.BulkData in mobile.txt -c -t, -SZOHL-02 -Usa -P1234567890 -E
I have 6 fields in the table to which i am exporting data.
One field is numeric and i have to set the identity to yes,
It gives me an error string data trucncated. When i remove the identity field, i am able to export data.So, how do i tackle this prob?
I used the -E attribute to keep the identity .But still i get the error. The text file has comma seperated fields.
I am using sql server 2000
You have to import your data to a temp table or an intermediate table before moving the data to the table with IDENTITY because a text file will appear to have NULL primary key and IDENTITY which is a primary key cannot be NULL. And no it is not a bug but just the nature of a text file compared to tabular data in a database. Hope this helps.
bcp command
Hi all,
I am trying to run a .bat file with this bcp command.
BCP "database.dbo.state" OUT "C:\TEMP\state.dat" -SServerName -U"userid" -P"password" -m1 -n -a65536 -E -q
However, it is not producing me a file as I expected.
Is there any other configuration I need to set before it work?
Any help would appreciated.What error messages are being produced? I don't think I've seen BCP fail silently in years. Have you ever done a successful BCP against that SQL Server from the same client maching?
-PatP|||The error is: "Error in attempting to load a pair of translation tables."|||The error is: "Error in attempting to load a pair of translation tables."
Excuse me?
Is it not creating a file, or not creating a file as "expected'
Because if it's the latter and you expect to "see" data, you won't. It's in native format.
You need to lose the -n and use -c|||The error is: "Error in attempting to load a pair of translation tables."That is definitely not an error message generated by BCP. What other programs are you running that might have generated that message?
-PatP
I am trying to run a .bat file with this bcp command.
BCP "database.dbo.state" OUT "C:\TEMP\state.dat" -SServerName -U"userid" -P"password" -m1 -n -a65536 -E -q
However, it is not producing me a file as I expected.
Is there any other configuration I need to set before it work?
Any help would appreciated.What error messages are being produced? I don't think I've seen BCP fail silently in years. Have you ever done a successful BCP against that SQL Server from the same client maching?
-PatP|||The error is: "Error in attempting to load a pair of translation tables."|||The error is: "Error in attempting to load a pair of translation tables."
Excuse me?
Is it not creating a file, or not creating a file as "expected'
Because if it's the latter and you expect to "see" data, you won't. It's in native format.
You need to lose the -n and use -c|||The error is: "Error in attempting to load a pair of translation tables."That is definitely not an error message generated by BCP. What other programs are you running that might have generated that message?
-PatP
bcp command
if i use:
--code--
DECLARE @.CMD VARCHAR(8000)
SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' +
@.@.servername + '" -T'
exec master..xp_cmdshell @.CMD
--end code--
how could i put the column headers in this spreadsheet?
or
is there a better way of getting a sql statement to save directly to excel?Have a look here:
76c9997" target="_blank">http://groups.google.de/group/micro...
76c9997
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"UNOTech" <UNOTech@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CDA8B4C3-3CB1-418A-A649-B928CB8FB275@.microsoft.com...
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
> +
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to
> excel?|||I'd advise to do it directly from Excel using the menu choices in Excel. go
to Data...Get Extranal Data...New Database Query... It is very clean. You
will need to set up an ODBC connection to do this.
"UNOTech" wrote:
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
+
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to excel?[/color
]
--code--
DECLARE @.CMD VARCHAR(8000)
SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"' +
@.@.servername + '" -T'
exec master..xp_cmdshell @.CMD
--end code--
how could i put the column headers in this spreadsheet?
or
is there a better way of getting a sql statement to save directly to excel?Have a look here:
76c9997" target="_blank">http://groups.google.de/group/micro...
76c9997
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"UNOTech" <UNOTech@.discussions.microsoft.com> schrieb im Newsbeitrag
news:CDA8B4C3-3CB1-418A-A649-B928CB8FB275@.microsoft.com...
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
> +
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to
> excel?|||I'd advise to do it directly from Excel using the menu choices in Excel. go
to Data...Get Extranal Data...New Database Query... It is very clean. You
will need to set up an ODBC connection to do this.
"UNOTech" wrote:
> if i use:
> --code--
> DECLARE @.CMD VARCHAR(8000)
> SET @.CMD = 'bcp "select * from TABLENAME" queryout C:\testfile.xls -c -S"'
+
> @.@.servername + '" -T'
> exec master..xp_cmdshell @.CMD
> --end code--
> how could i put the column headers in this spreadsheet?
> or
> is there a better way of getting a sql statement to save directly to excel?[/color
]
bcp column names
I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
Thanks
Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
Thanks
Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>
bcp column names
I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassword
> The command works beautifully except that it overwrites the header column of
> the excel file as well. Is there anyway in bcp to have the results be written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>
bcp column names
I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks
>
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:
> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks
>
BCP column insert question
HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:
Text file data:
1234 abc def ghi jkl mno
Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N
My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?
Appreciated any help.
Thanks,
TeresaTeresa,
Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.
Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.
Here is an example from the Transact SQL online:
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!
Barry
"TThai" <tpthai@.pepco.com> wrote in message
news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> HI,
> I'm trying to insert records to a table using bcp command. The
> problem is the input file to the bcp is a text file that looks like
> this:
> Text file data:
> 1234 abc def ghi jkl mno
> Expected result:
> column1 1234
> column2 abc def ghi jkl mno
> column3 null
> column4 N
> My table has four columns (column1, column2, column3, column4). I
> would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> column3 is blank and column4 is always 'N' as shown above. I setup the
> column4 to be defaulted to 'N'. However, I'm getting an error
> regarding string truncation because, I think, SQL server is trying to
> insert the 'abc def ghi jkl mno' into different columns. So the
> question is what can I do to tell the bcp utility that 'abc def ghi
> jkl mno' belongs to column2?
> Appreciated any help.
> Thanks,
> Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa
Hi Barry,
Appreciated your response. I'll try it and keep you posted. Have a good day.
Thanks,
Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.
BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')
Thanks,
Teresa
> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa|||TThai (tpthai@.pepco.com) writes:
> Hi Barry,
> I just tried and am getting an error 'Server: Msg 4860, Level 16,
> State 1, Line 1
> Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> there any preliminary setup that I have to do to recognize the file?
> Here is what I executed in SQL analyzer.
> BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> FROM 'C:\transactions.txt'
> with
> (FIELDTERMINATOR = '|')
BULK INSERT operates on the server, so it is looking a C:\ at your server.
If your file is on a client machine, you are better off with BCP. You
can specify field terminator with the -t options. Since | is a meta-
character for the command shell, you need to quote it:
bcp db..tbl in yourfile.txt -c -t "|" -S ...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns958BF277E1611Yazorman@.127.0.0.1>...
> TThai (tpthai@.pepco.com) writes:
> > Hi Barry,
> > I just tried and am getting an error 'Server: Msg 4860, Level 16,
> > State 1, Line 1
> > Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> > there any preliminary setup that I have to do to recognize the file?
> > Here is what I executed in SQL analyzer.
> > BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> > FROM 'C:\transactions.txt'
> > with
> > (FIELDTERMINATOR = '|')
> BULK INSERT operates on the server, so it is looking a C:\ at your server.
> If your file is on a client machine, you are better off with BCP. You
> can specify field terminator with the -t options. Since | is a meta-
> character for the command shell, you need to quote it:
> bcp db..tbl in yourfile.txt -c -t "|" -S ...
Thank you very much. It worked!
Teresa
BCP character conversion
I bcped in data into a database with SQL_Latin1_General_Cp1_CI_AS collation. The input data has an embedded character (ascii 174). I did not specify any code page using the -C parm. The data was converted to character (ascii 171). I ran the bcp trying -C1252 and -CRAW and both maintained the correct character. -C437 and -COEM change the character to .
Why did this happen? I thought that data would be converted to correctly without any code page specification.Different code pages map binary values to glyphs (the graphic symbols that humans know and love) differently. One binary value can map to many different glyphs using different code pages.
If BPC doesn't know which code page to use for translation, you get "pot luck", especially for characters that aren't well defined. Typically, you want the code page that created the data. Occaisionally, you want the code page that was intended (or at least used) to view the data. Because of the pot-pouri of mappings supported by the different code pages, the business of getting data from point A to point B has grown yet another potentially "interesting" twist to amuse those of us that do the moving!
-PatP|||Thanks for the quick reply.
BOL states:
"When bulk copying data using native or character format, bcp, by default, converts character data to:
OEM code page characters when exporting data from an instance of Microsoft SQL Server.
ANSI/Microsoft Windows code page characters when importing data into an instance of SQL Server. "
So wouldn't the bcp in use code page 1252 by default. This should be similar to -C1252.|||Well where did the data come from?|||The bcp ran from my workstation with a code page 437 - if that's your question.|||From Books Online topic bcp: "OEM Default code page used by the client. This is the default code page used by bcp if -C is not specified."
From that I suppose that SQL Server interpreted your file as being OEM 437 CP. mojza|||I guess I still don't understand why the character was changed during the bcp. I can view it correctly from my workstation which is 437, but if I bcp using -C437 or without -C(which uses default OEM code page) it gets converted. I think I'm missing something.|||In what editor can you see that character correctly? in ANSI (e.g.Notepad) or in OEM (e.g.Edit)? mojza|||Correctly in notepad or textpad, not correctly in edit. So bcp, running in a command window, is using 437 which changes the character to ?|||Then, in my opinion, your file was created in code page ANSI 1252 (notepad ok) and bcp interprets your file as cp 437 (default client OEM code page). That leads to a loss of some extended characters that are not compatible between these two pages unless you tell sql server to interpret him as 1252 or without any translation (RAW). Check out this Microsoft article. There is a good explanation and excellent examples. mojza
http://support.microsoft.com/default.aspx?scid=kb;en-us;199819|||Thanks for your help. That article definitely helped explain things. I also looked at the nls files for 437 and 1252 and character 174(offset x0178) reflects in the 1252 file and int 437 file.
Again, thanks.
Why did this happen? I thought that data would be converted to correctly without any code page specification.Different code pages map binary values to glyphs (the graphic symbols that humans know and love) differently. One binary value can map to many different glyphs using different code pages.
If BPC doesn't know which code page to use for translation, you get "pot luck", especially for characters that aren't well defined. Typically, you want the code page that created the data. Occaisionally, you want the code page that was intended (or at least used) to view the data. Because of the pot-pouri of mappings supported by the different code pages, the business of getting data from point A to point B has grown yet another potentially "interesting" twist to amuse those of us that do the moving!
-PatP|||Thanks for the quick reply.
BOL states:
"When bulk copying data using native or character format, bcp, by default, converts character data to:
OEM code page characters when exporting data from an instance of Microsoft SQL Server.
ANSI/Microsoft Windows code page characters when importing data into an instance of SQL Server. "
So wouldn't the bcp in use code page 1252 by default. This should be similar to -C1252.|||Well where did the data come from?|||The bcp ran from my workstation with a code page 437 - if that's your question.|||From Books Online topic bcp: "OEM Default code page used by the client. This is the default code page used by bcp if -C is not specified."
From that I suppose that SQL Server interpreted your file as being OEM 437 CP. mojza|||I guess I still don't understand why the character was changed during the bcp. I can view it correctly from my workstation which is 437, but if I bcp using -C437 or without -C(which uses default OEM code page) it gets converted. I think I'm missing something.|||In what editor can you see that character correctly? in ANSI (e.g.Notepad) or in OEM (e.g.Edit)? mojza|||Correctly in notepad or textpad, not correctly in edit. So bcp, running in a command window, is using 437 which changes the character to ?|||Then, in my opinion, your file was created in code page ANSI 1252 (notepad ok) and bcp interprets your file as cp 437 (default client OEM code page). That leads to a loss of some extended characters that are not compatible between these two pages unless you tell sql server to interpret him as 1252 or without any translation (RAW). Check out this Microsoft article. There is a good explanation and excellent examples. mojza
http://support.microsoft.com/default.aspx?scid=kb;en-us;199819|||Thanks for your help. That article definitely helped explain things. I also looked at the nls files for 437 and 1252 and character 174(offset x0178) reflects in the 1252 file and int 437 file.
Again, thanks.
BCP Cannot open database requested in login
I have several DB's that I am trying to bulk load with data using BCP
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?
Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first portion
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?
Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first portion
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>
BCP Cannot open database requested in login
I have several DB's that I am trying to bulk load with data using BCP
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot op
en database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first porti
on
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot op
en database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first porti
on
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot
open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>
BCP Cannot open database requested in login
I have several DB's that I am trying to bulk load with data using BCP
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first portion
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>
The problem is that all of them contain a decimal in the DB name.
When BCP runs it gets confused and tries to log on to just the first portion
of the database
For example with a DB named test.server I get the following error
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'test'. Login fails.
Is this just a limitation of BCP or is there some sort of work around?Scott,
Quote it.
c:\>bcp "[my_db_with_._etc].dbo.t1" out "c:\temp\test.txt" ...
AMB
"Scott B" wrote:
> I have several DB's that I am trying to bulk load with data using BCP
> The problem is that all of them contain a decimal in the DB name.
> When BCP runs it gets confused and tries to log on to just the first portion
> of the database
> For example with a DB named test.server I get the following error
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'test'. Login fails.
> Is this just a limitation of BCP or is there some sort of work around?
>
>
BCP call to stored procedure - broke during upgrade from SQL 7.0 to 2000
I have this stored procedure that takes a few parameters like date and
merchant ID, and basically goes through a set of if-then statements to build
a SQL SELECT string.
merchant ID, and basically goes through a set of if-then statements to build
a SQL SELECT string.
When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
worked from Query Analyzer, but not in BCP. It used to work in BCP just
fine with 7.0. The error I get now is:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
at least one column
What's really strange is, if I instruct the stored procedure to simply print
the SELECT string, then cut-and-paste it into the end of stored procedure
code (assigning it to the variable that already contains the SELECT string),
then it works from BCP.
Any help would be greatly appreciated.
AstonAston (alau@.selera.com) writes:
> I have this stored procedure that takes a few parameters like date and
> merchant ID, and basically goes through a set of if-then statements to
> build a SQL SELECT string.
> When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
> worked from Query Analyzer, but not in BCP. It used to work in BCP just
> fine with 7.0. The error I get now is:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
> at least one column
> What's really strange is, if I instruct the stored procedure to simply
> print the SELECT string, then cut-and-paste it into the end of stored
> procedure code (assigning it to the variable that already contains the
> SELECT string), then it works from BCP.
If I understand this right you are doing something like:
bcp "exec some_db..some_sp" queryout datafile.bcp -c -T
To find out what columns there are in the query, bcp first submits the
query with SET FMTONLY ON. This command is causes SQL Server to not execute
the statements in the procedure, but return data about any result sets
it finds. However, if you produces a dynamic SQL string and executes it,
there not be anyting executed with FMTONLY ON, and BCP will not find any
result set.
Why this worked in SQL 7, I don't know. (I never worked much with SQL 7,
jumped direct to SQL 2000 from 6.5.)
--
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 call to import data from computer other than server is this possible?
Hi,
I have written an application which is being used by only 4 people on different computers. The one with SQL Server installed is comp0, then comp1, .... comp4.
Everything was working fine until today when the only user who is authorised (by my application) to perform the data import of BCP files, tried to import the latest BCP files. I am making my bcp command line string and then calling it using the VB Shell call.
If I run the import option from my app on the server it works fine. So at first I thought, ah, the BCP files won't be in the other computers path. I've copied BCP.EXE and BCP.rll but it still doesn't work.
Is it even possible to run BCP from a computer other than the one with SQL Server installed? I really need the import to work on a computer other than the server.
Thanks in advance,
Paul
Hi Paul,
What is the bcp command you are tyring to call exactly?
Thanks
Cris
|||Hi Cris,
The calls is as follows:
strBCPCommand = "bcp database_name.schema." & _
table_name & _
" in " & _
full_path_of_bcp file & _
" -n -T -Sserver_name\sql -V65"
It all works fine when called from the application or command line on the server.
Thanks,
Paul
bcp bulk upload
Hello !!
Can anyone help, I am trying to do bulk upload:
EG.
set @.BULK = 'bcp "DQ_Central.dbo.tbl_CLI_UPLOAD" in "' + @.file_path + '" -S' + @.Server + ' -U' + @.User + ' -P' + @.PSW
and am getting this output message
Enter the file storage type of field service_number [char]:
Any one have any ideas ?!? Thanks u for yr helpyou forgot to specify the input file type, - character or native (-c or -n).
but you, you can do the same thing with bulk insert. this way you won't have to through your @.BULK at xp_cmdshell and get completely frustrated with char(39).
Can anyone help, I am trying to do bulk upload:
EG.
set @.BULK = 'bcp "DQ_Central.dbo.tbl_CLI_UPLOAD" in "' + @.file_path + '" -S' + @.Server + ' -U' + @.User + ' -P' + @.PSW
and am getting this output message
Enter the file storage type of field service_number [char]:
Any one have any ideas ?!? Thanks u for yr helpyou forgot to specify the input file type, - character or native (-c or -n).
but you, you can do the same thing with bulk insert. this way you won't have to through your @.BULK at xp_cmdshell and get completely frustrated with char(39).
BCP blank header line
I am using BCP to export the contents of a view into a text file. Everything is running jsut ifne, except the resulting file has a blank first line. Is there a way to preven this?
BCP statement is as follows:
bcp "select pospay from son_db.dbo.vw_pos_pay order by account desc, code asc" queryout D:\elite\USbank\PositivePay_Current\x340.d150364i. d100.txt -T -c
Thanks in advance for any help!probably the first row is a NULL. is the pospay column nullable? if so you can add a where clause to your query to omit the null.|||You can take help of :-F first_row Specifies the number of the first row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. In the absence of this parameter, the default is the first row of the file.|||that only matters for importing data, Satya. the OP is exporting.|||Jezemine
Look at the BOL entry I've pasted there: Specifies the number of the first row to export from a table or import from a data file
BCP statement is as follows:
bcp "select pospay from son_db.dbo.vw_pos_pay order by account desc, code asc" queryout D:\elite\USbank\PositivePay_Current\x340.d150364i. d100.txt -T -c
Thanks in advance for any help!probably the first row is a NULL. is the pospay column nullable? if so you can add a where clause to your query to omit the null.|||You can take help of :-F first_row Specifies the number of the first row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. In the absence of this parameter, the default is the first row of the file.|||that only matters for importing data, Satya. the OP is exporting.|||Jezemine
Look at the BOL entry I've pasted there: Specifies the number of the first row to export from a table or import from a data file
BCP between two different servers
How would you go about bcp two tables from 2000 on server a and bcping it to
6.5 on server b?
New SQL Server DBA
BCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
6.5 on server b?
New SQL Server DBA
BCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
BCP between two different servers
How would you go about bcp two tables from 2000 on server a and bcping it to
6.5 on server b?
--
New SQL Server DBABCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
6.5 on server b?
--
New SQL Server DBABCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
BCP between two different servers
How would you go about bcp two tables from 2000 on server a and bcping it to
6.5 on server b?
--
New SQL Server DBABCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
6.5 on server b?
--
New SQL Server DBABCP (OUT) to a file - BCP (IN) to the table on 6.5 See BCP in the SQL BOL
for more info on the syntax or run BCP /? from a CMD prompt.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA|||Hi,
If both the servers are accessible in network then go for DTS from SQL 2000
machine.
Select Source as SQL 2000 and destination as SQL 6.5 in DTS.
If both the servers are not accessible each other then:-
1. BCP OUT from SQL 2000 (See to that all the data column width matches. Eg:
SQL 6.5 Varchar and Char only support 255 characters).
2. BCP IN into SQL 6.5
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:05E71CDA-B47C-4543-90E1-83EA6E084F3F@.microsoft.com...
> How would you go about bcp two tables from 2000 on server a and bcping it
> to
> 6.5 on server b?
> --
> New SQL Server DBA
BCP Beginner - Please Help
Hi,
I am completely new to the BCP utility and fairly new to SQL Server
I am learning from a book and I am trying the following example (the server
I'm learning on is called contractor and a password has not been give to the
sa)
bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor
When I run this in Query Analyser i get the error message..
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
I have tried puuting quotes around the database and the table name as
follows-
bcp "pubs..authors" out authors.txt -C -r \n -t, -U sa -P -S contractor
and get the error
Server: Msg 179, Level 15, State 1, Line 1
Cannot use the OUTPUT option when passing a constant to a stored procedure.
Please can anybody help me to get this first bit working?
Thanks in anticipation.In your bcp statement, is the -C supposed to indicate that it is a
character file? If so, it needs to be lowercase (-c), otherwise it
indicates the code page, which expects a value. Also, I don't know if
this makes a difference, but I've never used spaces between the options
and the values (for example, -Usa instead of -U sa). I thought I
remembered both working, but in the help I didn't see anything about
the space being allowed and the samples given do not have spaces.
HTH,
-Tom.|||Stevie D (Steve@.127.0.0.1) writes:
> I am completely new to the BCP utility and fairly new to SQL Server
> I am learning from a book and I am trying the following example (the
> server I'm learning on is called contractor and a password has not been
> give to the sa)
> bcp pubs..authors out authors.txt -C -r \n -t, -U sa -P -S contractor
> When I run this in Query Analyser i get the error message..
You don't BCP from Query Analyzer. You run it from a command-line
window.
And, as Thomas pointed out, you should use -c and not -C. The
spacing should be OK, though.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
(The reason you get this syntax error is that bcp is not an SQL
command. Everything that is an identifier, but not a command is
taken as a stored procedure command. Next token is pubs. When calling
stored procedures quotes around strings that follow identifier syntax
are optional. (This is to permit us to write "sp_help tbl", instead
of "EXEC sp_help 'tbl'".) When the dot comes there is no longer
any saver.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
BCP bcp is not recognized
Hi
I am a bit new to SQL Server so please help me
I am trying to run a simple task at a customer but it doesn't work.
bcp isn't recognized.
I'm running this:
exec xp_cmdshell 'bcp "SELECT top 10 * FROM pubs..authors " queryout "C:\Temp\hej.txt" -c -S [ServerName] -T'
and it givs me this errormess.
'bcp' is not recognized as an internal or external command,
It works fine for me when I'm running it on my computer.
Can anyone please inform me what I'm missing. I haven't got a clue.
BR
CiottiIs the folder where bcp.exe is in your path (system) ?|||Hai ciotti,
Where is the "hej.txt" file located ? In the server hard disk or in the client machine hard disk.
When u use xp_cmdshell extended stored procdure, the command will be executed in the server and not in the client machine.
You have said that it works fine in ur system. So did u executed 'bcp' from the command line or using xp_cmdshell ?
If ur machine is the server and u executed it in the command prompt, then fine, u have to include the path of "bcp" in the System PATH.
Hope this helps u...Do let us know what happened.|||You were right. Someone had messed up the system path.
(Don't know who, and I dont care ;) )
Now the bcp command works.
The customer is happy and that makes me happy.
Thank you all for your quick answers.
/Ciotti
I am a bit new to SQL Server so please help me
I am trying to run a simple task at a customer but it doesn't work.
bcp isn't recognized.
I'm running this:
exec xp_cmdshell 'bcp "SELECT top 10 * FROM pubs..authors " queryout "C:\Temp\hej.txt" -c -S [ServerName] -T'
and it givs me this errormess.
'bcp' is not recognized as an internal or external command,
It works fine for me when I'm running it on my computer.
Can anyone please inform me what I'm missing. I haven't got a clue.
BR
CiottiIs the folder where bcp.exe is in your path (system) ?|||Hai ciotti,
Where is the "hej.txt" file located ? In the server hard disk or in the client machine hard disk.
When u use xp_cmdshell extended stored procdure, the command will be executed in the server and not in the client machine.
You have said that it works fine in ur system. So did u executed 'bcp' from the command line or using xp_cmdshell ?
If ur machine is the server and u executed it in the command prompt, then fine, u have to include the path of "bcp" in the System PATH.
Hope this helps u...Do let us know what happened.|||You were right. Someone had messed up the system path.
(Don't know who, and I dont care ;) )
Now the bcp command works.
The customer is happy and that makes me happy.
Thank you all for your quick answers.
/Ciotti
订阅：
博文 (Atom)