First of all, stop using SET FMTONLY OFF in your distributed queries. It has the effect that the query is executed twice. And for the cases where it works on SQL 2008, it will not work when both servers are running SQL 2012 or SQL 2014 or higher. (In this particular case, it serves no purpose at all.)
Next, I don't exactly follow why you are using OPENROWSET in the first place. As you saiying that you are on ServerA, and you make a call to ServerB and on ServerB you are running a query back to ServerA?
I don't know why you are doing this, but can't you collect the data on serverA and pass it in an XML document to serverB? (The actual parameter type would have to be nvarchar(MAX).)
The error message you get indicates that serverB is not able to make a trusted connection to serverA, which may be a double-hop issue. Which is a Windows problem, not an SQL Server problem. But if you set up a regular linked server, you can add login-mapping, if that is feasible, without storing username and password in the code.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
No comments:
Post a Comment