SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[9.5.452 Pro] - B: $$...$$ macro result in SQL Server

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[9.5.452 Pro] - B: $$...$$ macro result in SQL Server
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [9.5.452 Pro] - B: $$...$$ macro result in SQL Server Reply with quote
I've got a snippet that I use to quickly retrieve codes for eg. stored procedures, functions or triggers. It's very old and it's pretty crude but I'm still using it every now and then. Below is its code:
Code:

$$
SET ARITHABORT ON
DECLARE @obj_qualname sysname = '$OBJECT(ins_qualname)$';

IF    (
    @@version LIKE 'Microsoft SQL Server 2005%'
    OR @@version LIKE 'Microsoft SQL Server 2008%'
    or @@version like '%SQL Server 201%'
    or @@version like '%Denali%')
BEGIN
    DECLARE   
        @sql NVARCHAR(MAX)
   
    SELECT
        @sql = ''
   
    SET @sql = (
            SELECT
                STUFF(
                    aha.[definition]
                    ,CHARINDEX('CREATE' ,aha.[definition])
                    ,6
                    ,'ALTER'
                )
                + CASE WHEN Substring(aha.definition,LEN(aha.definition),1) NOT IN (CHAR(10), CHAR(13)) THEN CHAR(10) ELSE '' END 
                + 'GO' + CHAR(10)
                + CASE
                      WHEN    aha.disable_trigger <> ''        THEN    CHAR(10) + aha.disable_trigger        ELSE    ''
                  END + CHAR(10) + CHAR(10)
            FROM
                (
                    SELECT
                        sm.[definition]
                        ,'' AS                  disable_trigger
                    FROM
                        sys.all_sql_modules  AS sm
                    WHERE    sm.[object_id] = OBJECT_ID(@obj_qualname)
                   
                    UNION
                   
                    SELECT
                        sm.[definition]
                        ,CASE
                             WHEN    tr.is_disabled = 1        THEN    'DISABLE TRIGGER ' + tr.name + ' ON ' + SCHEMA_NAME(t.[schema_id]) + '.' + t.name + CHAR(10) + 'GO'
                                     + CHAR(10)
                                     +
                                     CHAR(10)        ELSE    ''
                         END                 AS disable_trigger
                    FROM
                        sys.tables           AS t
                    INNER JOIN
                        sys.triggers         AS tr
                            ON    tr.parent_id = t.[object_id]
                    INNER JOIN
                        sys.all_sql_modules  AS sm
                            ON    sm.[object_id] = tr.[object_id]
                    WHERE    t.[object_id] = OBJECT_ID(@obj_qualname)
                ) AS aha                 
                FOR XML PATH(''), TYPE
        ).value('(./text()[1])' ,'nvarchar(max)')
   
   
    SELECT
        @sql
END
ELSE
SELECT
    STUFF(sc.[text], CHARINDEX('CREATE', sc.[text], 1), 6, 'ALTER')
FROM
    [$DB$].dbo.syscomments AS sc
WHERE    sc.id = OBJECT_ID(@obj_qualname)
ORDER BY
    sc.colid;

IF OBJECT_ID(@obj_qualname, N'P') IS NOT NULL
    SELECT
        CASE
            WHEN perm.state <> 'W' THEN perm.state_desc       
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.[schema_id])) + '.' + QUOTENAME(obj.name) COLLATE
        database_default
        + CASE
              WHEN cl.column_id IS NULL THEN SPACE(0)       
              ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
        + CASE
              WHEN perm.state <> 'W' THEN SPACE(0)       
              ELSE SPACE(1) + 'WITH GRANT OPTION'
          END   
        + ';'     
        + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)  AS '--Object Level Permissions'
    FROM
        sys.database_permissions  AS perm
    INNER JOIN
        sys.objects               AS obj
        ON  perm.major_id = obj.[object_id]
    INNER JOIN
        sys.database_principals   AS usr
        ON  perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
        sys.columns               AS cl
        ON  cl.column_id = perm.minor_id
            AND cl.[object_id] = perm.major_id
    WHERE   1 = 1
        AND obj.[object_id] = OBJECT_ID(@obj_qualname);
       
IF OBJECT_ID(@obj_qualname ,N'P') IS NOT NULL
    SELECT
        -- CONCAT
        ('ADD SIGNATURE TO OBJECT::[$OBJECT(ins_schema)$].[$OBJECT(ins_object)$] BY CERTIFICATE [' + c.[name] + '] WITH PASSWORD = '''';' + char(13) + char(10) +'GO')
    FROM
        sys.procedures        AS pr
    INNER JOIN
        sys.sql_modules       AS sm
        ON  sm.[object_id] = pr.[object_id]
    INNER JOIN
        sys.crypt_properties  AS cp
        ON  cp.major_id = pr.[object_id]
    INNER JOIN
        sys.certificates      AS c
        ON  c.thumbprint = cp.thumbprint
    WHERE   1 = 1
        AND pr.[object_id] = OBJECT_ID(@obj_qualname)
;     
--$$


Now, I've run into a very interesting issue. For some stored procedures the snippet retrieves their code multiple times. If I remove the $$ parts and only let SA create the code and then execute it manually, the result contains the edit script for the stored procedure as it is expected. However, if I allow SA to retrieve the script using the $$...$$ macro and put it into the editor, the result contains the script concatenated 3 times. I've got yet no idea what triggers this behavior on some procedures and not on others.
Tue Feb 20, 2018 8:25 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
Maybe a coincidence, but inside $$..$$ you got 3 occurrences of [$OBJECT(ins_object)$], and you say the code is executed 3 times.

Is there anything special about the procedures that trigger it 3 times? case sensitive names? same schema, vs. different schema or database?
Tue Feb 20, 2018 10:49 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I believe it is a coincidence. If that would matter, it would consistently yield the single or the triple results for each stored procedure. Also, those aren't all $OBJECT(ins_object)$ but yes, there are 3 $OBJECTS(...)$ macros: $OBJECT(ins_qualname)$, $OBJECT(ins_schema)$ and $OBJECT(ins_object)$. The locations of those are why I said it's crude and old. I usually collect macros to the top part of my snippets and keep the main code clean of any macros if possible. This one I haven't touched in ages.

I'm not sure it triggers 3 times, but it definitely returns the results of an execution 3 times. I see no relevant common properties in affected procedures or dissimilarities to those unaffected. I'll try to clean up the snippet code to see if it makes any difference.

EDIT: Okay, the clean up did not help. The new code contains one $OBJECT(ins_qualname)$ and the $$...$$ encapsulating the entire script. It still returns the results 3 times for the same procedure.

The new code is:
Code:

$$
SET ARITHABORT ON
DECLARE @obj_qualname     sysname = '$OBJECT(ins_qualname)$';;
DECLARE @ins_schema       sysname = PARSENAME(@obj_qualname, 2)
       ,@ins_object       sysname = PARSENAME(@obj_qualname, 1)
;

IF    (
    @@version LIKE 'Microsoft SQL Server 2005%'
    OR @@version LIKE 'Microsoft SQL Server 2008%'
    or @@version like '%SQL Server 201%'
    or @@version like '%Denali%')
BEGIN
    DECLARE   
        @sql NVARCHAR(MAX)
   
    SELECT
        @sql = ''
   
    SET @sql = (
            SELECT
                STUFF(
                    aha.[definition]
                    ,CHARINDEX('CREATE' ,aha.[definition])
                    ,6
                    ,'ALTER'
                )
                + CASE WHEN Substring(aha.definition,LEN(aha.definition),1) NOT IN (CHAR(10), CHAR(13)) THEN CHAR(10) ELSE '' END 
                + 'GO' + CHAR(10)
                + CASE
                      WHEN    aha.disable_trigger <> ''        THEN    CHAR(10) + aha.disable_trigger        ELSE    ''
                  END + CHAR(10) + CHAR(10)
            FROM
                (
                    SELECT
                        sm.[definition]
                        ,'' AS                  disable_trigger
                    FROM
                        sys.all_sql_modules  AS sm
                    WHERE    sm.[object_id] = OBJECT_ID(@obj_qualname)
                   
                    UNION
                   
                    SELECT
                        sm.[definition]
                        ,CASE
                             WHEN    tr.is_disabled = 1        THEN    'DISABLE TRIGGER ' + tr.name + ' ON ' + SCHEMA_NAME(t.[schema_id]) + '.' + t.name + CHAR(10) + 'GO'
                                     + CHAR(10)
                                     +
                                     CHAR(10)        ELSE    ''
                         END                 AS disable_trigger
                    FROM
                        sys.tables           AS t
                    INNER JOIN
                        sys.triggers         AS tr
                            ON    tr.parent_id = t.[object_id]
                    INNER JOIN
                        sys.all_sql_modules  AS sm
                            ON    sm.[object_id] = tr.[object_id]
                    WHERE    t.[object_id] = OBJECT_ID(@obj_qualname)
                ) AS aha                 
                FOR XML PATH(''), TYPE
        ).value('(./text()[1])' ,'nvarchar(max)')
   
   
    SELECT
        @sql
END
ELSE
SELECT
    STUFF(sc.[text], CHARINDEX('CREATE', sc.[text], 1), 6, 'ALTER')
FROM
    dbo.syscomments AS sc
WHERE    sc.id = OBJECT_ID(@obj_qualname)
ORDER BY
    sc.colid;

IF OBJECT_ID(@obj_qualname, N'P') IS NOT NULL
    SELECT
        CASE
            WHEN perm.state <> 'W' THEN perm.state_desc       
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.[schema_id])) + '.' + QUOTENAME(obj.name) COLLATE
        database_default
        + CASE
              WHEN cl.column_id IS NULL THEN SPACE(0)       
              ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
        + CASE
              WHEN perm.state <> 'W' THEN SPACE(0)       
              ELSE SPACE(1) + 'WITH GRANT OPTION'
          END   
        + ';'     
        + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)  AS '--Object Level Permissions'
    FROM
        sys.database_permissions  AS perm
    INNER JOIN
        sys.objects               AS obj
        ON  perm.major_id = obj.[object_id]
    INNER JOIN
        sys.database_principals   AS usr
        ON  perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
        sys.columns               AS cl
        ON  cl.column_id = perm.minor_id
            AND cl.[object_id] = perm.major_id
    WHERE   1 = 1
        AND obj.[object_id] = OBJECT_ID(@obj_qualname);
       
IF OBJECT_ID(@obj_qualname ,N'P') IS NOT NULL
    SELECT
        -- CONCAT
        ('ADD SIGNATURE TO OBJECT::[' + @ins_schema + '].[' + @ins_object + '] BY CERTIFICATE [' + c.[name] + '] WITH PASSWORD = ''Password12we43'';' + char(13) + char(10) +'GO')
    FROM
        sys.procedures        AS pr
    INNER JOIN
        sys.sql_modules       AS sm
        ON  sm.[object_id] = pr.[object_id]
    INNER JOIN
        sys.crypt_properties  AS cp
        ON  cp.major_id = pr.[object_id]
    INNER JOIN
        sys.certificates      AS c
        ON  c.thumbprint = cp.thumbprint
    WHERE   1 = 1
        AND pr.[object_id] = OBJECT_ID(@obj_qualname)
;     
--$$

Wed Feb 21, 2018 4:33 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
This has been reproduced and confirmed as a bug. The processing of multiple result sets which are internal to the macro is incorrect. Good news is that will have a fix for that issue availability very soon.
Fri Feb 23, 2018 1:44 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
That's great! Thank you very much.

Can you share some info on what made this trigger for some procedures but not on others?
Fri Feb 23, 2018 5:46 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.