Support TSQL commands for linked server

Question

Support TSQL commands for linked server

Jack 49290

SSChasing Mays

Points: 650
More actions
March 13, 2017 at 2:24 am

#321333

Is there a concise list of supported syntax in a linked server query:
Works (at least starts to update the result set within 10 seconds):
Select * from openquery ([MAS90_Link], 'SELECT * FROM AP_Vendor')
Does not work (or at least takes several hours, with no display update):
Select * from openquery ([MAS90_Link], 'SELECT TOP(100) * FROM AP_Vendor')
... and canceling a query with the "red box", also never seems to work ... at least not for 90 minutes ...
I am SA on this linked server
TIA for any help

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Jack 49290 - Monday, March 13, 2017 2:24 AM
Is there a concise list of supported syntax in a linked server query:
Works (at least starts to update the result set within 10 seconds):
Select * from openquery ([MAS90_Link], 'SELECT * FROM AP_Vendor')
Does not work (or at least takes several hours, with no display update):
Select * from openquery ([MAS90_Link], 'SELECT TOP(100) * FROM AP_Vendor')
... and canceling a query with the "red box", also never seems to work ... at least not for 90 minutes ...
I am SA on this linked server
TIA for any help

Is your linked server also SQL Server?
OPENQUERY sends a query to the linked server (SQL pass-thru), where it's run like any other query. There's no concept of a restricted subset of commands which work with linked servers.
If you are only reading the remote table then four-part naming is generally easier to write. Use OPENQUERY for updates, inserts & deletes.
Have you tried putting a WHERE clause on the TOP query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jack 49290 SSChasing Mays Points: 650 More actions · Answer 2

Thank you for your response. I'm getting the feeling the linked server is NOT set up correctly. Working through your suggestions I've had the following issues:
1)   runningthe same query twice, causes the system to hang (first run very quick)
2)   SSMS query shows a heck of a lot more records on AP_Vendor than does a simple Crystal Reports report
3)   adding* freezes query - may have figured thisout, goes in front of OPENQUERY
4)   Addingwhere clause - causes system to hang
5)   Use4 part reference – when open catalog to determine database name, system hangs.

Life is like a box of chocolates, and I just got some rotten cherries ....

thanks again

Jack 49290 SSChasing Mays Points: 650 More actions · Answer 3

I'm still trying to implement your suggestions, but ever time I enter ANYTHING wrong OPENQUERY appears to hang... solution?

Example:
Select * from openquery ([MAS90_Link], 'SELECT * FORM AR_Division ')')

Transposing FROM to FORM ....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jack 49290 - Tuesday, March 14, 2017 3:36 PM
Thank you for your response. I'm getting the feeling the linked server is NOT set up correctly. Working through your suggestions I've had the following issues:
1)   runningthe same query twice, causes the system to hang (first run very quick)
2)   SSMS query shows a heck of a lot more records on AP_Vendor than does a simple Crystal Reports report
3)   adding* freezes query - may have figured thisout, goes in front of OPENQUERY
4)   Addingwhere clause - causes system to hang
5)   Use4 part reference â€“ when open catalog to determine database name, system hangs.
Life is like a box of chocolates, and I just got some rotten cherries ....
thanks again

You've posted this in the 2016 section so I assume that your client server is SQL Server. What flavour is the server you've linked to? If it's SQL Server then you can expect it to play fair if it's set up correctly. If it's DB2 then I know from experience that it can be bloody infuriating.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Issues could also depend on which specific driver you are using and how it's configured. There used to be specific ODBC drivers for MAS 90 and some convoluted connection string parameters to use. For MSDASQL, they used to recommend Enable Level 0 I think. Seems they've always had some different requirements around drivers.

Sue

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 6

What is the default database of the linked server user on the linked server side?

Even if you are using the OPENQUERY method, you could try using 3 part names. That may help.

As was suggested above, does it help to omit the OPENQUERY and just select from the 4 part name? What about expanding the linked server connection in the SSMS GUI? That is usually how I test my linked server connection if things are behaving strangely - check if I can actually SEE the table in the GUI.

Another thing to check is to connect to the linked server and see how long the query takes when run directly from there. Might as well check what the expected runtime is outside of the link and then compare that to using the link. The link will likely be a little bit slower, but depending on the amount of data being pushed across and the connection speed, you may not notice much difference. That being said, if you are pushing a GB of data along a 10 Mbps line, you will find it to be a lot slower. If you are getting 64KB (for example) you likely won't notice a difference between an old 10 Mbps NIC and multi-channel fibre at 10 Gbps.
I also wouldn't rely on the timing from different programs as it may be accessing the data differently. So comparing SSMS timing to Crystal Reports is not a valid test case (in my opinion). Do it all in 1 tool and make sure everything works as expected.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.