Post reply

Link Server Problem

  • September 26, 2007 at 9:57 am

    #118727

    Hi everyone,

    I created a link server from Server1 to Server2 to connect to a FoxPro database by using the sp_addlinkedserver stored procedure.

    Server1 (Windows Server 2003 + SQL Server 2000)

    Server2 (Windows Server 2000 + SQL Server 2000 + FoxPro 9)

    I can run querries locally in Server1 and they retrieve results with no problem calling the link server.

    However, when I try to run querries from a network machine by logining in SQL2000Server1 I have a problem.

    Error 7302: Could not create an instance of OLEDB provider 'vfpoledb'

    OLE DB error trace [Non-interface error: CoCreate of DSO for vfpoledb

    returned 0x80040154]

    Everyone can help me?

    Thanks

  • September 26, 2007 at 11:00 am

    #736457

    sounds like an MDAC issue. make sure you have the latest MDAC installed on the client.

  • September 26, 2007 at 11:16 am

    #736462

    Hi Janet,

    I have the latest (MDAC) version.

    However, (MDAC) 2.8 release does not include Microsoft Jet, the Microsoft Jet OLE DB Provider or ODBC driver, the Desktop Database ODBC Drivers, or the Visual FoxPro ODBC Driver.

    I have installed vfpoledb.exe version8.2.

    When I try to send a query to link server using administrator windows login, I can get the results. The problem if I try to login with a user login I cannot access.

    Do you have any idea how to set permission for OLEDB driver vfpoledb.?

    Thanks

  • September 26, 2007 at 2:03 pm

    #736526

    Finally,

    I have solved my problem.

    That was a WIN Server 2K3 security issue. This corresponds to MSDAINITIALIZE DCOM component. The The Solution is:

    Change permission settings for the component.

    {2206CDB0-19C1-11D1-89E0-00C04FD7A829}.

    Thanks everyone.

  • March 26, 2009 at 9:20 am

    #966768

    Can you please explain in details. I am having similar issue but can't find the solution.

  • May 21, 2010 at 8:40 am

    #1170880

    I think he's refering to this link::-)

    ==================

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/05258d18-d7e5-411d-b591-d98ba6674448/

    To resolve this I believe all you need to do is add the users who will be accessing the linked server (remotely or locally) to the "DB2USERS" (local group on the MSSQL server).

    The db2cli.ini file (and others) in the directory where DB2 components are installed is restricted based on membership in "DB2USERS" and "DB2ADMNS" groups.

    HTH.-Steve

    ==================

    ==================

    Personally, I'm still stuck.:crazy:

    Here's the error I get.

    TITLE: Microsoft SQL Server Management Studio Express

    ------------------------------

    "The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

    ------------------------------

    Cannot create an instance of OLE DB provider "Ifxoledbc" for linked server "INFORMIX". (Microsoft SQL Server, Error: 7302)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

    ============

    I clicked the "For Help" link and got: We're sorry

    There is no additional information about this issue in the Error and Event Log Messages or Knowledge Base databases at this t

    LOL

    Here are some other links that have at least helped me on the way.

    http://publib.boulder.ibm.com/infocente ... ledb20.htm

    Basics:

    http://www-01.ibm.com/support/docview.w ... wg21195578

    Helpful Link:

    http://blog.biztalk-info.com/archive/20 ... ormix.aspx

    MDAC download (installed in QA-D 5/21/2010):

    http://www.microsoft.com/downloads/deta ... layLang=en

    http://www.sqlservercentral.com/Forums/ ... 146-1.aspx

    Other hints:

    http://media.datadirect.com/download/do ... setifx.htm

    http://social.msdn.microsoft.com/Forums ... ba6674448/

    Any suggestions or helpful hints will earn you a beer if you are in the Littleton mass area. 😉

    Stephanie:w00t:

  • May 21, 2010 at 10:41 am

    #1170962

    Yeah Me

    OK it worked. Not sure exactly what but here are two things that are important.

    1) The Product Name. When You look into "MS SQL Server Mng Studio Express" under "Server Objects" -> "Linked Servers" -> Providers, you will see the provider Ifxoledbc.

    First be sure you use a capital 'i'.

    2) When you view the properties for this provider I enabled the following

    "Dynamic parameter"

    "Allow inprocess"

    and

    "Supports 'Like' operator"

    3) I did a single quote in the "Provider String" in the Linked Server Properties.

    ie:

    username='informix';password='mypassword'

    4) In Security in Linked Server Properties, I used:

    "Be made using this security context" and entered the same username and password as in step 3.

    So I clicked OK and did not receive any error messages.

    WaHoo.

    Hope this helps. I'm going for a beer now. I earned it.

    Steph

Viewing 7 posts - 1 through 6 (of 6 total)

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