stored procedure runs fine in all sessions but one

All we need is an easy explanation of the problem, so here it is.

There are procedures that run fine manually but not in a job, or fails when run from an application, or not work in SSIS SQL task

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

stored procedure runs fine in all sessions but one

it comes out with the warning

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

all good.

but then on this particular session:

stored procedure runs fine in all sessions but one

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

stored procedure runs fine in all sessions but one

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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 dynamic sql, and it uses internally a temporary table called #Radhe.

I was creating the temp table #Radhe inside of the dynamic sql.

I decided to do something different: instead of #Radhe I would have a temp table called ##Radhe that is created outside of the dynamic sql 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 triggers in a database(bear in mind there are two different types of triggers):

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:
stored procedure runs fine in all sessions but one

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 SSMS 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:

stored procedure runs fine in all sessions but one

Method 2

I wish I had more rep so I could just leave a comment on Marcello’s answer

But I would recommend against the global temp table as they’re often a security risk since any session can access it’s contents.

Just make it an actual table that you manage yourself…

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply