SQL Compare ignores Stored procedure calls using "exec" .

Liju

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

Brian Donahue

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.

Liju

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

Brian Donahue

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.