Red Gate forums :: View topic - SQL Compare ignores Stored procedure calls using "exec" .
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare ignores Stored procedure calls using "exec" .

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Liju



Joined: 09 Sep 2013
Posts: 2

PostPosted: Mon Sep 09, 2013 11:53 am    Post subject: SQL Compare ignores Stored procedure calls using "exec" . Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Thu Sep 12, 2013 11:34 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Liju



Joined: 09 Sep 2013
Posts: 2

PostPosted: Fri Sep 13, 2013 2:58 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Fri Sep 13, 2013 4:40 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group