locked
linked server to Microsoft Exchange 2007 database RRS feed

  • Question

  • Hello,

    From my SQL Server 2008 database I want to create and query a linked server to a Microsoft Exhange 2007 database. It is on the same host. I have seen this question before in this forum, but no working answer. Does someone in the mean time have found a solution ?

    Thanks in advance,

    André.

     

    Wednesday, December 14, 2011 6:22 AM

All replies

  • Hi Andre_Analytics,

    Based on your description, do you want to create and query data from Microsoft Exchange 2007 database in SQL Server 2008 by linked server.

    If so, Microsoft SQL Server distributed queries can be used to query data from the Exchange Web Store through this OLE DB Provider and can be joined to tables in SQL Server. The Exchange Web Store should be located in the same computer as SQL Server.

    The OLE DB Provider for Exchange is available as part of Microsoft Exchange:
    1. To create a linked server against an Exchange Web Store
    Use 'exoledb.DataSource.1' as the provider_name argument, and the URL corresponding to the root folder of the Web Store as the data_source argument of the sp_addlinkedserver system stored procedure:

    2. To access data in the Exchange Web Store from SQL Server, through a linked server as previously established:
    Create views that retrieve the required properties as columns from the Web Store folder of interest. The view definition converts string columns to nvarchar so that they can be filtered through conditions in the WHERE clause.

    For more information and code examples, please refer to this article.

    If you have any question related to the Exchange, please link to the Microsoft Exchange forum for further help.

    Regards, Amber zhang
    • Edited by amber zhang Thursday, December 15, 2011 3:27 AM
    Thursday, December 15, 2011 3:26 AM
  • Hello Amber,

    Thank you for your repsonse.

    I have tried the following statement:

    exec sp_addlinkedserver
    'Exchange',
    'Exchange OLE DB Provider',
    'exoledb.Datasource.1',
    'file:\\.\Program files\Microsoft\Exchange server\Mailbox\First Storage Group'

    This command is accepted, but when I try to open/click Catalogs -> default -> tables, I get the error:
    'Cannot create an instance of OLE DB provider 'exoledb.Datasource.1 for linked server (error 7302)'

    Is my sp_addlinkedserver statement correct ?
    If I would query it, instead of clicking it open, I have no idea what exact sql statement I should use (schema, table, attributes).

    I hope you can help me further.

    Thanks in advance,
    André.

     

    Thursday, December 15, 2011 8:13 PM
  • Hi Andre_Analytics,

    Regarding to the error message, which indicate that SQL Server is not able to initialize the OLEDB provider that is specified in the definition of the linked server. That occurs if any of the following conditions is true for the system that is running SQL Server where the linked server is defined:

    • The OLEDB provider is not yet installed.
    • The installed OLEDB provider's bitness does not match the bitness of SQL Server. For example, you may have installed 32 bit version of the provider but SQL Server is running in 64 bit mode.
    • The OLEDB provider is not registered.
    Note: When you create linked server using sp_addlinkedserver, SQL Server does not report any error messages even when one or more of the above conditions is true.

    So please verify the Exchange Ole DB Driver existed on the SQL Server machine.  Assuming the 2 services are not on the same machine.  If not, you may need to install the Exchange SDK or management tools on the SQL Server to get the OleDB driver installed. 

    The solution is try to install the OLEDB provider using the software from the corresponding vendor. Ensure that you are also installing the provider that corresponds to the platform [x86 or x64] of SQL Server.

    To get a list of installed OLEDB providers, use the SQL Server Management Studio and navigate to the "Server Objects" node and expand the "Linked Server" node. You could also use the catalog view sys.servers to find the list of all defined linked servers and their associated OLEDB providers.

    More information, please refer to this KB: http://support.microsoft.com/kb/2555855


    Regards, Amber zhang
    Friday, December 16, 2011 2:40 AM
  • Hello Amber,

    I am running Exchange on the same server as SQL Server, both 64 bit.
    At linked servers -> Providers I see: Exoledb.Dtasource
    Is that the right one ?
    Can you give an example of a query I can try ?

    Thanks in advance,
    André.

     

    Saturday, December 17, 2011 8:34 PM
  • Hi Andre_Analytics,

    >>At linked servers -> Providers I see: Exoledb.Dtasource
    Is that the right one ?

    The Microsoft OLE DB Provider is used for Exchange exposes data stored in a Microsoft Exchange 2000 Web Store in tabular form. Microsoft SQL Server distributed queries can be used to query data from the Exchange Web Store through this OLE DB Provider and can be joined to tables in SQL Server.

    The 'exoledb.DataSource.1' is as the provider_name argument used to create a linked server against an Exchange Web store. The code example I have provided above.

    >>Can you give an example of a query I can try ?

    To access data in the Exchange Web Store from SQL Server, through a linked server is established.
    You can try to create views that retrieve the required properties as columns from the Web Store folder of interest. And then the views can be queried and joined to local SQL Server tables like regular tables.

    The code example and steps please refer to To access data in the Exchange Web Store from SQL Server, through a linked server as previously established section.

    More information, please refer to OLE DB Provider for Exchange: http://msdn.microsoft.com/en-us/library/ms191461(v=SQL.100).aspx

     


    Regards, Amber zhang
    • Proposed as answer by amber zhang Thursday, December 22, 2011 2:41 AM
    • Unproposed as answer by Andre_Analytics Thursday, December 22, 2011 9:59 AM
    Tuesday, December 20, 2011 2:12 AM
  • Unfortunately, it doesn't work.

    Regards, André.

     

    Thursday, December 22, 2011 9:45 AM
  • Hi Andre_Analytics,

    >> Unfortunately, it doesn't work.

    Did you get any error message or error code? If so, please post it for further troubleshooting.

    Meanwhile did you follow the steps I mentioned above? Please verify the Exchange Ole DB Driver existed on the SQL Server machine and pay attention to the article OLE DB Provider for Exchange: http://msdn.microsoft.com/en-us/library/ms191461(v=SQL.100).aspx

    Regards, Amber zhang
    Friday, December 23, 2011 8:55 AM
  • I think the oledb driver exists, because at linked servers I see:
    Providers I see: Exoledb.Dtasource

    When I do a select like given in the article:

    SELECT convert(nvarchar(30),"urn:schemas:contacts:sn") LastName,
    Convert(nvarchar(30),"urn:schemas:contacts:givenname")
    FirstName,
       Convert(nvarchar(30), "urn:schemas:contacts:o") Company,
       Convert(nvarchar(50), "urn:schemas:contacts:email1") Email,
       "urn:schemas:contacts:bday" BirthDay
    FROM OpenQuery(Exchange,
      'SELECT "urn:schemas:contact:sn",
              "urn:schema:contacts:givenname",
              "urn:schemas:contacts:o",
           "urn:schemas:contacts:email1",
           "urn:schemas:contacts:bday"
       FROM ''.\contacts''')

    I get the message:

    Cannot create an instance of OLE DB provider 'exoledb.Datasource.1' for linked server 'Exchange'.

    Regards, André.

     

    Wednesday, December 28, 2011 8:02 PM
  • Hi Andre_Analytics,

    Could you go to c:\program files\common Files\System\Ole DB\ folder of your server, and please try to run regsvr32 sqloledb.dll to check the oledb provider?

    Regarding to the query you provided, seems you tried to access data in the Exchange Web Store from SQL Server. But you need to establish a linked server success first.

    To create a linked server against an Exchange Web Store, there are some authentication issue as below:
    For Windows authenticated logins, there are no login mappings required. The logins are impersonated by SQL Server when it connects to the OLE DB Provider for Exchange.
    For SQL Server authenticated logins, set up login mappings by supplying the user name and password, as required.

    Meanwhile, please verify you have the full control of the 'file:\\.\Program files\Microsoft\Exchange server\Mailbox\First Storage Group' folder you mentioned above.


    Regards, Amber zhang
    Thursday, December 29, 2011 7:56 AM
  • Hello Amber,

     

    When I run the regsvr32 command I get: DllRegisterServer in sqloledb.dll succeeded
    Then I made the linked server again, but when running the query I get the same error.

    I am running Exchage and SQL Server on the same machine, so I assume Windows authentication is working.

    You say I created a linked server to a web store. My goal is to query emails in the Exchange database. Is a linked server to the web store then in fact then the right thing to do ? (I do not know much of Echange)

    Regards, André.

     

    Friday, December 30, 2011 9:51 PM
  • Hi Andre_Analytics,

    >>My goal is to query emails in the Exchange database. Is a linked server to the web store then in fact then the right thing to do ? (I do not know much of Echange)

    Regarding to your requirement seems to be exchange issue. I think it’s better for you to link to Exchange Server TechCenter forums, which you can get better help there. 

    For information about the SQL language supported by the Exchange OLE DB provider, see the Microsoft Exchange 2007 documentation.

    If you have any other question related to SQL Server, please let me know.


    Regards, Amber zhang
    • Edited by amber zhang Tuesday, January 3, 2012 6:39 AM
    Tuesday, January 3, 2012 3:22 AM