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:
