Calculate TSQL Stored Procedure Complexity

Aalam Rangi9 Comments

I had a recent project where we planned to re-factor an existing application’s .Net and TSQL code. I wanted to find the complexity of the stored procedures and arrive at an estimate of the effort required to review all them. I had in mind that something like number of lines of code, number of dependencies and parameters in the stored procedure would give a good starting  point to create such an estimate.

I patched together a script that produced a report similar to the example below. I used that output and put in some more formulas to assign a complexity level (simple, medium or complex) and the approximate number of minutes required to review that procedure. I have not included those calculations here because they depend entirely on the estimator’s perception of complexity and the developer’s skill level.

The number of lines of code is a subjective metric here because it depends on the developer’s coding style and use of whitespace. Even then, it could be a useful reference point.

Complexity Report Screenshot

In case this seems useful then the script to create that report is available for download at Technet.

Download and rate the script at: http://gallery.technet.microsoft.com/Calculate-TSQL-Stored-831b683a

CHANGE HISTORY:
2012/Dec/01 – Version 1
2013/Feb/15 – Version 1.1 Added the CASE statement for complexity.
2014/Apr/29 – Version 1.2 Added Database and Schema name to the report.
Included objects that may not be refering to other objects.
Included views, functions and triggers.

Please feel free to share you feedback in the comments.

SSIS Package Development Effort Estimation

Aalam RangiLeave a comment

I blogged about a DTS to SSIS conversion effort estimation project that I had a chance to work upon previously. I had created an Excel workbook to help in that estimation.

That workbook was not just limited to the DTS to SSIS conversion estimates. It can be used for regular SSIS development project estimations too. I’ve updated the workbook a bit to reflect its generic nature.

The Excel workbook can help in estimating the effort required to create new SSIS packages. The new SSIS could either be a conversion of an existing DTS package or a fresh requirement. The estimation methodology would be similar in both cases.

Benefits of Using this Estimation Methodology

  • Standardize the estimates by using the same rules instead of guessing randomly, which could return widely varying figures depending on whom you talk to.
  • Standardizing also means that the estimation process for small, medium or large projects is the same. The importance of having a standardized process is directly proportional to the project size, to reduce the variance.
  • The estimates can be fine-tuned over time with real-world feedback so that the estimates get better with each new project.
  • Flexible to include the developer skill factor.

Two Steps to Fill the Estimation Workbook

  1. Put in the estimated number of minutes required for –
    a) creating each type of component in SSIS. This time could vary depending on the developer’s experience and skill. Sample values are included in the workbook.
    b) standard time additions for activities like analysis, error handling, logging and documentation. Sample values are included in the workbook.
  2. Update the workbook with the details like the type and number of all tasks, connections, transformations etc. that would be used in the SSIS package.
    a) If the basis is a DTS package then open the DTS and count the tasks, connections etc.
    b) If the basis is a fresh requirement then put the details according to those requirements. This will also help you to plan the package development.

A brief Help section is also included in the workbook.

Download

Download at: http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

How to Activate Microsoft SQL Server 2008 R2 180-day Evaluation Version After Expiration with a Product Key (Step-by-step)

Aalam Rangi2 Comments

The trial period of the Microsoft SQL Server 2008 R2 180-day Evaluation version had come to an end on my laptop and the SQL Server had stopped working. I had bought the Developer Edition media with product key and wanted to use that to have the SQL Server running again. The following are the step-by-step screenshots of the entire activation experience.

Click on images to enlarge.

Following is the error message that comes up while trying to start the SQL Server Management Studio on an expired SQL Server 2008 R2 showing that the trial period has expired.

1.ErrorMessage

I have Windows 7 Home Premium (64-bit) running on Intel Core 2 Duo 2.2 GHz, 4 GB RAM. To begin the activation, go to Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Installation Center (64-bit). Go to Maintenance and click on Edition Upgrade.

