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 Syntax

Please I need some help with bcp syntax. I have the default installation of SQL Express on my system. Also, I'm using windows authentication. Here are some syntax tried:

bcp database.dbo.table out "c:\table.dat" -U? -P? -c

I'm not sure what the user name and password should be but I've tried my windows user id and password without any success.

bcp database.dbo.table out "c:\table.dat" -T -c

I'm not sure if this is a trusted connection,

Do I have to be in App_data directory where my database reside.

There are 2 authentication modes that bcp supports:

a) SQL Server authentication in which case you have to specify your SQL Server login and password, say your SQL Server login ID is foo and password is bar then you should specify -Ufoo -Pbar.

b) If you can login to SQL Server using Windows authentication, then just specify -T and it will used Trusted connection.

Thanks

bcp from a named instance

The instalation has SS2K as default instance and SS2K5 as a named instance.
When runing the following where the db is on the SS2K5 instance:
bcp <dbname.dbo.tablename> format nul -T -n -f <filename>.fmt
I get the following:
SQLState=37000,NativeError=4060
Error=[Microsoft][ODBC SQLServer Driver][SQL Server]Cannot open database
requested in login '<dbname>'. Login fail.
When I add the SS2K5 server name I get the same error.
However when I run bcp against a table on the default instance it runs.
Does bcp only look at the default instance'
--
Thanks in advance
GHUse the -S server_name\Instance_name option to connect to a non-default
instance.
Tom
"GH" <vakar@.community.nospam> wrote in message
news:A5B00431-8DE2-4935-9C4F-9BAF7217B02F@.microsoft.com...
> The instalation has SS2K as default instance and SS2K5 as a named
> instance.
> When runing the following where the db is on the SS2K5 instance:
> bcp <dbname.dbo.tablename> format nul -T -n -f <filename>.fmt
> I get the following:
> SQLState=37000,NativeError=4060
> Error=[Microsoft][ODBC SQLServer Driver][SQL Server]Cannot open database
> requested in login '<dbname>'. Login fail.
> When I add the SS2K5 server name I get the same error.
> However when I run bcp against a table on the default instance it runs.
> Does bcp only look at the default instance'
> --
> Thanks in advance
> GH|||GH (vakar@.community.nospam) writes:
> The instalation has SS2K as default instance and SS2K5 as a named
> instance.
> When runing the following where the db is on the SS2K5 instance:
> bcp <dbname.dbo.tablename> format nul -T -n -f <filename>.fmt
> I get the following:
> SQLState=37000,NativeError=4060
> Error=[Microsoft][ODBC SQLServer Driver][SQL Server]Cannot open database
> requested in login '<dbname>'. Login fail.
> When I add the SS2K5 server name I get the same error.
> However when I run bcp against a table on the default instance it runs.
As Tom said, you need to specify the server as MACHINE\Instance or
.\Instance.
If you are generating the BCP command from within SQL Server and
execute it wityh help xp_cmdshell, get the servername from @.@.servername.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for Tom's input.
Hi GH,
I agree with Tom that you can use the "-S" option to specify the
non-default server instance name. e.g.
bcp AdventureWorks.Sales.Currency out Currency.dat -S
remote-server-name\sql2005instance -T
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi GH,
How are you doing on this issue or does the suggestion in our replies help
you a little? If there is still anything we can help, please feel free to
post here.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Batch Size

