Ran into this today at work. Building up a string that is the SQL statement to pass to openquery. Since the sql statement is calling a stored proc, and has params in it, it needs quotes itself to surround string based params.

When calling the SP directly on the server, it works fine. When called through OPENQUERY, it fails. Turns out when using OpenQuery, SQL makes an extra call of "SET FMTONLY ON" to get metadata info about the resultset. This FMTONLY setting blows up the execution of the sql statement.

Workaround: use 4-part name to call the SP, not through OpenQuery. Issue: have to turn on "RPC OUT" on the linked server setup to get the remote exec SP to work.

PRB: T-SQL Function OpenQuery Fails to Execute on Linked Servers


Posted 10:47 AM    |    0 comments    |    Permalink