2.SQLServerInstallationCenter

An in-progress message appears briefly.

3.InProgress

A setup rule verification window appears and shows the progress while the rules are verified. Each status message can be checked by clicking on the link. A summary of all the rules is also presented in HTML format. The path for the HTML file is mentioned at the end of the blog post. Wait for the setup to verify the rules to and click OK.

4.SQLServer2008R2SetupWindow1

Another set of rules are verified. Wait for this to complete and click Next.

5.SQLServer2008R2SetupWindow2

The next screen presents an option to enter the product key. Enter the key and click Next.

6.UpgradeTheEditionForSQLServer2008R2ProductKeyWindow

Read and accept the license terms by checking the checkbox. Click Next.

7.UpgradeTheEditionForSQLServer2008R2EULAWindow

Select the SQL Server instance from the dropdown and click Next.

8.UpgradeTheEditionForSQLServer2008R2SelectInstanceWindow

One more set of rules are verified. After completion click on Next. The next window shows a summary for review and a confirmation that the setup is ready to upgrade the edition. Click on Upgrade. The Upgrade button will be disabled but there will not be any other activity indication like a hour-glass cursor or a progress bar. This could be a little puzzling but wait for it to complete. It took about 4 minutes to complete on my laptop. Your mileage could vary.

10.UpgradeTheEditionForSQLServer2008R2_ReadyToUpgradeWindow

The final screen shows a completion message and the path of the setup log file. Click on the log file path to open it. Click OK on the setup window to close it.

11.UpgradeTheEditionForSQLServer2008R2_CompleteWindow

HTML report of the setup rules:
C:Program FilesMicrosoft SQL Server100Setup BootstrapLogYYYYMMDD_HHMISSSystemConfigurationCheck_Report.htm

Text log files:
C:Program FilesMicrosoft SQL Server100Setup BootstrapLog
and its child folders.

How to monitor IDENTITY column values in SQL Server

Aalam RangiLeave a comment

One (lesser known) task of a DBA should be to keep an eye on the values of the IDENTITY columns so that they do not hit the ceiling for their data type and catch you by surprise. Not a pleasant one, of course. This is all the more important if you have tables that grow quickly.

Vyas Kondreddi has a nice script to look for current IDENTITY values in all tables of a database and compare that value against its datatype. It could be useful to automate it as an alert or run it manually once in a while. He has versions of the script for SQL Server 7.0, 2000, 2005 and later but all of them run against one database at a time which might make it a little inconvenient to run against all your 200 databases.

I’ve taken the script compatible with SQL Server 2005 and later, and enhanced it a bit to run it against all databases in one go using the undocumented stored procedure SP_MSFOREACHDB. There goes your excuse to procrastinate this!

Change the value against the TOP keyword to suit your preference. I’ve used TOP 5.

Download my script from the TechNet Gallery link –
http://gallery.technet.microsoft.com/Monitor-IDENTITY-column-fd9c6552

The output would look like the screenshot below.

MonitorIdentityColumns

How to Embed a PDF Document in a SharePoint Wiki Page with a Page Viewer Webpart

Aalam Rangi2 Comments

I had a PDF document that I wanted to embed in a SharePoint wiki page such that it was ready to view instead of just being a download link. My document was a single page diagram but you could have multi-page documents that you need to display like this on a wiki.

I’ve used the Page Viewer web part to implement this. The downside to this approach is that every time a Wiki page with an embedded PDF is opened or edited, the standard dialog box with the “Open, Save or Save As” option comes up. This dialog box can be canceled each time but I’m not yet aware of any way to disable it permanently. There is another way to embed a document using a Content Editor web part too (shared here and here) but I’ve not had a chance to try that out.

Steps:

1. Upload the PDF document to an appropriate SharePoint library and copy the URL in a Notepad. The URL is needed later.

2. Open the Wiki page where the document has to be embedded and click on the Edit icon at the top-left.

