SQL Power Software Forum

Any idea when support for SQL Server 2008 will be available?

SQL Server 2008 support isn't something we're actively working on at the moment. I expect it will be very close to SQL Server 2005. The differences between our SQL Server 2000 DDL generator and SQL Server 2005 DDL generator are small.

Have you run into any specific compatibility problems when using the Architect's SQL Server 2005 dialect against SQL Server 2008?

-Jonathan

Were you able to successfully connect to Sql Server 2008? If so, what driver did you use?

Thanks,
Sid
szzfrank@gmail.com

I've installed an instance of SQL Server 2008 express to test with, and I have not been able to connect to it with Microsoft's JDBC driver version 1.2 or the new 2.0 preview release. Both refuse to connect with the following error message:

com.microsoft.sqlserver.jdbc.SQLServerException: The server version is not supported. The target server must be SQL Server 2000 or later.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
	at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source)
	at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
	at ca.sqlpower.sql.SPDataSource.createConnection(SPDataSource.java:457)
	at ca.sqlpower.sql.SPDSConnectionFactory.createConnection(SPDSConnectionFactory.java:42)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
	at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:840)
	at ca.sqlpower.sqlobject.SQLDatabase.getConnection(SQLDatabase.java:553)
	... 7 more

I'm assuming bgreene and szzfrank ran into this error when trying to connect.

The good news is, you can connect to SQL Server 2008 using jTDS, which is an open source driver for SQL Server and Sybase.

Here's what to do:

1. Download jtds 1.2.2 from SourceForge.

2. Unzip it in a known location where you're happy to leave it permanently.

3. Start the Architect

4. Go to Connections -> Database Connection Manager, then click JDBC Drivers...

5. Click the "+" button to create a new database connection type. When prompted, choose to copy defaults from SQL Server 2005.

6. In your new database type, remove the built-in sqlserver_2005.jar file.

7. Add the jtds-1.2.2.jar file (it came out of the zipfile you extracted in step 2). Click the net.sourceforge.jtds.jdbc.Driver that appears under it (this will update the Driver Class field)

8. Change the connection string template to jdbc:jtds:sqlserver://<Hostname>:<Port:1433>

9. Give it an appropriate name, such as SQL Server (jTDS).

The result should look something like the attached screenshot.

Hope this helps. I have done a small amount of casual testing, and everything I tried appears to work fine. Please report compatibility problems if you encounter them!

-Jonathan

Thanks Janathan,

It didn't work for me. I followed your steps. I'm getting following error.

java.sql.SQLException: Network error IOException: Connection timed out: connect

But, I'm able to connect SQL server 2008 thru' SQL server Management Express Studio.

Thanks and appreciate your support.

Kanya wrote:I'm getting following error.

java.sql.SQLException: Network error IOException: Connection timed out: connect

Sounds like you are either using the wrong servername or the wrong port number

Jonathan wrote:I've installed an instance of SQL Server 2008 express to test with, and I have not been able to connect to it with Microsoft's JDBC driver version 1.2 or the new 2.0 preview release. Both refuse to connect with the following error message:

com.microsoft.sqlserver.jdbc.SQLServerException: The server version is not supported. The target server must be SQL Server 2000 or later. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.DBComms.Prelogin(Unknown Source) at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) at ca.sqlpower.sql.SPDataSource.createConnection(SPDataSource.java:457) at ca.sqlpower.sql.SPDSConnectionFactory.createConnection(SPDSConnectionFactory.java:42) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:840) at ca.sqlpower.sqlobject.SQLDatabase.getConnection(SQLDatabase.java:553) ... 7 more
I'm assuming bgreene and szzfrank ran into this error when trying to connect.

The good news is, you can connect to SQL Server 2008 using jTDS, which is an open source driver for SQL Server and Sybase.

Here's what to do:

1. Download jtds 1.2.2 from SourceForge.

2. Unzip it in a known location where you're happy to leave it permanently.

3. Start the Architect

4. Go to Connections -> Database Connection Manager, then click JDBC Drivers...

5. Click the "+" button to create a new database connection type. When prompted, choose to copy defaults from SQL Server 2005.

6. In your new database type, remove the built-in sqlserver_2005.jar file.

7. Add the jtds-1.2.2.jar file (it came out of the zipfile you extracted in step 2). Click the net.sourceforge.jtds.jdbc.Driver that appears under it (this will update the Driver Class field)

8. Change the connection string template to jdbc:jtds:sqlserver://<Hostname>:<Port:1433>

9. Give it an appropriate name, such as SQL Server (jTDS).

The result should look something like the attached screenshot.

Hope this helps. I have done a small amount of casual testing, and everything I tried appears to work fine. Please report compatibility problems if you encounter them!

-Jonathan

Hi Jonathan,

i did exactly what you wrote in your suggestion. unfortunately i get an error message like this:

network error ioexception: connection refused

on the windows site i activated tcp/ip for the sql-server disabled the firewall and the user i try to logon with is member of sysadmin-group

error details:

java.sql.SQLException: Network error IOException: Connection refused
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:385)
at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)
at ca.sqlpower.sql.SPDataSource.createConnection(SPDataSource.java:482)
at ca.sqlpower.swingui.SPDataSourcePanel$1.actionPerformed(SPDataSourcePanel.java:157)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1882)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2202)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:25

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
at java.awt.Component.processMouseEvent(Component.java:5602)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3135)
at java.awt.Component.processEvent(Component.java:5367)
at java.awt.Container.processEvent(Container.java:2010)
at java.awt.Component.dispatchEventImpl(Component.java:406

at java.awt.Container.dispatchEventImpl(Container.java:206

at java.awt.Component.dispatchEvent(Component.java:3903)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4256)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3936)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3866)
at java.awt.Container.dispatchEventImpl(Container.java:2054)
at java.awt.Window.dispatchEventImpl(Window.java:1801)
at java.awt.Component.dispatchEvent(Component.java:3903)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:190)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:176)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:432)
at java.net.Socket.connect(Socket.java:520)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:304)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:255)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:310)
... 28 more

is there any chance to help me?

thx

andreas

Hi Andreas,

Caused by: java.net.ConnectException: Connection refused

This can only mean that the remote server is refusing the TCP connection request. Go over your SQL Server (and firewall) configurations again; there must be something missing.

-Jonathan

I have installed the power architect application and SQL Server 2008 developer's edition as a named instance (SS2K

on a laptop. I'm having trouble just connecting to the master database. I've gone through existing threads on the same topic and it just doesn't seem intuitive enough. Here are the fields I use

Connection Name: Test
Database Type: SQL Server 2008
Hostname: MyLaptop\SS2K8
Port: 1109
JDBC URL: jdbc:sqlserver://MyLaptop\SS2K8: 1109
Username: sa
Password: myPassword

Error reads: Exception type:java.sql.SQLException
Detail string: Couldn't connect to database "master":
JDBC driver net.sourceforge.jtds.jdbc.Driver

al_corleone wrote:Connection Name: Test
Database Type: SQL Server 2008
Hostname: MyLaptop\SS2K8
Port: 1109
JDBC URL: jdbc:sqlserver://MyLaptop\SS2K8: 1109
Username: sa
Password: myPassword

Error reads: Exception type:java.sql.SQLException
Detail string: Couldn't connect to database "master":
JDBC driver net.sourceforge.jtds.jdbc.Driver

You are using JDBC URL for the Microsoft JDBC driver, yet the error message indicates that you are using the jTDS driver which uses a differen URL scheme.

Check out the jTDS documentation:

http://jtds.sourceforge.net/faq.html#urlFormat

Thanks Jonathan for the info. Worked through a couple of issues, but I'm all set now.

For those still having issues, here are some tips.

1. Pay attention to the screen shot above and ensure your Driver Class and Connection String Template fields match the picture. My Driver Class did not update automatically as indicated.

2. Note that you should not need to add "\SQLExpress" to the host name if you are using SQL Express and have only a single instance.

3. Make sure your SQL instance is listening on the TCP/IP port. This is not on by default for Express. I'm not sure about the paid versions.

4. Be sure to note that SQL Express does not listen to port 1433. The default is 1846 for 2008 and 1874 for 2005.

Happy modeling.

I just got a connection to SQL Express 2008 in Windows 7 to work.
I followed the instructions in http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx Option A.
Then, I created a Connection in SQL Power Architect.
Connection Name: SQL Express
Database Type: SQL Server 2008
Hostname: localhost\SQLEXPRESS
Port: 2301 (from Option A above)
JDBC URL (created by SQL PowerArchitect): jdbc:sqlserver://localhost\SQLEXPRESS:2301
Username: sa
Password: <MyPassWord>

Is there an update to the initial bggreene/Jonathan exchange that starts this thread? Jonathan, Just having access to the new 2008 Date data type via a SQL Server 2008 option for “Generate DDL for database type” would make it all worthwhile. Then add a Use statement to the scripts (see below) and I could start using the Execute button. Now I have to save the scripts, edit a few data types, and load them by hand.

Scripts need to specify the target database by adding a Use statement (Use <dbname>). Without this statement when Execute is clicked the scripts execute against the Master database; where they either add tables to the Master (a BAD thing to do) or fail depending on the login’s rights.

i really need sql server 2008 new datatypes. such as datetime2, date, time...
could you please support these types?