First, download the Microsoft JDBC driver.

Once extracted, I copied the sqljdbc4.jar file into the lib folder under the jdbc folder of the SQL Developer Data Modeler but you just need to know where the jar file is located.
Go to Tools > Preferences > Data Modeler > Third Party JDBC Drivers and click the plus to add the location of the jar file. Then click the OK button.
If on a Windows machine and you want to use integrated security, edit your system path to include the location of the sqljdbc_auth.dll file or copy the sqljdbc_auth.dll to an existing location in your system path, like Windows\System32.
Then click File > Import > Data Dictionary.
Click the Add button.
Click the JDBC tab
Choose Other ThirdParty Driver
For JDBC URL, type jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value] – or look here for details about how to form the URL.
For Driver Class, type com.microsoft.sqlserver.jdbc.SQLServerDriver
For Database Type, choose SQL Server.

Assuming your URL is correct, click the test button and you should get success.

I had to review a stored procedure to figure out why it was causing the web application to timeout when being passed certain parameters. The web application was using this one stored procedure to not only show a search results page (with 9 columns) but also to show the detail for a particular row (showing all 39 columns in a separate window). The stored procedure was using a CTE to handle/enable paging. Reviewing the code showed at the end of the CTE, the code had the following:

1

 SELECT * FROM CTE

This version of the code would take up to 4 minutes to display a result based upon the parameters used.

The fix was to change

1

 SELECT *

to

1

 SELECT ColumnName

and this improved the performance dramatically. Where the original code took up to 4 minutes to display a result, this change had the results display instantly (well, under a second).

The lesson, only use

1

 SELECT * FROM OBJECT

when absolutely necessary (and when is that?).

Microsoft has released SQL Server 2012 Release Candidate and is supposed to be feature compete and production ready (as production ready as a release candidate can be).

Get the bits here! (until the link dies)

SQL Server 2008 Service Pack 3 has been released.

Here is where you can get it.

SQL Server Code Name “Denali” CTP 3 is now available. Get it here. Continue reading →

Today SQL Server 2008 R2 Service Pack 1 is released today.
Get it here.

In a previous post, I displayed one method to encrypt data. This post is to show how to show the decrypted data. This post refers to the objects used/created in the previous post. Continue reading →

Recently I had to show that data can be encrypted in SQL Server 2008 database. The purpose was to not store the raw value in a table where anyone with access could see the sensitive data. The original process truncated the destination table and then populated the destination table with data pulled from the source table. Seeing that as an issue, I made a change to the process in addition to adding encryption. Continue reading →

A person with a small amount of SQL Server DBA experience came to me and asked if there was an automatic job deletes users or changes user permissions. I told them there was no default job that does that. I then suggested they look at the audits to see if there was any activity. Come to find out, there were no audits on the installations. I decided to implement some audits so the question I was originally asked could be answered by viewing the audits.

This led to explaining how to use audits. In SQL Server 2008 (and 2008 R2), hereinafter referenced as SQL 2008, auditing is so easy, there is no reason not to do it. Basic process for creating an audit is to create audit and then define the specification for the audit. Continue reading →

I recently was playing around with SQL Server 2008 Reporting Services and switching between Native and SharePoint integrated mode.  In the last change, I switched from SharePoint Integrated to Native but came across an error when opening the Report Manager, specifically HTTP Error 503: Service is Unavailable. As usual, I over analyzed it but did find the solution. Continue reading →