Re: VB-ADO-SQL Server : SQL Server performs logins after some queries
From: Oscar (oku_at_xs4all.nl)
Date: 03/21/04
- Next message: Veign: "Re: Is this the right method for opening recordsets in ADO ?"
- Previous message: Oscar: "Re: Is this the right method for opening recordsets in ADO ?"
- In reply to: Val Mazur: "Re: VB-ADO-SQL Server : SQL Server performs logins after some queries"
- Next in thread: Val Mazur: "Re: VB-ADO-SQL Server : SQL Server performs logins after some queries"
- Reply: Val Mazur: "Re: VB-ADO-SQL Server : SQL Server performs logins after some queries"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 21 Mar 2004 14:55:51 +0100
Hi Val,
Setting the Activeconnection was a good approach. The problems almost were
solved. However, sometimes it appeared again. After this I have set the
Cursorlocation to Client side and also didn't set the Activeconnection
property and this looks like the solution, since I didn't see the problem
again up to now.
thanks, so far for your help.
Oscar
"Val Mazur" <group51a@hotmail.com> schreef in bericht
news:uyub0orDEHA.3980@TK2MSFTNGP09.phx.gbl...
> 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 ?
> >> >
> >> >
> >>
> >>
> >
> >
>
>
- Next message: Veign: "Re: Is this the right method for opening recordsets in ADO ?"
- Previous message: Oscar: "Re: Is this the right method for opening recordsets in ADO ?"
- In reply to: Val Mazur: "Re: VB-ADO-SQL Server : SQL Server performs logins after some queries"
- Next in thread: Val Mazur: "Re: VB-ADO-SQL Server : SQL Server performs logins after some queries"
- Reply: Val Mazur: "Re: VB-ADO-SQL Server : SQL Server performs logins after some queries"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
