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