Re: VB-ADO-SQL Server : SQL Server performs logins after some queries

From: Val Mazur (group51a_at_hotmail.com)
Date: 03/20/04 

Date: Sat, 20 Mar 2004 15:30:38 -0500

Hi Oscar,

It should not open new connection, based on your code. Looks like your
recordsets pickup connection string from your opened connection instead of
the actual connection. Try to set ActiveConnection property of your
recordset before calling Open and remove connection from the method itself

Set rst = New ADODB.Recordset
Set rst.ActiveConnection= DB
rst.Open strSQL, , adOpenForwardOnly, adLockReadOnly 

-- 
Val Mazur
Microsoft MVP
"Oscar" <oku@xs4all.nl> wrote in message 
news:405a480b$0$32701$e4fe514c@dreader17.news.xs4all.nl...
> Hi Val,
>
> At the start of the application the connection object is created
>
> Set db = New ADODB.Connection
> DB.Provider = "SQLOLEDB"
> DB.Properties("Data Source").Value = "80.xxx.yy.aaa,1433"
> DB.Properties("Network Library").Value = "DBMSSOCN"
> DB.Properties("Initial Catalog").Value = "CompanyDB"
> DB.Properties("User ID").Value = "sa"
> DB.Properties("Password").Value = pw
> DB.Open
>
>
> All recordsets in all procedure refer to this specific DB connection 
> object
>
>
> for opening not updatable recordset :
> Set rst = New ADODB.Recordset
> rst.Open strSQL, DB, adOpenForwardOnly, adLockReadOnly
>
> for adding/updating records
> Set rst = New ADODB.Recordset
> rst.Open strSQL, DB, adOpenForwardOnly, adLockOptimistic
> rst.Add
> rst!field1=value1
> rst.Update
> rst.Close
>
> for deleting records
> Set rst = New ADODB.Recordset
> rst.Open strSQL, DB, adOpenForwardOnly, adLockOptimistic
> rst.Delete
> rst.Close
>
>
>
> Meanwhile I've found the origin of all my SQL Server errors that I faced :
> lifetime of connection object
> I am porting a VB- Access DAO application to a VB-ADO SQL Server 2K
> application. While the translated coding didn't give problems for small
> routines there was one large Subroutine that processed an employee list 
> one
> for one in order to report a table. During the processing of each employee
> there were exactly 15 recordsets created while sometimes about 8 
> recordsets
> were 'live' at the same time. All these recordsets were closed correctly.
> While all these recordsets referred to the same connection object DB it
> probably caused capacity problems, SQL Server appeared quite instable and
> every time reported that 'SQL was not there or that there was no accesss
> granted nearby the completion of the subroutine. At such a point, SQl 
> Server
> hanged and I had to waite about 2-3 minutes before the VB debugger was 
> able
> to continue. One of your responses to another thread in this group gave me
> the idea to think about more connection objects. In the case of  DAO I was
> not used to open one or more connection objects for different recordsets 
> and
> the application was able to have all recordsets refer  to the one and only
> Database object. I decided to create 15 ADODB connection objects in order 
> to
> have each recordset refer to its own connection object and see : all 
> errors
> disappeared and the subroutine was performed almost 3 times as fast as I 
> was
> used to.
>
> Probably my problems were initiated by a wrong lifetime control of
> connection objects. Therefore I'd like to know which method I should use :
>
> Create about 15 connection objects at start up of the application. Each
> recordset within the procedures should refer to one of these 15 connection
> objects.
>
> OR
>
> Each subroutine should create its own connection objects and have the
> recordset refered to this object when its created. In case that a new
> recordset needs to be created while there is still a recordset opened with
> the former connection object, a new connnection object should be created 
> in
> order to have the new recordset point at the new connection object and
> therefore release the former connection objects from capacity problems.
>
> I'd like to hear your advise very much.
>
> Oscar
>
>
>
>
>
>
>
>
>
>
>
>
> "Val Mazur" <group51a@hotmail.com> schreef in bericht
> news:%234fIMVUDEHA.2920@TK2MSFTNGP09.phx.gbl...
>> Hi Oscar,
>>
>> How do you open your recordsets?
>>
>> -- 
>> Val Mazur
>> Microsoft MVP
>>
>>
>> "Oscar" <oku@xs4all.nl> wrote in message
>> news:405a08a8$0$141$e4fe514c@dreader4.news.xs4all.nl...
>> >
>> > I am testing a VB application that connects through ADO 2.7 and OLEDB
> with
>> > A
>> > SQL Server 2K database.
>> > For a reason that I don't know SQL Server seems to perform a login each
>> > time
>> > that some queries are executed.
>> >
>> > This is what was logged in the tracer :
>> >
>> > SQLBatchCompleted      SELECT *  FROM TabEmployee ORDER BY kind
>> > errorlog                            (date) (time)  logon     login
>> > succeeded
>> > for user 'sa' Connection non-trusted
>> > eventlog                           18454  login succeeded for user 'sa'
>> > Connection non-trusted
>> > .
>> > .
>> > .
>> > SQLBatchCompleted      SELECT *  FROM TabClient
>> > exception                        error 2809 , severity 18 , state 1
>> > SQLBatchCompleted      exec tblContracting
>> > exception                        error 2809 , severity 18 , state 1
>> >
>> >
>> >
>> > this is my connectionstring :
>> >
>> > Set db = New ADODB.Connection
>> > db.Provider = "SQLOLEDB"
>> > db.Properties("Data Source").Value = "80.xxx.yy.aaa,1433"
>> > db.Properties("Network Library").Value = "DBMSSOCN"
>> > db.Properties("Initial Catalog").Value = "CompanyDB"
>> > db.Properties("User ID").Value = "sa"
>> > db.Properties("Password").Value = pw
>> > db.Open
>> >
>> > The client only uses this connectionstring at the start up of the 
>> > client
>> > app. Each selection and update query looks like "SELECT * FROM
> tblEmployee
>> > WHERE ........"
>> > Each record adding event uses the ADODB  .update event and all these
>> > queries
>> > refer to the db object which was set right at the startup.
>> > For not updatable recordsets I use the cursors
>> > adOpenForwardOnly,adLockReadOnly
>> > For adding ,deleting and update queries I use the adOpenForwardOnly,
>> > adLockOptimistic cursors.
>> >
>> > Any idea what the problem(s) could be ?
>> >
>> >
>>
>>
>
>

