none
Multiple Instances of same Stored Procedure (with SYNONYM)

    Question

  • We have a Stored Procedure on a Database that transfers data to different databases depending on the ClientId passed.

    To take care of this we have incorporated SYNONYM for tables used in the queries.

    This works fine for different clients.

    We have thought of running multiple instances of the stored procedure, each one with a different ClientId.

    How can this be done ?


    Mr. Pinakee Das

    Thursday, October 03, 2013 11:43 AM

Answers

  • You're dropping and creating synonyms at runtime. Don't do that.  Apart from being unnecessarily complex, a synonym is a shared object and any different sessions will see each others data if you do that.

    Either use dynamic SQL for the body of the stored procedure, or provision a database or schema for each client that contains _permanent_ synonyms to their objects.  You can use an "if not exists" check in the stored procedure to ensure that the client's schema or database is properly provisioned with synonym definitions.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 03, 2013 2:47 PM

All replies

  • To run the multiple instances of the same procedure, you can execute the proc using SSIS. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 03, 2013 11:52 AM
  • Hi Latheesh,

        We are calling this procedure from a web page. This can be executed 'n' no. of times.

    The sample procedure can be as below:

    CREATE PROCEDURE pTransferData(@ClientCode VARCHAR(50))
    AS
    BEGIN
    	SET NOCOUNT ON;
    	DECLARE @ServerName		VARCHAR(50)
    			,@ClientDBName	VARCHAR(100)
    			,@SQLStr		NVARCHAR(1000);
    			;
    	IF EXISTS(SELECT 1 FROM dbo.DBConfigurations AS DBConf WHERE DBConf.ClientCode=@ClientCode)
    	BEGIN
    		--Looking for the database name and server name basing on the client id. 
    		SELECT	@ServerName=DBConf.ServerName
    				,@ClientDBName=DBConf.DBName
    		FROM	dbo.DBConfigurations AS DBConf
    		WHERE	DBConf.ClientCode=@ClientCode;
    
    		--Creating Synonym basing on the clientdb and the server dynamically.		
    		SET @SQLStr='IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N''DB_Origins'') DROP SYNONYM [dbo].[DB_Origins]; CREATE SYNONYM [dbo].[DB_Origins] FOR [' 
    					+ @ServerName + '].[' + @ClientDBName+ '].[dbo].[Origins];';
    		EXECUTE sp_executesql @SQLStr; 
    	
    		--Transferring data basing on the synonym
    		IF NOT EXISTS(SELECT 1 FROM DB_Origins AS Org01 INNER JOIN dbo.Origins AS Org02 ON Org01.OriginCode=Org02.OriginCode)
    		BEGIN
    			INSERT INTO DB_Origins
    			SELECT *
    			FROM dbo.Origins AS Org02
    			WHERE NOT EXISTS(SELECT 1 FROM DB_Origins AS Org01 WHERE Org01.OriginCode=Org02.OriginCode);
    		END		
    	END
    END


    Mr. Pinakee Das


    • Edited by Pinakee Thursday, October 03, 2013 12:44 PM
    Thursday, October 03, 2013 12:36 PM
  • You're dropping and creating synonyms at runtime. Don't do that.  Apart from being unnecessarily complex, a synonym is a shared object and any different sessions will see each others data if you do that.

    Either use dynamic SQL for the body of the stored procedure, or provision a database or schema for each client that contains _permanent_ synonyms to their objects.  You can use an "if not exists" check in the stored procedure to ensure that the client's schema or database is properly provisioned with synonym definitions.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 03, 2013 2:47 PM