0

I have a situation where I need to copy a stored procedure from one server to another server every day. I cannot use Transfer SQL object task as those servers are not linked. I'm looking for a solution something like, export procedure to a text file and import from text file to the destination. Can anyone help me with this? I wanted this to be done through SSIS as I already have a package which is transforming data between these two servers daily.

Maximus
  • 792
  • 9
  • 19

2 Answers2

1
  1. Log into Server A using SQL Server Management Studio.
  2. Find the stored procedure in the tree on the left and generate a CREATE script by right clicking
  3. Run the script in your target database
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Sorry, i forgot to mention that this needs to be done every day. That is, i need to keep both versions to be synced. – Maximus Apr 12 '13 at 09:03
  • Here is one suggestion: http://stackoverflow.com/questions/483568/how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2 – Nick.Mc Apr 12 '13 at 09:05
0

I think we can do it as:

  1. Execute SQL Task: using ADO.NET to run "sp_helptext YourSPName" (which returns the definition of your stored procedure as a single column table result). Save the "Full Result Set" to an Object type variable, say "ResultSet".

  2. Script Task: pass in "ResultSet" as readonly. Create another variable, say "SPConent" type of String, pass in as readwrite. Inside the script, using the following code to reassemble the stored procedure definition to a string

    public void Main()
{
    // TODO: Add your code here
    var table = ((DataSet) Dts.Variables["ResultSet"].Value).Tables[0];
    string spContent = "";

    foreach (DataRow row in table.Rows)
    {
        spContent += row[0];
    }

    Dts.Variables["SPContent"].Value = spContent;
    Dts.TaskResult = (int)ScriptResults.Success;
}

  3. Execute SQL Task: delete the stored procedure in target database.

  4. Execute SQL Task: run sql command from "SPContent" against the target database.

Jian Fu
  • 519
  • 3
  • 3