主页 / dba / 问题 / 312594
Accepted
Marcello Miorelli
Asked: 2022-05-26 23:13:36 +0800 CST

存储过程在所有会话中运行良好，但一个

  • 772

有些程序可以手动运行但不能在作业中运行，或者在从应用程序运行时失败，或者在 SSIS SQL 任务中无法运行。

我的工作在所有会话中，但一个。

这是我正在运行的代码 - 它调用一个存储过程，该过程获取触发器定义并将其保存在临时表中。

工作正常，这是我自动化工作的一部分。

            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

我在没有触发器的表上运行它 - 只是为了让它尽可能简单

在此处输入图像描述

它带有警告

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

都好。

但是在这个特定的会话中：

在此处输入图像描述

到目前为止，我可以发现本次会议没有什么不同。

SELECT *
  FROM sys.dm_exec_sessions 
  where login_name = 'my_company\my_user'
    and  session_id = @@SPID

在此处输入图像描述

第二个是失败的地方。所有其他它工作正常。

我该怎么做才能找出不同之处？甚至更好的是，更改程序以便尽管存在差异，但它仍然可以工作？

sql-server stored-procedures

2 个回答

  1. Best Answer

    我找到了解决这个问题的方法。我找不到也没有花时间去理解为什么只有一个会话有这个问题。

    我的这个过程是用动态 sql打包的，它在内部使用了一个名为#Radhe.

    我正在动态 sql#Radhe中创建临时表。

    我决定做一些不同的事情：#Radhe我将有一个名为的临时表##Radhe，它是在动态 sql之外创建的，但在其中以相同的方式使用。

    在这种情况下，这对我有用。

    这是存储过程中代码的部分视图：

            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])

    这是动态 sql 代码的另一个一瞥：

                         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'''') '')

    执行几个动态 sql 块并将结果写入表 - 如果提供了参数，或者只是一个选择。

    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

    这是执行此过程的结果示例 - 删除数据库中所有触发器的脚本（请记住，有两种不同类型的触发器）：

    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

    更新

    现在我知道发生了什么，因为我可以重现错误。我也可以避免。我还将共享临时表更改##Radhe为在动态 sql 中创建的临时表，这正是我需要的地方。它被称为#Jagannatha_Baladeva。

    我调用的存储过程sp_getTriggerDef接受一个临时表名参数，我用它来返回生成的脚本。

    当我创建这个临时表并且名称与我的过程中的一个临时表的名称相同时，就会出现问题。

    因此，因为有一个名为#Jagannatha_BaladevaINSIDE my stored procedure 的表，如果在外部创建同名表，我可能会遇到奇怪的情况。

    为了清楚起见，在外部表中，我命名了第一列DB5 sysname not null,，但在表 INSIDE 我的过程中，调用了相同的列，DB sysname not null,因此当我运行以下代码时：

            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

    我得到了我原来的错误： 在此处输入图像描述

    我将列名更改为DB，临时表名更改为，#some_other_table_name但我仍然遇到相同的错误，直到我在SSMS中打开另一个会话并再次运行代码：

            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

    然后它工作正常：

    在此处输入图像描述

    • 1

  2. 我希望我有更多的代表，所以我可以对Marcello 的回答发表评论......

    但我建议不要使用全局临时表，因为它们通常存在安全风险，因为任何会话都可以访问它的内容。

    只需将其设为您自己管理的实际表格...

    • 0

相关问题