如何将存储过程获取为文本并在另一台服务器上进行更改

Question

我想知道如何将存储过程获取为文本，并使用excel宏在另一台服务器上更新其twin(！(。所以我在服务器上得到了这个存储过程，它有存储过程的最新版本：

Declare @Lines Table (Line NVARCHAR(MAX));
Declare @FullText NVARCHAR(max) = '';
INSERT @Lines EXEC sp_helptext 'StoredProcName';
Select @FullText = @FullText + Line From @Lines;
Select @FullText

@全文具有完整的代码"StoredProcName"。我想得到这段代码，剪切前6个字母(CREATE(，并在其后面加上"；ALTER"；并在目标服务器/数据库上运行它以更新其twin(！(。我得到了这个excel宏来实现它：

Sub GetStoredProcedure()
Dim cn As ADODB.Connection 
Dim rs As ADODB.Recordset 
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command

'Getting data from local
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=SQLOLEDB;" & _
"Data Source=myDataSource;" & _
"Initial Catalog=myDataBase;" & _
"Integrated Security=SSPI;"


cn.Open 'Connection establishment.

cmd1.ActiveConnection = cn
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "UpdateStoredProcedure"

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Parameter = Cells(i, 1).Value
cmd1.Parameters.Refresh
cmd1.Parameters("@sPName").Value = Parameter
Set rs = cmd1.Execute
rs.Open
Debug.Print rs.State
Range("K2").CopyFromRecordset rs
Next i


rs.Close 'Deactivating the recordset.
cn.Close 'Deactivating the connetion.



End Sub

运行此宏后，我得到运行时错误"3704"：对象关闭时不允许操作。

提前感谢您的帮助。

Answer 1

尝试

Option Explicit
Sub GetStoredProcedure()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim cmd1 As ADODB.Command

'Getting data from local
Set cn = New ADODB.Connection
cn.ConnectionString = _
"Provider=SQLOLEDB;" & _
"Data Source=myDataSource;" & _
"Initial Catalog=myDataBase;" & _
"Integrated Security=SSPI;"

cn.Open 'Connection establishment.

Dim sProc As String, sCode As String, n As Long, i As Long
With Range("K:L")
.Font.Name = "Lucida Console"
.Font.Size = 11
.NumberFormat = "@"
.ColumnWidth = 85
End With

Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cn
.CommandType = adCmdText

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
sProc = Cells(i, 1).Value
.CommandText = "EXEC sp_helptext '" & sProc & "';"
Set rs = .Execute
sCode = rs.GetString
sCode = Replace(sCode, vbCrLf & vbCrLf, vbCrLf)
Range("K" & i).Value = sCode
Range("L" & i).Value = Replace(sCode, "CREATE PROCEDURE", "ALTER PROCEDURE")
n = n + 1
Next i
End With

rs.Close 'Deactivating the recordset.
cn.Close 'Deactivating the connetion.
MsgBox n & " procedures", vbInformation

End Sub