The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “@thatjeffsmith”

Get thee to KScope13!

Jeff Smith (@thatjeffsmith) just posted a great little piece about why you can’t afford to miss this year’s ODTUG event in New Orleans: KScope13. As usually I will be speaking there as well and running my now annual Morning Chi Gung classes.

Get thee to KScope13!

See you there.


Tech Tip: Connect to SQL Server Using Oracle SQL Developer (updated)

I spend a lot of time reverse engineering client databases to see what kind of design they are working with or to simply create a data model diagram for them (so they know what they have).

Along the way I often need to actually look at the data as well to do some analysis and profiling.

Often this means looking at data and models in SQL Server as well as Oracle.

What’s an Oracle Data Warrior to do?

Hook up my FREE handy dandy Oracle SQL Developer to the SQL Server database.

How do you do that?

First you need to get the right driver. You can find it here:

NOTE: For SQL Developer 4.0EA3 and SQL Developer Data Modeler 4.0 (production) you now need jtds-1.3.1. Get it here:

Then follow these steps:

  1. Download and unzip the file into the main SQL Developer directory (or the directory of your choice).
  2. In SQL Developer go to Tools -> Preferences -> Database -> Third party JDBC Drivers
  3. Click the “add entry” button
  4. Navigate to the jtds-1.2.5.jar file. (or the 1.3.1 file for 4.x installs)
  5. Save and exit preferences.
  6. Close and restart SQL Developer
  7. Open “Add Connection” – there should now be a SQL Server tab.
SQL Developer Preferences

SQL Developer Preferences

With this in place, you can now connect to SQL Server without having to load any other software.

Pretty useful.

Happy Querying!


P.S. You can connect to other non-Oracle dbs as well. Check out this post by Jeff Smith for even more details.

Additional Notes on SSO errors:

Lots of folks, including me, have had issues getting the native Windows SSO connection to SQL Server to work. Based on answers on the OTN Forum and this post ( I finally got my new 4.x versions to work.

For SQL Developer 4.0EA3, I did as suggested in the article: I put the ntlmauth.dll where my JDK 1.7 was installed: C:\Program Files\Java\jdk1.7.0_40\jre\bin

For Data Modeler 4.0.13 (production), based on a suggestion from Jeff Smith, I put the dll file here: C:\SQLDeveloper\SQLDeveloper4.0.13\sqldeveloper\sqldeveloper\bin

If I was better at setting windows paths, I am sure there is a better way to do this.

The best FREE data modeling tool ever

Yup, I said FREE!

Oracle just released the latest and greatest version of SQL Developer Data Modeler  (SDDM) and it is free to the world to not only download but to use in your production environment to develop all your models.

As many of you know, I have been using this tool for several years now and have mentioned it many times on various LinkedIn forums (just search for me and check out my activity). I have used SDDM for both Oracle and SQL Server. For forward engineering and reverse engineering. For conceptual, logical, and physical data models.

I think it is a great tool (even if it was not free).

I loved Oracle Designer and got quite good at that, but once shops stopped buying and using Designer (and Oracle pretty much sun-setted the tool), I suffered for a few years using other tools.

I was a very happy camper when Oracle came out with this new data modeling tool. I am even happier now with the new features they have added.

The one I like the most, so far, is the visual editor they added for defining views. The previous version had a decent declarative approach that allowed you to specify tables, columns, and joins, but you could not really “see” the implied data model.

The newest version of SDDM (version 3.1) has added in a visual editor that shows you a diagram of the tables, columns, and joins. So now when you open (or define) a view and press the “query” property button you get drag and drop interface to build the view and a nice visual diagram.

And the best part is when you upgrade your existing models from previous versions, the old views automatically get diagrammed.

To get the best out of the new version you need to run a one time utility labeled “Parse Older Style Views”. You can find that off the right mouse menu in any diagram with views. It runs very fast and basically reads the SQL for your views then parses it out to show up properly in the diagram.

One nice new feature with the parsed views is that if the underlying tables in the view are part of the same design file (hopefully you did not drop those), then the view object on your diagram will now list those tables below all the columns. This is nice because now I do not have to open the view definition to see which tables the view is pulling from.

The other great new feature is the “Test Query” button on the view property dialog.

No more writing views that do not work. You press the button, specify a database connection to use, then the base query for the view fires.

If there is an error in your syntax, or a table you don’t have access to, you find out immediately.

So gone are the days of writing the view in your modeling tools, loging into SQL Plus or SQL Developer, testing the view, having it fail, then going back to SDDM to fix it.

Now you can do agile view development! In one tool!


Oh, and if the view works, there is a data tab so you can see the actual data the view will produce – live. Right in the data modeling tool.

Pretty cool.

Nice job guys.

Convinced yet? Head over to the Oracle site and download your own copy and give it try.

UPDATE 2015: Data Modeler is now up to version 4.1 and going strong. Plus now there is an Oracle Press book available on Amazon:

And I even have a tips and tricks Kindle book out on SDDM. You can find that here.

Let me know what you think in the blog comments.

Talk to you all later.


P.S. For all the new features in SDDM 4.1 check out the full list over here.

P.P.S Need training on SDDM? Check out my post about my new workshop.

Post Navigation

%d bloggers like this: