0

I have a SQL Server 2008 R2 Express database with one table. Windows XP.

I wrote this macro to request table size and delete rows if the table bigger than I need, I call VBA from SCADA every 1 hour.

Option Explicit
Dim strConn As String 
Dim strSQL As String 
Dim objConn As Object 
Dim objRecSet As Object 
Dim Command As Object 
Dim objRecSetValue as Double

On Error Resume Next
Err.Number = 0
strConn = "Provider=SQLOLEDB;Data Source=……….." 

Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = strConn
objConn.CursorLocation = 3
objConn.Open 

Set objRecSet = CreateObject("ADODB.Recordset")
Set Command = CreateObject("ADODB.Command")
Command.CommandType = 1
Set Command.ActiveConnection = objConn

strSQL = "SELECT SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB FROM sys.tables t "
strSQL = strSQL & "INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id "
strSQL = strSQL & "INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id "
strSQL = strSQL & "INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id "
strSQL = strSQL & "WHERE t.NAME LIKE 'Statistic' AND i.index_id <= 1 GROUP BY t.NAME "

Command.CommandText = strSQL
Set objRecSet = Command.Execute

objRecSet.MoveFirst
objRecSetValue = objRecSet.Fields(0).Value
objRecSet.Close

If objRecSetValue> 5000 then
    strSQL = "delete from dbo.statistic where dateandtime BETWEEN (SELECT MIN(dateandtime) FROM dbo.statistic) AND (SELECT MIN(dateandtime) FROM dbo.statistic) + 1"
Command.CommandText = strSQL
Set objRecSet = Command.Execute
End if

objConn.Close

ErrorHandler1:
Set Command = Nothing
Set objConn = Nothing
Set objRecSet = Nothing

Now I want to avoid VBA, and create procedure/function in SQL Server Management Studio (SSMS) , call it with Windows Sheduler and sqlcmd every 1 hour, but I do not know T-SQL and procedure/function well. Could somebody help me, to make the same code in T–SQL? Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Роман Кириллов
  • 139
  • 1
  • 10
  • 1
    You have all of the SQL code written. What are you struggling with? Here is a link on how to create a procedure (examples at the bottom): https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure ; here is a link on how to write an `IF` statement: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql – Alex Oct 09 '17 at 09:28

1 Answers1

1

Here is the procedure code

CREATE PROCEDURE proPurgeTableData
AS
BEGIN TRY
    --variable for storing tablesize
    DECLARE @tablesizeMB    INT

    --setting size to variable
    SELECT 
        @tablesizeMB = SUM(a.total_pages) * 8 / 1024
    FROM 
        sys.tables t
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE 
        t.NAME LIKE 'Statistic' AND i.index_id <= 1 
    GROUP BY 
        t.NAME

    --if size greater than 5000 delete from table
    IF (ISNULL(@tablesizeMB,0)>5000)
    BEGIN
        BEGIN TRAN
            DELETE FROM dbo.statistic 
            WHERE dateandtime BETWEEN (SELECT MIN(dateandtime) FROM dbo.statistic) AND (SELECT MIN(dateandtime) FROM dbo.statistic) + 1
        COMMIT TRAN
    END
END TRY
BEGIN CATCH
    IF @@TRANCOUNT>0 ROLLBACK TRAN
END CATCH

You can execute this as EXEC proPurgeTableData, just add this in the sql job.

Bijith T
  • 54
  • 4