BcpBatchSize problem in replication

I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>

bcp won't run

I'm having trouble running bcp from a command line. It fails without an erro
r
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik JohnsonAre you running from a .BAT file, or typing in the BCP command in Windows, S
tart, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get
a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window
,,,
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an er
ror
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows,
Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you ge
t a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command wind
ow,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows,
Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you ge
t a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command wind
ow,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
[vbcol=seagreen]
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I ge
t
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
>|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:

> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
>
>

bcp won't run

I'm having trouble running bcp from a command line. It fails without an error
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik Johnson
Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window,,,
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an error
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson
|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>
|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:

> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>
>
|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
[vbcol=seagreen]
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I get
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:

> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
>
sql

bcp won't run

I'm having trouble running bcp from a command line. It fails without an error
message and closes the DOS window.
Does anyone know what I might be missing here?
-Nik JohnsonAre you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
from there.
I don't think I've ever heard of a command line app closing a command window,,,
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> I'm having trouble running bcp from a command line. It fails without an error
> message and closes the DOS window.
> Does anyone know what I might be missing here?
> -Nik Johnson|||I think the window closing happened because I tried running from a shortcut.
"Tibor Karaszi" wrote:
> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> > I'm having trouble running bcp from a command line. It fails without an error
> > message and closes the DOS window.
> >
> > Does anyone know what I might be missing here?
> >
> > -Nik Johnson
>
>|||C>A little more experimentation shows that if I RUN the whole path
(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
get the following:
Parameter format not correct
Specified COMMAND search directory bad
Too many parameters
Too many parameters
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-1999.
If I move everything to a directory immediately under the root (c:bcp) I get
a message: "Unable to load BCP resource DLL. BCP cannot continue."
-Nik
"Tibor Karaszi" wrote:
> Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> from there.
> I don't think I've ever heard of a command line app closing a command window,,,
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> > I'm having trouble running bcp from a command line. It fails without an error
> > message and closes the DOS window.
> >
> > Does anyone know what I might be missing here?
> >
> > -Nik Johnson
>
>|||Nik,
Type command.com /? (at a command prompt) for a summary of how to use
command.com.
Type bcp (at a command prompt for a summary of how to use bcp.
Running bcp.exe with no parameters does not cause an error. It
displays the various switches that can be used with bcp and returns
control to the caller. That's what you're seeing when you run a batch
containing nothing but one line saying bcp or bcp.exe. What are you
expecting? As far as I know, there is no interactive mode for bcp, so
you can't just "start bcp" and then enter commands.
Steve Kass
Drew University
Nik Johnson wrote:
>C>A little more experimentation shows that if I RUN the whole path
>(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
>get the following:
>Parameter format not correct
>Specified COMMAND search directory bad
>Too many parameters
>Too many parameters
>Microsoft(R) Windows DOS
>(C)Copyright Microsoft Corp 1990-1999.
>If I move everything to a directory immediately under the root (c:bcp) I get
>a message: "Unable to load BCP resource DLL. BCP cannot continue."
>-Nik
>
>
>"Tibor Karaszi" wrote:
>
>>Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
>>PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
>>from there.
>>I don't think I've ever heard of a command line app closing a command window,,,
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
>>news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
>>
>>I'm having trouble running bcp from a command line. It fails without an error
>>message and closes the DOS window.
>>Does anyone know what I might be missing here?
>>-Nik Johnson
>>
>>|||Steve-
I'm expecting exactly the behavior that you describe, i.e., a summary of how
to use bcp. But instead I get a message that a dll can't be loaded.
I have a feeling that the necessary dll is either missing or not in the
path. But I have no idea what that dll is.
-Nik
"Steve Kass" wrote:
> Nik,
> Type command.com /? (at a command prompt) for a summary of how to use
> command.com.
> Type bcp (at a command prompt for a summary of how to use bcp.
> Running bcp.exe with no parameters does not cause an error. It
> displays the various switches that can be used with bcp and returns
> control to the caller. That's what you're seeing when you run a batch
> containing nothing but one line saying bcp or bcp.exe. What are you
> expecting? As far as I know, there is no interactive mode for bcp, so
> you can't just "start bcp" and then enter commands.
> Steve Kass
> Drew University
> Nik Johnson wrote:
> >C>A little more experimentation shows that if I RUN the whole path
> >(command.com C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe) I
> >get the following:
> >
> >Parameter format not correct
> >Specified COMMAND search directory bad
> >Too many parameters
> >Too many parameters
> >Microsoft(R) Windows DOS
> >(C)Copyright Microsoft Corp 1990-1999.
> >
> >If I move everything to a directory immediately under the root (c:bcp) I get
> >a message: "Unable to load BCP resource DLL. BCP cannot continue."
> >
> >-Nik
> >
> >
> >
> >
> >"Tibor Karaszi" wrote:
> >
> >
> >
> >>Are you running from a .BAT file, or typing in the BCP command in Windows, Start, Rum? If from a BAT file, out
> >>PAUSE after the BCP command. If start, don't. Open a DOS windows so you get a proper command prompt and run
> >>from there.
> >>
> >>I don't think I've ever heard of a command line app closing a command window,,,
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://www.solidqualitylearning.com/
> >>
> >>
> >>"Nik Johnson" <Nik Johnson@.discussions.microsoft.com> wrote in message
> >>news:A6D6EB0B-BF4B-4DED-8BFE-E99D954F2D9A@.microsoft.com...
> >>
> >>
> >>I'm having trouble running bcp from a command line. It fails without an error
> >>message and closes the DOS window.
> >>
> >>Does anyone know what I might be missing here?
> >>
> >>-Nik Johnson
> >>
> >>
> >>
> >>
> >>
>

BCP with named pipes

i have a SQL Server 7.0 and SQL Server 2000 running on 1 machine. The 7.0
version is the default instance.( obviously 2000 version will be a named
instance)
i want to use BCP to import data into a table in the SQL Server 2000
instance using named pipes. According to the docs if u don't specify the
server name with -S, named pipes are used.
if i do a "bcp table1 in datafile -U user -P user -c" i get an
error as:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Se
rver does
not ex
ist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(Conn
ect()).
My guess is that it is trying to connect to the default instance (SQL Server
7.0 which is not running, since i don't use that) and fails.
So how can i connect to a named instance with BCP using named pipes
thanks
--
Vivek T S
Member Technical Staff (Inucom)I'm not sure I completely understand your question.
Is there a reason you can't use the -S option?
-S<servername\instance_name>
Rick Sawtell
MCT, MCSD, MCDBA
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:A43D4B3A-4404-4D3A-A12A-6436ABE88FE2@.microsoft.com...
> i have a SQL Server 7.0 and SQL Server 2000 running on 1 machine. The 7.0
> version is the default instance.( obviously 2000 version will be a named
> instance)
> i want to use BCP to import data into a table in the SQL Server 2000
> instance using named pipes. According to the docs if u don't specify the
> server name with -S, named pipes are used.
> if i do a "bcp table1 in datafile -U user -P user -c" i get
an
> error as:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL
Server does
> not ex
> ist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Co
nnectionOpen
> (Conn
> ect()).
>
> My guess is that it is trying to connect to the default instance (SQL
Server
> 7.0 which is not running, since i don't use that) and fails.
>
> So how can i connect to a named instance with BCP using named pipes
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||when u use the -S servername option, even though u r in the same machine
tcp/ip will be used for the data transfer. Whereas named pipes (i.e. shared
memory)
is the fastest way to transfer data when u r doing a bcp on the same machine
as the server. to make use of the named pipes, the docs say that, -S option
should be dropped. but i don't know how i can make this work with multiple
instances on my machine.
eg: on a machine with a single instance of SQL Server:
a) bcp table in datafile -U user -P passwd (should work and named
pipes will be used)
b) bcp table in datafile -S servername -U user -P passwd (in this case
tcp/ip will be used)
But on a machine with multiple instances of SQL Server how (a) above work is
my doubt.
thanks
"Rick Sawtell" wrote:

> I'm not sure I completely understand your question.
> Is there a reason you can't use the -S option?
> -S<servername\instance_name>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:A43D4B3A-4404-4D3A-A12A-6436ABE88FE2@.microsoft.com...
> an
> Server
>
>|||I see...
Well if it's speed that you are looking for how about :
bcp out from the 7.0 server and then use BULK INSERT on the 2000 instance.
Bulk Insert in most cases is going to be faster than bcp anyhow. It's not
much, but ...
HTH
Rick Sawtell
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:0F5062CB-BA46-44DB-A4B1-B879C879F385@.microsoft.com...
> when u use the -S servername option, even though u r in the same machine
> tcp/ip will be used for the data transfer. Whereas named pipes (i.e.
shared
> memory)
> is the fastest way to transfer data when u r doing a bcp on the same
machine
> as the server. to make use of the named pipes, the docs say that, -S
option
> should be dropped. but i don't know how i can make this work with multiple
> instances on my machine.
> eg: on a machine with a single instance of SQL Server:
> a) bcp table in datafile -U user -P passwd (should work and
named
> pipes will be used)
> b) bcp table in datafile -S servername -U user -P passwd (in this case
> tcp/ip will be used)
> But on a machine with multiple instances of SQL Server how (a) above work
is[vbcol=seagreen]
> my doubt.
> thanks
>
> "Rick Sawtell" wrote:
>
7.0[vbcol=seagreen]
named[vbcol=seagreen]
the[vbcol=seagreen]
get[vbcol=seagreen]
does[vbcol=seagreen]
Memory]ConnectionOpen[vbcol=seagreen]|||If you are running BCP on the server it will by default use Shared Memory,
based on your Client Network settings. (See Client Network Utility)
If you want to use Named Pipes for your local server connection there are
two options:
1) Use a client alias, created with the Client Network Utility and use the
name of the alias in the -S parameter
2) Use a protocol prefix as described in
0use%20of%20a%20netw
ork%20protocol%20without%20changing%20th
e%20Client%20Network%20settings" target="_blank">http://sqldev.net/faq. htm#FAQ:%20H...work%20settings
Like bcp testdb.dbo.loadsrc out loadtest.bcp -S np:gertd00\sql80 -T -n
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:%23k5rTpPkEHA.556@.tk2msftngp13.phx.gbl...
>I see...
> Well if it's speed that you are looking for how about :
> bcp out from the 7.0 server and then use BULK INSERT on the 2000 instance.
> Bulk Insert in most cases is going to be faster than bcp anyhow. It's
> not
> much, but ...
> HTH
> Rick Sawtell
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:0F5062CB-BA46-44DB-A4B1-B879C879F385@.microsoft.com...
> shared
> machine
> option
> named
> is
> 7.0
> named
> the
> get
> does
> Memory]ConnectionOpen
>

BCP with named pipes

i have a SQL Server 7.0 and SQL Server 2000 running on 1 machine. The 7.0
version is the default instance.( obviously 2000 version will be a named
instance)
i want to use BCP to import data into a table in the SQL Server 2000
instance using named pipes. According to the docs if u don't specify the
server name with -S, named pipes are used.
if i do a "bcp table1 in datafile -U user -P user -c" i get an
error as:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
not ex
ist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Conn
ect()).
My guess is that it is trying to connect to the default instance (SQL Server
7.0 which is not running, since i don't use that) and fails.
So how can i connect to a named instance with BCP using named pipes
thanks
Vivek T S
Member Technical Staff (Inucom)
I'm not sure I completely understand your question.
Is there a reason you can't use the -S option?
-S<servername\instance_name>
Rick Sawtell
MCT, MCSD, MCDBA
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:A43D4B3A-4404-4D3A-A12A-6436ABE88FE2@.microsoft.com...
> i have a SQL Server 7.0 and SQL Server 2000 running on 1 machine. The 7.0
> version is the default instance.( obviously 2000 version will be a named
> instance)
> i want to use BCP to import data into a table in the SQL Server 2000
> instance using named pipes. According to the docs if u don't specify the
> server name with -S, named pipes are used.
> if i do a "bcp table1 in datafile -U user -P user -c" i get
an
> error as:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
> not ex
> ist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Conn
> ect()).
>
> My guess is that it is trying to connect to the default instance (SQL
Server
> 7.0 which is not running, since i don't use that) and fails.
>
> So how can i connect to a named instance with BCP using named pipes
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||when u use the -S servername option, even though u r in the same machine
tcp/ip will be used for the data transfer. Whereas named pipes (i.e. shared
memory)
is the fastest way to transfer data when u r doing a bcp on the same machine
as the server. to make use of the named pipes, the docs say that, -S option
should be dropped. but i don't know how i can make this work with multiple
instances on my machine.
eg: on a machine with a single instance of SQL Server:
a) bcp table in datafile -U user -P passwd (should work and named
pipes will be used)
b) bcp table in datafile -S servername -U user -P passwd (in this case
tcp/ip will be used)
But on a machine with multiple instances of SQL Server how (a) above work is
my doubt.
thanks
"Rick Sawtell" wrote:

