Based on Breck's following blog article and the fact that I generally prefer FOR loops over the explicit DECLARE/OPEN CURSOR syntax, I've tried to use both together.
I.e. I tried to run something like
This fails with SQCLCODE -143, "Column 'nCnt' not found".
Just to confirm my understanding (and Breck's expectation):
Am I right that FOR loops cannot be used with this new system procedure as the local server seems unable to infer the number and the name and data type of the result set's columns?
(And therefore the explicit DECLARE/OPEN CURSOR is needed to bind each of the result set's columns to the local variables?)
(Note: Independent of the answer to come, it's a very nice enhancement IMHO!)
asked 21 Feb '11, 16:36
You are correct that the sp_forward_to_remote_server procedure cannot be used with the for statements. The reason for this restriction is identical to the reason why the new procedure cannot be used in the from clause of a select statement. For both the for statement and using a procedure in the from clause of a select statement, the schema of the returning result set must be predefined and the engine uses the procedure result clause to predefine the result set schema of the procedure. Unfortunately, with a procedure like sp_forward_to_remote_server, the returning result set can have any schema. Also, the reason the procedure is defined with a dummy result set at all is to make sure API's like ODBC explicitly open a cursor when a call to the procedure is made in order to properly define the resulting result set once the schema for that result set is determined.
On a completely side note, I'm glad you like the new feature. I should point out that the procedure can also be used to retrieve multiple result sets. For example, executing the following for an SA remote server will return two result sets:
answered 22 Feb '11, 12:44
(Note, this is not as answer nor an explanation but another hint:)
The reason seems that for such procedures, the server seems to expect a result set with a single int column called dummy, as the following call shows:
This seems to be independent of the SQL statement sent to the remote server - and seems to be a consequence of the entries in the system catalog for procedure sp_forward_to_remote_server:
returns the same information.
As a consequence, the following (not very useful) usage of the dummy result column (instead of nCnt) will work in this particular case of a result set with exactly one integer column:
But in the general case FOR loops won't be able to handle the particular result set of remote queries.
answered 22 Feb '11, 08:58