String Replace in Stored Procedures with Powershell

Question

String Replace in Stored Procedures with Powershell

thedspaincrew

Ten Centuries

Points: 1042
More actions
November 6, 2018 at 12:35 pm

#368357

Comments posted to this topic are about the item String Replace in Stored Procedures with Powershell

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

thedspaincrew - Tuesday, November 6, 2018 12:35 PM
Comments posted to this topic are about the item String Replace in Stored Procedures with Powershell

Not intended towards you but in general, the problem with the task
" My task wasn't to find out why that was happening but to find a way to script out the SPs and replace the proper text and alter the procedures as it was causing an outage".
So then with someone creating stored procedures with the setting off, they will need to run this every time that person deploys new stored procedures.
Seems the client should have worked on understanding the issue and knowing they could check sys.sql_modules to find the settings for the stored procedures. If it was me, I'd want to know that I was screwing this up every time. And I'd learn things in the process.

Sue

thedspaincrew Ten Centuries Points: 1042 More actions · Answer 2

It was an issue on the customer side. The application, obviously has a SQL Server backend, but it seems customers think databases just run themselves and don't require folks to work on them. They have no dedicated DBA resource. I provided this script to the customer as a way to fix an issue they were creating on their side. We have multiple customers using this application all over the world on this specific version that do not have this issue. Now if it was my database, that I managed, as a part of my SQL estate then yes I would agree, I should get to the root cause, but I am a "third-party vendor". I hope this better explains the issue.

Michael

doug.bishop SSC Enthusiast Points: 118 More actions · Answer 3

One concern, CREATE PROC and CREATE PROCEDURE are the same. This will not work if procedure was created using CREATE PROC.

thedspaincrew Ten Centuries Points: 1042 More actions · Answer 4

doug.bishop - Tuesday, November 13, 2018 12:16 PM
One concern, CREATE PROC and CREATE PROCEDURE are the same. This will not work if procedure was created using CREATE PROC.

regarding this issue, there were no "Create Proc" statements I had to worry about, but that could easily be accounted for.

thx
Michael