> I'm not sure I completely understand your question.
> Is there a reason you can't use the -S option?
> -S<servername\instance_name>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:A43D4B3A-4404-4D3A-A12A-6436ABE88FE2@.microsoft.com...
> an
> Server
>
>
|||I see...
Well if it's speed that you are looking for how about:
bcp out from the 7.0 server and then use BULK INSERT on the 2000 instance.
Bulk Insert in most cases is going to be faster than bcp anyhow. It's not
much, but ...
HTH
Rick Sawtell
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:0F5062CB-BA46-44DB-A4B1-B879C879F385@.microsoft.com...
> when u use the -S servername option, even though u r in the same machine
> tcp/ip will be used for the data transfer. Whereas named pipes (i.e.
shared
> memory)
> is the fastest way to transfer data when u r doing a bcp on the same
machine
> as the server. to make use of the named pipes, the docs say that, -S
option
> should be dropped. but i don't know how i can make this work with multiple
> instances on my machine.
> eg: on a machine with a single instance of SQL Server:
> a) bcp table in datafile -U user -P passwd (should work and
named
> pipes will be used)
> b) bcp table in datafile -S servername -U user -P passwd (in this case
> tcp/ip will be used)
> But on a machine with multiple instances of SQL Server how (a) above work
is[vbcol=seagreen]
> my doubt.
> thanks
>
> "Rick Sawtell" wrote:
7.0[vbcol=seagreen]
named[vbcol=seagreen]
the[vbcol=seagreen]
get[vbcol=seagreen]
does[vbcol=seagreen]
Memory]ConnectionOpen[vbcol=seagreen]
|||If you are running BCP on the server it will by default use Shared Memory,
based on your Client Network settings. (See Client Network Utility)
If you want to use Named Pipes for your local server connection there are
two options:
1) Use a client alias, created with the Client Network Utility and use the
name of the alias in the -S parameter
2) Use a protocol prefix as described in
http://sqldev.net/faq.htm#FAQ:%20How...ork%20settings
Like bcp testdb.dbo.loadsrc out loadtest.bcp -S np:gertd00\sql80 -T -n
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:%23k5rTpPkEHA.556@.tk2msftngp13.phx.gbl...
>I see...
> Well if it's speed that you are looking for how about:
> bcp out from the 7.0 server and then use BULK INSERT on the 2000 instance.
> Bulk Insert in most cases is going to be faster than bcp anyhow. It's
> not
> much, but ...
> HTH
> Rick Sawtell
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:0F5062CB-BA46-44DB-A4B1-B879C879F385@.microsoft.com...
> shared
> machine
> option
> named
> is
> 7.0
> named
> the
> get
> does
> Memory]ConnectionOpen
>

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?
I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David
|||Any recomended reading?
|||?
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegr oups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Serv.../dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-S...dp/0764584359/
David

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-Server-Integration-Services-Programmer/dp/0764584359/
David

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Ser...5
84359/
David

