如何在SQL Server 2008 R2数据库中创建事件/函数而不是VBA宏?
问题描述:
我有一个SQL Server 2008 R2 Express数据库。 Windows XP。如何在SQL Server 2008 R2数据库中创建事件/函数而不是VBA宏?
我写了这个宏来请求表大小和删除行,如果表大于我需要,我每1小时从SCADA调用VBA。
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
现在我想避免VBA,并在SQL Server Management Studio中(SSMS)创建过程/函数,与Windows Sheduler调用它,SQLCMD每隔1小时,但我不知道T-SQL和程序/功能很好。有人可以帮助我,在T-SQL中制作相同的代码吗? 谢谢。
答
下面是程序代码
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
您可以执行此作为EXEC proPurgeTableData,只是在SQL作业添加此。
+0
辉煌! :' - )MS SQL Express没有Agent SQL,但我将使用sqlcmd和调度程序 –
您已经编写了所有的SQL代码。你在挣扎什么?以下是关于如何创建过程的链接(底部的示例):https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure;这里是关于如何编写'IF'语句的链接:https://docs.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql – Alex