How to make a SPD database connection to Oracle 10

Question

Using SPD "connect to database" how can I make a connection to a Oracle 10.2.0.4.0  database?  I found the following connection string but it is erroring out.

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;

Do I have to install the Oracle Client on our SharePoint Services 3.0 server?

---

MSI-Linda SharePoint Architect

Answer 1

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 modify the xml file generated in the fpdatasources document library to change the connection string (from/_catalogs/fpdatasources/yourCustomQuery.xml in SPD) and remove the "Initial Catalog=root" part in the XML file.

 

Hope it can help you.

---

Xue-Mei Chang

Answer 2

I am trying the workaround, but there is nothing under the fpdatasources folder because it never lets me save my initial database connection I try because of the oracle error?

---

MSI-Linda SharePoint Architect & Designer

Answer 3

Hi,

 

Initially when you first create the database connection with the custom connection string, you will get error, when you click OK on this error message you will see another screen in the Configure Database Connection listing "root" in the Database dropdown and no tables or views displayed. You should click on "Finish" and an xml file gets generated in the fpdatasources document library.

 

Let me know the result

---

Xue-Mei Chang

Answer 4

I don't ever see this other screen you refer to.  When I press OK on the error dialog box it just stays right where it was...at the Configure Database Connection screen with everything greyed out but Edit, Next, and Cancel.  Those are my only options.

This is what I am doing:


1. Click Database Connections - Connect to a database
2. Enter Server Name
3. Provider Name = Microsoft .NET Framework Data Provider for SQL Server (or OLE DB either provide same resulting error)
4. Select Use custom connection string EDIT - I enter the following string but edited with my information:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword; 

5. In Provider Name drop down list I select ...For Oracle  
6. Click OK
7. Click Next
8. get the authentication password in clear text dialog box and click OK
9. get the Server Error dialog box with only an OK option to click.
10. And it returns me right back up to #7 and the drop down list has the same options as before.

---

MSI-Linda SharePoint Architect & Designer

Answer 5

Hi,

 

Base on your description, I suppose that you haven’t connected to the Oracle database successfully using the connection string, so please check if the connection string is right and you have set the parameters correctly. And could you please give me the detailed error message that you got?

 

The known issue I said in the pre-post is happened after we connect to the Oracle database successfully. So you cannot see the “root” database dropdown in the Configure Database Connection screen.

 

Sorry for your inconvenience.

---

Xue-Mei Chang

Answer 6

Do I need to have the Oracle Client installed on the SharePoint server or my desktop I am trying this from?

---

MSI-Linda SharePoint Architect & Designer

Answer 7

Hi,

Can you try the same by odbc connection so that youy dont need to install the Oracle Client.

---

Manish Patil http://patilmanishrao.wordpress.com Posting is provided "AS IS" with no warranties, and confers no rights.

Answer 8

At step number 9 that I list above I get the following error message:

Server Error: An error occurred while retrieving the list of Databases from : The server for the data source 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 administrator.


It is basically the same error message as if I would enter nothing at all for the connection string.

Do I need to install the Oracle Client on my desktop or SharePoint front end server for the connection to work?

---

MSI-Linda SharePoint Architect & Designer

Answer 9

Hi,

Well i think you may require to install oracle client on the Front end server.

---

Manish Patil http://patilmanishrao.wordpress.com Posting is provided "AS IS" with no warranties, and confers no rights.

Answer 10

I installed the Oracle Client on the front end server, and I can connect via the Oracle Client SQL Plus through command line.  But when I try through SharePoint Designer I still get the same exact error that I list in a previous entry on July 7.

Can anyone confirm for me that my steps I listed 1-10 are even correct and that my connection string should be correct?

Is there a white paper or article that explains any of this in detail?

---

MSI-Linda SharePoint Architect & Designer

Answer 11

Linda,

I have the same problem connecting to an Oracle DB through SharePoint Designer.  Did you ever find an answer to this problem?  I'm doing almost the exact same steps that you are.  Plus, I've installed the Oracle Client on my desktop and the SharePoint server.  However, it still doesn't work.

Any tips that anyone can offer would be greatly appreciated.

Best,

Answer 12

YES, 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

Answer 13

Thank you, Linda.  I really appreciate the help.  I think I still have some connection issues: windows authentication login.  However, you've made is easier.

Thanks.

Answer 14

Then in your case you might want to ensure you're connection string is correct and you can connect 'outside' of SharePoint Designer.  So if you have Visual Studio installed on your desktop, you might want to create a sample project there and create the connection through VS to ensure that you are connecting to Oracle.  If that works then the above should work for you.  If you can't connect in VS then, yes you might have other issues that are preventing you from authenticating to Oracle.

---

MSI-Linda SharePoint Architect & Designer

Answer 15

Thanks, Linda.  I'll give it a shot.

Dan

Answer 16

YES, 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

Thanks Linda for this solution :-)

Anders