Checking MS SQL Server Objects

I have written about a method to check stored procedures in MS SQL Server using T-SQL before. I extended this method to also include views and stored functions, and wrote a small utility called checktsql.

The idea is to retrieve all views, stored procedures and functions from an MS SQL Server database, and execute:

• SELECT TOP(1) * FROM [View]
• EXECUTE [Procedure]
• SELECT [Function]
• SELECT * FROM [TableValuedFunction]

Each statement is executed within a transaction that is rolled back after execution. All expected parameters are set to NULL. All Exceptions are caught and displayed by the application making it easier to find out which objects are out-of-date and need to be fixed.

checktsql supports to modes of operation: plain execution and setting FMTONLY ON. In the latter case, SQL Server checks all conditional branches (i.e. ignores IF/ELSE).

The application opens one tab per connection/check, and displays the result for each object in a list, along with the type of exception and the exception message. The result list can be filtered to the entries containing errors, and be copied to the clipboard using the context menu. (see screenshots)

Some warnings

• Even though a stored procedure may execute correctly in normal usage, passing NULL parameters may break its functionality and generate error messages like

SQL Exception    Cannot insert the value NULL into column ‘ID’, table ‘mydb.dbo.tablefoo’; column does not allow nulls. INSERT fails. INSERT fails. The statement has been terminated. The statement has been terminated.

This is caused by the NULL values being passed to the procedure.

• Long-running stored procedures may time out

• If you have stored procedure that “clean up” tables or reset or destroy data, you should add a dummy parameter if there is none, and distinguish the check mode from real execution by checking the parameter value for NOT NULL.

• Setting FMTONLY will cause exceptions if the stored procedure uses temporary tables

SQL Exception    Invalid object name ‘#TBL’.

Finally, if you get a message stating

SQL Exception    Invalid object name ‘MyOtherTable’.

or

SQL Exception    Could not find stored procedure ‘sp_Foo’.

you know where to start to fix your T-SQL code…

checktsql is available for download here.

This is a real version 1.00, so send your comments if something is missing

SMOscript 0.17

The most recent version of SMOscript is linked to the SMO libraries of SQL Server 2008.

The new version also generates the “GO” statement terminator after each batch.

One would assume this is automatically done by setting (or leaving) the NoCommandTerminator property false. It is not, as discussions on MS Connect show. Ask the Intertubes.

SMOscript is available for download here.

Saving Embedded Pictures in Outlook 2007

Outlook 2000 used to provide a simple way to save an embedded image from an email: simply right-click on the image, select Save…, and you’re done.

In Outlook 2007, you can only Copy an image to the clipboard.

One widely circulating solution involves writing (or pasting from the source) a macro which is executed by pressing a customized button.

I just discovered a different solution which does not involve code or other tools:

• Open the message in a window (i.e. not only in the preview pane)
• In the Actions tab, select the Other Actions dropdown
• Select View in Browser

This opens your default browser displaying the original HTML email and automatically downloading embedded images. Use your browser’s menu and commands to download the images.