Tuesday, May 13, 2008

Working with SQL Server in NetBeans 6.1

The JDBC-ODBC bridge wasn't recognizing any of the primary keys I had set up in SQL Server. After lots of head banging, here's the solution I came up with:

1. Download the appropriate driver. SQL Server 2000 requires the SQL Server 2000 JDBC driver (http://www.microsoft.com/downloads/details.aspx?FamilyId=07287B11-0502-461A-B138-2AA54BFDC03A&displaylang=en).
SQL Server 2005 download: (http://www.microsoft.com/downloads/details.aspx?familyid=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en)
3. After installing, right-click on "Libraries" in your project, and choose "Add Library...". Next, give it a name (i.e. SQLServer2000), and select "Class Libraries".
4. On the next screen, find the JAR files (should be in C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\), and add them under the "Classpath" tab. It will now be available under "Libraries" for future projects.
5. You can now create a connection to a specific database under the "Services" tab (next to "Projects" and "Files" in the top left of the screen). Select "Microsoft Sql Server 2000 (Microsoft Driver)" and format the "Database URL" like this:

jdbc:microsoft:sqlserver//YOURSERVER:1433;DatabaseName=YOURDATABASE

1433 is the default port, though your DBA may have changed it.

I posted a simpler version of this on the NetBeans.org FAQ page - they had the following title with no content on the answer page:

"Cannot Select Tables From MsSql 2000 Because It Says No Primary Key Next To Each Table But The Tables DO Have A Primary Key. What Do I Do?"

Looks like somebody was really frustrated, and posted a question in the hopes that somebody would answer it :-) Of course I was really frustrated seeing a question with no answer, so I added one after I figured it out myself.

12 comments:

Unknown said...

Hi,Everything works fine but I'm unable to See the tables under the "Tables" tree which are there in SQL Server.

Unknown said...

Not sure what's going on, but here's a few ideas:

- make sure you're connected to the db. Right-click on the connection, and click on "Connect..." if it's not greyed out, though I don't think you'd see the "Tables" folder if you weren't connected.

- try right-clicking on the "Tables" folder, and click on "refresh".

- try building your project (F11) and then check the tables again.

- make sure you've got primary keys. Beans binding requires them - maybe this affects whether or not they show up as well.

Let me know how it goes.

Unknown said...

In the New Database Connection dialog boos, go to the Advanced tab and select dbo (or data base owner) in the select schema drop down box.

This should resolve your problem.

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...

Hi Mike,
It works :)

Unknown said...

Hi Mike, It Works.

Anonymous said...

Mike that's great! Thanks a lot!

Pipe said...

It worked for me too.
But remember... If your problem is the connection, you might revise the port number, because MSSQL 2005 use dynamic ports by default.

Anonymous said...

Hi Mike,

I face similar issue, however my advanced tab is disabled, do you know how to resolve this??

Thank you for your help.

Jacelyn