6

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

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

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".

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.

Eoin Campbell
  • 43,500
  • 17
  • 101
  • 157

1 Answers1

6

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

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 1
    Yeah this is how I'm currently getting round it but it's F-UGLY as hell. the real version actually calls the offending SQL as part of the query in a cursor, so then the Dynamic sql becomes the entire, DECLARE Vars, Declare Cursor, Open & Fetch & Deallocate which is about ~100 lines of sql. not exactly pretty. I was hoping there was some "SET NO_SQL_COMPILER_VALIDATION ON" type option :-\ – Eoin Campbell Sep 26 '11 at 16:44
  • Ok... no more feedback so the dirty dynamic sql way it is. Cheers for the info Joe. – Eoin Campbell Sep 27 '11 at 08:29