I'm using VB6 with ADODB connection to connect to sql server 2012 database and execute queries on the database, I'm facing memory leak where I see that memory usage does not decrease after query finished even after I terminate my program the memory usage dose not decrease.
Below is my code:
Public adoCon As New ADODB.Connection
Public serverName As String, databaseName As String, tableName As String Public sqlCmd As String
Public Sub adodb_Connect()
If Tenzfrm.adoCon.State = adStateOpen Then Tenzfrm.adoCon.Close
serverName = Tenzfrm.server_Name.Text
databaseName = Tenzfrm.database_Name.Text
Tenzfrm.adoCon.Provider = "sqloledb"
Tenzfrm.adoCon.Properties("Data Source").Value = serverName
Tenzfrm.adoCon.Properties("Initial Catalog").Value = databaseName
Tenzfrm.adoCon.Properties("Integrated Security").Value = "SSPI"
Tenzfrm.adoCon.CommandTimeout = 0
Tenzfrm.adoCon.Open
End Sub
Public Sub adodb_Close()
If Tenzfrm.adoCon.State = adStateOpen Then Tenzfrm.adoCon.Close
Set Tenzfrm.adoCon = Nothing
End Sub
Private Sub appHourlySTS_Click()
Call adodb_Connect
proccess.Caption = "Preparing tables !"
proBar2.Width = 2 * proBar1.Width / 100
sqlCmd = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tenz_Cell_STS_Hourly_Temp') DROP TABLE Tenz_Cell_STS_Hourly_Temp;"
adoCon.Execute (sqlCmd)
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'CREATE TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp](';"
sqlCmd = sqlCmd & "SELECT @sqlCmd+='['+Headers+'] FLOAT,' FROM [dbo].[Tenz_colReg];"
sqlCmd = sqlCmd & "SET @sqlCmd=LEFT(@sqlCmd,LEN(@sqlCmd)-1)+')';"
sqlCmd = sqlCmd & "EXEC(@sqlCmd);"
adoCon.Execute (sqlCmd)
sqlCmd = "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [Date] VARCHAR(23);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [Time] VARCHAR(10);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [NE Name] VARCHAR(15);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [Cell Name] VARCHAR(64);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [CI] VARCHAR(64);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [LAC] VARCHAR(10);"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' table from CS1 !"
proBar2.Width = 5 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'INSERT INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] '; "
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=[dbo].[Tenz_CS1].['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND ([Name]='Date' OR [Name]='Time' OR [Name]='Cell Name' OR [Name]='NE Name' OR [Name]='CI' OR [Name]='LAC');"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_CS1].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_CS1].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1);"
sqlCmd = sqlCmd & "DECLARE @dc NVARCHAR(MAX) = ''; "
sqlCmd = sqlCmd & "SELECT @dc+='['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND ([Name]='Date' OR [Name]='Time' OR [Name]='Cell Name' OR [Name]='NE Name' OR [Name]='CI' OR [Name]='LAC');"
sqlCmd = sqlCmd & "SELECT @dc+='['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @dc=LEFT(@dc,LEN(@dc)-1);"
sqlCmd = sqlCmd & "SET @sqlCmd+='('+@dc+') SELECT '+@sc+' FROM [dbo].[Tenz_CS1]';"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' from CS2 !"
proBar2.Width = 35 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'MERGE INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] USING [dbo].[Tenz_CS2] ON [dbo].[Tenz_Cell_STS_Hourly_Temp].Date = [dbo].[Tenz_CS2].Date AND [dbo].[Tenz_Cell_STS_Hourly_Temp].Time = [dbo].[Tenz_CS2].Time AND [dbo].[Tenz_Cell_STS_Hourly_Temp].[Cell Name] = [dbo].[Tenz_CS2].[Cell Name] WHEN MATCHED THEN UPDATE SET ';"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_CS2].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_CS2].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS2') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1)+';';"
sqlCmd = sqlCmd & "SET @sqlCmd+=@sc;"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' from PS1 !"
proBar2.Width = 50 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'MERGE INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] USING [dbo].[Tenz_PS1] ON [dbo].[Tenz_Cell_STS_Hourly_Temp].Date = [dbo].[Tenz_PS1].Date AND [dbo].[Tenz_Cell_STS_Hourly_Temp].Time = [dbo].[Tenz_PS1].Time AND [dbo].[Tenz_Cell_STS_Hourly_Temp].[Cell Name] = [dbo].[Tenz_PS1].[Cell Name] WHEN MATCHED THEN UPDATE SET ';"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_PS1].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_PS1].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_PS1') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1)+';';"
sqlCmd = sqlCmd & "SET @sqlCmd+=@sc;"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' from PS2 !"
proBar2.Width = 65 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'MERGE INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] USING [dbo].[Tenz_PS2] ON [dbo].[Tenz_Cell_STS_Hourly_Temp].Date = [dbo].[Tenz_PS2].Date AND [dbo].[Tenz_Cell_STS_Hourly_Temp].Time = [dbo].[Tenz_PS2].Time AND [dbo].[Tenz_Cell_STS_Hourly_Temp].[Cell Name] = [dbo].[Tenz_PS2].[Cell Name] WHEN MATCHED THEN UPDATE SET ';"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_PS2].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_PS2].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_PS2') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1)+';';"
sqlCmd = sqlCmd & "SET @sqlCmd+=@sc;"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Finishing !"
proBar2.Width = 80 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'INSERT INTO [dbo].[Tenz_Cell_STS_Hourly] '"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = ''; "
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_Cell_STS_Hourly');"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1);"
sqlCmd = sqlCmd & "SET @sqlCmd+='('+@sc+') SELECT '+@sc+'FROM [dbo].[Tenz_Cell_STS_Hourly_Temp]';"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proBar2.Width = 100 * proBar1.Width / 100
MsgBox ("Statistics appended to 'Tenz_STS_Cell_Hourly' successfully !")
proBar2.Width = 0
proccess.Caption = "Idle ! "
sqlCmd = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tenz_Cell_STS_Hourly_Temp') DROP TABLE Tenz_Cell_STS_Hourly_Temp;"
adoCon.Execute (sqlCmd)
Call adodb_Close
End Sub
