I have a windows service that runs on about 25 client machines. The service reads local text files and when it comes across records its configured to look for, it performs a SQL insert. There are several functions and subroutines in the service that do other SQL updates and selects.
My problem is that on my SQL Server, I am looking in the Current Activity --> Processes and see about 500 processes under the SQL account set up for these windows services. I thought that by default I was using connection pooling, maybe I am but why would I have 500 or so processes under this account?
When I look at the "Login Time" in the SQL Current Activity --> Processes form all of the Login Times for these processes in question are from within the last hour, nothing is left over and seeming abandoned.
This is how I make my connections in the Windows Service:
I have a Module in which I declare my global variables. I use this variable for my connection string:
Public sConnStr As String = "Provider=SQLOLEDB;data source=192.168.1.1;initial catalog=MyDatabase;user id=MyUser;Password=MyPassword"
Whenever I have to make a SQL connection, I do so like this:
Public Sub Declare_Client_Version(ByVal iEventID As Integer, ByVal sVersion As String, ByVal dtVersionDate As DateTime)
Dim sSQL As String
sSQL = "UPDATE t_LogReader_Config SET " & _
"Client_Version = '" & sVersion & "'" & _
", Client_Version_Date = '" & dtVersionDate & "' " & _
"WHERE EventID = " & iEventID
Dim oDC As New OleDbConnection(sConnStr)
Dim cmd As New OleDbCommand(sSQL, oDC)
Catch ex As Exception
I cannot tell if connection pooling is working normally or if the 500 connections will hose up my SQL Server. Any advice?