SQL Compare ignores Stored procedure calls using "exec" .

LijuLiju Posts: 3
edited September 13, 2013 11:40AM in SQL Compare Previous Versions
Hi ,

I have a folder containing few stored procedures which i have created for my project. My requirement is , i need to create a single DB script which have the contents of all stored procedure available at the folder. I have used SqlCompare command line tool to generate the script.

Sqlcompare created the script but ,the script dont have some procedure calls like " exec logging.dbo.storedprocedure1 'storedprocedure2',@DB " . SqlCompare ignored theese procedure calls .

Please help me to resolve this issue.
I need a single script generated with all the lines availbale the procedures in the folder.

Thanks and regards
Liju

Comments

  • Hello,

    I'm afraid I am not all that clear on what you are trying to do. SQL Compare can output the creation DDL of the stored procedures to a single file or a folder with separate files for each creation command (CREATE PROC [x] AS...). It would not output calls to the stored procedures, though - I'm not sure how the EXEC commands you ant to have fit into the functionality of the SQL Compare software.
  • Hi Brian,

    My exact requirement is below.

    I have a folder with 10 SPs. I neeed to give this folder as 1 input to SQLCompare and my Existing DB as second input. I wish to get output as a single script which have the gist of 10 SPs available in the 1st folder. Later i can use the same script to update the DB with the changes available at the folder1.

    The issue i am facing is below.

    --> Sample SP available at the folder below:

    USE [databaseName]
    GO

    -- Delete the Sproc if it already exists
    Declare @DB nvarchar(128)
    Set @DB = db_name()
    exec logging.dbo.SP1 'SP2',@DB
    GO



    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

    CREATE PROCEDURE [dbo].[SP2]
    (
    //Variable Declerations
    )
    AS

    DECLARE


    BEGIN TRY

    //Logic

    END TRY

    BEGIN CATCH
    //LoggingLogic
    END CATCH

    Go

    // Call SP3 which give permission
    Declare @DB nvarchar(128)
    Set @DB = db_name()
    exec SP3 'SP1', @DB
    GO


    --> I am able to create an output script by using SQLCompare , but it have few sections missing , which is needed actually .
    The missing sections are

    -- Delete the Sproc if it already exists
    Declare @DB nvarchar(128)
    Set @DB = db_name()
    exec logging.dbo.SP1 'SP2',@DB
    GO


    // Call SP3 which give permission
    Declare @DB nvarchar(128)
    Set @DB = db_name()
    exec SP3 'SP1', @DB
    GO



    I wish to get theese sections too included in my query result generated by SqlCompare.


    Thanks and Regards
    Liju G
  • The best answer I can think of would be to use SQL Comparison SDK and write a program that creates a synchronization script, then inject the custom SQL you want between stored procedure creation statements.

    It's something you could do if you knew C#. I don't have a specific example for that, but there are some code examples on sdk.red-gate.com.

    The commercial version of the program can only output SQL data-definition scripts.
Sign In or Register to comment.