BCP Usage with SQL Server 2005 Clustered Server

How can I use BCP to create an XML format file on a SQL Server 2005
cluster that has multiple instances of SQL Server running? Is there
some sample syntax, etc.?
Thx.
-ak
This syntax "appears" to work...
bcp [sqlcls1dev\inst2005dev].[BusinessContacts].[Branch] format -x -T
-fc:\temp\myfile.xml
However, I get a connection timeout error that also states that the SQL
Server 2005 by default doesn't allow remote connections. But, I see
that this server does allow remote connections in the options. I am
logged into the box using Win authent and my Win acct has sysadmin
privileges on the sql server instance. Any information would be
appreciated.
-ak
Ayron wrote:
> How can I use BCP to create an XML format file on a SQL Server 2005
> cluster that has multiple instances of SQL Server running? Is there
> some sample syntax, etc.?
> Thx.
> -ak

BCP Usage with SQL Server 2005 Clustered Server

How can I use BCP to create an XML format file on a SQL Server 2005
cluster that has multiple instances of SQL Server running? Is there
some sample syntax, etc.?
Thx.
-akThis syntax "appears" to work...
bcp [sqlcls1dev\inst2005dev].[BusinessContacts].[Branch] format -x -T
-fc:\temp\myfile.xml
However, I get a connection timeout error that also states that the SQL
Server 2005 by default doesn't allow remote connections. But, I see
that this server does allow remote connections in the options. I am
logged into the box using Win authent and my Win acct has symin
privileges on the sql server instance. Any information would be
appreciated.
-ak
Ayron wrote:
> How can I use BCP to create an XML format file on a SQL Server 2005
> cluster that has multiple instances of SQL Server running? Is there
> some sample syntax, etc.?
> Thx.
> -ak

