I have a Pharmacy System supported by the Vendor.
It uses SQL 2000 SP4 32 Bit.
I have Read Only Access Rights to the SQL 2000 SP4 Database.
The vendor refuses to make any changes to the SQL Database Setup.
I want to Create a View in my SQL Server 2008 64 Bit Medical Records System to access and display the Pharmacy Data for our clients.
I setup a linked server connection between the two servers.
I can see the Linked Pharmacy Server, Database and Tables.
There is a perminent connection to the Pharmacy SQL 2000 SP4 Server.
When I try to do a SELECT on a table in the Pharmacy Server SQL 2000 SP4 32 Bit Database from The Medical Records SQL 2008 SP3 64 Bit Server, I get a MSG 7311. After researching, this MSG indicates that I need to upgrade the SQL 2000 SP4 32 BIT System's stored procedures. Well, as I stated above, the vendor refuses to do that for a single installation.
I can access the database using Microsoft Access using an ODBC Connection.
Is there any way to create an ODBC connection on the 2008 SQL Server and then use that ODBC Connection to access the Pharmacy System's SQL 2000 SP4 database in a view on the SQL 2008 Server SP3 Medical Records system?