I am beginning to connect SAS to more and more Databases such as Microsoft SQL Server 2005 and MySQL. I find that I need more than just SAS or Enterprise Guide to view the database, columns, and data types. I use 2 tools frequently. The first is SQLyog for MySQL databases. The community version is free to use (I believe for personal use). The second is DbVisualizer which can be used on a wide variety of databases such as MySQL and MS SQL. There is a free-for-personal-use version available.
DbVisualizer has installers for MS Windows, Mac, and Linux. SQLyog has an installer for MS Windows, but I have it working on Ubuntu Linux via the Wine Project.
Of course, there is always the free Microsoft SQL Server Management Studio Express from Microsoft. But I find this to be incredibly bloated software. You can download it from here.
DbVisualizer can be odd to use at first when you are not used to JDBC (Java) drivers. To connect DbVisualizer to Microsoft SQL Server, I use either the following 2 drivers:
DbVisualizer has a good tutorial for how to install these drivers. Basically, just fire up the software, then go to Tools –> Driver Manager. Scroll to SQL Server entries and select the JTDS SQL Server. On the right hand side click on the folder button and navigate to the file you would have downloaded and unzipped from the JTDS website. Select the .jar file in that folder. Do the same thing for the SQL Server 2005 entry and the Microsoft Driver. Next time you fire up the software, the drivers will load with the other drivers.
Now for the tricky part. The Connection String for the Microsoft JDBC Driver is:
jdbc:sqlserver://servername:port;DatabaseName=mydatabase;InstanceName=myinstance
You cannot put the instance name in the servername name area as you would with other connection software. I.e. servername\instancename does not work. You have to put the instance name in the parameter section as above. Also, some instances can be on other Ports. So although the server is running on port 1433 or what have you, the instance could be on some other port altogether. If you use an instance, ensure you have the correct instance name.
Also note that some documentation says your URL connection string should be jdbc:microsoft:sqlserver://etc… Take out the microsoft part, it will not work.
If you use the JTDS JDBC driver, some documentation says your Connection String URL should be:
jdbc:jtds:sqlserver://servername;DatabaseName=mydatabase;instance=myinstance
Presto! You can now connect to your database.