Relevant Pages

  • Re: Does Bulk Insert require different permission to a SQL Sever database?
    ... I am trying to transfer data from Excel to a table in SQL Server. ... created two connection objects. ... The other connection object which connects successfully to the Excel ...
    (microsoft.public.vb.database.ado)
  • Re: VB6 ADO DB2 - Update Problem
    ... Jeweils ein Feld der beiden ... Was veranlasst Dich zu der Annahme, dass Daten eines Recordsets mit ... Man kann eine Connection auch ohne ein solches Ungetüm öffnen, ... Warum serverseitiger Cursor? ...
    (microsoft.public.de.vb.datenbank)
  • Re: Too many client tasks - help please
    ... It will delay the change to SQL Server at any rate ... > Don't use recordsets for data modification. ... on each page open your connection ... > strings instead of open connection objects when opening recordsets. ...
    (microsoft.public.inetserver.asp.db)
  • Re: ADODB
    ... In general, use datasets (the .NET equivalent of recordsets), and bind ... I make a connection to a SQL2005 database when my app loads using the ... LogonUsers = 0 ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Timeout
    ... Dim Rst as ADODB.Recordset ... rst.Open sql, cn, adOpenKeyset, adLockReadOnly <- hier kommt der timeout ... Eigenschaften Deines Recordsets schon vor dem .Open mit den richtigen Werten ... Set .Active Connection = Cnn ...
    (microsoft.public.de.vb.datenbank)