How to execute multiple procedures on same sql process
-
Friday, September 14, 2012 3:12 PMI am wondering if there is a way to call several stored procedures from a remote server and have each of the procedures executed on the same database process (SPID).
We have a desktop application using .NET remoting. On the remote server, we use the Microsoft Enterprise Library Data Access Application Block to execute the stored procedures and queries. My goal is to execute several procedures in a way that all of them are executed on the same SPID. Currently, I have tried something like the following (spName1, spName2, ...spNameN are strings that represent the stored procedures' names):
'create the database object and a connection to it
Dim db As Database = DatabaseFactory.CreateDatabase
Dim conn As DbConnection = db.CreateConnection()
'create the commands for all the procedures
Dim cmd1 As DbCommand = db.GetStoredProcCommand(spName1)
Dim cmd2 As DbCommand = db.GetStoredProcCommand(spName2)
...
Dim cmdN As DbCommand = db.GetStoredProcCommand(spNameN)
'open the connection
conn.Open()
'assign it to all the commands
cmd1.Connection = conn
cmd2.Connection = conn
...
cmdN.Connection = conn
'execute the procedures
db.ExecuteReader(cmd1)
db.ExecuteReader(cmd2)
...
db.ExecuteReader(cmdN)
'close the connection
conn.Close()
My expectation here was that when I opened the connection, it'd be assigned a database process (SPID). Then, I could use it to execute all the procedures on that process and when I closed it, that connection and associated database process would be returned to the pool. Unfortunately, this does not appear to be the case. All procedures write their SPID into a log table, and I can see in that table that the procedures are being executed on different processes. I have searched for solutions, but am coming up empty. The only thing that seemed to work was starting a DB transaction, then executing the procedures using that transaction, then committing the transaction. However, these procedures will be used for data retrieval, so (a) there is no need for a transaction and (b) it seems to greatly degrade performance (understandably).
Any help or point in a direction would be appreciated! Thanks!

