Sql Server View to ODBC Connection

Posted on 2012-08-29
Last Modified: 2012-09-09
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?

Question by:SCMHC
    LVL 13

    Expert Comment

    by:Jesus Rodriguez
    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.

    Author Comment

    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?

    LVL 13

    Expert Comment

    by:Jesus Rodriguez
    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'
    LVL 13

    Expert Comment

    by:Jesus Rodriguez
    When you clcik on your linked server you can see the Catalogs/Database/Tables??

    Assisted Solution

    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

    Author Comment

    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

    Accepted Solution

    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.

    Author Comment

    I'm not sure if I did this already but I submitted 2 soultions both work but the second is much better

    Author Closing Comment

    Best way to Link a 64 Bit SQL Server to a 32 Bit SQL Server

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Title # Comments Views Activity
    How can I run a function on a database column? 5 27
    SQL User Mappings error 3 32
    SQL Server 2008 9 30
    Delete from table 6 26
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now