BCP Slower Standalone vs. A/P Cluster

I have an issue with the BCP util, it is extremely slow when running a BCP in to the same database on an A/P cluster vs. Standalone system. It is a vendor application, so I have little control as to what I can do. The BCP runs from a jobserver (remote) through the network, but it runs the same way in the standalone system, yet the process is about 10X as slow with the cluster, and the only diff is cluster vs. standalone.

SQLClusterName is specified in -S, not the active node, and it uses a trusted connection, not a local account. Is there anything about a cluster config that would cause the BCP to run slower ?

Any thoughts ??

Thanks,
PaulTry to add "-a32764" and see if that makes a difference.|||Thanks, but I also found out that the Cognos Jobserver (where BCP command is running from) is on a diff subnet than the sql cluster (data is going through a router) On the current standalone config, the jobserver and sqlserver are on the same subnet, also there are NETWORKIO status' for the BCP sessions when the Cognos publish is running, which may indicate a network problem.

Thanks,
Paul|||Thanks, did some initial testing with a 135K row sample, sped it up 5 fold, I am going to see if vendor can add this param to their gen'd BCPs.

Thanks Again,
Paul

BCP problem running from EM and Query analyzer

I am running on W2K server with Sql Server 7. I am having a lot of
problems getting some of my stuff to run. I suspect it has to do with
permissions.
But in this case I am trying to run the following command from EM as a
Job as well as directly from Query Analyzer.
BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
I get the following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
I copied and pasted it into a cmd window and it worked fine.
What is the problem here? It is getting very frustrating.
Thanks,
TomHi,
You cant execute directly a BCP command from Query analyzer. As a job you
can execute it but the job type should be "operating system command".
Otherwise use xpcmdshell to execute the BCP from query analyzer
master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
sa -S DINO'
Thanks
Hari
MCDBA
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:3FC704D7.1030207@.deltanet.com...
> I am running on W2K server with Sql Server 7. I am having a lot of
> problems getting some of my stuff to run. I suspect it has to do with
> permissions.
> But in this case I am trying to run the following command from EM as a
> Job as well as directly from Query Analyzer.
> BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
> I get the following error.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '.'.
> I copied and pasted it into a cmd window and it worked fine.
> What is the problem here? It is getting very frustrating.
> Thanks,
> Tom
>|||Hari wrote:
> Hi,
> You cant execute directly a BCP command from Query analyzer. As a job you
> can execute it but the job type should be "operating system command".
> Otherwise use xpcmdshell to execute the BCP from query analyzer
> master..xp_cmdshell 'BCP pubs..phone_book out c:\temp\authors.txt -c -U
> sa -S DINO'
>
That was it.
I was having trouble getting it to work in EM, where I don't use the
xp_cmdshell SP and just copied it directly into the query analyzer.
All works fine now
Thanks,
Tom.
> Thanks
> Hari
> MCDBA
>
> "Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
> news:3FC704D7.1030207@.deltanet.com...
>>I am running on W2K server with Sql Server 7. I am having a lot of
>>problems getting some of my stuff to run. I suspect it has to do with
>>permissions.
>>But in this case I am trying to run the following command from EM as a
>>Job as well as directly from Query Analyzer.
>>BCP pubs..phone_book out c:\temp\authors.txt -c -U sa -S DINO
>>I get the following error.
>>Server: Msg 170, Level 15, State 1, Line 1
>>Line 1: Incorrect syntax near '.'.
>>I copied and pasted it into a cmd window and it worked fine.
>>What is the problem here? It is getting very frustrating.
>>Thanks,
>>Tom
>>
>

BCP Out Slow?

I am running the following BCP to extract a table with 156641604 rows.

bcp TestDB..data out test3.bcp -T -b1000000 -a32000

When running this i notice that the disk read bytes\sec counter in performance monitor on the drive that has the database devices is only reading 30mb\sec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?

Hi Andy,

Try not using the -a option, and reducing the batch size to 100000.

What is the purpose of extracting the data. Do you need it to be in char format.

If use -n for native, it will be a bit quicker.

Jag

|||

Hi Jag

That does help with throughput. The thing I don't understand is that even if I run a Select * From TestDB..data i get low read IO on this table. I know the disk IO is far more capable and if I run a DBCC SHOWCONTIG command for instance, the disk read bytes\sec counter in performance monitor jumps to over 70mb\sec. My question is, why doesn't BCP or a Select statement achieve this IO?

|||That's because it might not need to. The DBCC is going to explicitly hit the disk drive. Doing a BCP or a SELECT is going to utilize the normal infrastructure. It is going to read it from memory and then go out to disk as needed. Since SQL Server has an intelligent read ahead capability, it might only need 30mb/sec to keep up with the write process out the other side.|||Ok i can see what you are saying but the write IO is hovering around 30mb\sec also when it can write much faster than that. Is this IO limitation possibly due to the row count being so high, or some other factor?|||The row count shouldn't have any effect on this. BCP is going to run just as fast, regardless of the number of rows. It simply starts at the beginning and is done when SQL Server doesn't send anything else to it. I would suggest comparing this to the performance you get using SSIS. From everything that I've seen thus far, SSIS is going to move stuff a lot faster than BCP. Has to do with the basic interface that is used to connect to and get the data.

BCP not exporting the "{" or "}" characters

Hello,

I am running a bcp command to export all data from one single table. One of the table field has the flower brackets at the begining and the end and the column is defined as of data type uniqueidentifier (length 16).

The original command that I used is :

c:\bcp dbo.Product out c:\product.csv -w -t"|" -r\n

The ProdID column in Product table have values like : {3E116F82-5E52-4EF9-9A97-8756EA6E9A16}

But in the out put file, the flower brackets are being omitted.

So, I tried the following query :

c:\bcp "Select ID,Name,"+'"{"+'+"convert(varchar(100),ProdID)"+'+"}"'+",ProdType,IsActive,TitleCode,CreationDate,CreatorID,AllowView,AllowRead,AllowWrite,AllowDelete,AdminURL,ReportProc from dbo.Product" queryout c:\Product16Feb.csv -w -t"|" -r\n

But it is giving the following error message:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near ','.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepared.

The SQL statement is correct because when I run it individuall, it works but not with the bcp command.

I just want the ProdID column to have the flower brackets in the output file using bcp. How can I achieve this?

Many thanks

Ratan

You can define your SQL statement as a view and then BCP the view. Otherwise, consider editing the file after the BCP operation and applying the brackets then.

bcp licensing

Hi,
SQL Server 2000 Standard Edition
I do have license for running SQL Server on a machine. Do I need to get
separate license for installing bcp on a user machine and using bcp from the
user machine to export data out from SQL Server ?
Thanks,
payyans
payyans,
Yes, you can but you need a client access license (CAL) from the client
that bcp is running on.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
payyans wrote:
> Hi,
> SQL Server 2000 Standard Edition
> I do have license for running SQL Server on a machine. Do I need to get
> separate license for installing bcp on a user machine and using bcp from the
> user machine to export data out from SQL Server ?
> Thanks,
> payyans
>
|||Thanks Mark
"Mark Allison" wrote:

> payyans,
> Yes, you can but you need a client access license (CAL) from the client
> that bcp is running on.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> payyans wrote:
>

bcp licensing

