@@SPID and ADODB.Connection : Example

Here an code example code with this problem :
Option Explicit
Private Sub Form_Load()
Dim Connect As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Connect.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=Morgann;Data Source=(local);Use Procedure
for Prepare=1;Auto Translate=True;Packet Size=4096;Application
Name=Graphiplus 11.0.2;Workstation ID=SYLVAIN;Use Encryption for
Data=False;Tag with column collation when possible=False"
Debug.Print Connect.Execute("Select @.@.SPID").Fields(0).Value 'Return 52
Rs.Open "Select * From DEVIS", Connect
Debug.Print Connect.Execute("Select @.@.SPID").Fields(0).Value 'Retuen 53
End Sub
hi Sylvain,
Sylvain Aufrre wrote:
> Here an code example code with this problem :
using a SQL Server trace, you can monitor that performing the SELECT @.@.SPID
statement using a server side cursorlocation (adUseServer, the default
setting) will cause
set @.P2=8
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=1
exec sp_cursoropen @.P1 output, N'SELECT @.@.SPID', @.P2 output, @.P3 output, @.P4
output
select @.P1, @.P2, @.P3, @.P4
that's to say a server cursor operation...
as only 1 statement can be active per connection and the cursor has not been
still completely fetched and released (the recordset is still open), the
successive call to
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value
will force to open an implicit connection to perform the desired operation
(or cancelling the preceding task is required)..
destroying the adodb.recordset will reset the server side cursor situation
to normality
or... changing the connection CursorLocation to client (adUseClient) will
force the OLE DB Cursor Service to be involved, and, as it's known as Client
Cursor Engine, will force the entire result to be transferred not one row at
a time (server side standard) but as a whole, as the Client Cursor Engine
will provide the required storage area at client level int it's own cache,
also providing scroll, filter, sort and search features as it provides a
Rowset in local (client) memory
Dim oCon As ADODB.Connection
Set oCon = New ADODB.Connection
With oCon
.ConnectionString = "Provider=sqloledb;Data Source=(Local);Initial
Catalog=pubs;Integrated Security=SSPI;"
.CursorLocation = adUseClient
.Open
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value 'Retuen 53
End With
Dim oRs As ADODB.Recordset
Set oRs = New ADODB.Recordset
oRs.Open "SELECT @.@.SPID", oCon, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print oRs.Fields(0).Value
oRs.Close
Set oRs = Nothing
Set oRs = New ADODB.Recordset
'oRs.Open "SELECT TOP 1 * FROM authors", oCon, adOpenStatic,
adLockReadOnly, adCmdText
oRs.Open "SELECT @.@.SPID", oCon, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print oRs.Fields(0).Value
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value 'Retuen 53
oRs.Close
Set oRs = Nothing
Debug.Print oCon.Execute("SELECT @.@.SPID").Fields(0).Value 'Retuen 53
oCon.Close
Set oCon = Nothing
--<--
52
52
52
52
52
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply