Is it possible to query an MSSQL database and an ODBC connection in 1 transaction.

Question

Is it possible to query an MSSQL database and an ODBC connection in 1 transaction.

roy.tollison

SSCommitted

Points: 1930
More actions
January 23, 2017 at 11:07 am

#339612

i have need of querying an MSSQL table and an ODBC table. Client has a hybrid of their data in MSSQL and ODBC and i am attempting to come up with a way (any) to use both connections in one query.

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

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

roy.tollison - Monday, January 23, 2017 11:07 AM
i have need of querying an MSSQL table and an ODBC table. Client has a hybrid of their data in MSSQL and ODBC and i am attempting to come up with a way (any) to use both connections in one query.

Yes, If you create the ODBC DSN on the SQL Server and create a linked server and configure it RPC True, RPC Out True and Enable Promotion to Distributed Transaction True.

roy.tollison SSCommitted Points: 1930 More actions · Answer 2

Joe Torre - Monday, January 23, 2017 11:46 AM
roy.tollison - Monday, January 23, 2017 11:07 AM
i have need of querying an MSSQL table and an ODBC table. Client has a hybrid of their data in MSSQL and ODBC and i am attempting to come up with a way (any) to use both connections in one query.
Yes, If you create the ODBC DSN on the SQL Server and create a linked server and configure it RPC True, RPC Out True and Enable Promotion to Distributed Transaction True.

Does this mean i will have to use openquery instead of direct query?

Joe Torre SSChampion Points: 10248 More actions · Answer 3

roy.tollison - Monday, January 23, 2017 11:52 AM
Joe Torre - Monday, January 23, 2017 11:46 AM
roy.tollison - Monday, January 23, 2017 11:07 AM
i have need of querying an MSSQL table and an ODBC table. Client has a hybrid of their data in MSSQL and ODBC and i am attempting to come up with a way (any) to use both connections in one query.
Yes, If you create the ODBC DSN on the SQL Server and create a linked server and configure it RPC True, RPC Out True and Enable Promotion to Distributed Transaction True.
Does this mean i will have to use openquery instead of direct query?

No

roy.tollison SSCommitted Points: 1930 More actions · Answer 4

Joe Torre - Monday, January 23, 2017 12:06 PM
roy.tollison - Monday, January 23, 2017 11:52 AM
Joe Torre - Monday, January 23, 2017 11:46 AM
roy.tollison - Monday, January 23, 2017 11:07 AM
i have need of querying an MSSQL table and an ODBC table. Client has a hybrid of their data in MSSQL and ODBC and i am attempting to come up with a way (any) to use both connections in one query.
Yes, If you create the ODBC DSN on the SQL Server and create a linked server and configure it RPC True, RPC Out True and Enable Promotion to Distributed Transaction True.
Does this mean i will have to use openquery instead of direct query?
No

thanks got it working triple dot instead of double dot