有没有一种方法可以防止SQL Server在CREATE/ALTER期间验证存储过程中的SQL

问题描述

我们系统的一个方面要求我们的SQL Server实例通过链接的服务器连接与MySQL Server通信.

One aspect of our system requires our SQL Server instance to talk to a MySQL Server via a linked Server Connection.

MSSQL-> LinkedServer(MSDASQL ODBC Provider)-> MySQL ODBC Connector-> MySQL DB

MSSQL -> LinkedServer(MSDASQL ODBC Provider) -> MySQL ODBC Connector -> MySQL DB

在SPROC中，按以下方式调用链接服务器上的表

The Table on the linked server is called in a SPROC along the lines of

CREATE PROCEDURE DoStuff
AS

SELECT a.ID, a.Name, b.OtherProperty
FROM   MyDatabase..MyTable a
JOIN   LINKSRVR...OtherTable b
ON     a.ID = b.ID

GO

问题是MySQL数据库位于另一个网络中，只能由VPN&除非VPN处于活动状态，否则CREATE或ALTER语句会因以下错误而中断.

The problem is that the MySQL database lives on another network, only accessible by VPN & that the CREATE or ALTER statement breaks with the following error unless the VPN is active.

The OLE DB provider "MSDASQL" for linked server "LINKSRVR" reported an error.
 The provider did not give any information about the error.

Cannot initialize the data source object of OLE DB provider
"MSDASQL" for linked server "LINKSRVR".

无论如何，有没有强迫SQL Server继续使用我告诉它的SQL创建SPROC，而不是尝试验证LinkedServer是否正常运行.

Is there anyway to force SQL Server to just go ahead and create the SPROC with the SQL I tell it and not to try and validate if the LinkedServer is up/down.

推荐答案

您必须通过将其放置在字符串中并将其作为动态SQL执行，才能从编译器中隐藏"有问题的SQL.

You'd have to "hide" the offending SQL from the compiler by placing it in a string and executing it as dynamic SQL.

这篇关于有没有一种方法可以防止SQL Server在CREATE/ALTER期间验证存储过程中的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！