[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

Sql Server View to ODBC Connection

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?

  • 6
  • 3
2 Solutions
Jesus RodriguezIT ManagerCommented:
Did you try to do a single select or execute an store procedure on the SQL 2000 from the sql 2008?? if you can see the tables you will be abble to get any data from a select query. I got the same structure as you on my Corporate Office and works fine..

Remember o the SELECT add the linked server


Also check that you have access to read from the tables, not only from the database.
SCMHCAuthor Commented:
I did a single select similar to
That is when I got 3 messages

OLE DB provider "SQLNCLI10" for linked server "S005869" returned message
     "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "S005869" returned message
     "The stored procedure required to complete this operation could not be found
       on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1
     Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB
     provider "SQLNCLI10" for linked server "S005869".
     The provider supports the interface, but returns a failure code when it is used.

This error indicates a required upgrade to the SQL 2000 Server which I cannot do.

Can I create a view using an ODBC Data Soutce
     Select TOP 10 * FROM ODBC_Source_Name.Table_Name?

Or something like that?

Jesus RodriguezIT ManagerCommented:
Still you will have to has your linled server well configure. Try to Delete the linked server and add run this queries on the database that you want to add the linked server

Try to execute this queries on the server to add the linked server(Remember to put the server names) and later try to do the queries

EXEC master.dbo.sp_addlinkedserver @server = N'YOUR PHARMACY SERVER', @provider=N'SQLNCLI', @datasrc=N'PHARMACY SERVER INSTANCE EX: PHARMACY\INSTANCE'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'YOUR PHARMACY SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'SqlUserToConnect',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'YOUR PHARMACY SERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Jesus RodriguezIT ManagerCommented:
When you clcik on your linked server you can see the Catalogs/Database/Tables??
SCMHCAuthor Commented:
I was able to get a connection to the server in the following way.

I was never able to connect using the linked server as stated above.

So, I connected to the server by doing the following:
   1)  I set up an ODBC connection on the SQL Server
   2)  Set up a linked Server using the ODBC Connection

This worked but I then ran into another issue with Dates when I tried to create
a view using the linked server.  I could run a query and it would work correctly.
When I tryed to make it a view it would error out stating that a difference in length
between compile and run time field size.  This was on a Date/Time field.

The compile length was 8 and the run time was 16.

I could not convert the date in the view on the calling server,
I had to create a view in the Pharmacy server converting the to Character.
Using this view I could access the data I desired.
Hopefully the vendor doesn't determine the creation of a view on there server
voids the maintenance agreement.

Thanks Everyone
SCMHCAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for SCMHC's comment #a38364271

for the following reason:

I have tested this solution and it functions correctly
SCMHCAuthor Commented:
Found a better way:

Just add the following stored procedure to the 32-Bit SQL 2000 server:

USE master

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Set up a standard LinkedServer using 'SQL Server' as the Product

All the date issues I described above also went away.
SCMHCAuthor Commented:
I'm not sure if I did this already but I submitted 2 soultions both work but the second is much better
SCMHCAuthor Commented:
Best way to Link a 64 Bit SQL Server to a 32 Bit SQL Server

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now