由于语句无法确定元数据 [英] The metadata could not be determined because the statement
问题描述
我们最近将我们的SQL服务器从2008R2升级到2017年 - 下面的代码无效,任何替代解决方案?
我们有我们的业务流程中的100 IF条件具有动态结果集,因此我想我不能轻易使用WITH RESULT SETS子句?
/ *
创建或更改过程t1_test @ t1 int
作为
开始
设置fmtonly off
if @ t1 = 1
select getdate()dt,@@ SERVERNAME s
if @ t1 = 2
选择GETDATE()d2
结束
* /
- 插入临时表
SELECT * INTO #TempTable
FROM OPENROWSET(' SQLNCLI','Server = localhost; Trusted_Connection = yes;',
'设置fmtonly off; EXEC mydb1.dbo.t1_test 2')
drop table #TempTable
GO
错误:
消息11512,级别16,状态1,过程sys.sp_describe_first_result_set,第1行[批量开始行0]由于语句无法确定元数据在过程't1_test'中选择getdate()dt,@@ SERVERNAME s'与过程't1_test'中的'select GETDATE()d2'语句不兼容。
Mahesh
启动Sql 2012,OPENQUERY和OPENROWSET需要提供返回的结果集的结构;因为它已经开始使用sys.sp_describe_first_result_set
阅读以下链接,这将有助于你 -
http://www.sommarskog.se/share_data.html#OPENQUERY
We recently upgraded our SQL server from 2008R2 to 2017 - the below piece of code is not working, any alternative solution?
We have 100 IF condition in our business proc with dynamic result set, hence I guess I cannot use WITH RESULT SETS clause easily?
/* Create or alter procedure t1_test @t1 int as begin set fmtonly off if @t1=1 select getdate() dt, @@SERVERNAME s if @t1=2 select GETDATE() d2 end */ -- Insert into Temp Table SELECT * INTO #TempTable FROM OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'set fmtonly off; EXEC mydb1.dbo.t1_test 2') drop table #TempTable GO
Error:
Msg 11512, Level 16, State 1, Procedure sys.sp_describe_first_result_set, Line 1 [Batch Start Line 0]
The metadata could not be determined because the statement 'select getdate() dt, @@SERVERNAME s' in procedure 't1_test' is not compatible with the statement 'select GETDATE() d2' in procedure 't1_test'.
Mahesh
Starting Sql 2012, OPENQUERY and OPENROWSET needs to provide the structure of the result set returned; as it has started using sys.sp_describe_first_result_set
Read below link, this will help you -
这篇关于由于语句无法确定元数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!