multi-thread tasks using a single connection object concurrently

is it possible to use a single connection object shared by several
tasks where each task is handled by a thread? these tasks call stored
procedures that return record sets, no editing, update or delete.

my objective is that db connection is expensive and each user can only
have 1 connection object. each user submits a request to the web
server, and the result web page construction consists of a number of
result sets obtained from several stored procedure from the same
database instance.

i could obtained my result sets by making each sp call one at a time
but was thinking whether i could instead break up the tasks, create a
thread for each task that shares the same db connection object
concurrently. the connection object will be alive until all tasks are
completed, that is if the final object ive could be achieved in a
shorter time. No connection pooling here.

i am wondering over questions like:

1. within the same db instance, if 2 stored procedures need to access
the same table, does SQLserver queue-up and service one request at a
time.

2. would there be any problem using the connection object this way,
sharing between multiple theads?

3. is it possible that a 'dead-lock' may occur within the DB?

of cos this whole idea can be absurd and into the trash if the
connection object doesn't support multi-thread and is queue based in
the first place.

pardon me if my SQL server basics is off track. been doing too many
other things.

thanks,
mengmeng (hui_km@.star-quest.com) writes:
> i could obtained my result sets by making each sp call one at a time
> but was thinking whether i could instead break up the tasks, create a
> thread for each task that shares the same db connection object
> concurrently. the connection object will be alive until all tasks are
> completed, that is if the final object ive could be achieved in a
> shorter time. No connection pooling here.

I honestly don't know if you can share connection objects between threads,
but in any case it seems like a pointless thing, because the connection
object would be a common resource. You cannot submit to queries on the
same connection object at the same time. ...ah, wait, actually with ADO
you can, but what happens is that ADO opens a new connection behind your
back for you.

> 1. within the same db instance, if 2 stored procedures need to access
> the same table, does SQLserver queue-up and service one request at a
> time.

Depends on how they procedures access the tables. For read operations,
the procedures may well execute in parallel. But if one process starts
to update, the other process will be blocked. (Unless it uses an
isoalation level of uncommitted.)

> 2. would there be any problem using the connection object this way,
> sharing between multiple theads?

As I said, it would be a pointless thing to do.

> 3. is it possible that a 'dead-lock' may occur within the DB?

Yes. That is not to say that you will experience dead-locks, only that
it could happen to you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi erland,

i was thinking the connection object is like a handle to a database
connection, but if has its own state, and if its not a thread-safe
object, then it would not work at all. even if i can wrap the connection
object in thread-safe codes, if it cannot handle thread context switch,
the idea is useless. the part about ado opening a connection on its own
accord is interesting though. i didn't know that at all.

the piece of info is helping decide how i should implement and write my
app server components. thanks for your response.

regards,
meng

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Multiprotocol Encryption Through a Firewall

Hi All,
I am now on my fourth day of trying to establish an 'encrypted' (net-library
encryption) multiprotocol connection from QueryAnalyzer to a remote MSDE 20
00 SP3, through a firewall. At this point, after about 10,000 KB articles,
none of which are exactly o
n point, but all enticingly related, I am to the point of offering up my 'fi
rst-born' to anyone who can help:-)
First SSL is not an option in this environment, so I am trying to enable 'Cl
ientSide' multiprotocol encryption to the remote server. I have enabled the
multiprotocol net library on the server and specified a single port for the
RPC traffic associated wit
h the multiprotocol traffic on the server. I chose 1433 (not to be confused
with the standard TCP/IP listening port) for the RPC traffic because it is
one of the few ports that the 'keepers of the server' have opened through th
eir firewall. I also speci
fied 1433 as the client side RPC port. I set this up per KB 164667.
As long as I don't enable encryption on the client side, I am able to connec
t to and query the remote server. A network packet analyzer shows that a mu
ltiprotocol over TPC/IP (ncacn_ip_tcp) has indeed been established.
When I make the registry entry to enable client side encryption at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\RPCNetlib
Value Name: Security
Value Type: REGS_SZ
Value: Encrypt
I am no longer able to connect to the remote server. After the timeout, I g
et the standard "SQL Server does not exist or access denied" message.
My best guesses as to the probable problem areas is either the need to open
additional ports through the firewall (though unencrypted traffic flows thro
ugh it just fine on 1433) or some sort of RPC authentication problem on the
server end. FWIW, the serv
er computer is a member of a Workgroup, not a domain.
The 'Keepers of the Server' will not allow opening ALL ports through the fir
ewall for troubleshooting, even with a promise that unnecessary ports can be
closed later.
I am most interested in advice from someone who has actually made this confi
guration work but any and all suggestions would be most welcome!
Thanks,
DougYou'll also need the RPC endpoint mapper ports open as well to get this
working.
This would require ports 135 open on the firewall.
The other problem is that the machine is in a workgroup, so the
authentication might be failing.
See:
239894 How to establish encrypted multiprotocol connections with SQL Server
7.0
http://support.microsoft.com/?id=239894
Make sure that you don't have the encrypt bit flipped on both the client
and the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Establishing an Encrypted Multiprotocol Net Library Connection to a
Remote SQL Server 2000 (MSDE 2000) through a firewall with only a single
port open.
We have just emerged, scarred but successful from the very painful
process of trying to establish an encrypted multiprotocol net library
connection to a remote SQL Server (MSDE) and I wanted to share the
technique in hopes of sparing others the pain.
The Problem:
A Db-Library application must establish a secure connection, over the
internet, to a remote MSDE. The MSDE sits behind a firewall at a very
security conscious facility. The SQL Server is running on a Windows
2003 Server computer that is not part of domain, but instead in a
Workgroup. Opening a large number of ports through the firewall, and
especially the RPC end-point mapper port (TCP 135) is not an option. We
cannot use SSL (an easy solution using a CA certificate due to a
Microsoft bug in db-lib (Bug #94129 submitted by Kevin McDonnell (MSFT)
which prevents db-lib connections from establising a connection to a
server using a certifcate.
The Solution:
For over a week, we were unable to find any information on the web in
newsgroups, MSDN or Microsoft site that was exactly on point, but we did
compile a number of articles that led us to the solution. Some of these
articles pertained to SQL Server 6.5, some to 7.0, and some had to do
with setting up Replication (which in itself was of no interest to us).
A special thanks to Kevin McDonnel of Microsoft for some very helpful
responses to desperate postings on various newgroups.
Firstly, a closely guarded Microsoft HotFix (KB-814950 which references
Microsoft Bug # 363783) must be obtained by calling Microsoft. There is
a lot of possible confusion here because after about 30 minutes of
'grilling' by a Microsoft tech to make sure that you really needed the
HotFix, you will be directed to a download site for a HotFix (KB-815495
entitled Cumulative Security Patch For SQL Server and discusses Named
Pipes vulnerability, with no mention of fixing a problem with
multiprotocol encryption). Download and apply the patch anyway to the
MSDE 2000.
On the MSDE 2000 computer, using the Server Network Utility, enable the
Multiprotocol Net Library and the TCP/IP Net Library. Select
Multiprotocol and with the Properties button and check the Enable
Encryption checkbox. This will force encryption for all multiprotocol
net library connections. Now select the TCP/IP Net Library and with the
Properties button change the Port number to anything other than 1433
(the default) just to get it out of the way of Port 1433 that will be
used by RPC over TCP/IP. Press Apply, acknowledge the Restart message,
press Close and again acknowledge the same message.
Still on the MSDE 2000 computer, create the registry entry to cause SQL
Server to listen for RPC/Multiprotocol connections on Port 1433.
Reference KB-164667. At
HKEY_LOCAL_MACHINE\Software\Microsoft\MS
SQLSERVER\MSSQLSERVER, create a
Key, if it is not already present, entitled RpCNetlib. To this key
create a MultiString entry (REG_MULTI_SZ) entitled RPCprotocols and for
the value enter "ncacn_ip_tcp,1433".
From the System tray, stop and then restart the SQL Server service.
While on the MSDE 2000 computer, make sure that the RPC service is
running and if not, start it and set it to start
automatically. The RPC Locator service is not required because we are
forcing the RPC over TCP/IP connection to take place on Port 1433 (not
to be confused with the default TCP/IP Port of 1433). As an aside, we
chose to use 1433 for the RPC over TCP/IP connection because the 3rd
party hosting the MSDE 2000 was willing to open Port 1433 through their
firewall. I suspect that any other port of your choosing would work
just as well.
Still on the MSDE 2000 computer, create a local login for the RPC
authentication. I chose to create the login with local administrator
permissions (remember that this computer is a member of a Workgroup, not
a Domain), but again, this level of permissions may not be required, but
at this point, I wanted to take no chances!
You are done with the MSDE 2000 Server computer.
Now for the Client computer, still generally referencing KB-164667,
start the Client Network Utility and create an Alias to the remote MSDE
2000. Specify Multiprotocol and for the Server enter <ip address of
remote MSDE>[1433]. Important* You may be tempted to enter the
"ncacn_ip_tpc:" prefix to the Server connection string as is referenced
in KB-164667, but don't do
it. With this prefix, QueryAnalyzer(which uses ODBC) will work fine and
can be used for verifying the connection and encryption BUT db-library
will not connect, presumably because it does not know how to handle the
prefix! In the Additional Parameters field, you must enter your
authentication credentials so the remote MSDE 2000 computer can validate
your RPC connection. Enter as "<remote computer user name>,<remote
computer password>".
That's it! I would recommend that you test the connection first with
QueryAnalyzer. We tested by first starting a Network Monitor to watch
network traffic and then connecting with QueryAnalyzer using the Alias
that was just created above. Run a query to return some reasonable
amount of data and verify that the data is indeed encrypted. If you
have successfully reached this point, there is every reason to believe
that the db-library application will establish and successfully use the
encrypted connection.
Some Final Thoughts:
This method has allowed us to establish an encrypted multiprotocol
connection which will be honored by db-library, as well as any other
connection method, without the use of SSL certificates on a single port
(1433 in our case) without the need to open a large number of
potentially vulnerable ports (especially 135) through the remote
firewall.
Note that the example above forces the encryption on the server, meaning
that all clients wishing to establish a multiprotocl connection will
have to be set up as described above. The technique works just as well,
however, if you want to establish encryption on a client by client
basis. For this, on the remote MSDE 2000 server computer, do NOT check
the Enable Encryption checkbox in Server Network Utilities but instead
on each client computer, make the following registry entry.
At HKEY_LOCAL_MACHINE\Software\Microsoft\MS
SQLSERVER\Client\RpcNetlib,
create a REG_SZ value name of "Security" and for the value enter
"Encrypt".
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Doug,
Sorry for all the pain & for my delayed responses.
I filed the bug you referenced as well documnted in the kb;
814950 FIX: A computer might transmit nonencrypted data when clients use the
http://support.microsoft.com/?id=814950
The problem with Multiprotocol is that it was used more in SQL 7.x than
with SQL 2000. Since we moved to using certificates with SSL,
in SQL 2000 we see very few calls on it anymore. Most of them ended up in
my lap. <g> DBlib is another dead API that we rarely get issues on.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We are setting up something similar and I'm looking for advice/comments/idea
s. On the client side:
1. We can't run a client utility on the client computer, but our application
can set any necessary registry settings, register dlls, etc.
2. We must use SQL login, not Windows login.
3. The IP address of the SQL server is read dynamically by the client from a
web server.
Thanks,
Ralph

Multiprotocol Encryption Through a Firewall

Hi All,
I am now on my fourth day of trying to establish an 'encrypted' (net-library encryption) multiprotocol connection from QueryAnalyzer to a remote MSDE 2000 SP3, through a firewall. At this point, after about 10,000 KB articles, none of which are exactly o
n point, but all enticingly related, I am to the point of offering up my 'first-born' to anyone who can help:-)
First SSL is not an option in this environment, so I am trying to enable 'ClientSide' multiprotocol encryption to the remote server. I have enabled the multiprotocol net library on the server and specified a single port for the RPC traffic associated wit
h the multiprotocol traffic on the server. I chose 1433 (not to be confused with the standard TCP/IP listening port) for the RPC traffic because it is one of the few ports that the 'keepers of the server' have opened through their firewall. I also speci
fied 1433 as the client side RPC port. I set this up per KB 164667.
As long as I don't enable encryption on the client side, I am able to connect to and query the remote server. A network packet analyzer shows that a multiprotocol over TPC/IP (ncacn_ip_tcp) has indeed been established.
When I make the registry entry to enable client side encryption at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\RPCNetlib
Value Name: Security
Value Type: REGS_SZ
Value: Encrypt
I am no longer able to connect to the remote server. After the timeout, I get the standard "SQL Server does not exist or access denied" message.
My best guesses as to the probable problem areas is either the need to open additional ports through the firewall (though unencrypted traffic flows through it just fine on 1433) or some sort of RPC authentication problem on the server end. FWIW, the serv
er computer is a member of a Workgroup, not a domain.
The 'Keepers of the Server' will not allow opening ALL ports through the firewall for troubleshooting, even with a promise that unnecessary ports can be closed later.
I am most interested in advice from someone who has actually made this configuration work but any and all suggestions would be most welcome!
Thanks,
Doug
You'll also need the RPC endpoint mapper ports open as well to get this
working.
This would require ports 135 open on the firewall.
The other problem is that the machine is in a workgroup, so the
authentication might be failing.
See:
239894 How to establish encrypted multiprotocol connections with SQL Server
7.0
http://support.microsoft.com/?id=239894
Make sure that you don't have the encrypt bit flipped on both the client
and the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Establishing an Encrypted Multiprotocol Net Library Connection to a
Remote SQL Server 2000 (MSDE 2000) through a firewall with only a single
port open.
We have just emerged, scarred but successful from the very painful
process of trying to establish an encrypted multiprotocol net library
connection to a remote SQL Server (MSDE) and I wanted to share the
technique in hopes of sparing others the pain.
The Problem:
A Db-Library application must establish a secure connection, over the
internet, to a remote MSDE. The MSDE sits behind a firewall at a very
security conscious facility. The SQL Server is running on a Windows
2003 Server computer that is not part of domain, but instead in a
Workgroup. Opening a large number of ports through the firewall, and
especially the RPC end-point mapper port (TCP 135) is not an option. We
cannot use SSL (an easy solution using a CA certificate due to a
Microsoft bug in db-lib (Bug #94129 submitted by Kevin McDonnell (MSFT)
which prevents db-lib connections from establising a connection to a
server using a certifcate.
The Solution:
For over a week, we were unable to find any information on the web in
newsgroups, MSDN or Microsoft site that was exactly on point, but we did
compile a number of articles that led us to the solution. Some of these
articles pertained to SQL Server 6.5, some to 7.0, and some had to do
with setting up Replication (which in itself was of no interest to us).
A special thanks to Kevin McDonnel of Microsoft for some very helpful
responses to desperate postings on various newgroups.
Firstly, a closely guarded Microsoft HotFix (KB-814950 which references
Microsoft Bug # 363783) must be obtained by calling Microsoft. There is
a lot of possible confusion here because after about 30 minutes of
'grilling' by a Microsoft tech to make sure that you really needed the
HotFix, you will be directed to a download site for a HotFix (KB-815495
entitled Cumulative Security Patch For SQL Server and discusses Named
Pipes vulnerability, with no mention of fixing a problem with
multiprotocol encryption). Download and apply the patch anyway to the
MSDE 2000.
On the MSDE 2000 computer, using the Server Network Utility, enable the
Multiprotocol Net Library and the TCP/IP Net Library. Select
Multiprotocol and with the Properties button and check the Enable
Encryption checkbox. This will force encryption for all multiprotocol
net library connections. Now select the TCP/IP Net Library and with the
Properties button change the Port number to anything other than 1433
(the default) just to get it out of the way of Port 1433 that will be
used by RPC over TCP/IP. Press Apply, acknowledge the Restart message,
press Close and again acknowledge the same message.
Still on the MSDE 2000 computer, create the registry entry to cause SQL
Server to listen for RPC/Multiprotocol connections on Port 1433.
Reference KB-164667. At
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\ MSSQLSERVER, create a
Key, if it is not already present, entitled RpCNetlib. To this key
create a MultiString entry (REG_MULTI_SZ) entitled RPCprotocols and for
the value enter "ncacn_ip_tcp,1433".
From the System tray, stop and then restart the SQL Server service.
While on the MSDE 2000 computer, make sure that the RPC service is
running and if not, start it and set it to start
automatically. The RPC Locator service is not required because we are
forcing the RPC over TCP/IP connection to take place on Port 1433 (not
to be confused with the default TCP/IP Port of 1433). As an aside, we
chose to use 1433 for the RPC over TCP/IP connection because the 3rd
party hosting the MSDE 2000 was willing to open Port 1433 through their
firewall. I suspect that any other port of your choosing would work
just as well.
Still on the MSDE 2000 computer, create a local login for the RPC
authentication. I chose to create the login with local administrator
permissions (remember that this computer is a member of a Workgroup, not
a Domain), but again, this level of permissions may not be required, but
at this point, I wanted to take no chances!
You are done with the MSDE 2000 Server computer.
Now for the Client computer, still generally referencing KB-164667,
start the Client Network Utility and create an Alias to the remote MSDE
2000. Specify Multiprotocol and for the Server enter <ip address of
remote MSDE>[1433]. Important* You may be tempted to enter the
"ncacn_ip_tpc:" prefix to the Server connection string as is referenced
in KB-164667, but don't do
it. With this prefix, QueryAnalyzer(which uses ODBC) will work fine and
can be used for verifying the connection and encryption BUT db-library
will not connect, presumably because it does not know how to handle the
prefix! In the Additional Parameters field, you must enter your
authentication credentials so the remote MSDE 2000 computer can validate
your RPC connection. Enter as "<remote computer user name>,<remote
computer password>".
That's it! I would recommend that you test the connection first with
QueryAnalyzer. We tested by first starting a Network Monitor to watch
network traffic and then connecting with QueryAnalyzer using the Alias
that was just created above. Run a query to return some reasonable
amount of data and verify that the data is indeed encrypted. If you
have successfully reached this point, there is every reason to believe
that the db-library application will establish and successfully use the
encrypted connection.
Some Final Thoughts:
This method has allowed us to establish an encrypted multiprotocol
connection which will be honored by db-library, as well as any other
connection method, without the use of SSL certificates on a single port
(1433 in our case) without the need to open a large number of
potentially vulnerable ports (especially 135) through the remote
firewall.
Note that the example above forces the encryption on the server, meaning
that all clients wishing to establish a multiprotocl connection will
have to be set up as described above. The technique works just as well,
however, if you want to establish encryption on a client by client
basis. For this, on the remote MSDE 2000 server computer, do NOT check
the Enable Encryption checkbox in Server Network Utilities but instead
on each client computer, make the following registry entry.
At HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\ Client\RpcNetlib,
create a REG_SZ value name of "Security" and for the value enter
"Encrypt".
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Doug,
Sorry for all the pain & for my delayed responses.
I filed the bug you referenced as well documnted in the kb;
814950 FIX: A computer might transmit nonencrypted data when clients use the
http://support.microsoft.com/?id=814950
The problem with Multiprotocol is that it was used more in SQL 7.x than
with SQL 2000. Since we moved to using certificates with SSL,
in SQL 2000 we see very few calls on it anymore. Most of them ended up in
my lap. <g> DBlib is another dead API that we rarely get issues on.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||We are setting up something similar and I'm looking for
advice/comments/ideas. On the client side:
1. We can't run a client utility on the client computer, but our
application can set any necessary registry settings, register dlls,
etc.
2. We must use SQL login, not Windows login.
3. The IP address of the SQL server is read dynamically by the client
from a web server.
Thanks,
Ralph
rbrackert
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message200884.html

Multiple/Duplicate SQL Server Worker Processes

I don't even know where to begin looking... I have a page that loads multiple web user controls...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How?
What do I do to check either my code is creating the connection object properly. Thanks!
Larry
I have discovered, when I created my connection object for ExecuteNonQuery(), I forgot to Close() the connection.
Since I didn't close the connection object when I was finished... anda few new instances of the connection object was created, it created anew connection object.
In other functions, I was using the DataAdapter which opened and closedthe connection for you, so I took that feature for granted andcompletely forgot to close the connection object when doing aExecuteNonQuery().
I get a doht for the day...
Thanks for taking your time in reading...

Multiple threads

Is it safe to use one database connection shared across multiple threads. Each thread has its own set of data i.e its own SQL command etc, but sharing a single database connection. Actually the scenario is that in a web site I want to open a single database connection when web application starts up. On each page hit will construct a SQL command and query/update the database.

I just want to know if multiple requests come at the same time will threads be blocked if I share global connection. What will happen if one request is trying to retrieve the data and another request is updating the database using the same connection?

Thanks

Hi!

I don't think it's a good idea to share DB connection across threads. One of them may open it, another may close, another may break... So many things to keep in mind.

DB connections are fast for open/close and if you make many of them, internally there is pool of connections, so framework will optimize access. If you do not have proven performance drop because of frequent connections opening, then don't waste your time with it - create, open and close connections as logic requires.

One more thing - if you have local connections, then your code is less dependent and can be better maintained, supported, evolved.

|||

Let us imagine a situation that thousands of requests come to web site at a particular time then if I open connection for every request then even the connection pool will be exausted. Second thing I want to clarify that thread does not know if the connection is shared or not so thread will never end up closing the connection. Thread will talk to some kind of manager which will keep connection internally and leave it open for every request.

I still don't know how the thread behaviour will be. If all the requests use the same connection then whether each request will be serialized (running one after previous one is finished) or each can run in parallel.

Any idea?

Thanks

|||

I will agree about thousands and speed problems when I will see some real numbers. So, you can try to simulate test load and watch how the system performs.

Also I want to notice, that connection is logical, it have state, which can be not only Open or Close, but also Executing, Fetching, Broken. What if one of your threads do fetching? All others must wait until it finish. And If you have 2 connections, then you can install 2 network adapters and they can split job. So I still think global connection is not a good thing to do because of scalability & reliability reasons.

Don't think of connections as of physical connections, you do not connect to some port in fact, instead you connect to client side layer, which will take care of real connection. You work with logical connections.

|||Thanks for the suggestions. I will see how it goes.

Multiple statement handles on one connection

I want to use multiple concurrently active statement
handles on a single connection handle. An ODBC call to
SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
indicating that there is a limit of 1 active statement on
a single connection. If I try to SQLExecDirect on another
statement handle on the same connection handle I get an
error complaining that there is another active statement
on the same connection.
Is there any way to configure either SQLServer or its ODBC
driver to allow multiple concurrently active statement
handles on the same connection? This is pretty common
database programming practice to Exec one query, then use
a loop with SQLFetch and then execute multiple SQL
statements on another statement handle within the loop.
Even lowly MSAccess allows multiple concurrently active
statement handles on the same connection.
My ODBC driver version is 2000.85.1022.00.
My SQLServer version SQL Server Developer Edition 8.00.194
(RTM)This is not possible with current versions of SQL Server or the SQL Server
ODBC Driver, when using the default "firehose" cursor. You can use
server-side cursors, which allow you to fetch a single row at a time from
the server (and therefore free up the connection between each row), or you
can cache the data from the first statement yourself (therefore freeing up
the connection). And of course, you can open up a second connection for the
second statement.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Lee Scheffler" <anonymous@.discussions.microsoft.com> wrote in message
news:b47e01c4077d$ef011af0$a101280a@.phx.gbl...
> I want to use multiple concurrently active statement
> handles on a single connection handle. An ODBC call to
> SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
> indicating that there is a limit of 1 active statement on
> a single connection. If I try to SQLExecDirect on another
> statement handle on the same connection handle I get an
> error complaining that there is another active statement
> on the same connection.
> Is there any way to configure either SQLServer or its ODBC
> driver to allow multiple concurrently active statement
> handles on the same connection? This is pretty common
> database programming practice to Exec one query, then use
> a loop with SQLFetch and then execute multiple SQL
> statements on another statement handle within the loop.
> Even lowly MSAccess allows multiple concurrently active
> statement handles on the same connection.
> My ODBC driver version is 2000.85.1022.00.
> My SQLServer version SQL Server Developer Edition 8.00.194
> (RTM)

Multiple SqlDataSources on a Page

I was wondering, if you have multiple SqlDataSources on a single aspx page, does the framework need to open and close a SEPERATE connection for each SqlDataSource. This would make it much less efficient than coding with the ADO.NET objects and just opn and close the connection once irrespective how many quries that page needs.

Your comments would be appreciated.

Yes, each SqlDataSource is unaware of other data sources on the page. Each SQL Data Source can have it own connection string. Thus, sharing a connection between two might not always be possible if they use different connections.

You are correct, the SqlDataSource isnot meant for performance (but it isn't terrible, either). It is a WYSIWYG control meant for developers that are more comfortable designing their application in the IDE designer rather than by code or for simple pages that don't need the overkill of DataSet, Table Adapters, etc.

|||Many Thanks vcsjones.|||Just another question. Is it possible to tell all sqldatasources to use 1 connection object if the connections are all the same?|||

No, not that I am aware of.

|||Thanks again.

Multiple SqlDataSource on a page

Hi,

I placed several SqlDataSource objects on my page which work with the same database (same connection string).

As I know, connection operation to database is costly in the performance prespective.

Do these SqlDataSource controls work with the same connection object or each of them create his own connecton object?

If each create it's own, then can they be changed to work with one connection object?

Thanks

As long as the connections strings are exactly identical, then the connection will get reused automatically as connections are automatically pooled.

Read tip #3:http://msdn.microsoft.com/msdnmag/issues/05/01/ASPNETPerformance/#S4

|||

Thanks,

Yet, another question:

If a SqlDataSource is not connected to a data UI control than does it still go retrieving the data or does it wait to be useed?

|||

It waits for the data to be requestsed such as by calling its Select() method

Multiple shared data source

I have a report that uses one data source connection, and the parameter drop
down list requires a different data source connection. After the report is
deployed, I want the report to use shared data source. I can only change to
a single shared data source through the report properties on the Report
Manager screen. Is it possible to use multiple shared data source?
Better yet, can Microsoft make this feature available for the production
release?You should see all data sources in Report Manager assuming your report has
multiple data sets using multiple shared data sources.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alex Fung" <AlexFung@.discussions.microsoft.com> wrote in message
news:572B623D-725C-4F47-9268-9BB2A24EE32C@.microsoft.com...
>I have a report that uses one data source connection, and the parameter
>drop
> down list requires a different data source connection. After the report
> is
> deployed, I want the report to use shared data source. I can only change
> to
> a single shared data source through the report properties on the Report
> Manager screen. Is it possible to use multiple shared data source?
> Better yet, can Microsoft make this feature available for the production
> release?