1

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

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?

5
  • Invalid column name 'DB' have you tried to change the column name?
    – McNets
    May 26 at 7:38
  • @marcello miorelli, can you provide the code for the stored proc sp_getTriggerDef
    – Biju jose
    May 26 at 9:12
  • What happens in that same session if you just directly call exec sp_getTriggerDef ...?
    – J.D.
    May 26 at 11:43
  • 1
    You would get this if there is a table called #Jagannatha_sp_getTriggerDef that is created at an outer scope and does not have column DB and this is present when the statement is compiled May 27 at 16:43
  • @MartinSmith I was just reading about this in your answer May 27 at 17:14

2 Answers 2

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

enter image description here

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.