Asynchronous ADO procedure invocation?

Is there any way to send several ADO Command.Execute asynchronously at once?
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeIn the following example, an event handler has been implemented to print to
the Debug window when the command has completed:
Dim WithEvents conn As ADODB.Connection
Sub Form_Load()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=SQLOLEDB;Data Source=sql70server;" _
& "User ID=sa;Password='';Initial Catalog=pubs"
conn.Open
Set cmd.ActiveConnection = conn
cmd.Execute "select * from authors", , adAsyncExecute
Debug.Print "Command Execution Started."
End Sub
Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)
Debug.Print "Completed Executing the Command."
End Sub
Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
> Is there any way to send several ADO Command.Execute asynchronously at
> once?
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Thanks for your answer.
However, I found that the secondary asynchronous Execute fails while the
first asynchronous Execute is not completed yet. Is this phenomenon
ordinary? Or is there any way to enable more overlapped Execute be allowed?
Thanks.
Regards,
Hyun-jik Bae
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> In the following example, an event handler has been implemented to print
> to
> the Debug window when the command has completed:
> Dim WithEvents conn As ADODB.Connection
> Sub Form_Load()
> Dim cmd As ADODB.Command
> Set cmd = New ADODB.Command
> Set conn = New ADODB.Connection
> conn.ConnectionString = _
> "Provider=SQLOLEDB;Data Source=sql70server;" _
> & "User ID=sa;Password='';Initial Catalog=pubs"
> conn.Open
> Set cmd.ActiveConnection = conn
> cmd.Execute "select * from authors", , adAsyncExecute
> Debug.Print "Command Execution Started."
> End Sub
> Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
> pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
> pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
> ByVal pConnection As ADODB.Connection)
> Debug.Print "Completed Executing the Command."
> End Sub
> Hope you mean ADO not ADO.NET ;-) For further information look in MSDN
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
> news:eHtTcykRFHA.3988@.tk2msftngp13.phx.gbl...
>|||There should be the possibility to do this unless you will not use the same
connection for it.
Jens Suessmeyer.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> schrieb im Newsbeitrag
news:uCgnSElRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> Thanks for your answer.
> However, I found that the secondary asynchronous Execute fails while the
> first asynchronous Execute is not completed yet. Is this phenomenon
> ordinary? Or is there any way to enable more overlapped Execute be
> allowed?
> Thanks.
> Regards,
> Hyun-jik Bae
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfiXV1kRFHA.3156@.TK2MSFTNGP15.phx.gbl...
>

Asynchronous ADO Command execute

I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,
ThanksIt also happens inconsistently. Sometimes, it just runs fine and sometimes, it just gives a Program Error and everything blows out.|||Originally posted by archnam
I am getting a "Program Error" when I try to run a stored procedure. It says it generates an error log but I don't find it in Windows 2000.
Has anyone seen anything like this?
Please help,

Thanks

Have you looked in the SQL Server error log in enterprise manager?|||Where exactly are error logs in the SQL Server under Enterprise Manager?
Also, if I put a breakpoint in my program, it seems to run fine and when I just run it, it bombs, do you think may be I should put some delay in it. The Command.Execute might be taking some time to run.

AsyncExecute problem

Hi there!
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:

mdbConnection->BeginTrans();
_CommandPtr cmd;
cmd.CreateInstance( __uuidof( Command ) );
cmd->ActiveConnection = mdbConnection;
cmd->CommandType = adCmdStoredProc;
cmd->CommandText = _bstr_t( SP_DDPOM );
cmd->Parameters->Append( cmd->CreateParameter( "@.0", adVarChar,
adParamInput, 10, _bstr_t( dateod ) ) );
cmd->Parameters->Append( cmd->CreateParameter( "@.1", adVarChar,
adParamInput, 10, _bstr_t( datedo ) ) );
cmd->Execute( &vtEmpty, &vtEmpty2, adAsyncExecute );
int state;
while((state=cmd->State) & adStateExecuting) {
TRACE("State = %ld\n", state );
// if cancel then mdb->RollbackTrans(); return;
}
mdbConnection->CommitTrans();

The thing is that the state is adStateExecuting for about 16 seconds
(30days interval), and afterwards drops to 0 (adStateClosed), while the
sp is still executing (which usually takes about a minute). The process
waits at the CommitTrans() until the sp is done. There aren't any errors
reported or exceptions thrown.

BUT: NO DATA IS FILLED IN THE TABLE.

I've tested that begin-commit works by adding a call to another sp, and
it works fine.

The weird thing is that the sp works if the execution takes less then
this 16 or so second limit. E.g. for 3days interval, it works fine.

The weirdest thing is that the whole thing worked at first, i.e. did not
produce erroneous state and filled the table, and that for 5 minutes
execution and more. Then I changed something in the sp (commented out a
single line!!), and ever since then it doesn't work anymore. I've got
the database backup from when it worked, but doesn't anymore.

The sp works fine when executed from Query Analyzer.

Has anyone got any ideas? Am I missing something?
Thanks,
Juhu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!juhu (juhu@.bruhu.com) writes:
> The thing is that the state is adStateExecuting for about 16 seconds
> (30days interval), and afterwards drops to 0 (adStateClosed), while the
> sp is still executing (which usually takes about a minute). The process
> waits at the CommitTrans() until the sp is done. There aren't any errors
> reported or exceptions thrown.

I don't have any experience of asynchronous queries. But it could be
that the command timeout strikes. This timeout is by default 30 seconds.
Set it to 0 to get rid of it:

cmd->CommandTimeout = 0;

My first thought was that you have a pseudo-recordset in form of a
rowcount from an INSERT into a temp table. But since it works with
smaller amount of data it may not be that. Nevertheless, add a SET
NOCOUNT ON in the stored procedure if you don't have one.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

assigning process priority to transactions?

I am using ADO 2.8 and SQL server 2000.
Is there any way to assign process priorities to multiple DB connections?
For example, consider that there is a long-time taking transaction and many
short-time transactions running on a server machine. I want to assign low
priority to the long-time transaction to prevent from starvation of
short-time transactions. Of couse, let's assume that there is no locking
influence between them.
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeHi
I am affraid you cannot do that. Perhaps you want to look at SET
LOCK_TIMEOUT command.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:upjA1nJbFHA.228@.TK2MSFTNGP12.phx.gbl...
> I am using ADO 2.8 and SQL server 2000.
> Is there any way to assign process priorities to multiple DB connections?
> For example, consider that there is a long-time taking transaction and
many
> short-time transactions running on a server machine. I want to assign low
> priority to the long-time transaction to prevent from starvation of
> short-time transactions. Of couse, let's assume that there is no locking
> influence between them.
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>

ASP.NET w/ SQL Server Sessions & Cluster Failover

Is there an elegant way to clear out the ADO connection pool used by the SQL
Server State Service following a cluster failover?
We are using a SQL Cluster to store both the ASP Session State database and
our application database.
When the SQL Server cluster is failed-over, any "pointers" to the live
connections stored in the connection pool are made invalid. When the
application tries to reference these "damaged" connections, it throws an
exception, and the pooling mechanism drops the bad connections from the
pool.
In our application, we are using a looping try/catch block to clear out the
bad connections from the pool following a failover, and the users typically
are not affected by the failover as a result of any exceptions in our
application code.
However, we don't have access to the source code for managing the ASPState
connections. It appears as though there is no mechanism for the ASPState
database to clear out the bad connections from the pool following a
failover, and thus the exceptions are affecting users.
Is SQL Server Session State Management even supposed to work with a SQL
Cluster?
Mike Olund
OpenRoad Communications
Same thing happens to us. We use ASP.Net connection pools very heavily and
they have some issues with bad connections after a failover. We also use a
try/catch to reconnect if there is a bad connection. If you find anything
else, please let me know.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"news.microsoft.com" <molund@.oroad.com> wrote in message
news:OeKzPeBREHA.568@.TK2MSFTNGP12.phx.gbl...
> Is there an elegant way to clear out the ADO connection pool used by the
SQL
> Server State Service following a cluster failover?
> We are using a SQL Cluster to store both the ASP Session State database
and
> our application database.
> When the SQL Server cluster is failed-over, any "pointers" to the live
> connections stored in the connection pool are made invalid. When the
> application tries to reference these "damaged" connections, it throws an
> exception, and the pooling mechanism drops the bad connections from the
> pool.
> In our application, we are using a looping try/catch block to clear out
the
> bad connections from the pool following a failover, and the users
typically
> are not affected by the failover as a result of any exceptions in our
> application code.
> However, we don't have access to the source code for managing the ASPState
> connections. It appears as though there is no mechanism for the ASPState
> database to clear out the bad connections from the pool following a
> failover, and thus the exceptions are affecting users.
> Is SQL Server Session State Management even supposed to work with a SQL
> Cluster?
> Mike Olund
> OpenRoad Communications
>
|||there is support in asp.net 2.0, the supported method in asp.net 1.1 is to
change the connection string (thus not reusing any old connections).
if you want an unsupported method, you can use reflection to call an
undocumented method.
(http://www.sys-con.com/dotnet/article.cfm?id=483)
-- bruce (sqlwork.com)
"news.microsoft.com" <molund@.oroad.com> wrote in message
news:OeKzPeBREHA.568@.TK2MSFTNGP12.phx.gbl...
> Is there an elegant way to clear out the ADO connection pool used by the
SQL
> Server State Service following a cluster failover?
> We are using a SQL Cluster to store both the ASP Session State database
and
> our application database.
> When the SQL Server cluster is failed-over, any "pointers" to the live
> connections stored in the connection pool are made invalid. When the
> application tries to reference these "damaged" connections, it throws an
> exception, and the pooling mechanism drops the bad connections from the
> pool.
> In our application, we are using a looping try/catch block to clear out
the
> bad connections from the pool following a failover, and the users
typically
> are not affected by the failover as a result of any exceptions in our
> application code.
> However, we don't have access to the source code for managing the ASPState
> connections. It appears as though there is no mechanism for the ASPState
> database to clear out the bad connections from the pool following a
> failover, and thus the exceptions are affecting users.
> Is SQL Server Session State Management even supposed to work with a SQL
> Cluster?
> Mike Olund
> OpenRoad Communications
>