Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Automate scripting out object ALTERs for rollback

Author  Topic 

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-23 : 18:01:17
Hello all,

The place I work at is finally getting around to upgrading from SQL 2005 to SQL 2012. Unfortunately, the legacy system I inherited has hundreds of objects with the deprecated RAISERROR syntax:

RAISERROR {error number} {error message}

I have scripted out a method to detect the objects (within a margin of error) that have this issue and will post it below, but the reason for this post is that I am looking for the path of least resistance to script out rollback scripts as a starting point for the team. We are starting down the path of powershell but the SMO doesn't seem to have a clean way to get to ALTERs. We can solve this with a replace, but it's just not as clean as we hoped. Furthermore, it looks like the USE [database] and ansi settings have to be concatenated in. Just wondering if anyone knows of a quicker method to take a list of objects and generate scripts. I was thinking there has to be an API that SSMS uses to generate scripts that could be tapped into?

In any case, here's my RAISERROR detection script in case it helps anyone else. For those that don't know, MS says that upgrade advisor won't be fixed to include these these because the tool was not built to handle this type of pattern detection:
https://connect.microsoft.com/SQLServer/feedback/details/694484/denali-ctp3-upgrade-advisor-misses-deprecated-raiserror-syntax

I am using the REGEX CLR function at http://www.codeproject.com/Articles/19502/A-T-SQL-Regular-Expression-Library-for-SQL-Server and there are some caveats, but it seems to work pretty good:


Use Master;
/*
Currently, the RegEx CLR functions are installed on MB1 in Master
RegEx CLR can be found at http://www.codeproject.com/Articles/19502/A-T-SQL-Regular-Expression-Library-for-SQL-Server

Note that this will still find the RAISERROR pattern in a string provided there is no concatenation between elements
The number of false positives or missed objects should be very low and able to be handled on a case by case basis
*/
GO
IF OBJECT_ID('tempdb.dbo.#tblObjectInfo') IS NOT NULL
DROP TABLE #tblObjectInfo;

CREATE TABLE #tblObjectInfo
(
DatabaseName SYSNAME,
ObjectID INT,
SchemaName SYSNAME,
ObjectName SYSNAME,
ObjectType CHAR(2),
ObjectText NVARCHAR(MAX),
PRIMARY KEY (DatabaseName,ObjectID)
);


DECLARE @SQL NVARCHAR(MAX);

SET @SQL =
'
use ;
INSERT #tblObjectInfo
(
ObjectID,
DatabaseName,
SchemaName,
ObjectName,
ObjectType,
ObjectText
)
SELECT o.Object_ID,
DB_NAME() AS DatabaseName,
s.name AS SchemaName,
o.Name AS ObjectName,
o.type AS ObjectType,
--m.definition AS ObjectText -- Uncomment for raw sp deinitions
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --
-- The below line will replace all COMMENTS in object definitions with empty string
-- This allows our global search to avoid returning false positives
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --
Master.dbo.ufn_RegExReplace(m.definition,''(--.*)|(((/\*)+?[\w\W]+?(\*/)+))'', '''', 1) AS ObjectText
FROM sys.all_sql_modules AS m
JOIN sys.all_objects AS o
ON m.Object_ID = o.Object_ID
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped=0
------------------------------------------------------
-- custom filter
------------------------------------------------------
AND m.definition LIKE ''%RAISERROR%''
AND (o.name NOT LIKE ''sp[_]%'' OR CONVERT(VARBINARY(MAX),o.name) <> CONVERT(VARBINARY(MAX),LOWER(o.name)));
';

EXEC dbo.sp_msforeachDB @SQL;

SELECT t.DatabaseName,
t.SchemaName,
t.ObjectName
FROM #tblObjectInfo AS t
WHERE Master.dbo.ufn_RegExIsMatch(t.ObjectText, '\bRAISERROR[\t\n ]+[@0-9]', 1) = 1
ORDER BY t.DatabaseName,
t.SchemaName,
t.ObjectName;


Thanks in advance for any help.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-28 : 10:14:16
Judging by the lack of response, I am going to assume that the approach we are taking with Powershell is the proper course. Any other options?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 10:32:25
What I'd do is:

1. use SQLs Generate Scripts task for the procedures in question.
2. Edit the scripts in a text editor to mass change the RAISERROR statements
3. Run the modified scripts on SQL 2012.
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2014-10-28 : 16:33:36
Fair enough, but we are looking to do this on 1k objects. It would be nice to automate.
Go to Top of Page
   

- Advertisement -