Bcp.exe on client machine

Hi,
We use BCP.EXE to bulk upload to SQL Server. However, some of our clients
use SQL Server while others use MSDE.
We need to have BCP.EXE available on each client machine.
In case of a MSDE server does this mean that each client machine needs the
full MSDE installed just to get bcp.exe?
In case of the full SQL Server server does this mean that each client
machine needs a client tool like Query Analyzer installed just to get
bcp.exe?
Can I run SQLREDIS.EXE on a client machine instead and then just copy the
BCP.EXE and BCP.RLL files from the server machine instead? I read the
licenses but it is still unclear to me if this is allowed.
I've Googled to try find a solution but things are still as clear as mud.
Thanks in advance for any help,
Jako GroblerJako Grobler wrote:
> Hi,
> We use BCP.EXE to bulk upload to SQL Server. However, some of our
> clients use SQL Server while others use MSDE.
> We need to have BCP.EXE available on each client machine.
> In case of a MSDE server does this mean that each client machine
> needs the full MSDE installed just to get bcp.exe?
> In case of the full SQL Server server does this mean that each client
> machine needs a client tool like Query Analyzer installed just to get
> bcp.exe?
> Can I run SQLREDIS.EXE on a client machine instead and then just copy
> the BCP.EXE and BCP.RLL files from the server machine instead? I read
> the licenses but it is still unclear to me if this is allowed.
> I've Googled to try find a solution but things are still as clear as
> mud.
> Thanks in advance for any help,
> Jako Grobler
I'd just install the client utilities on every client machine (unless it's
a server at the same time). Disk space is cheap and this is definitely
the most hassle free solution - apart from licensing maybe.
Kind regards
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in
news:ONhaOCncFHA.3120@.TK2MSFTNGP12.phx.gbl:

> I'd just install the client utilities on every client machine (unless
> it's a server at the same time). Disk space is cheap and this is
> definitely the most hassle free solution - apart from licensing maybe.
> Kind regards
> robert
The problem is that the client utilities option is not available with MSDE.
Also, some companies are not happy installing Query Analyzer or Enterprise
Manager on client machines.
SQLREDIS.EXE does not install BCP.EXE, I tried it already.
:-(
Jako|||Jako Grobler wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in
> news:ONhaOCncFHA.3120@.TK2MSFTNGP12.phx.gbl:
>
> The problem is that the client utilities option is not available with
> MSDE.
You can simply use SQL Server client utilities. They should happily
connect to an MSDE instance.

> Also, some companies are not happy installing Query Analyzer or
> Enterprise Manager on client machines.
Well then...

> SQLREDIS.EXE does not install BCP.EXE, I tried it already.
> :-(
> Jako
Kind regards
robert

Bcp.exe on client machine

Hi,
We use BCP.EXE to bulk upload to SQL Server. However, some of our clients
use SQL Server while others use MSDE.
We need to have BCP.EXE available on each client machine.
In case of a MSDE server does this mean that each client machine needs the
full MSDE installed just to get bcp.exe?
In case of the full SQL Server server does this mean that each client
machine needs a client tool like Query Analyzer installed just to get
bcp.exe?
Can I run SQLREDIS.EXE on a client machine instead and then just copy the
BCP.EXE and BCP.RLL files from the server machine instead? I read the
licenses but it is still unclear to me if this is allowed.
I've Googled to try find a solution but things are still as clear as mud.
Thanks in advance for any help,
Jako Grobler
Jako Grobler wrote:
> Hi,
> We use BCP.EXE to bulk upload to SQL Server. However, some of our
> clients use SQL Server while others use MSDE.
> We need to have BCP.EXE available on each client machine.
> In case of a MSDE server does this mean that each client machine
> needs the full MSDE installed just to get bcp.exe?
> In case of the full SQL Server server does this mean that each client
> machine needs a client tool like Query Analyzer installed just to get
> bcp.exe?
> Can I run SQLREDIS.EXE on a client machine instead and then just copy
> the BCP.EXE and BCP.RLL files from the server machine instead? I read
> the licenses but it is still unclear to me if this is allowed.
> I've Googled to try find a solution but things are still as clear as
> mud.
> Thanks in advance for any help,
> Jako Grobler
I'd just install the client utilities on every client machine (unless it's
a server at the same time). Disk space is cheap and this is definitely
the most hassle free solution - apart from licensing maybe.
Kind regards
robert
|||"Robert Klemme" <bob.news@.gmx.net> wrote in
news:ONhaOCncFHA.3120@.TK2MSFTNGP12.phx.gbl:

> I'd just install the client utilities on every client machine (unless
> it's a server at the same time). Disk space is cheap and this is
> definitely the most hassle free solution - apart from licensing maybe.
> Kind regards
> robert
The problem is that the client utilities option is not available with MSDE.
Also, some companies are not happy installing Query Analyzer or Enterprise
Manager on client machines.
SQLREDIS.EXE does not install BCP.EXE, I tried it already.
:-(
Jako
|||Jako Grobler wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in
> news:ONhaOCncFHA.3120@.TK2MSFTNGP12.phx.gbl:
>
> The problem is that the client utilities option is not available with
> MSDE.
You can simply use SQL Server client utilities. They should happily
connect to an MSDE instance.

> Also, some companies are not happy installing Query Analyzer or
> Enterprise Manager on client machines.
Well then...

> SQLREDIS.EXE does not install BCP.EXE, I tried it already.
> :-(
> Jako
Kind regards
robert

Bcp.exe on client machine

Hi,
We use BCP.EXE to bulk upload to SQL Server. However, some of our clients
use SQL Server while others use MSDE.
We need to have BCP.EXE available on each client machine.
In case of a MSDE server does this mean that each client machine needs the
full MSDE installed just to get bcp.exe?
In case of the full SQL Server server does this mean that each client
machine needs a client tool like Query Analyzer installed just to get
bcp.exe?
Can I run SQLREDIS.EXE on a client machine instead and then just copy the
BCP.EXE and BCP.RLL files from the server machine instead? I read the
licenses but it is still unclear to me if this is allowed.
I've Googled to try find a solution but things are still as clear as mud.
Thanks in advance for any help,
Jako GroblerJako Grobler wrote:
> Hi,
> We use BCP.EXE to bulk upload to SQL Server. However, some of our
> clients use SQL Server while others use MSDE.
> We need to have BCP.EXE available on each client machine.
> In case of a MSDE server does this mean that each client machine
> needs the full MSDE installed just to get bcp.exe?
> In case of the full SQL Server server does this mean that each client
> machine needs a client tool like Query Analyzer installed just to get
> bcp.exe?
> Can I run SQLREDIS.EXE on a client machine instead and then just copy
> the BCP.EXE and BCP.RLL files from the server machine instead? I read
> the licenses but it is still unclear to me if this is allowed.
> I've Googled to try find a solution but things are still as clear as
> mud.
> Thanks in advance for any help,
> Jako Grobler
I'd just install the client utilities on every client machine (unless it's
a server at the same time). Disk space is cheap and this is definitely
the most hassle free solution - apart from licensing maybe.
Kind regards
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in
news:ONhaOCncFHA.3120@.TK2MSFTNGP12.phx.gbl:
> I'd just install the client utilities on every client machine (unless
> it's a server at the same time). Disk space is cheap and this is
> definitely the most hassle free solution - apart from licensing maybe.
> Kind regards
> robert
The problem is that the client utilities option is not available with MSDE.
Also, some companies are not happy installing Query Analyzer or Enterprise
Manager on client machines.
SQLREDIS.EXE does not install BCP.EXE, I tried it already.
:-(
Jako|||Jako Grobler wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in
> news:ONhaOCncFHA.3120@.TK2MSFTNGP12.phx.gbl:
>> I'd just install the client utilities on every client machine (unless
>> it's a server at the same time). Disk space is cheap and this is
>> definitely the most hassle free solution - apart from licensing
>> maybe.
>> Kind regards
>> robert
> The problem is that the client utilities option is not available with
> MSDE.
You can simply use SQL Server client utilities. They should happily
connect to an MSDE instance.
> Also, some companies are not happy installing Query Analyzer or
> Enterprise Manager on client machines.
Well then...
> SQLREDIS.EXE does not install BCP.EXE, I tried it already.
> :-(
> Jako
Kind regards
robert

BCP Why does it never work?

Hello All,
I have dreaded this day for some time but I knoew it would arrive one
day...and thats where I need to use the BCP utility to bulk upload data to
my web hosting service (telstra - Australia). Unfortunately they do not
allow the use of the Transact SQL statement BULK INSERT, as you guessed it
that works. Here's the problem I have been working on for a couple of days.
I have a data file created from SQL2000 server here in the office, it
contains 4 fields:
PartNumber varcha(15)
Description varchar(25)
QtyOnHand int
Price money
Some sample data cut and pasted from the data file, fixed length no nasties
between fields and a ODOA at the end of each line.
1000FGM FUEL FILTER/WATER SE 0 542.05
1000FGP Fuel Filter, Water S 0 580
1000FH2 Fuel Filter, Water S 8 548.13
1000MA Fuel Filter, Water S 3 594.5
11007 Lid, Bowl & Base Gas 23 3.29
11040 Bowl Drain Fitting 1 16.87
110A Fuel Filter, Water S 2 195.55
11350 T Handle O'Ring 12 2.06
12003 LID GASKET 1 7.83
12014 GASKET LOWER LID 1 5.49
12041 Bowl Plug 1 2.46
120AS Fuel Filter, Water S 2 244.59
122R FUEL FILTER/WATER SE 0 222.61
130R-T-16S Fuel Filter, Water S 0 207.56
15005 Lid Gasket 2 2.27
15009 Bowl Gasket 5 7.15
I have let BCP create the format file, and this doen't work, I have defined
the format file myself and still does not work. Here's a sample of the
format file:
This is the format file I used to run the bcp last time.
7.0
4
1 SQLCHAR 0 15 "" 1
PartNumber Latin1_General_CI_AS
2 SQLCHAR 0 25 "" 2
Description Latin1_General_CI_AS
3 SQLINT 0 4 "" 3
QtyOnHand ""
4 SQLMONEY 1 8 "\r\n" 4 Price
""
When I run the bcp command this is the error that is generated:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
I am becoming extemely frustrated with this horrid little, but necessary
utility (Please Mr Telstra enable BULK INSERT command for me).
I have tried a number of variations, tab delimited files, no format file but
using the -n and -c switches but still no luck... If any body can provide
me with a solution it would be greatly appreciated.
Any suggestions '
Please reply directly to me at andrew.hull@.hcma.com.au
Thanks in advance.
Safe Sailing
AndrewBCP needs a NULL charater for empty fields. I strugled with this too.
Let Excel import it properly, then once you get it in your table, BCP it out
to a different file. THen compare the two files with LIST.EXE. Once you
are in LIST, hit H to go to Hex mode. You will see the NULL characters.
You cannot see them in NOTEPAD.
You would think that BCP is smart enough to move to a new record when it
hits the 0x0D 0x0A, but no, it is not.
"Plato" <andrew.hull@.hcma.com.au> wrote in message
news:OBk8%23OUiDHA.1964@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I have dreaded this day for some time but I knoew it would arrive one
> day...and thats where I need to use the BCP utility to bulk upload data
to
> my web hosting service (telstra - Australia). Unfortunately they do not
> allow the use of the Transact SQL statement BULK INSERT, as you guessed it
> that works. Here's the problem I have been working on for a couple of
days.
> I have a data file created from SQL2000 server here in the office, it
> contains 4 fields:
> PartNumber varcha(15)
> Description varchar(25)
> QtyOnHand int
> Price money
> Some sample data cut and pasted from the data file, fixed length no
nasties
> between fields and a ODOA at the end of each line.
> 1000FGM FUEL FILTER/WATER SE 0 542.05
> 1000FGP Fuel Filter, Water S 0 580
> 1000FH2 Fuel Filter, Water S 8 548.13
> 1000MA Fuel Filter, Water S 3 594.5
> 11007 Lid, Bowl & Base Gas 23 3.29
> 11040 Bowl Drain Fitting 1 16.87
> 110A Fuel Filter, Water S 2 195.55
> 11350 T Handle O'Ring 12 2.06
> 12003 LID GASKET 1 7.83
> 12014 GASKET LOWER LID 1 5.49
> 12041 Bowl Plug 1 2.46
> 120AS Fuel Filter, Water S 2 244.59
> 122R FUEL FILTER/WATER SE 0 222.61
> 130R-T-16S Fuel Filter, Water S 0 207.56
> 15005 Lid Gasket 2 2.27
> 15009 Bowl Gasket 5 7.15
> I have let BCP create the format file, and this doen't work, I have
defined
> the format file myself and still does not work. Here's a sample of the
> format file:
> This is the format file I used to run the bcp last time.
> 7.0
> 4
> 1 SQLCHAR 0 15 "" 1
> PartNumber Latin1_General_CI_AS
> 2 SQLCHAR 0 25 "" 2
> Description Latin1_General_CI_AS
> 3 SQLINT 0 4 "" 3
> QtyOnHand ""
> 4 SQLMONEY 1 8 "\r\n" 4 Price
> ""
> When I run the bcp command this is the error that is generated:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> found in BCP format-file
> I am becoming extemely frustrated with this horrid little, but necessary
> utility (Please Mr Telstra enable BULK INSERT command for me).
> I have tried a number of variations, tab delimited files, no format file
but
> using the -n and -c switches but still no luck... If any body can provide
> me with a solution it would be greatly appreciated.
>
> Any suggestions '
> Please reply directly to me at andrew.hull@.hcma.com.au
> Thanks in advance.
> Safe Sailing
> Andrew
>
>|||Hi Anthony,
The data itself is extracted from our ERP system and is massaged extensively
to produce a file where there are no blank fields and no null values, as you
know SQL and NULL are not good combination.
I have just got it working, using tab delimited file and using the -c switch
with no format file. All loads perfectly.
Over the last couple of days I have tried so many combinations that its not
funny. The simplest of them works but it took me ages to get to this point.
Now I have the process nailed to the wall so I will never forget the
pain...
Thanks again for your suggestion
Andrew
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:e5I0KOWiDHA.3324@.TK2MSFTNGP11.phx.gbl...
> BCP needs a NULL charater for empty fields. I strugled with this too.
> Let Excel import it properly, then once you get it in your table, BCP it
out
> to a different file. THen compare the two files with LIST.EXE. Once you
> are in LIST, hit H to go to Hex mode. You will see the NULL characters.
> You cannot see them in NOTEPAD.
> You would think that BCP is smart enough to move to a new record when it
> hits the 0x0D 0x0A, but no, it is not.
>
>
> "Plato" <andrew.hull@.hcma.com.au> wrote in message
> news:OBk8%23OUiDHA.1964@.TK2MSFTNGP10.phx.gbl...
> > Hello All,
> >
> > I have dreaded this day for some time but I knoew it would arrive one
> > day...and thats where I need to use the BCP utility to bulk upload data
> to
> > my web hosting service (telstra - Australia). Unfortunately they do not
> > allow the use of the Transact SQL statement BULK INSERT, as you guessed
it
> > that works. Here's the problem I have been working on for a couple of
> days.
> >
> > I have a data file created from SQL2000 server here in the office, it
> > contains 4 fields:
> > PartNumber varcha(15)
> > Description varchar(25)
> > QtyOnHand int
> > Price money
> >
> > Some sample data cut and pasted from the data file, fixed length no
> nasties
> > between fields and a ODOA at the end of each line.
> >
> > 1000FGM FUEL FILTER/WATER SE 0 542.05
> > 1000FGP Fuel Filter, Water S 0 580
> > 1000FH2 Fuel Filter, Water S 8 548.13
> > 1000MA Fuel Filter, Water S 3 594.5
> > 11007 Lid, Bowl & Base Gas 23 3.29
> > 11040 Bowl Drain Fitting 1 16.87
> > 110A Fuel Filter, Water S 2 195.55
> > 11350 T Handle O'Ring 12 2.06
> > 12003 LID GASKET 1 7.83
> > 12014 GASKET LOWER LID 1 5.49
> > 12041 Bowl Plug 1 2.46
> > 120AS Fuel Filter, Water S 2 244.59
> > 122R FUEL FILTER/WATER SE 0 222.61
> > 130R-T-16S Fuel Filter, Water S 0 207.56
> > 15005 Lid Gasket 2 2.27
> > 15009 Bowl Gasket 5 7.15
> >
> > I have let BCP create the format file, and this doen't work, I have
> defined
> > the format file myself and still does not work. Here's a sample of the
> > format file:
> >
> > This is the format file I used to run the bcp last time.
> > 7.0
> > 4
> > 1 SQLCHAR 0 15 "" 1
> > PartNumber Latin1_General_CI_AS
> > 2 SQLCHAR 0 25 "" 2
> > Description Latin1_General_CI_AS
> > 3 SQLINT 0 4 "" 3
> > QtyOnHand ""
> > 4 SQLMONEY 1 8 "\r\n" 4
Price
> > ""
> >
> > When I run the bcp command this is the error that is generated:
> >
> > SQLState = S1000, NativeError = 0
> > Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> > found in BCP format-file
> >
> > I am becoming extemely frustrated with this horrid little, but necessary
> > utility (Please Mr Telstra enable BULK INSERT command for me).
> >
> > I have tried a number of variations, tab delimited files, no format file
> but
> > using the -n and -c switches but still no luck... If any body can
provide
> > me with a solution it would be greatly appreciated.
> >
> >
> >
> > Any suggestions '
> >
> > Please reply directly to me at andrew.hull@.hcma.com.au
> >
> > Thanks in advance.
> > Safe Sailing
> > Andrew
> >
> >
> >
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:
> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>sql

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.
Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

bcp right truncation: how to ignore

Hi,
I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.
I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?
I couldn't find such an option in the documentation.
Any help is greatly appreciated.
Thanks,
Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?
Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.
One possibility would be to write a program that reads the file, and
truncates the over-long rows.
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.
Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).
Kind regards
robert

bcp right truncation: how to ignore

Hi,

I'm trying to upload a large number of log entries currently stored as
text files into a database table using bcp. For a few rows I get a
"right truncation" error and the offending rows are not uploaded to the
table.

I don't want to increase the size of the table varchar fields because
it's only about a dozen out of almost million rows that have this
problem ... I want to provide an override - i.e. if a row will result
in truncated data, truncate but still bulk copy the offending row. Is
that possible?

I couldn't find such an option in the documentation.

Any help is greatly appreciated.

Thanks,

Mudassir Latif(mudassir.latif@.gmail.com) writes:
> I'm trying to upload a large number of log entries currently stored as
> text files into a database table using bcp. For a few rows I get a
> "right truncation" error and the offending rows are not uploaded to the
> table.
> I don't want to increase the size of the table varchar fields because
> it's only about a dozen out of almost million rows that have this
> problem ... I want to provide an override - i.e. if a row will result
> in truncated data, truncate but still bulk copy the offending row. Is
> that possible?

Not really. Well, if you have SQL 6.5 around, you can use the BCP
program that comes with 6.5. Or you could write a program tha uses
the BCP routines in DB-Library. The reason this would work, is because
with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
ON with other means of connection. And with ANSI_WARNINGS, truncattion
is not accepted.

One possibility would be to write a program that reads the file, and
truncates the over-long rows.

--
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9788F12BC3957Yazorman@.127.0.0.1...
> (mudassir.latif@.gmail.com) writes:
>> I'm trying to upload a large number of log entries currently stored as
>> text files into a database table using bcp. For a few rows I get a
>> "right truncation" error and the offending rows are not uploaded to the
>> table.
>>
>> I don't want to increase the size of the table varchar fields because
>> it's only about a dozen out of almost million rows that have this
>> problem ... I want to provide an override - i.e. if a row will result
>> in truncated data, truncate but still bulk copy the offending row. Is
>> that possible?
> Not really. Well, if you have SQL 6.5 around, you can use the BCP
> program that comes with 6.5. Or you could write a program tha uses
> the BCP routines in DB-Library. The reason this would work, is because
> with DB-Library the setting ANSI_WARNINGS will be OFF, whereas it is
> ON with other means of connection. And with ANSI_WARNINGS, truncattion
> is not accepted.
> One possibility would be to write a program that reads the file, and
> truncates the over-long rows.

Or look into freetds, even when on Windows. Either it provides the
functionality or it could be added (open source).

Kind regards

robert

BCP Question.

Am using bcp to upload data files to sql server. The data file contains some
duplicate values. So I used something like ">bcp pubs.dbo.stores in
"stores.txt" -m 50..." assuming, the bcp stops only after encountering more
than 50 duplicate. But BCP stops whenever it encounters the first duplicate
value!!
How to continue with BCP, ignoring the duplicate values?
Thanks.
I think it would be best to bcp into a staging table and then do an insert
with a not in subquery keying off the pk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Viga" <Viga@.discussions.microsoft.com> wrote in message
news:89E94705-F691-4DCD-8E9C-BAEEDDD5C722@.microsoft.com...
> Am using bcp to upload data files to sql server. The data file contains
some
> duplicate values. So I used something like ">bcp pubs.dbo.stores in
> "stores.txt" -m 50..." assuming, the bcp stops only after encountering
more
> than 50 duplicate. But BCP stops whenever it encounters the first
duplicate
> value!!
> How to continue with BCP, ignoring the duplicate values?
> Thanks.

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).