0

I'm looking to replace connection strings across multiple stored procedures.The development database I'm working with is restored from production and stored procedures within contain connection strings to a production linked server. My aim is replace the linked server connection strings to point at a development linked server for testing purposes. I'm looking to automated this as a step in the SQL Agent restore job to run immediately after the restore.

The issue I'm having is setting the stored proc definition as a variable while keeping the formatting. When selecting the definition text, the stored proc is all on one line so after replacing the CREATE for ALTER along with the connection stings I cannot execute the SQL due to the formatting. I've tried playing around with STRING_SPLIT as but to no avail. Is there a way to do this or is it not possible?

Here is an example of part of one of the procedures

   SELECT
        a.AgreementNumber,
        a.AgreementProposalID,
        c.CustomerNumber,
        pc.Id CustomerID,
        a.AgreementCreateDate
    INTO
        #a
    FROM
        SENTINEL.DotDot_S3DB01_Replica.dbo.AgreementTable a
    INNER JOIN
        SENTINEL.DotDot_S3CUSTDB_Replica.dbo.CustomerTable c ON a.AgreementCustomerNumber = c.CustomerNumber
    INNER JOIN
        SENTINEL.DotDot_Proposal_Replica.dbo.Customer pc ON pc.Code = c.CustomerNumber
    WHERE
        a.AgreementCreateDate > @LastEnteredDate;

After my changes it needs to look like this to reference the test database names

    SELECT
        a.AgreementNumber,
        a.AgreementProposalID,
        c.CustomerNumber,
        pc.Id CustomerID,
        a.AgreementCreateDate
    INTO
        #a
    FROM
        SENTINEL.DotDotUAT_S3DB01.dbo.AgreementTable a
    INNER JOIN
        SENTINEL.DotDotUAT_S3CUSTDB.dbo.CustomerTable c ON a.AgreementCustomerNumber = c.CustomerNumber
    INNER JOIN
        SENTINEL.DotDotUAT_Proposal .dbo.Customer pc ON pc.Code = c.CustomerNumber
    WHERE
        a.AgreementCreateDate > @LastEnteredDate;

I've tried using this code to select the stored procedure code as a variable so I can execute the alter procedure command with the changes however the object definition selected from sys.procedures is displayed all on one line.

DECLARE @SQL VARCHAR(MAX) 

SET @SQL = ( SELECT OBJECT_DEFINITION(object_id)
FROM   sys.procedures
WHERE  name = 'Usp_Proc')

SET @SQL = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SQL,'CREATE','ALTER'),'OriginalString','ReplacementString'),'OriginalString','ReplacementString'),'OriginalString','ReplacementString'),'OriginalString','ReplacementString')

EXEC (@SQL)
TheCoxta
  • 17
  • 5
  • 3
    Don't use connection strings in the stored procedures in the first place. Linked servers are an object just like tables. [Create them once](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-2017#Procedures) and reuse them. In fact, one has to wonder what's going on with the connection credentials in all those stored procedures – Panagiotis Kanavos Sep 04 '19 at 10:17
  • The credentials aren't stored in the SPs, the SPs are merely selecting from a DB on the already set up link server so they have only been created once as you said. Sorry if I wasn't clear about that. – TheCoxta Sep 04 '19 at 11:04
  • What are you trying to replace then? Why not just create linked server objects with the same names in the development environment? What does the stored procedure look like? Even if string replacement was appropriate, it would be impossible to guess what the problem is without looking at the strings – Panagiotis Kanavos Sep 04 '19 at 11:04
  • After the restore, the selects within the stored procedures need to reference test databases on the linked server. For example: [LinkedServer].[TestDatabase].[Table1] instead of [LinkedServer].[LiveDatabase].[Table1] . I'm trying to automate this process so the devs don't have to alter each stored procedure every time the DB is restored. – TheCoxta Sep 04 '19 at 11:14
  • Database names aren't connection strings. You're asking how to reference *different databases*, not linked servers or change any connection strings. It would be *far easier* to create a new SQL Server named instance and create databases with the same name. Otherwise you won't be testing the production code at all, you'd be testing something *very* different. – Panagiotis Kanavos Sep 04 '19 at 11:20
  • You should describe the *actual* problem - you want to restore a database that contains code referencing other databases. This isn't solved by string replacement. Why can't you use the *same* database names to begin with? Are those databases local or remote? – Panagiotis Kanavos Sep 04 '19 at 11:22
  • That might be possible further down the line but right now I need a method for swapping the DB names, is it possible? – TheCoxta Sep 04 '19 at 11:24
  • It's impossible to answer string replacement questions without having any idea what the strings look like. The *easy* solution is to *not* have to rename anything. Why don't you use the same names in the first place? Are you using a development database server perhaps to host the production data? – Panagiotis Kanavos Sep 04 '19 at 11:25
  • We have recently merged with another company so the linkedservers are remote and not owned by us therefore changing DB names won't be an easy process. – TheCoxta Sep 04 '19 at 11:26
  • The SQL statement you posted doesn't show anything related to database names either - are the names quoted or not? 3 or 4-part names? Replacing `[Production]` with `[TestProduction]` is a *lot* easier than replacing `Production` which can appear eg in a table name – Panagiotis Kanavos Sep 04 '19 at 11:28
  • Check for example [How to create an alias of database in SQL Server](https://stackoverflow.com/questions/21722066/how-to-create-an-alias-of-database-in-sql-server). You can't create synonyms for a *database* but you can create databases with the names you want locally and fill them with views or synonyms that target the original databases. You could create SYNONYMs for each remote object to make it appear as a local object, but that depends on how the stored procedures are written – Panagiotis Kanavos Sep 04 '19 at 11:35
  • 1
    Can you [edit] your question with an example of a procedure from production that need to be altered, and the way it should look after being altered? (We don't need to see the entire procedure, just enough to understand exactly what it is that you are attempting to do) - say, for example `create procedure procXXX as ... from server.database.schema.table ...` to `create procedure procXXX as ... from differentServer.differentDatabase.differentSchema.differentTable ...` – Zohar Peled Sep 04 '19 at 11:35
  • I've updated the question with part of the procedure. Hope this helps. – TheCoxta Sep 04 '19 at 11:54
  • My advice is this: Take the business penalty and have the newly merged company create a dedicated SQL Server instance for UAT. Then, in your development environment, change the linked server definition from prod to UAT and be done with it. This is much easier and much safer than anything else you can do. In fact, anything in the Development and UAT environment should have no way to access production databases. – Zohar Peled Sep 04 '19 at 13:37

0 Answers0