Connecting Oracle Data Integrator and MS SQL Server
1. Refer to this Download link for Microsoft SQL Server 2005 JDBC Driver 1.2
and for Microsoft SQL Server JDBC Driver 2.0 here
JDBC Driver 1.2
Extract the downloaded .exe or .gz and copy jar file ” sqljdbc.jar” into the $ODI_HOMEDRIVERS folder
Now go to the topology and Under the Physical architecture of Microsoft SQL Server. Define a New Data Server with the required Name , User connection.
Go the Next JDBC tab , enter the JDBC Driver and JDBC Url
JDBC Driver : com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC Url : jdbc:sqlserver://<Server address> :<Default port=1433 >selectMethod=cursor
JDBC Url : jdbc:sqlserver://<Server address> :<Default port=1433 >selectMethod=cursor;responseBuffering=adaptive
The above command works great with both SQL Server 2005 and for SQLServer 2000 try this
JDBC Driver : com.microsoft.jdbc.sqlserver.SQLServerDriver
JDBC Url : jdbc:microsoft:sqlserver://<Server address> :<Default port=1433 >selectMethod=cursor
Learn more about option like selectMethod=cursor and responseBuffering=adaptive here http://msdn.microsoft.com/en-us/library/bb879937.aspx and other connection strings here http://msdn.microsoft.com/en-us/library/ms378428.aspx.
To learn more about the Url Option check the ” Release ” document available with extracted .exe or . gz
Click Test to test the connection and Select the appropriate database and owner for Physical Schema and define the appropriate Logical Schema.
Note for ‘ Owner ( Schema )‘ Select the right owner of the database , mostly ” dbo ” . check with your SQL Server DBA for right schema owner
For the Work schema owner , the right user who have permission to access the schema has to be selected.
JDBC Driver 2.0
JDBC Driver ver 2.0 works only with JDK or JRE 1.5 or greater and as ODI is shipped with 1.4 so you would surely encounter the error.
To Resolve this issue download Jdk or Jre 1.5 or greater and change the ODI_JAVA_HOME accordingly and point to the new JAVA_HOME,
Sql Server JDBC Driver is shipped with both ‘sqljdbc‘ and ‘sqljdbc4‘ .To use JDBC Type 4 driver copy only ‘ sqljdbc4‘ in the ‘ oracledi/drivers ‘ folders .
[ Note : Please refer to the release document – point number 1 under Known Issues ]
Copy the ‘ sqljdbc4 ‘driver and test the connection. Restart the agent and try the same Driver and URL as shown above and refer to its ‘ Release ‘ document for more details.
Learn more about SQL JDBC driver from your extracted driver folder at ‘ enuhelpindex.html ‘ or access all these information online http://msdn.microsoft.com/en-us/library/ee229547%28SQL.10%29.aspx
October 16, 2017 at 5:28 PM
Hi,
Initially we have 2 schemas(dbo and guest) in SQL. Recently they have created 1 new schema(test) in sql.
While trying to create a physical schema i am not able to find the newly created schema through ODI.
October 17, 2017 at 4:26 PM
Hi,
did you check all security permissions? Just checking… because it should work.
May 3, 2011 at 8:53 AM
Hi,
Can you please provide the URL/path to get the release notes.
Thanks,
Chary
May 4, 2011 at 8:34 AM
When you extract the archive you will be able to find release.txt under enu folder
September 24, 2009 at 2:45 AM
You should make a note in this that yu should use the selectMethod=cursor option on the URL for SQL Server. Otherwise, you will get various exceptions thrown.
September 24, 2009 at 9:16 AM
Thanks craig.I have updated and included links so viewers can learn more about selectmethod.