Hi,
SQL Server 2000 Standard Edition
---
I do have license for running SQL Server on a machine. Do I need to get
separate license for installing bcp on a user machine and using bcp from the
user machine to export data out from SQL Server ?
Thanks,
payyanspayyans,
Yes, you can but you need a client access license (CAL) from the client
that bcp is running on.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
payyans wrote:
> Hi,
> SQL Server 2000 Standard Edition
> ---
> I do have license for running SQL Server on a machine. Do I need to get
> separate license for installing bcp on a user machine and using bcp from the
> user machine to export data out from SQL Server ?
> Thanks,
> payyans
>|||Thanks Mark
"Mark Allison" wrote:
> payyans,
> Yes, you can but you need a client access license (CAL) from the client
> that bcp is running on.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> payyans wrote:
> > Hi,
> > SQL Server 2000 Standard Edition
> > ---
> > I do have license for running SQL Server on a machine. Do I need to get
> > separate license for installing bcp on a user machine and using bcp from the
> > user machine to export data out from SQL Server ?
> > Thanks,
> > payyans
> >
>

bcp licensing

Hi,
SQL Server 2000 Standard Edition
---
I do have license for running SQL Server on a machine. Do I need to get
separate license for installing bcp on a user machine and using bcp from the
user machine to export data out from SQL Server ?
Thanks,
payyanspayyans,
Yes, you can but you need a client access license (CAL) from the client
that bcp is running on.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
payyans wrote:
> Hi,
> SQL Server 2000 Standard Edition
> ---
> I do have license for running SQL Server on a machine. Do I need to get
> separate license for installing bcp on a user machine and using bcp from t
he
> user machine to export data out from SQL Server ?
> Thanks,
> payyans
>|||Thanks Mark
"Mark Allison" wrote:

> payyans,
> Yes, you can but you need a client access license (CAL) from the client
> that bcp is running on.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> payyans wrote:
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million row
s
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time th
e
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server
.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is bei
ng
> truncated? How is the BCP process (the one that is misbehaving) called?
Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>

bcp inserts more rows than are in the text file

I'm running a scheduled BCP job that exports data from a table on one db
server into a text file and then inserts the data in the text file into a
matching table on a different db server. I'm truncating the table on the
target server prior to inserting the data, but when the job ends, the table
on the target server has many more rows than the table on the source server.
For example, the job ran this morning and the source table has 1 million rows
but the target table has 4 million rows! This job has been running fine for
months and this problem just started a week ago. When I rerun the job later
in the day using the same text file, it inserts the correct number of rows
into the target table. The only activity on the target server at the time the
job ran was a database backup. Has anyone ever seen anything like this?
Thanks!
No, I have not observed this behavior. Are you sure that the table is being
truncated? How is the BCP process (the one that is misbehaving) called? Is
it executed from a job? From a stored procedure? Manually? When you
re-run the job later in the day do you run the same process/steps as the
earlier failed/misbehaving import, or are you doing something differently?
Keith
"rsquared" <rsquared@.discussions.microsoft.com> wrote in message
news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> I'm running a scheduled BCP job that exports data from a table on one db
> server into a text file and then inserts the data in the text file into a
> matching table on a different db server. I'm truncating the table on the
> target server prior to inserting the data, but when the job ends, the
table
> on the target server has many more rows than the table on the source
server.
> For example, the job ran this morning and the source table has 1 million
rows
> but the target table has 4 million rows! This job has been running fine
for
> months and this problem just started a week ago. When I rerun the job
later
> in the day using the same text file, it inserts the correct number of rows
> into the target table. The only activity on the target server at the time
the
> job ran was a database backup. Has anyone ever seen anything like this?
> Thanks!
|||1. I'm sure the table is being truncated. In fact, I manually truncated the
table last night before the job's scheduled start time.
2. The BCP process is called from a scheduled job the agent.
3. We run the same job during the day and it works fine.
We've got a copy of the database on a disaster recovery server and we're
looking into the possibility that the job is also kicking off on that server.
"Keith Kratochvil" wrote:

> No, I have not observed this behavior. Are you sure that the table is being
> truncated? How is the BCP process (the one that is misbehaving) called? Is
> it executed from a job? From a stored procedure? Manually? When you
> re-run the job later in the day do you run the same process/steps as the
> earlier failed/misbehaving import, or are you doing something differently?
> --
> Keith
>
> "rsquared" <rsquared@.discussions.microsoft.com> wrote in message
> news:6CF196CC-CBBD-43E9-A208-077B8295AB78@.microsoft.com...
> table
> server.
> rows
> for
> later
> the
>