0

I'm doing a proof of concept to assess whether we can upgrade some our servers piecemeal to SQL Server 2022.

Unfortunately we still have a SQL Server 2008 which holds an old version of Microsoft Dynamics which we cannot upgrade at this time :(

When trying to connect between SQL Server 2022, we notice that the query won't work as the SNAC has been removed in 2022.

I'm wondering whether there is another way to perform linked server requests across multiple databases and instances.

Has anyone managed to get it to work?

We think SQL Server 2019 --> SQL Server 2008 will work, as would SQL Server 2022 --> SQL Server 2012, but hoping someone has found a way of working around the driver problem, even if it's in the short term.

Anybody have any ideas?

10
  • 1
    What's wrong with what replaced SQLNCLI, MSOLEDBSQL?
    – Thom A
    Commented Jan 31 at 10:30
  • 1
    @ThomA officially MSOLEDBSQL only supports connections back to SQL Server 2012. Ref: Support policies for OLE DB Driver for SQL Server (It might still connect to 2008, it's just not tested nor supported.) Commented Jan 31 at 10:53
  • 2
    Tough spot to be in: SQL Server 2008 and R2 ran out of extended support back in 2019. Commented Jan 31 at 10:57
  • You can "probably" reroute your linked server thing through sql server 2019, ie. create linked server between 2008 and 2019, and between 2019 and 2022, and then select "through" it with OPENQUERY, something like: select * from openquery(srv2019,'select * from srv2008.db.schema.table') Commented Jan 31 at 11:02
  • True, @AlwaysLearning, though if it does work, you're probably in a better position than using a driver that's been unsupported for ~10 years.
    – Thom A
    Commented Jan 31 at 11:22

1 Answer 1

2

I happen to have access to similar stack due to... reasons, and there doesn't seem to be any problem of accessing the 2008 (nor 2005) server:

EXEC    sys.sp_addlinkedserver @server = 'sigge,42008'
go
EXEC    sys.sp_addlinkedserver @server = 'sigge,42005'
go

SELECT  @@version AS localVersion, oq.*
FROM    OPENQUERY([sigge,42008], 'select top 1 @@version AS version, * from master.sys.objects so') oq
union all
SELECT  @@version AS localVersion, oq.*
FROM    OPENQUERY([sigge,42005], 'select top 1 @@version AS version, * from master.sys.objects so') oq

Outputs:

localVersion version
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) - redacted Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) - redacted
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) - redacted Microsoft SQL Server 2005 - 9.00.5000.00 (X64)

Although to be fair, perhaps SNAC got installed by some previous version

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.