August 22, 2002 at 10:59 pm
Hi,
I'm new to SQL Server so this may be basic and obvious but is there a difference between Connection objects and the User Connection count? I'm developing in VB and use Janus Grid for the user interface. I open a form and get a connection for the grid. In my code I open another connection for 'internal' work I need to do - fine to here, the database connection count has increased by two. All my internal recordsets share the same connection object but when I open recordsets (using cn.Command) my user connection count increases by one for each recordset opened. Is this normal??
While I'm here, one other question - I'm using Terminal Services with an App Server connected to a Data Server. I know SQL Server licencing requires a licence for each 'connected device', eg workstation but is my App Server counted as one device or is each Terminal Services session counted as a device?
Cheers, Peter
August 23, 2002 at 5:25 am
Dont think its normal, but I'd have to double check to be 100% certain. Not uncommon for 3rd party controls to open additional connections unknown to you, but I think if you're using one connection that is all that should show up. Can ou post some code?
On the licensing, I truly expect that each TS session needs a CAL. When in doubt, call your MS rep. Deciphering their licenses....not a lot of fun.
Andy
August 23, 2002 at 2:55 pm
Hi Andy,
Thanks for taking the time with this. Following is code snippit that demonstrates the problem and shows how I'm access the database. My environment is:
Visual Studio SP 5
Windows Advanced Server SP1
SQL Server SP2
ActiveX Data Object 2.7
Option Explicit
Dim mclsLoadRecordsets As New TMSGradeUtes.clsLoadRecordsets
Dim mrsSTAStation As ADODB.Recordset
Dim mrsSTRStationTerm As ADODB.Recordset
Dim mrsPPAPackProfileAlloc As ADODB.Recordset
Dim mrsPPSPalletGradeWeights As ADODB.Recordset
Dim mrsPKPPackProfiles As ADODB.Recordset
Dim cn As ADODB.Connection
Private Sub Form_Load()
CreateDBConnection
SetGlobalRS
With mclsLoadRecordsets
Set mrsSTAStation = .GetSTArs(cn, "Sammyer 1")
Set mrsSTRStationTerm = .GetSTRrs(cn, 1, "{4443776C-5CFC-4B8E-A41B-513484A68E44}")
Set mrsPPAPackProfileAlloc = .GetPPArs(cn, "{32A60EE4-D5A4-47AA-8290-797F37737E8E}")
Set mrsPPSPalletGradeWeights = .GetPPSrs(cn, "{32A60EE4-D5A4-47AA-8290-797F37737E8E}")
Set mrsPKPPackProfiles = .GetPKPrs(cn, "{32A60EE4-D5A4-47AA-8290-797F37737E8E}")
End With
' At this point my user connection count (as measured by NetIQ's free utiliy)
' has increased by 5.
Stop
End Sub
Private Sub CreateDBConnection()
Set cn = New Connection
cn.ConnectionString = "FILE NAME=C:\TMS001\TMS.udl"
cn.Open
If cn.State <> adStateOpen Then
MsgBox "NO DATABASE CONNECTION", vbCritical, "NO DATABASE"
End
End If
End Sub
Private Sub SetGlobalRS()
Set mrsSTAStation = New ADODB.Recordset
SetRSOptions mrsSTAStation
Set mrsSTRStationTerm = New ADODB.Recordset
SetRSOptions mrsSTRStationTerm
Set mrsPPAPackProfileAlloc = New ADODB.Recordset
SetRSOptions mrsPPAPackProfileAlloc
Set mrsPPSPalletGradeWeights = New ADODB.Recordset
SetRSOptions mrsPPSPalletGradeWeights
Set mrsPKPPackProfiles = New ADODB.Recordset
SetRSOptions mrsPKPPackProfiles
End Sub
Private Sub SetRSOptions(ByRef rs As ADODB.Recordset)
With rs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With
End Sub
' My Business layer receives the call for a recordset and returns
' it. Following is code for GetSTRrs class used in the above
' code sample. All other recordset creation classes are
' essentially the same. The all receive a passed in connection
' object and data selection criteria.
'
'Public Function GetSTArs(ByRef cn As Connection, ByVal strStationName As String) As ADODB.Recordset
' Dim varParas() As Variant
' ReDim varParas(4)
' varParas(1) = "StationName" ' SQL Parameter name
' varParas(2) = adVariant ' Para data type
' varParas(3) = adParamInput ' This para is going into the SP
' varParas(4) = strStationName ' The station's name
' Set GetSTArs = mclsGetData.GetDataUsingCmd(varParas, 1, cn, gcConnTimeOut, adCmdStoredProc, "stp_GetSTAStation")
'End Function
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'My DB Access layer gets a recordset using a command object with
'parameters passed by the Business layer. All recordsets are
'created using stored procedures. The DB connection is passed
'as a parameter to this function.
'
'The function "GetDataUsingCmd" creates the recordset, eg
'
' Set cmdCommand = New ADODB.Command
' cmdCommand.ActiveConnection = cn
' cmdCommand.CommandType = intCommandType
' cmdCommand.CommandTimeout = intTimeOut
' cmdCommand.CommandText = strCommand
' Initialise the parameters (if there are any)
' If intNoOfParas > 0 Then
' For intIndexLoop = 1 To intNoOfParas
' Set prmPara = New ADODB.Parameter
' intIndex = ((intIndexLoop - 1) * 4)
' prmPara.Name = varParas(intIndex + 1)
' prmPara.Type = varParas(intIndex + 2)
' prmPara.Direction = varParas(intIndex + 3)
' prmPara.Value = varParas(intIndex + 4)
' cmdCommand.Parameters.Append prmPara
' Next intIndexLoop
' End If
' ' Get the recordset
' Set GetDataUsingCmd = cmdCommand.Execute()
' Set cmdCommand = Nothing
'Exits function at this point
August 23, 2002 at 5:34 pm
I dont see anything there that is obviously wrong/weird. I'll set up a simpler test scenario to see if I can see anything. Have you double checked what NetIQ is saying by querying sysprocesses directly?
Andy
August 23, 2002 at 7:22 pm
Hi Andy,
As I said I'm new to SQL Server so no I hadn't check sysprocesses but I have now. All I can report that my test program creates 5 new entries in that table. All have the same Host Process with a cmd of "Awaiting Command". The first process has a dbid of 8. a uid of 1 and cpu of 16 while the other 4 processes of zero for these values. Thanks for being so prompt with this - I'm impressed!!
Cheers, Peter
August 23, 2002 at 8:12 pm
More on the licensing issue... you're not going to like what I'm about to say: it depends. I love the ambiguity!
We had this disucussion on a Citrix discussion list a few months back and found that different organizations were told different things by their Microsoft reps. In Citrix, there is a data store which holds licensing information as well as information on installed applications, security, and printers. The data store by default is an Access database but can be pointed at SQL Server or Oracle.
Typically the server which handles communication with the data store is called the Data Collector. However, when you set up SQL Server or Oracle you have the option of pointing each Citrix server in the Citrix farm directly to the database server. The servers still talk to the data collector, but they can issue queries against the datastore (this is confirmed by using Profiler to watch the activity). So here's a case where you have to ask the question, "Do I need one CAL or X CALs where X is the number of servers I have?" But wait, there's more! The licensing for SQL Server talks about multiplexing devices not counting... one has to have licenses per known user. This means the concept of the data collector or a particular server doesn't count. Microsoft threw this in to keep us from saying, "Yes, I have 1500 users in my organization, but I only have 1 web server connecting to SQL Server, so I only need 1 CAL!" So based on the multiplexing clause, you've got to have a CAL for every user who uses Citrix. Obviously this is a significantly greater number.
What we found was that people had heard all three scenarios for licensing. So to reinforce what Andy has said, get the info from your Microsoft rep, in writing. That's the only way to protect yourself. Of course, if you're licensing per processor, the CALs issue goes away.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 24, 2002 at 7:50 am
You can get the database name by using the db_name() function, something like this:
select db_name(dbid) from sysprocesses
More info on the other columns in books online. Not that it probably matters as far as your original question! I've got some other stuff I've got to get done this weekend, so here's a suggestion on how to maybe figure this out. Step through the code and keep checking sysprocesses (or NetIQ), see what/where the additional connections are opening.
Andy
August 24, 2002 at 5:19 pm
Hi Andy,
I had already done this with NetIQ but now I know about sysprocesses I repeated the test and it did reveal something I hadn't noticed before. Connections are created with:
cn.Open - as expected
mrsSTAStation - open and no increase in connections (as I would normally expect).
mrsSTRStationTerm - open and a new entry appears in sysprocesses. This entry is fired by the the "GetDataUsingCmd.cmdExecute() line in GetDataUsingCmd function.
This behaviour repeated for the other three recordset open lines - I'm left with 5 entries in the sysprocesses table. What I hadn't noticed before is the count doesn't increase by one for each opened recordset - the first one doesn't get an entry in sysprocesses - only the subsequent ones.
As I said before, I'm new to SQL Server but maybe this is normal - a bit like the 'firehose' cursor when SQL Server opens another connection in the background to cater for nth firehouse cursors. But, if this was the case, I suspect it would be documented somewhere in the same way the firehose cursor behaviour is documented.
I await your learned comment with interest.
Cheers, Peter
August 24, 2002 at 6:57 pm
It might be documented, dont remember seeing it. I'll try to look through the MDAC to see what is there.
Andy
August 26, 2002 at 4:26 pm
Hi Andy,
Well I've done a bit more research (bought David Sceppa's book Programming ADO) and the problem seems to be the connection objects cursor location - it defaults to Server Side, when I change it to Client Side no more 'extra' connections.
But, what is the downside to changing my connection from the default Server Side to Client Side? Presumably it defaults to Server Side for good reason. Any comments?
Sceppa's book says rs.Open is the best way to go for controlling things like cursors but I read a lot that says using stored procedures and passing parameters is the way to go for SQL Server performance and this is what I done. Thus I don't use rs.Open at all - all my database work is through stored procedures. Again, any comments would be greatly appreciated.
Cheers, Peter
August 26, 2002 at 5:14 pm
Server side cursors (different from TSQL cursors) dont get used very often. They can be more efficient at times since you only pull x records to the client, even though the query might return 100x records. Client side recordsets (static) pull everything to the client. Sometimes is good, sometimes not. ADO.Net leans heavily toward the client side/disconnected recordset, scales better.
Using stored procs is usually a win, especially if you explicitly build the parameters collection and avoid the refresh method. A win both in terms of performance (compiled query plan) and code packaging/security. The "best" is to avoid a recordset altogther and just use output params, but recordsets are pretty useful so I wouldnt advocate scrapping them. As far as .open vs rs=cn.execute proc, the only difference should be the query plan build time, its still got to bring all the data into the recordset.
Did the book say why the extra connection? Background processing?
Andy
August 26, 2002 at 7:04 pm
Hi Andy,
It says: "..one important caveat: SQL Server can only support one active query on a connection" and goes on to say that the OLE-DB spec requires that extra connections be created as needed rather than creating an error if the connection is in use (I get the impression that an error used to be what happened in the past.
I guess in my case the queries are considered "active" because I have multiple recordsets open, ie an open recordset represents an 'active query'.
BTW I keep these recordsets open because I can't disconnect the recordsets due to the fact they are created using the cn.Command object. I guess there's no way around this, is there? I just want these recordsets in memory becuase they are used intensively and I don't want to be going back and forth to the database all the time.
Cheers, Peter
August 27, 2002 at 5:04 am
Shouldnt matter how they are created, just do:
set rs.activeconnection=nothing
Andy
August 27, 2002 at 3:41 pm
Hi Andy,
It would be great if I could do this but that code generates the error:
Runtime 3705
Operation is not allowed when the object is open.
Then, if I close the recordset, (sounds a pretty dumb thing to do) before setting the connection to nothing I get the error (when executing rs.activeconnection = nothing):
Runtime 3707
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.
It is this error that led me to believe the I couldn't disconnect a recordset created the way I want to open recordsets, eg stored procedures with parameters.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply