stored procedure runs fine in all sessions but one



  • There are procedures that run fine manually https://dba.stackexchange.com/a/276427/22336 , or fails when run https://dba.stackexchange.com/a/170620/22336 , or https://dba.stackexchange.com/q/311672/22336

    Mine works in all sessions but one.

    this is the code I am running - it calls a stored procedure that gets the trigger definition(s) and save it in a temp table.

    works fine and it is part of my automation work.

                IF OBJECT_ID('tempdb.dbo.#Jagannatha_sp_getTriggerDef') IS NOT NULL 
                DROP TABLE #Jagannatha_sp_getTriggerDef 
    
            CREATE TABLE #Jagannatha_sp_getTriggerDef ( 
            DB sysname not null,
            parent_name nvarchar(600) not null,
            object_id int not null,
            trigger_name sysname not null,
            is_disabled bit,
            i int not null,
            [trigger_definition] NVARCHAR(MAX) not null,
            primary key clustered (DB,trigger_name,i))
    
            truncate table #Jagannatha_sp_getTriggerDef 
    
            
            exec sp_getTriggerDef @dbname      = 'APCore', 
                                  @TableName   = 'dbo.receivedLog',
                                  @Drop_ONly   = 0,
                                  @Radhe       = '#Jagannatha_sp_getTriggerDef'
    
    
    SELECT *
          FROM #Jagannatha_sp_getTriggerDef
          order by db,i
    

    I run it on a table that has no triggers - just to make it as simple as possible

    enter image description here

    it comes out with the warning

    IF 'my_server\_DEVELOPMENT' <> @@ServerName THROW 50001, 'Wrong Server!!!',1 
    

    all good.

    but then on this particular session:

    enter image description here

    and there is nothing different in this session, that I could spot so far.

    SELECT *
      FROM sys.dm_exec_sessions 
      where login_name = 'my_company\my_user'
        and  session_id = @@SPID
    

    enter image description here

    the second one is where it fails. all others it works fine.

    what can I do to find out what is different? or even better, change the procedure so that it would work despite the difference?



  • I found a way out of this problem. I could not find out nor took the time to understand why exactly only one session was having this issue.

    This procedure of mine is packed with https://dba.stackexchange.com/q/224118/22336 , and it uses internally a temporary table called #Radhe.

    I was creating the temp table #Radhe inside of the https://dba.stackexchange.com/a/172067/22336 .

    I decided to do something different: instead of #Radhe I would have a https://dba.stackexchange.com/a/86854/22336 called ##Radhe that is created outside of the https://%20https://dba.stackexchange.com/a/172300/22336 but used inside it in the same way.

    and that did the trick for me in this situation.

    this is a partial view of the code that is inside the stored procedure:

            CREATE TABLE ##Radhe(
            DB sysname not null,
            parent_name nvarchar(600) not null,
            object_id int not null,
            trigger_name sysname not null,
            is_disabled bit default(0),
            is_drop bit default(0),
            i int not null identity(1,1),
            [trigger_definition] NVARCHAR(MAX) not null,
            PRIMARY KEY CLUSTERED (DB,trigger_name ,i)
        );
    
    SET @sql1 = N'USE '+ QUOTENAME(@dbname) + N'
    

    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    ' +
    CASE WHEN @RADHE IS NULL
    THEN N'INSERT INTO ##Radhe([DB],[parent_name],[object_id],[trigger_name],[is_disabled],[trigger_definition])
    SELECT [DB]=''[Radhe]'',
    [parent_name]=''___Shyam'',
    [object_id]=16108,
    [trigger_name]=''___Today only happens today!'',
    [is_disabled]=0,
    [trigger_definition]=''IF ''' + ''''' + @Server + ''''' + ''' <> @@ServerName THROW 50001, ''''Wrong Server!!!'''',1 '''

                    ELSE N' IF NOT  EXISTS (SELECT * FROM ' + @Radhe + ' R WHERE R.[DB]=''[__Radhe__]'')' + @vCrlf +
                       + N'INSERT INTO ##Radhe([DB],[parent_name],[object_id],[trigger_name],[is_disabled],[trigger_definition])
    

    this is another glimpse of the dynamic sql code:

                         INSERT INTO ##Radhe(DB,parent_name,object_id,trigger_name,is_disabled,is_drop,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,1, 
                                 ''IF EXISTS (SELECT ''''Radhe'''' FROM sys.triggers t wHERE t.[name] = ''   
                                    + '''''''' + @strigger_name + '''''''' 
                                    + '' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id(''  
                                    + '''''''' + @parent_name   + '''''''' + '') END  = t.parent_id )
                                 '')
    
                 INSERT INTO ##Radhe(DB,parent_name,object_id,trigger_name,is_disabled,is_drop,trigger_definition) values
                             (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,1,''EXEC(''''BEGIN DROP TRIGGER '' + @trigger_name + '' '' +  CASE WHEN   @parent_name != ''DATABASE'' THEN  '' ''   ELSE  '' ON DATABASE END '' END +  ''END'''') '')
    

    execution of several dynamic sql blocks and writing the result to a table - if parameter was supplied, or just a select.

    SELECT @SQL4 = CASE WHEN @RADHE IS NULL THEN N'' 
                        ELSE N' INSERT INTO ' 
                                + @Radhe 
                                + N'([DB],[parent_name],[object_id],[trigger_name],[is_disabled],[i],[trigger_definition])'
                    END       + N'
    
                            SELECT [DB],
                                   [parent_name],
                                   [object_id],
                                   [trigger_name],
                                   [is_disabled],
                                   [i],
                                   [trigger_definition]
                            FROM ##Radhe R
                            WHERE 1=1
                              AND (@Drop_ONly = 0) OR (@Drop_ONly = 1 AND (R.is_drop=1 OR R.[DB]=''[__Radhe__]'')  )
                            ORDER BY DB,I
    

    OPTION (RECOMPILE)

    '
    SET @SQL4 = CAST(@SQL1 + @SQL2 + @SQL3 + @SQL4 AS NVARCHAR(MAX));
    SET @ParamDefinition = N'@Drop_ONly BIT, @Server sysname, @DBNAME sysname, @CHECK_IF_TRIGGER_EXISTS BIT, @TABLEIDOUT int OUTPUT';

        BEGIN TRY
    
            EXEC sp_executesql @SQL4, 
                               @ParamDefinition, 
                               @Server                  = @Server,
                               @DBNAME                  = @DBNAME, 
                               @Drop_ONly               = @Drop_ONly, 
                               @CHECK_IF_TRIGGER_EXISTS = @CHECK_IF_TRIGGER_EXISTS, 
                               @TABLEIDOUT              = @TABLEID OUTPUT
    
        END TRY
        BEGIN CATCH
    

    and this is an example of the result of the execution of this procedure - a script to drop all https://dba.stackexchange.com/a/264620/22336 in a database(bear in mind there are two different types of https://dba.stackexchange.com/a/250382/22336 😞

    IF 'MY_SERVER' <> @@ServerName THROW 50001, 'Wrong Server!!!',1 
    GO
    use [MY_DATABASE]
    GO
    IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tr_MStran_altertable' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('DATABASE') END  = t.parent_id )           
    EXEC('BEGIN DROP TRIGGER tr_MStran_altertable  ON DATABASE END END') 
    GO
    GO
    use [MY_DATABASE]
    GO
    IF EXISTS (SELECT 'Radhe' FROM sys.triggers t wHERE t.[name] = 'tgr_cola_update_from_profile_change' AND CASE WHEN t.parent_id = 0 THEN t.parent_id ELSE object_id('[dbo].[repl_ApplicationProfile]') END  = t.parent_id )           
    EXEC('BEGIN DROP TRIGGER [dbo].[tgr_cola_update_from_profile_change]  END') 
    GO
    

    UPDATE

    Now I know what happened as I can reproduce the error. I can also avoid it. I have also changed my shared temp table ##Radhe to a temp table created inside the dynamic sql, exactly where I needed it. It is called #Jagannatha_Baladeva.

    My stored procedure called sp_getTriggerDefaccepts a parameter that is a temp table name, that I use to return the scripts generated.

    When I create this temp table and name is with the same name of a temp table inside my procedure, there are problems.

    So because there is a table called #Jagannatha_Baladeva INSIDE my stored procedure if outside I create a table with the same name, I might face strange situations.

    to make it clear, in the outside table I name the first column DB5 sysname not null, but in the table INSIDE my procedure the same column is called DB sysname not null, so when I run the following code:

            IF OBJECT_ID('tempdb.dbo.#Jagannatha_Baladeva') IS NOT NULL 
            DROP TABLE #Jagannatha_Baladeva 
    
        CREATE TABLE #Jagannatha_Baladeva ( 
        DB5 sysname not null,
        parent_name nvarchar(600) not null,
        object_id int not null,
        trigger_name sysname not null,
        is_disabled bit,
        i int not null,
        [trigger_definition] NVARCHAR(MAX) not null,
        primary key clustered (DB5,trigger_name,i))
    
        truncate table #Jagannatha_Baladeva 
    
        
        exec sp_getTriggerDef @dbname      = 'ORCA_Repl_Sub', 
                              @TableName   = null,--'dbo.receivedLog',
                              @Drop_ONly   = 1,
                              @Radhe       = '#Jagannatha_Baladeva'
    

    SELECT *
    FROM #Jagannatha_Baladeva
    order by db,i

    I get my original error: enter image description here

    I changed my column name to DB and my temp table name to #some_other_table_name but I still had the same error UNTIL I opened another session in my https://dba.stackexchange.com/q/184909/22336 and run the code again:

            IF OBJECT_ID('tempdb.dbo.#some_other_table_name') IS NOT NULL 
            DROP TABLE #some_other_table_name
    
        CREATE TABLE #some_other_table_name ( 
        DB sysname not null,
        parent_name nvarchar(600) not null,
        object_id int not null,
        trigger_name sysname not null,
        is_disabled bit,
        i int not null,
        [trigger_definition] NVARCHAR(MAX) not null,
        primary key clustered (DB,trigger_name,i))
    
        truncate table #some_other_table_name
    
        
        exec sp_getTriggerDef @dbname      = 'ORCA_Repl_Sub', 
                              @TableName   = null,--'dbo.receivedLog',
                              @Drop_ONly   = 1,
                              @Radhe       = '#some_other_table_name'
    

    SELECT *
    FROM #some_other_table_name
    order by db,i

    and then it worked fine:

    enter image description here




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2