SQL Compare ignores Stored procedure calls using "exec" .

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

SQL Compare ignores Stored procedure calls using "exec" .

Postby Liju » Mon Sep 09, 2013 11:53 am

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
Liju
 
Posts: 2
Joined: Mon Sep 09, 2013 9:42 am

Postby Brian Donahue » Thu Sep 12, 2013 11:34 am

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby Liju » Fri Sep 13, 2013 2:58 pm

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
Liju
 
Posts: 2
Joined: Mon Sep 09, 2013 9:42 am

Postby Brian Donahue » Fri Sep 13, 2013 4:40 pm

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests