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

From: Oscar (oku_at_xs4all.nl)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 02:08:29 +0100

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: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... If your query works very slow, then you could face an issue with ... > with a SQL Server 2K database as well as MS Access> DAO. ... Can using of ADODB recordsets instead of object give a far more better> performance? ... >>>>> At the start of the application the connection object is created ...
    (microsoft.public.vb.database.ado)
  • Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
    ... Cursorlocation to Client side and also didn't set the Activeconnection ... >> At the start of the application the connection object is created ... >> All recordsets in all procedure refer to this specific DB connection ... >> rst.Open strSQL, DB, adOpenForwardOnly, adLockReadOnly ...
    (microsoft.public.vb.database.ado)
  • Re: Help needed with ADO Update
    ... Several vbscript examples show the same usage, ... This is ASP using vbscript, ... References suggest that it is an option to pass the connection object ... recordsets should be used exclusively for retrieving ...
    (microsoft.public.data.ado)
  • Is this the right method for opening recordsets in ADO ?
    ... What is the good way to open recordsets in ADO. ... connection object just one time and have all recordsets when they are ...
    (microsoft.public.vb.database.ado)
  • Re: passing parameters/globals
    ... So you have global variables that are passed through the DTSRUN interface to ... DataSource property of the text file connection object. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > The dts package consists of just 2 connection objects and a transform ...
    (microsoft.public.sqlserver.dts)
Loading