SQL parameter size limitation in OPENQUERY

Question

SQL parameter size limitation in OPENQUERY

baruchl

SSC Veteran

Points: 234
More actions
December 24, 2002 at 9:43 am

#58436

I have a query with more than 8000 characters that I need to run in OPENQUERY. How can I run it in an OPENQUERY, if, from what I've seen, it takes only the first 8000 chars of the SQL text parameter?
Thanks,
Busi
December 27, 2002 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 1

Since OPENQUERY passes a string through as it does, I don't believe you can get around that part. However, you can do a lot by creating a stored proc on the remote server, and calling it with openquery, or as just a simple remote procedure call, whichever your enviroment likes best. With it being a stored proc, I would think you'd get better performance from it as well. Just keep in mind that you want to transfer as little as possible between the servers if your query joins on the data. Do remote joins remotely, etc...

baruchl SSC Veteran Points: 234 More actions · Answer 2

I can't call a stored procedure since the SQL is dynamically generated. Regarding the remote joins - is there an option to do all the joins remotely using the object 4 name?

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 3

When using the 4 part identifiers, sql query optimizer handles it behind the scenes, so to speak using DTS and passing table results. It's just when using openquery or designing subqueries in procs and so forth you mostly consider that. I've seen people do multiple joins against several stored procs using openquery to pull resultsets, when they could have performed all the joins remotely, and pulled back a single resultset. Its just something to consider.

You know, even with dynamic sql, you could use a proc on the remote machine to take several strings, say a select list, a table list, a where clause, as variables, assemble them on the remote machine, execute it and return the results to the local machine. Seems like it would be pretty ugly, but no worse than building dynamic sql against a remote connection. I don't think you would be able to see enough re-use of the execution plans to make the pre-compile worth it, and I think I would set it to recompile on each execution to prevent sporadic results, but it would get you around the string length limit. It would be about the same thing as just a direct execution against the remote machine, using the 4 part names, but through procs, moving the actual compile of the SQL to the remote machine for the data retrieval, and keeping the local call just accepting the results back.

baruchl SSC Veteran Points: 234 More actions · Answer 4

Thanks for the replies!

One more question, though. when I join 2 tales using the 4 part name, doesn't the SQL server make the joins localy (e.g. - retrieve the data of both tables) and performing the join in the local table? (I tried doing so, and looking at the execution plan, this is what it does). if this is the situation, then this wouldn't be good since we are talking here about millions of records. How can I assure the joins are made remotely?