locked
SharePoint Designer 2007 and Oracle 10g - connection string problems RRS feed

  • Question

  • I am trying to connect to a Oracle 10g database on SharePoint Designer 2007 using the "Connect to a database..." option.  I use a custom connection string since the tsnnames.ora is not configured at the server. I set the provider name to "Microsoft .NET Framework Data Provider for Oracle" and connection string as follows:

    Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

    I get an error, "Server Error: An error occurred while retreiving the list of databases from (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=.... The server for the datasource returned a non-specific error when trying to execute your query. Check the format and content of your query and try again. If the problem persists, contact the server adminstrator".

    Has anyone been able to resolve this problem. Any help would be greatly appreciated. Thanks.

    • Edited by Mike Walsh FIN Friday, April 3, 2009 3:40 AM connection string problems added to title
    • Moved by Mike Walsh FIN Friday, April 3, 2009 3:41 AM SPD q
    Thursday, April 2, 2009 9:32 PM

Answers

  • Hi,

     

    This is a known issue that we are unable to connect to an Oracle [10g] database using SPD data source catalog. I am sorry that there is no hotfix currently.

     

    The workaround is to insert a Grid View and using the ASP.NET data binding UI successfully connected to the database. To use the data grid view inside a web part , a copy / paste of the code is required as the connection String will be saved with the control (not in the web.config)

     

    Hope it can help you.


    Xue-Mei Chang
    Tuesday, April 7, 2009 6:33 AM
    Moderator
  • I finally broke down and called support.  I asked for SharePoint Designer support team (not SharePoint Support!!), which is only about 3 guys I think, so I got great support.  He stepped me through it right away.

    For my configuration, I did need the Oracle client installed on my SharePoint front end server, not sure whether you would always need that.

    Then it was never getting to the point of creating the XML datasource file that goes in the _catalogs\fpdatasources folder that they were referring to above.  So the support person showed me how to create one using Visual Studio, create sample project, connect to datasource, etc...that connection worked, just not SharePoint Designer.

    So he had me create a SQL datasource connection in SharePoint Designer first so that I had an XML file in datasources to edit.

    Then he had me copy the XML file contents from my Visual Studio file over to my SQL XML file, and it then worked.

    The XML file should look something like this:

    <?xml version="1.0" encoding="utf-8" ?>

    <udc:DataSource xmlns:udc="http://schemas.microsoft.com/data/udc" MajorVersion="2" MinorVersion="0">

                    <udc:Name>Oracle11</udc:Name>

                    <udc:ConnectionInfo>

                                    <DataSourceControl><![CDATA[

                                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=YourOracleDB;Persist Security Info=True;User ID=YourUserID;Password=YourPassword;Unicode=True" ProviderName="System.Data.OracleClient" SelectCommand=" select .......ENTER YOUR CUSTOM QUERY STRING HERE"></asp:SqlDataSource>]]></DataSourceControl>

                    </udc:ConnectionInfo>

                    <udc:Type MajorVersion="1" MinorVersion="0" Type="Sql"/>

    </udc:DataSource>

    You can skip the step of creating the XML file through Visual Studio if you already know your connection works.  You can just copy the XML above into your XML the SQL Connection made and edit with your Oracle DB and Query unique information.  I have created several Oracle connections since just by copying the above XML in as I stated.


    MSI-Linda SharePoint Architect & Designer
    • Proposed as answer by Barkolounger2 Friday, January 8, 2010 3:59 AM
    • Marked as answer by Mike Walsh FIN Saturday, January 15, 2011 6:19 AM
    Friday, August 28, 2009 4:31 PM

All replies

  • The logs in the 12 hive should give you a little more detail and may lead to an answer.
    --Paul Galvin of EMC Consulting, New York area Microsoft MVP - SharePoint Blogging @ http://feeds.feedburner.com/PaulGalvinsSharepointSpace Twitter @ http://www.twitter.com/pagalvin
    Friday, April 3, 2009 12:23 AM
  • Hi Paul,

    Thanks for the response. I do not have access to the server logs and cannot change any config files (to show better errors). I access SharePoint over the intranet and am able to update the site using the SharePoint Designer.

    Regards.

    Friday, April 3, 2009 2:01 AM
  • SPD questions go to the Design/Cust forum. 

    Moving there (and amending the subject)

    WSS FAQ sites: WSS 2.0: http://wssv2faq.mindsharp.com WSS 3.0 and MOSS 2007: http://wssv3faq.mindsharp.com
    Total list of WSS 3.0 and MOSS 2007 Books (including foreign language titles) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx
    Friday, April 3, 2009 3:40 AM
  • Hi,

     

    This is a known issue that we are unable to connect to an Oracle [10g] database using SPD data source catalog. I am sorry that there is no hotfix currently.

     

    The workaround is to insert a Grid View and using the ASP.NET data binding UI successfully connected to the database. To use the data grid view inside a web part , a copy / paste of the code is required as the connection String will be saved with the control (not in the web.config)

     

    Hope it can help you.


    Xue-Mei Chang
    Tuesday, April 7, 2009 6:33 AM
    Moderator
  • Thanks Xue-Mei. I tried adding gridview, sqldatasource is able to connect to the database in design mode. At runtime, it says, "An unexpected error has occurred". I checked with my server folks and found that oracle client is missing on the sharepoint server. I have a feeling that is causing the error (sqldatasource is interacting with my computer's oracle client instead of the server in design mode). They should atleast install the oracle db instant client (oci) on the sharepoint server for me to get this working.
    Wednesday, April 8, 2009 6:01 PM
  • Do you know if there is a fix for this yet, or an associated KB article to follow, as I have a cutomer that needs to connect to 10g version?

    Also, does the Oracle Client need to be installed on the SharePoint Services front end server for connections to Oracle to work? 

    MSI-Linda SharePoint Architect & Designer
    Thursday, June 25, 2009 3:16 PM
  • Read the date of that reply from Xue-Mei Chang.

    It's from just over two months ago.

    I.e. chances are there extremely high that there is no change.
    WSS FAQ sites: http://wssv2faq.mindsharp.com and http://wssv3faq.mindsharp.com
    Total list of WSS 3.0 / MOSS 2007 Books (including foreign language) http://wssv3faq.mindsharp.com/Lists/v3%20WSS%20FAQ/V%20Books.aspx
    Thursday, June 25, 2009 4:03 PM
  • Yes I saw that.  My question is for my customer that wants to know an answer to both questions. Thus my two questions still are valid.
    MSI-Linda SharePoint Architect & Designer
    Thursday, June 25, 2009 4:14 PM
  • I am trying to following the recommendation with the GridView but something is not making sense.  Can someone please elaborate?  I think it ultimately uses an SQL connector, which I am having trouble configuring - cannot set server name and data source independently.

    First I tried with an ASP.NET SQL data source, which could not find an Oracle client until I installed one with the Oracle Universal Installer.

    Now when I test the connection I get => ORA-12504: TNS:listener was not given SERVICE_NAME in CONNECT_DATA.

    It seems to me that the Data Source parameter should be the Oracle SID; but whatever I put in the Data Source gets plopped into the Server Name.  I am unable to set the server name and data source independently.  And I cannot edit the connect string.

    I can connect to the Oracle DB from this PC with the Oracle SQL Developer tool; hence I know that the fundamentals are working.

    Thanks,
    Wayne
    Thursday, June 25, 2009 6:49 PM
  • Wayne,

    I suggest you use a SQLDataSource with a custom connection string like this,

    Data Source="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<IP>)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=<SID>)))";Persist Security Info=True;User ID=<USER>;Password=<PASSWORD>;Unicode=True

    Let me know if that works.

    Regards,
    Ganesh

    Friday, August 28, 2009 4:21 PM
  • I finally broke down and called support.  I asked for SharePoint Designer support team (not SharePoint Support!!), which is only about 3 guys I think, so I got great support.  He stepped me through it right away.

    For my configuration, I did need the Oracle client installed on my SharePoint front end server, not sure whether you would always need that.

    Then it was never getting to the point of creating the XML datasource file that goes in the _catalogs\fpdatasources folder that they were referring to above.  So the support person showed me how to create one using Visual Studio, create sample project, connect to datasource, etc...that connection worked, just not SharePoint Designer.

    So he had me create a SQL datasource connection in SharePoint Designer first so that I had an XML file in datasources to edit.

    Then he had me copy the XML file contents from my Visual Studio file over to my SQL XML file, and it then worked.

    The XML file should look something like this:

    <?xml version="1.0" encoding="utf-8" ?>

    <udc:DataSource xmlns:udc="http://schemas.microsoft.com/data/udc" MajorVersion="2" MinorVersion="0">

                    <udc:Name>Oracle11</udc:Name>

                    <udc:ConnectionInfo>

                                    <DataSourceControl><![CDATA[

                                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=YourOracleDB;Persist Security Info=True;User ID=YourUserID;Password=YourPassword;Unicode=True" ProviderName="System.Data.OracleClient" SelectCommand=" select .......ENTER YOUR CUSTOM QUERY STRING HERE"></asp:SqlDataSource>]]></DataSourceControl>

                    </udc:ConnectionInfo>

                    <udc:Type MajorVersion="1" MinorVersion="0" Type="Sql"/>

    </udc:DataSource>

    You can skip the step of creating the XML file through Visual Studio if you already know your connection works.  You can just copy the XML above into your XML the SQL Connection made and edit with your Oracle DB and Query unique information.  I have created several Oracle connections since just by copying the above XML in as I stated.


    MSI-Linda SharePoint Architect & Designer
    • Proposed as answer by Barkolounger2 Friday, January 8, 2010 3:59 AM
    • Marked as answer by Mike Walsh FIN Saturday, January 15, 2011 6:19 AM
    Friday, August 28, 2009 4:31 PM