none
Handling reference in different environments?

    Question

  • Hi All,

    I have stored procedures in DEV environment. And the quries are pointintg to DEV server and LINKEDSERVER (in DEV environment).

    When we do dumping from PROD to TEST or DEV. We have to manually change all the FROMs of the SPs.

    Please share, if there is any easy way out for this problem.

    Thanks


    Hassan

    Thursday, November 14, 2013 12:41 AM

Answers

  • So your problem is with the different Linked server names in Dev, Test and Prod environments? We used to have this problem in the past. You can overcome this problem by naming the linked servers in all the 3 environments with the same names. You have to use alias while creating Linked servers, see below for sysntax to create linked server:

    EXEC master.dbo.sp_addlinkedserver
        @server = N'DesiredName',
        @srvproduct=N'MSSQL',
        @provider=N'SQLNCLI',
        @provstr=N'PROVIDER=SQLOLEDB;SERVER=ActualServerName'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DesiredName',
        @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='password'

     If you follow this method you don't need to edit your stored procs while changing the environments.

    • Marked as answer by Hassanz Friday, November 15, 2013 1:17 AM
    Thursday, November 14, 2013 10:19 PM
  • We are restoring the database, can we do it? or in any other way it is possible?

    Hassan

    In that case instead of specifying the server name directly in the SPs, use synonyms.

    Create a synonym for the linked server and use that in your query. When the PROCs are moved to another environment, you just need to update the synonym definition.

    Synonyms in sql server


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

    • Marked as answer by Hassanz Friday, November 15, 2013 1:17 AM
    Thursday, November 14, 2013 11:45 PM

All replies

  • Hi Hassan,

    I would suggest you to create another procedure with the same code as the current procedure and changed Forms.

    You can then call the correct procedure as per the testing env.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Thursday, November 14, 2013 1:05 AM
  • In other words keep three copies of a stored procedure one for each environment?

    Do you mind explaing a bit more with example.

    Thanks


    Hassan


    • Edited by Hassanz Thursday, November 14, 2013 1:20 AM
    Thursday, November 14, 2013 1:09 AM
  • Can you post 1 copy of the procedure? That will help me explain for the other copies.

    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Thursday, November 14, 2013 2:59 AM
  • Please see below...

    USE [TEST_MSCRM]
    GO
    /****** Object:  StoredProcedure [dbo].[DE_spInsertTrackIT_Trader]    Script Date: 11/13/2013 17:19:42 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

     

    ALTER proc [dbo].[DE_spInsertTrackIT_Trader]
     @organisationID varchar(50),@createdDate datetime, @createdBy varchar(50), @traderName nvarchar(250),
     @smallTrader bit, @s87B bit,
     @scOut int output, @scOutDesc varchar(255) output
    as

    begin

    -- NOTE: For Prod, change references to Linked server:
     --[TRACKITTEST]..[MARS].
     --to
     --[TRACKITPROD]..[MARS].


     declare @createdUserID varchar(10)

     select @createdUserID = upper(substring(DomainName, PATINDEX('%\%', DomainName)+ 1, len(DomainName)-PATINDEX('\', DomainName)))
     from SystemUser where SystemUserId = @createdBy

     -- Ensure UserID is valid in TrackIT - if not, use 'INFOC'
     declare @userCountTrackIT int  
     SELECT @userCountTrackIT = COUNT(userid) FROM [TRACKITTEST]..[MARS].[USER_LIST] WHERE userid = @createdUserID

     if ISNULL(@userCountTrackIT,0) = 0
     begin
      set @createdUserID = 'INFOC'
     end
      
     -- Check if INSERT or UPDATE to Trackit Matter
     declare @exists smallint
     select @exists = COUNT(TRADER_ID) from [TRACKITTEST]..[MARS].[TRADER] where CRM_TRADER_ID = @organisationID 

     if isnull(@exists,0) = 0 -- INSERT
     begin 

      --Check if Trader Name already exists in TrackIT
      declare @traderNameExistsInTrackIT int
      declare @traderIDFromTrackIT int
      
      select @traderIDFromTrackIT = [TRADER_ID], @traderNameExistsInTrackIT = count([TRADER_ID])
      from  [TRACKITTEST]..[MARS].[TRADER]
      where [TRADER_NAME] = @traderName
      group by [TRADER_ID]

      if @traderNameExistsInTrackIT = 1
      begin
       
       -- Update TrackIT ID with CRM Organisation
       update [TRACKITTEST]..[MARS].[TRADER] set crm_trader_id = @organisationID where [TRADER_NAME] = @traderName and crm_trader_id is null
       
       -- Update CRM Organisation with TrackIT ID
       update AccountExtensionBase set de_TrackITID = @traderIDFromTrackIT where AccountId = @organisationID and de_TrackITID is null
      end

      if isnull(@traderNameExistsInTrackIT,0) = 0
      begin


       declare @nextTrackITID int
       select @nextTrackITID = dbo.DE_fnTrackITSequence_Trader()

       INSERT INTO [TRACKITTEST]..[MARS].[TRADER]

            (
            Trader_ID,
            crm_trader_id,
            Created_Date,
            Created_By,
            Trader_Name,
            Small_Trader,
            Checked,
            PCC,
            HAS87B
            --,
            --INFOCENTRE_ACTION
            )

       VALUES
        (
        @nextTrackITID, --traderid
        @organisationID,
        @createdDate,
        @createdUserID,
        @traderName,
        @smallTrader,
        0, --Checked
        0, --PCC 
        @s87B
        --,
        --0 --Infocentre Action
        )
        
       -- Update CRM Organisation with TrackIT ID
       update AccountExtensionBase set de_TrackITID = @nextTrackITID where AccountId = @organisationID
      end  
     end

     if @exists = 1 -- UPDATE
     begin 
     
      update
       [TRACKITTEST]..[MARS].[TRADER]
      set
       Trader_Name = @traderName,
       Small_Trader = @smallTrader,
       HAS87B = @s87B
      where
       crm_trader_id = @organisationID


      -- Update CRM Organisation with TrackIT ID
      declare @trackitIT int
      select @trackitIT = TRADER_ID from [TRACKITTEST]..[MARS].[TRADER] where CRM_TRADER_ID = @organisationID 
      
      update AccountExtensionBase set de_TrackITID = @trackitIT where AccountId = @organisationID
       
     end
     
    end

     

    • Edited by Hassanz Thursday, November 14, 2013 3:10 AM
    Thursday, November 14, 2013 3:07 AM
  • When you update the DEV server, why not just copy only the tables.

    Chuck

    Thursday, November 14, 2013 9:17 PM
  • We are restoring the database, can we do it? or in any other way it is possible?

    Hassan

    Thursday, November 14, 2013 9:58 PM
  • So your problem is with the different Linked server names in Dev, Test and Prod environments? We used to have this problem in the past. You can overcome this problem by naming the linked servers in all the 3 environments with the same names. You have to use alias while creating Linked servers, see below for sysntax to create linked server:

    EXEC master.dbo.sp_addlinkedserver
        @server = N'DesiredName',
        @srvproduct=N'MSSQL',
        @provider=N'SQLNCLI',
        @provstr=N'PROVIDER=SQLOLEDB;SERVER=ActualServerName'

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DesiredName',
        @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='password'

     If you follow this method you don't need to edit your stored procs while changing the environments.

    • Marked as answer by Hassanz Friday, November 15, 2013 1:17 AM
    Thursday, November 14, 2013 10:19 PM
  • We are restoring the database, can we do it? or in any other way it is possible?

    Hassan

    In that case instead of specifying the server name directly in the SPs, use synonyms.

    Create a synonym for the linked server and use that in your query. When the PROCs are moved to another environment, you just need to update the synonym definition.

    Synonyms in sql server


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

    • Marked as answer by Hassanz Friday, November 15, 2013 1:17 AM
    Thursday, November 14, 2013 11:45 PM
  • Thanks for suggesting Synonyms but only problem with them is I cannot use them in OpenQuery functions?

    Regards,


    Hassan

    Friday, November 15, 2013 1:18 AM