How to get a stored procedure as a text and alter it on another server-CodePudding

I would like to know how to get a stored procedure as a text and update its twin(!) on the other server with an excel macro. So I got this stored procedure on the server which has the newest version of the stored procedure:

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

@Fulltext has the complete code of 'StoredProcName'. I would like to get this code, cut the first 6 letters (CREATE), append it with "ALTER" and run it on the target server/database to update its twin(!). I got this excel macro to realize it:

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

After running this macro I get the Run-time error '3704': Operation is not allowed when the object is closed.

Thanks in advance for your help.

CodePudding user response：

Try

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