Question
Why ZappySys does not support 4Part Linked Server Query while using ZappySys Data Gateway / ODBC Drivers and suggests OPENQUERY approach? For example, Loading JSON / REST API into SQL Server using T-SQL article we refer OPENQUERY approach only. Some users may prefer a simple 4Parts query but ZappySys doesn't support it.
Example of OPENQUERY (Recommended)
SELECT * FROM OPENQUERY(MY_LINKED_SERVER, 'SELECT * FROM MyDb.MySchema.MyTable')
Example of 4Parts (Not Supported)
SELECT * FROM MY_LINKED_SERVER.MyDb.MySchema.MyTable
Answer
In this article, we will clarify some questions/ concerns users may raise about ZappySys Data Gateway and ODBC PowerPack Usage in SQL Server
4Part naming is a very complex issue to implement behind the scene. But just to give you a few points why we didn't implement has few reasons. Hope this clarifies your doubts and gives you some idea why it's very complex to add support of ODBC in Linked Server.
- Linked server created using ODBC Driver adds a lot more complexity so if you used other Linked servers in the past then check to see if they were using ODBC Driver or not... (It might be OLEDB and not ODBC)
- To avoid the ODBC interface we created Data Gateway Service, you can use SqlNative Client Driver (OLEDB) to connect to our Data Gateway, and this way you do not have to use ODBC... But this approach has some limitations.
- Now about 4Part Question - It adds a lot more complexity, especially performance issues, and adds unseen BUGs because SQL Server sends extra metadata queries behind the scene to transform your SQL query to Driver Specific query (basically it rewrites your 4 Part query to a driver-specific query behind the scene and it's not accurate many times - user may not realize all these happening behind the scene in simple SELECT query, sometimes simple is very complex)...
- We suggest OPENQUERY rather than 4Parts - Using OPENQUERY these extra calls are avoided (OPENQUERY also known as Pass-Through Queries because it doesnt rewrite your original query)
- The approach we suggest in Linked server is via ZappySys Data Gateway rather than calling ODBC Driver directly in SQL Server (We suggest this approach to invoke OutOf Process Driver Loading so when Driver Crash it doesn't affect your SQL Server Process.
Using ODBC Driver via OLEDB for ODBC in a Linked server is a Very Bad approach so we have created ZappySys Data gateway - as a Bridge Service)
- Here is an alternate approach you may try but we do not recommend it (It used ODBC DSN directly in Linked server)... This uses a deprecated bridge driver (Microsoft OLEDB Provider for ODBC)... also known as MSDASQL Driver which calls other ODBC Driver and interfaces with Linked server. If you are lucky you may be able to call ZappySys ODBC Driver directly without Gateway. You can Try SQL Server or PostgreSQL ODBC as a Test see you have any luck.
https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration-part-2/
Contact Us
If you have more question(s) feel free to contact us via Live chat or email to support@zappysys.com
Comments
0 comments
Please sign in to leave a comment.