SQL Server 2016中BCP命令无法访问全局临时表问题求助

原执行代码 #

IF OBJECT_ID('tempdb..##TempTrans') IS NOT NULL DROP TABLE ##TempTrans;

SET @XMLFile = '<?xml version="1.0" encoding="UTF-8"?>' + CAST(@XMLDocument AS VARCHAR(MAX));

SELECT REPLACE (CONVERT(VARCHAR(MAX), @XMLFile), 'xmlns=""','') AS x INTO ##TempTrans;

SET @SQLFile = 'bcp "SELECT x FROM ##TempTrans" queryout "'+ @FilePath + '" -c -T'
EXEC xp_cmdshell @SQLFile 

问题现象 #

上述代码在SQL Server 2016执行时会报错：

Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '##TempTrans'.

但相同代码在SQL Server 2019中可正常运行。

原因分析 #

SQL Server 2016中，xp_cmdshell启动的bcp进程会建立独立的数据库会话，全局临时表（##开头）的跨会话可见性存在限制：如果创建临时表的会话操作未完全提交，或版本本身的机制导致临时表无法被新会话即时识别，就会出现找不到对象的错误。SQL Server 2019优化了全局临时表的跨会话可见性逻辑，因此不存在该问题。

解决方案 #

方案1：替换全局临时表为tempdb普通表 #

改用tempdb下的普通表（非临时表），摆脱会话绑定限制，让所有会话都能访问：

IF OBJECT_ID('tempdb.dbo.TempTrans') IS NOT NULL DROP TABLE tempdb.dbo.TempTrans;

SET @XMLFile = '<?xml version="1.0" encoding="UTF-8"?>' + CAST(@XMLDocument AS VARCHAR(MAX));

SELECT REPLACE (CONVERT(VARCHAR(MAX), @XMLFile), 'xmlns=""','') AS x INTO tempdb.dbo.TempTrans;

SET @SQLFile = 'bcp "SELECT x FROM tempdb.dbo.TempTrans" queryout "'+ @FilePath + '" -c -T'
EXEC xp_cmdshell @SQLFile 

DROP TABLE tempdb.dbo.TempTrans;

方案2：直接在bcp查询中生成XML内容，跳过临时表 #

把XML处理逻辑直接整合到bcp的查询语句中，完全避免临时表的使用：

-- 对XML内容中的单引号转义，避免语法错误
DECLARE @EscapedXML VARCHAR(MAX) = REPLACE(CAST(@XMLDocument AS VARCHAR(MAX)), '''', '''''');

SET @SQLFile = 'bcp "SELECT REPLACE(CONVERT(VARCHAR(MAX), ''<?xml version=""1.0"" encoding=""UTF-8""?>'' + CAST(''' + @EscapedXML + ''' AS VARCHAR(MAX))), ''xmlns=""""'','''') AS x" queryout "'+ @FilePath + '" -c -T'
EXEC xp_cmdshell @SQLFile 

方案3：确保全局临时表创建操作提交后再调用bcp #

如果必须使用全局临时表，显式提交创建操作，让其他会话能即时识别：

IF OBJECT_ID('tempdb..##TempTrans') IS NOT NULL DROP TABLE ##TempTrans;

SET @XMLFile = '<?xml version="1.0" encoding="UTF-8"?>' + CAST(@XMLDocument AS VARCHAR(MAX));

-- 显式提交临时表创建操作
BEGIN TRANSACTION;
SELECT REPLACE (CONVERT(VARCHAR(MAX), @XMLFile), 'xmlns=""','') AS x INTO ##TempTrans;
COMMIT TRANSACTION;

SET @SQLFile = 'bcp "SELECT x FROM ##TempTrans" queryout "'+ @FilePath + '" -c -T'
EXEC xp_cmdshell @SQLFile 

DROP TABLE ##TempTrans;

内容的提问来源于stack exchange，提问作者Ali_Raja