3. Click on the location or bring the cursor to the location in the Wiki page where you’d like to embed the PDF.

4. Click on the Editing Tools > Insert tab at the top of the page ().

5. Click on Web Part.

6. Select Media and Content in the Categories list box on the left of the page and select Page Viewer from the Web Parts list. Click on Add. This will insert the Page Viewer web part at the location of the cursor in the page.
Image 1 - Insert Page Viewer web part

7. Hover over the inserted Page Viewer web part. Click on the drop down button and click the Edit Web Part option.
Image 2 - Edit Page Viewer web part

8. Paste the URL, give a title and specify a height for the web part and click OK. You are done! The PDF will now appear as a document in a window and Adobe Reader’s normal features like zoom, pan, print etc. would be available to interact with the document.
Image 3 - Set Page Viewer web part properties

How to Fix Error Code: 80004005 in DTS Caused by 64-bit DB2 ODBC Driver

Aalam RangiLeave a comment

Issue:

Data Transformation Services packages (DTS) that use DB2 as an ODBC source, fail when the execution environment is upgraded to 64-Bit Windows Server 2008, Windows 7 etc.

Error Message:

The error logs of the DTS would show the following message –

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Cause:

DTS are 32-bit applications and need the Data Source Names (DSN) based on 32-bit ODBC drivers. A 64-bit Operating System points to the 64-bit drivers by default and uses the ODBC Data Source Administrator at –

C:WINDOWSsystem32odbcad32.exe

The standard DSN creation scripts used by system administrators might use the 64-bit version because it is at the default location.

The 32-bit ODBC Data Source Administrator is in –

C:WINDOWSSysWOW64odbcad32.exe

The required DSN should be created again using this 32-bit version and it will update the Windows registry or the environment path variables appropriately.

Fix:

Start the 32-bit ODBC Data Source Administrator Wizard from the SYSWOW64 directory.

C:WINDOWSSysWOW64odbcad32.exe

Go to System DSN tab and click on Add.

clip_image001

Select the available IBM ODBC driver.

clip_image002

Give a DSN name.

clip_image003

Confirm the action.

clip_image004

The last message box verifies if you want to overwrite the existing DSN alias in DB2CLI.INI file. This file is at the below location on a 64-bit machine –

C:ProgramDataIBMDB2DB2_V95db2cli.ini

Clicking on No will abort the wizard. Click on Yes to continue. The timestamp for the INI file would be updated. If you are just recreating an existing alias as in the example above, the file content would appear to be the same, though the ODBC Data Source Administrator Wizard will create or modify the appropriate registry and environment variable entries.

Summary:

Microsoft maintains a backward compatibility feature known as WOW64 (Windows-32-On-Windows-64) that lets older 32-bit applications to run smoothly. This is implemented by maintaining a separate copy of the necessary 32-bit run time resources (configuration utilities, drivers, registry settings, other DLLs etc.) in different folders/locations.

This example refers to an ODBC DSN for a DB2 server but the concept could be relevant in other such scenarios where there is a conflict between 32 and 64-bit drivers.

Reference:

How to point to 32-bit IBM DB2 ODBC driver on 64-bit Microsoft system.
http://www-01.ibm.com/support/docview.wss?uid=swg21384435

Use a One-Time Schedule for Adhoc Job Executions

Aalam RangiLeave a comment

If there is ever a need to run a SQL Server job on an adhoc basis outside of its normal schedule, don’t change its original schedule. Rather, create a new one-time only schedule for that job and leave it like that even after the the execution.

If you modify the original schedule then you’d have to remember to revert it back. This can be easily missed out and then the job would be running at the modified schedule after that! Oh, so you say you have a water-tight reminder mechanism and you’ll never forget to revert it?! Please read the next line.

If there was a requirement to run the job out-of-schedule once, then it could happen again too. The one-time schedule would be handy at that time and can be reused.