OPENQUERY 与 WIN2K8\SQL2K12 一起使用时抛出错误 [英] OPENQUERY throws error when used with WIN2K8\SQL2K12

问题描述

我正在尝试使用以下 Sql 查询将我的存储过程结果移动到表中

SELECT *进入#tmpTableFROM OPENQUERY(WIN2K8\SQL2K12, 'EXEC vcs_gauge @gauge_name=vs1_bag,@first_rec_time=2014-09-01 09:00:00,@last_rec_time=2014-09-01 10:00:00')

当我执行查询时抛出以下错误.

<块引用>

'\' 附近的语法不正确.

我不想添加链接服务器.如何解决这个问题?

EDIT1

当我做[win2k8\sql2k12]时，先执行下面的命令

<块引用>

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

有新消息来了

<块引用>

链接服务器WIN2K8\SQL2K12"的 OLE DB 提供程序SQLNCLI11"返回消息无法完成延迟准备.".Msg 8180, Level 16, State 1, Line 1无法准备报表.消息 102，级别 15，状态 1，第 1 行'-' 附近的语法不正确.

解决方案

您需要将 DATETIME 值括在单引号中.并且由于您的查询本身是一个字符串，因此这些单引号需要按如下方式加倍/转义(并且您可能还应该将第一个参数的值放在转义单引号中，因为它显然是一个字符串).

您还应该使用 [DatabaseName].[SchemaName]..

完全限定存储过程名称

并且由于 vcs_gauge proc 使用动态 SQL，您需要指定 WITH RESULT SETS 子句.有关此条款的详细信息，请参阅 EXECUTE 的 MSDN 页面.

SELECT *进入#tmpTable从 OPENQUERY([WIN2K8\SQL2K12],N'EXEC [DatabaseName].[SchemaName].vcs_gauge@gauge_name = ''vs1_bag'',@first_rec_time = ''2014-09-01 09:00:00'',@last_rec_time = ''2014-09-01 10:00:00''带有结果集({ column_specification})；');

I'm trying the following Sql query to move my stored procedure result into table

SELECT *
  INTO #tmpTable
FROM OPENQUERY(WIN2K8\SQL2K12, 'EXEC vcs_gauge  @gauge_name=vs1_bag,@first_rec_time=2014-09-01 09:00:00,@last_rec_time=2014-09-01 10:00:00')

following error is thrown, when I execute the query.

Incorrect syntax near '\'.

I don't want to add linked server .How to resolve this issue?

EDIT1

When I do [win2k8\sql2k12], and first execute the following command

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE

A new message comes

OLE DB provider "SQLNCLI11" for linked server "WIN2K8\SQL2K12" returned message "Deferred prepare could not be completed.". Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'.

解决方案

You need to enclose DATETIME values in single quotes. And since your query is in a string itself, those single-quotes need to be doubled / escaped as follows (and you should probably also put the first parameter's value in escaped-single-quotes as it is clearly a string).

You should also fully qualify the stored procedure name with [DatabaseName].[SchemaName]..

And since the vcs_gauge proc uses Dynamic SQL, you need to specify the WITH RESULT SETS clause. For more info on this clause, please see the MSDN page for EXECUTE.

SELECT *
INTO #tmpTable
FROM OPENQUERY([WIN2K8\SQL2K12],
             N'EXEC [DatabaseName].[SchemaName].vcs_gauge
                      @gauge_name = ''vs1_bag'',
                      @first_rec_time = ''2014-09-01 09:00:00'',
                      @last_rec_time = ''2014-09-01 10:00:00''
               WITH RESULT SETS ( { column_specification} );
             ');

这篇关于OPENQUERY 与 WIN2K8\SQL2K12 一起使用时抛出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！