2

I want to avoid the using of deprecated code.

Like the new feature Nr. 8 http://msdn.microsoft.com/en-us/library/ms178053.aspx

I want to force them to use "WITH" for example, or a semicolon at the end.

Is there an easy way to force this with ease?

  • Bjorn, are they adding code into stored procedures, functions, or straight into the application? Probably the most pain free way would be with code reviews and use of the deprecated extended event: mssqltips.com/sqlservertip/1857/… – Sean Gallardy Jul 11 '14 at 14:00
  • Hey Sean, we use stored procedures, functions and triggers. Code-Review is a good idea, but i will prevent every single create func, proc, trigger to use old code. Thanks for your Link, I'll check this out! – derBjörn Jul 11 '14 at 14:23
  • Do code review and dock their pay for each violation. big evil grin – Greenstone Walker Jul 13 '14 at 3:12
  • I found a great tool for analyze code. I thing that would cover my needs sqlcodeguard.com Thanks for your answers, guys! – derBjörn Jul 14 '14 at 11:29
4

Bjorn,

There is nothing out of the box that will force the developers to use non-deprecated features (as they are deprecated, not removed). The most tailored solution that could be created is with DDL trigger(s), but note that they can be tricky... especially if not extremely familiar with them.

I would suggest PBM, but it has limitations that won't necessarily capture everything you're looking for. As already discussed, you can use extended events (in the previous link I had) to check for the use of the deprecated items.

Here is an example that will stop the use of fn_get_sql() and give a helpful message while stopping the use. Note that multiple checks can be made in the same trigger, this is just a trivial example.

/* Create a test datbase */
CREATE DATABASE No_Deprecated_Items;
GO

USE No_Deprecated_Items;
GO

CREATE TRIGGER Stop_Deprecated_Items
ON DATABASE
AFTER CREATE_FUNCTION, ALTER_FUNCTION, CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER
AS
BEGIN
    DECLARE @Command NVARCHAR(MAX)

    SELECT @Command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    IF(PATINDEX('%fn_get_sql%', @Command) > 0)
    BEGIN
        RAISERROR('fn_get_sql is deprecated, use sys.dm_exec_sql_text() instead!', 16, 1)
        ROLLBACK
    END
END
GO

/*
This is created as it passed the constraints in the trigger
*/

CREATE PROCEDURE Good_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM sys.dm_exec_sql_text(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is created
SELECT * FROM sys.procedures
GO

/*
This will be rolledback as it fails the trigger logic.
*/
CREATE PROCEDURE Bad_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM ::fn_get_sql(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is NOT created
SELECT * FROM sys.procedures
GO

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://msdn.microsoft.com/en-us/library/ms187909.aspx

http://msdn.microsoft.com/en-us/library/bb510452.aspx

2

You are asking 2 different questions. So I will try to answer this accordingly.

  • Hey Kin, thanks for your Answer, I will give policies a try. Today I found a great tool to analyze code. sqlcodeguard.com This will help us to find depreacted code with ease. Thank you Kin for your answer! – derBjörn Jul 14 '14 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.