stored procedure runs fine in all sessions but one

[guilherme]

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

it comes out with the warning

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

all good.

but then on this particular session:

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

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?

[briley]

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:

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: