Tuesday, September 07, 2010
Using Oracle SQL Developer with MS SQL
Having chosen Oracle SQL Developer as your preferred Oracle database tool, do you have to install and learn a new technology for supporting your MS SQL databases? Nope! It's easy to connect SQL Developer to MS SQL databases, and I'll show you how.
Background
For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases they'd only give me command-line SQLPlus access. Consequently I never really chose a preferred Oracle client, nor became particularly proficient with anything other than command-line SQLPlus.
Eventually I found myself working directly with a client, and could finally choose whichever tool I liked best, and like many of you I chose Raptor - now known as Oracle SQL Developer. Explaining my choice is outside the scope of this article, but suffice it to say that it's a powerful time-saver at best, and a big step up on command-line SQLPLus at worst.
Connecting to MS SQL
Not all the database applications we support are going to be Oracle-based, and the most popular of the alternatives is Sybase's curious nephew MS-SQL. Fortunately there's no need to find another application for your MS-SQL instances, you can still use SQL Developer, and here's how.
Step 1. Get the JDBC driver for MS SQL
If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember where.
Step 2. Load the JDBC driver
As explained in Oracle's tutorial, under Preferences, scroll down to Database and Third Party JDBC Drivers. Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection (You get Oracle and Access by default).
Step 3. Set up your new database connection
Now you can create a new MS SQL database connection the same you would an Oracle connection. The only difference is that you need to select the SQLServer tab instead of Oracle. If you don't see that tab, then something went wrong - either you got the wrong jar file or it didn't load correctly.
If you're brand new to Oracle SQL Developer, Anders Andreasen has an example with a few extra screenshots.
Use the Test button to make sure you can connect, and if you can't the error message ought to be descriptive enough to give you a starting point to troubleshoot.
Troubleshooting
Needless to say, technologies as complex as these will occasionally present you with unexpected difficulties.
For instance, you may find that you need to copy the ntlmauth.dll file from the SSO directory to SQL Developer's jdk/jre/bin directory.
Or, you may need to check the Configuration Manager on your SQL Server database to make sure you have TCP/IP communication enabled, and over default port 1433.
If you used the driver I recommended, they have a FAQ that can help you track down some types of errors.
Recap
If you support multiple databases, there may not be a need to install multiple tools. Oracle SQL Developer can be used to connect to different flavours of databases, including MS SQL. Just download and load in the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.
Background
For years I worked in technical support for software vendors, and I never knew what client tool would be available when I accessed a customer system. In fact, in many cases they'd only give me command-line SQLPlus access. Consequently I never really chose a preferred Oracle client, nor became particularly proficient with anything other than command-line SQLPlus.
Eventually I found myself working directly with a client, and could finally choose whichever tool I liked best, and like many of you I chose Raptor - now known as Oracle SQL Developer. Explaining my choice is outside the scope of this article, but suffice it to say that it's a powerful time-saver at best, and a big step up on command-line SQLPLus at worst.
Connecting to MS SQL
Not all the database applications we support are going to be Oracle-based, and the most popular of the alternatives is Sybase's curious nephew MS-SQL. Fortunately there's no need to find another application for your MS-SQL instances, you can still use SQL Developer, and here's how.
Step 1. Get the JDBC driver for MS SQL
If you don't already have one, download a JDBC driver for MS-SQL. I use the jTDS driver, which is open-source Java. It doesn't matter where you place it, but remember where.
Step 2. Load the JDBC driver
As explained in Oracle's tutorial, under Preferences, scroll down to Database and Third Party JDBC Drivers. Once you load the jtds-1.2 jar file you should see both SQL Server and it's aging uncle Sybase added to your list of options when creating a new database connection (You get Oracle and Access by default).
Step 3. Set up your new database connection
Now you can create a new MS SQL database connection the same you would an Oracle connection. The only difference is that you need to select the SQLServer tab instead of Oracle. If you don't see that tab, then something went wrong - either you got the wrong jar file or it didn't load correctly.
If you're brand new to Oracle SQL Developer, Anders Andreasen has an example with a few extra screenshots.
Use the Test button to make sure you can connect, and if you can't the error message ought to be descriptive enough to give you a starting point to troubleshoot.
Troubleshooting
Needless to say, technologies as complex as these will occasionally present you with unexpected difficulties.
For instance, you may find that you need to copy the ntlmauth.dll file from the SSO directory to SQL Developer's jdk/jre/bin directory.
Or, you may need to check the Configuration Manager on your SQL Server database to make sure you have TCP/IP communication enabled, and over default port 1433.
If you used the driver I recommended, they have a FAQ that can help you track down some types of errors.
Recap
If you support multiple databases, there may not be a need to install multiple tools. Oracle SQL Developer can be used to connect to different flavours of databases, including MS SQL. Just download and load in the JDBC driver, and you should be able to create a new database connection as easily as you can with Oracle.
