August 17, 2018 at 8:15 am
Is there any way to do a find and replace in the triggers on a database?
I am working on setting up a dev / testing environment for an ERP system, and need to be able to replace the server name in a bunch of triggers so it is not pointed at our production system. There are a couple hundred of them, so scripting each one out individually is pretty painful. Given that Sp_configure no longer works, I am going to need to figure out a better way to do it.
My original idea was to use code like the following :
Declare @TriggerText nvarchar(MAX)
Declare TriggerFixer Cursor FOR SELECT text FROM Syscomments C WHere ID IN
(
SELECT OBJECT_ID FROM [sys].[triggers] Where name Like 'rmarepl_mstDel'
)
Open TriggerFixer
FETCH NEXT FROM TriggerFixer into @TriggerText
While @@FETCH_STATUS = 0
Begin
SET @TriggerText = REPLACE(@Triggertext, 'Prodservername', 'devservername')
EXEC @TriggerText
FETCH NEXT FROMTriggerFixer into @TriggerText
END
Close TriggerFixer
Deallocate TriggerFixer
Unfortunately, many of them are sufficiently long that they have multiple entries in syscomments (largest one has 38 entries).
I am not really sure where to go from here.
Additional Information
The ERP System has 3 databases - an _App Database, a _Forms database and an _objects database. These are prefixed with the name of the environment - DEV_App, TEST_App, etc...
The Current Process For updating our Dev environment is as follows:
Use Microsoft DPM to overwrite the current app, forms and objects databases with a backup of the current database
run Sql Queries to update the Objects & Forms database names in the _app Database.
run some commands to clear the application layer's cache and restart the service
The final step is to reconfigure the "Replication" triggers that exist for multi site configurations to not be pointed at the production server. The triggers use a 4 part name despite everything running on the same server. The triggers essentially make it so updates, inserts, etc on specific tables are not only updated to all the places in they need to be done in the same database, but also to the "master" database.
August 17, 2018 at 8:50 am
Try this. Run the query, then copy and paste the results into another query window, inspect, then run. You may find it mangles "&" or "<" or ">" signs, so look out for this (or type "for xml preserve ampersands" into your favourite search engine).
A couple of questions for you first:
(1) Why do you need server names at all in your triggers? Are they linked servers? (If so, that could lead to performance problems.)
(2) 38 rows in syscomments? Since a trigger is going to run every time you do something to a table (not a technical term!), it needs to be short and snappy. It sounds like a potential performance nightmare if you have that much code in your triggers.
John
August 17, 2018 at 9:26 am
Steven.Grzybowski - Friday, August 17, 2018 8:15 AMIs there any way to do a find and replace in the triggers on a database?
I am working on setting up a dev / testing environment for an ERP system, and need to be able to replace the server name in a bunch of triggers so it is not pointed at our production system. There are a couple hundred of them, so scripting each one out individually is pretty painful. Given that Sp_configure no longer works, I am going to need to figure out a better way to do it.
Wouldn't go there, rather, sandbox the systems and keep everything identical apart from the "real world" which you'll emulate in the sandboxes.
😎
Changes between the environments will affect the integrity of the testing, not a good idea.
August 17, 2018 at 9:28 am
John Mitchell-245523 - Friday, August 17, 2018 8:50 AMTry this. Run the query, then copy and paste the results into another query window, inspect, then run. You may find it mangles "&" or "<" or ">" signs, so look out for this (or type "for xml preserve ampersands" into your favourite search engine).A couple of questions for you first:
(1) Why do you need server names at all in your triggers? Are they linked servers? (If so, that could lead to performance problems.)
(2) 38 rows in syscomments? Since a trigger is going to run every time you do something to a table (not a technical term!), it needs to be short and snappy. It sounds like a potential performance nightmare if you have that much code in your triggers.John
If you linked something somewhere, I am unable to find the links
( 1) They are using a 4 part identifier for some reason, even though it is not a linked server.
(2)
Each trigger has somewhere around 70-100 lines of comments at the start- copyright stuff, and update history. It has to do with replicating the changes made to everywhere in the system that it is referenced.
Agreed, but unfortunately this is not something I can change, it is part of the core functionality of the ERP system. Each field effects different other tables, and there are something like 60 fields in that specific table. Each field can effect between 2 and 50 other tables.
I am not having issues with stuff getting mangled, just that the script is being split into multiple rows, so I need to find a way to handle that. I am thinking that If I could assign each row to a variable, I could just do exec @var1+@var2... etc, but not sure how to deal with the differing amounts of entries per trigger.
August 17, 2018 at 9:32 am
Eirikur Eiriksson - Friday, August 17, 2018 9:26 AMSteven.Grzybowski - Friday, August 17, 2018 8:15 AMIs there any way to do a find and replace in the triggers on a database?
I am working on setting up a dev / testing environment for an ERP system, and need to be able to replace the server name in a bunch of triggers so it is not pointed at our production system. There are a couple hundred of them, so scripting each one out individually is pretty painful. Given that Sp_configure no longer works, I am going to need to figure out a better way to do it.Wouldn't go there, rather, sandbox the systems and keep everything identical apart from the "real world" which you'll emulate in the sandboxes.
😎Changes between the environments will affect the integrity of the testing, not a good idea.
The Sole differences in the triggers would be the name of the server, which needs to change from the prod environment to the dev environment. The ERP system has a front end tool for doing this, but it means that the updating of a dev environment to have the same data as prod is not able to be scripted out.
August 17, 2018 at 9:37 am
Oh rats - forgot to post the script!
It's my script that will mangle stuff, not yours. However, it'll do it all in one go instead of using a cursor. If you prefer to reverse engineer it so that it uses the cursor, that'll work.
My advice is to get rid of the four-part naming. Use this opportunity to chop the server name off for good. And, if it's all in the same database, lose the database name, too.
SELECT REPLACE(m.definition,'Prodservername.Databasename','') + CHAR(10)
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
FOR XML PATH ('')
John
August 17, 2018 at 10:37 am
John Mitchell-245523 - Friday, August 17, 2018 9:37 AMOh rats - forgot to post the script!It's my script that will mangle stuff, not yours. However, it'll do it all in one go instead of using a cursor. If you prefer to reverse engineer it so that it uses the cursor, that'll work.
My advice is to get rid of the four-part naming. Use this opportunity to chop the server name off for good. And, if it's all in the same database, lose the database name, too.
SELECT REPLACE(m.definition,'Prodservername.Databasename','') + CHAR(10)
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
FOR XML PATH ('')John
It is not in the same database unfortunately, the ERP system uses 3 different databases per "site" with a master site with 3 databases and at least 3 instances.
Just started checking into the script, it is doing a bit of mangling, but it is at least negating the need to pull a ton of rows.
Also, Ended up looking at something like this
DDeclare @Triggertext nvarchar(MAX)
Declare TriggerFixer Cursor For
SELECT REPLACE(m.definition,'Prodservername ','DevServerName') String INTO #TMp1
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
OPEN TriggerFixer
fetch next from TriggerFixer INTO @Triggertext
While @@Fetch_status = 0
Begin
fetch next from TriggerFixer INTO @Triggertext
EXEC @Triggertext
END
This way I dont have to worry about XML Manglage.
August 17, 2018 at 4:38 pm
Steven.Grzybowski - Friday, August 17, 2018 10:37 AMJohn Mitchell-245523 - Friday, August 17, 2018 9:37 AMOh rats - forgot to post the script!It's my script that will mangle stuff, not yours. However, it'll do it all in one go instead of using a cursor. If you prefer to reverse engineer it so that it uses the cursor, that'll work.
My advice is to get rid of the four-part naming. Use this opportunity to chop the server name off for good. And, if it's all in the same database, lose the database name, too.
SELECT REPLACE(m.definition,'Prodservername.Databasename','') + CHAR(10)
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
FOR XML PATH ('')John
It is not in the same database unfortunately, the ERP system uses 3 different databases per "site" with a master site with 3 databases and at least 3 instances.
Just started checking into the script, it is doing a bit of mangling, but it is at least negating the need to pull a ton of rows.Also, Ended up looking at something like this
D
Declare @Triggertext nvarchar(MAX)
Declare TriggerFixer Cursor For
SELECT REPLACE(m.definition,'Prodservername ','DevServerName') String INTO #TMp1
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
OPEN TriggerFixer
fetch next from TriggerFixer INTO @Triggertext
While @@Fetch_status = 0
Begin
fetch next from TriggerFixer INTO @Triggertext
EXEC @Triggertext
ENDThis way I dont have to worry about XML Manglage.
Stop it! Stop using 3 and 4 part naming! 😉 You're just killing yourself here over and over again. You should never have to change code just to move to another box even if all the databases are named differently. Use SYNONYMs and just change the synonyms. There are no code changes required after that.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2018 at 7:24 pm
Jeff Moden - Friday, August 17, 2018 4:38 PMSteven.Grzybowski - Friday, August 17, 2018 10:37 AMJohn Mitchell-245523 - Friday, August 17, 2018 9:37 AMOh rats - forgot to post the script!It's my script that will mangle stuff, not yours. However, it'll do it all in one go instead of using a cursor. If you prefer to reverse engineer it so that it uses the cursor, that'll work.
My advice is to get rid of the four-part naming. Use this opportunity to chop the server name off for good. And, if it's all in the same database, lose the database name, too.
SELECT REPLACE(m.definition,'Prodservername.Databasename','') + CHAR(10)
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
FOR XML PATH ('')John
It is not in the same database unfortunately, the ERP system uses 3 different databases per "site" with a master site with 3 databases and at least 3 instances.
Just started checking into the script, it is doing a bit of mangling, but it is at least negating the need to pull a ton of rows.Also, Ended up looking at something like this
D
Declare @Triggertext nvarchar(MAX)
Declare TriggerFixer Cursor For
SELECT REPLACE(m.definition,'Prodservername ','DevServerName') String INTO #TMp1
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
OPEN TriggerFixer
fetch next from TriggerFixer INTO @Triggertext
While @@Fetch_status = 0
Begin
fetch next from TriggerFixer INTO @Triggertext
EXEC @Triggertext
ENDThis way I dont have to worry about XML Manglage.
Stop it! Stop using 3 and 4 part naming! 😉 You're just killing yourself here over and over again. You should never have to change code just to move to another box even if all the databases are named differently. Use SYNONYMs and just change the synonyms. There are no code changes required after that.
Jeff,
I wish I could change things to use synonyms instead, but this is all code that comes out of the box from an ERP vendor, and changing any kind of functionality like that would mean that the system would be out of support. Any time an issue comes up, the first thing this vendor does is check for any code changes from the stock system, and refuse to do much of anything to help with it until you remove any altered code.
August 17, 2018 at 7:54 pm
Steven.Grzybowski - Friday, August 17, 2018 7:24 PMJeff Moden - Friday, August 17, 2018 4:38 PMSteven.Grzybowski - Friday, August 17, 2018 10:37 AMJohn Mitchell-245523 - Friday, August 17, 2018 9:37 AMOh rats - forgot to post the script!It's my script that will mangle stuff, not yours. However, it'll do it all in one go instead of using a cursor. If you prefer to reverse engineer it so that it uses the cursor, that'll work.
My advice is to get rid of the four-part naming. Use this opportunity to chop the server name off for good. And, if it's all in the same database, lose the database name, too.
SELECT REPLACE(m.definition,'Prodservername.Databasename','') + CHAR(10)
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
FOR XML PATH ('')John
It is not in the same database unfortunately, the ERP system uses 3 different databases per "site" with a master site with 3 databases and at least 3 instances.
Just started checking into the script, it is doing a bit of mangling, but it is at least negating the need to pull a ton of rows.Also, Ended up looking at something like this
D
Declare @Triggertext nvarchar(MAX)
Declare TriggerFixer Cursor For
SELECT REPLACE(m.definition,'Prodservername ','DevServerName') String INTO #TMp1
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
OPEN TriggerFixer
fetch next from TriggerFixer INTO @Triggertext
While @@Fetch_status = 0
Begin
fetch next from TriggerFixer INTO @Triggertext
EXEC @Triggertext
ENDThis way I dont have to worry about XML Manglage.
Stop it! Stop using 3 and 4 part naming! 😉 You're just killing yourself here over and over again. You should never have to change code just to move to another box even if all the databases are named differently. Use SYNONYMs and just change the synonyms. There are no code changes required after that.
Jeff,
I wish I could change things to use synonyms instead, but this is all code that comes out of the box from an ERP vendor, and changing any kind of functionality like that would mean that the system would be out of support. Any time an issue comes up, the first thing this vendor does is check for any code changes from the stock system, and refuse to do much of anything to help with it until you remove any altered code.
But.... you're changing the code with what you're trying to do, are you not?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2018 at 8:20 pm
Jeff Moden - Friday, August 17, 2018 7:54 PMSteven.Grzybowski - Friday, August 17, 2018 7:24 PMJeff Moden - Friday, August 17, 2018 4:38 PMSteven.Grzybowski - Friday, August 17, 2018 10:37 AMJohn Mitchell-245523 - Friday, August 17, 2018 9:37 AMOh rats - forgot to post the script!It's my script that will mangle stuff, not yours. However, it'll do it all in one go instead of using a cursor. If you prefer to reverse engineer it so that it uses the cursor, that'll work.
My advice is to get rid of the four-part naming. Use this opportunity to chop the server name off for good. And, if it's all in the same database, lose the database name, too.
SELECT REPLACE(m.definition,'Prodservername.Databasename','') + CHAR(10)
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
FOR XML PATH ('')John
It is not in the same database unfortunately, the ERP system uses 3 different databases per "site" with a master site with 3 databases and at least 3 instances.
Just started checking into the script, it is doing a bit of mangling, but it is at least negating the need to pull a ton of rows.Also, Ended up looking at something like this
D
Declare @Triggertext nvarchar(MAX)
Declare TriggerFixer Cursor For
SELECT REPLACE(m.definition,'Prodservername ','DevServerName') String INTO #TMp1
FROM sys.sql_modules m
JOIN sys.triggers t ON m.object_id = t.object_id
OPEN TriggerFixer
fetch next from TriggerFixer INTO @Triggertext
While @@Fetch_status = 0
Begin
fetch next from TriggerFixer INTO @Triggertext
EXEC @Triggertext
ENDThis way I dont have to worry about XML Manglage.
Stop it! Stop using 3 and 4 part naming! 😉 You're just killing yourself here over and over again. You should never have to change code just to move to another box even if all the databases are named differently. Use SYNONYMs and just change the synonyms. There are no code changes required after that.
Jeff,
I wish I could change things to use synonyms instead, but this is all code that comes out of the box from an ERP vendor, and changing any kind of functionality like that would mean that the system would be out of support. Any time an issue comes up, the first thing this vendor does is check for any code changes from the stock system, and refuse to do much of anything to help with it until you remove any altered code.But.... you're changing the code with what you're trying to do, are you not?
While I am "changing the code", I am doing it in a manner identical to what their front end tool does, rather than altering the actual functionality. in this case, there is no difference in the end result of my scripted change vs their GUI driven change.
As long as the final results are indistinguishable from what their tools do, they don't give any grief.
I am just taking the series of manual, gui driven steps and automating them as scripts.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply