Actually, the difficulty is caused by a bug of Microsoft. Below are detail steps:
1. Click "Connect to a database" in "Database connections", click Configure database connection, click Use Custom String, click Edit, select "Provider for OLEDB" and type the following connection string:
Provider=OraOLEDB.Oracle;Data Source=wise;Persist Security Info=True;User ID=wiseq;Password=wiseq;Unicode=True
(Replace the parameters for Data Source, User ID and Password with your own)
2. Click Next and you will see an error which says "an error occurred while retrieving the list of tables from the root Database", click OK, click "Specify custom Select, Update, Insert…", click Edit Command and type the command which looks like:
select * from project
click Finish, click OK.
3. Then, expand the left pane of SharePoint designer, expand _catalogs--fpdatasources, you will find a new xml file whose name is the same as your connection name generated. Double click it to open it. The current string shows
<udc:DataSource xmlns:udc="http://schemas.microsoft.com/data/udc" MajorVersion="2" MinorVersion="0">
<udc:Name>changeora</udc:Name>
<udc:ConnectionInfo>
<DataSourceControl><CDATA><asp:SqlDataSource id="SqlDataSource1" runat="server" __designer:Provider="OraOLEDB.Oracle" __designer:customcommand="true" ProviderName="System.Data.SqlClient" __designer:customconnectionstring="true" SelectCommand="select p.deptno, p.dname from scott.dept p" ConnectionString="Data Source=wise;Persist Security Info=True;User ID=wiseq;Password=wiseq;Unicode=True;Initial Catalog=root;" /></CDATA></DataSourceControl>
</udc:ConnectionInfo>
<udc:Type MajorVersion="1" MinorVersion="0" Type="Sql"/>
</udc:DataSource>
You can modify the provider name and delete the Initial Catalog. The expected result looks like
<udc:DataSource xmlns:udc="http://schemas.microsoft.com/data/udc" MajorVersion="2" MinorVersion="0">
<udc:Name>Custom Query</udc:Name>
<udc:ConnectionInfo>
<DataSourceControl><CDATA><asp:SqlDataSource runat="server" ConnectionString="Data Source=wise;User ID=wiseq;Password=wiseq;" ProviderName="System.Data.OracleClient" ID="SqlDataSource1" SelectCommand="select p.deptno, p.dname from scott.dept p" __designer:Provider="OraOLEDB.Oracle" _designer:customcommand="true" __designer:customconnectionstring="true"></asp:SqlDataSource></CDATA></DataSourceControl>
</udc:ConnectionInfo>
<udc:Type MajorVersion="1" MinorVersion="0" Type="Sql"/>
</udc:DataSource>
4. Save the xml. When you click Show Data and you should able to see the data populated.
4 comments:
that unicode=true is what made it for me. i havent found that anywhere else. thank you. thank you. thank you.
This solution worked for me. Thanks a lot.
I tried your solution ,in sharepoint designer after entering the select it said ok. but I couldn't see any xml file on the left pane. Should I open anything from the Menu bar to see that. Any help would be appreciated. I am struggling hard toconnect to oracle database.
Hi, Shreya,
Sorry, I can not help. I have long time not touch SharePoint, and with no SharePoint at hand. Please try hard again and see if there is any other solution.
Post a Comment