Using Embarcadero Optimization tools with MS SQL Server...

I received an email from a community member asking how to set up a datasource for our database optimization tools and Java optimization tools (DB Optimizer and J Optimizer) to connect to Microsoft SQL Server.

"From: Jacques (in Australia) - Hi David, I have installed the Optimization tools. I cannot add a datasource. I am using DBArtisan and i never had any problem creating or connecting to datasource. Is there anything i need to do to see or create a datasource I am trying to connect to MS SQL ... server."

Here is the reply I sent and a note about how to set the proper TCP/IP port.

For DB Optimizer, for example, I used the JDBC Driver configuration "SQL Server JDBC Version 1.2 Driver" - this is downloadable from Microsoft. http://msdn.microsoft.com/en-us/data/aa937724.aspx

Then I pointed the connection to my SQL Server Express database - I used the AdventureWorks database example from Microsoft CodePlex http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

Connection URL for the driver is jdbc:sqlserver://localhost:1433. Note that you might have to make sure you have the right TCP/IP port set for SQL Server (see notes from support below). The same setup should work for J Optimizer using the same JDBC driver from MS.

Setting the correct TCP/IP port for MS Sql Server to work with the JDBC driver:

1) Open the SQL Server 2005 Configuration Manager by selecting Start->All Programs->Microsoft Server 2005->Configuration Tools->SQL Server Configuration Manager2) Click on the SQL Server Network Configuration node to expand it

3) Click on Protocols for SQLEXPRESS node

4) Right click on the TCP/IP protocol on the right panel and select Enable

5) Right click on the TCP/IP protocol on the right panel and click Properties

6) Click on the IP Address tab

7) On the IPAll section, clear out the TCP Dynamic Ports field and enter 1433 (which is the default port for SQL Server 2005) on the TCP Port field

Then try connecting again