Hi
I'm trying to improve a crawl's performance. Online books refers to
changing the default batch size from 1600 to a number associated with the
number of processors on my server.
For the life of me, I have no idea how to go about changing the batch size
for full-text search. Any and all pointers apprciated.
Rob
Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
hyperthreading. fast XEON procesors (don't exactly remember how fast).
Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
populate. I've done the other hardware things suggested by books online.
Thanks.
"Robert G." wrote:

> Hi
> I'm trying to improve a crawl's performance. Online books refers to
> changing the default batch size from 1600 to a number associated with the
> number of processors on my server.
> For the life of me, I have no idea how to go about changing the batch size
> for full-text search. Any and all pointers apprciated.
> Rob
|||Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.
Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?
FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."
Thanks,
John
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
> I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
> hyperthreading. fast XEON procesors (don't exactly remember how fast).
> Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
> populate. I've done the other hardware things suggested by books online.
> Thanks.
> "Robert G." wrote:
|||Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.
Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?
FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."
Thanks,
John
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
> I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
> hyperthreading. fast XEON procesors (don't exactly remember how fast).
> Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
> populate. I've done the other hardware things suggested by books online.
> Thanks.
> "Robert G." wrote:
|||Hi
Your assumption is correct. Clustered index, files spread over 3 logical
disks (and two channels, for good measure).
Low is 1. According to that article, I should be seeing batches in the 4 -
8 range (maybe 16, since SQL believes I have 8 processors (hyperthreading)).
Low CPU is 0 - 5%, sometimes peaking at 30%, but rarely.
The article states "if the number of batches is low ... Increase full-text
batch size". That's what I'm trying to figure out.
They even give a suggested range - "default is 1600 rows per batch. For an
8-way computer 700Mhz CPU, the batch size recommend is 5000 rows."
Sounds like a great configuration change, if I could figure out how to
change the configuration!
Thanks again.
Rob
to quote the article:
"John Kane" wrote:

> Robert,
> Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
> SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
> officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
> checkout my blog entry from today for some of the initial details: "SQL
> Server 2005 has RTM'ed !!"
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
> You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
> (Full-Text Search)"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
> Can I assume that you have already done the following?
> 1. Ensure the base table has a clustered index.
> 2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
> full-text catalog on separate disks.
> Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
> queue" perfmon counter values? I'm not sure what constitutes "low" for your
> server, but could you reply back with the range of values you are seeing now
> while the FT Indexing is ongoing?
> FYI, the explain for this performance counter: "Number of batches in the
> ready queue. This queue buffers work that will be given to the filter
> daemons."
> Thanks,
> John
> --
> SQL 2005 Full Text Search
> http://spaces.msn.com/members/jtkane/
>
> "Robert G." <RobertG@.discussions.microsoft.com> wrote in message
> news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...
>
>

Batch processing of Stored Procedure

Hi,
I am trying to batch processing stored procedures with SQL 2000, MDAC
2.8.
All settings are default. SQLSetStmtAttr is called with
SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using
ODBC CALL escape sequence.
I noticed from SQL Profiler trace that the stored procedure is
executed as many
times as the elements in the array (e.g., in separate batches).
If I change the stored procedure call to a vanilla INSERT then all of
the INSERTs are performed in one batch.
Is there any extra settings to be done to execute the procedure in a
true batch execution mode rather than a simulated batch processing.
In the past someone complained about the same issue but the only
suggestion was to concatenate all stored procedure call strings into
one big string and then execute it in one batch. But then there is no
way to return the array of results or errors.
Any hints will be appreciated. Thanks.
KongThis is by design. Each stored procedure is executed within its own scope fo
r
each call.
-oj
http://www.rac4sql.net
"Kong Li" <likong@.email.com> wrote in message
news:9e121089.0403112016.372b4871@.posting.google.com...
> Hi,
> I am trying to batch processing stored procedures with SQL 2000, MDAC
> 2.8.
> All settings are default. SQLSetStmtAttr is called with
> SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using
> ODBC CALL escape sequence.
> I noticed from SQL Profiler trace that the stored procedure is
> executed as many
> times as the elements in the array (e.g., in separate batches).
> If I change the stored procedure call to a vanilla INSERT then all of
> the INSERTs are performed in one batch.
> Is there any extra settings to be done to execute the procedure in a
> true batch execution mode rather than a simulated batch processing.
> In the past someone complained about the same issue but the only
> suggestion was to concatenate all stored procedure call strings into
> one big string and then execute it in one batch. But then there is no
> way to return the array of results or errors.
> Any hints will be appreciated. Thanks.
> Kong|||Hi, oj,
Thanks for the reply. But according to MSDN
http://msdn.microsoft.com/library/d...br />
2msz.asp
It is possible to batch stored procedure calls.
I understand Microsoft manual page is not always correct :-) Just
want to make sure that it is indeed "by design" before I resort to
other methods.
Thanks again.
Kong
"oj" <nospam_ojngo@.home.com> wrote in message news:<egJEgLACEHA.684@.tk2msftngp13.phx.gbl>..
.
> This is by design. Each stored procedure is executed within its own scope
for
> each call.
> --
> -oj
> http://www.rac4sql.net
>
> "Kong Li" <likong@.email.com> wrote in message
> news:9e121089.0403112016.372b4871@.posting.google.com...|||Kong,
This statement implies that you do not have any control.
"The Microsoft SQL ServerT ODBC driver automatically batches stored
procedure calls to the server when appropriate."
I'll check to see if there is any and post back when I have an answer.
-oj
http://www.rac4sql.net
"Kong Li" <likong@.email.com> wrote in message
news:9e121089.0403121052.4137690b@.posting.google.com...
> Hi, oj,
> Thanks for the reply. But according to MSDN
>
http://msdn.microsoft.com/library/d..._6_035_2msz.asp[
color=darkred]
> It is possible to batch stored procedure calls.
> I understand Microsoft manual page is not always correct :-) Just
> want to make sure that it is indeed "by design" before I resort to
> other methods.
> Thanks again.
> Kong
>
> "oj" <nospam_ojngo@.home.com> wrote in message[/color]
news:<egJEgLACEHA.684@.tk2msftngp13.phx.gbl>...
scope for