TSQL Gotcha – ISNUMERIC Function Results

March 24, 2013Aalam RangiLeave a comment

The ISNUMERIC function (MSDN documentation) is used to validate if a string is a valid number. In the demonstration that follows, you would expect the results from 1 to 5 but look closely at the results 6, 7 and 8. There are multiple commas in the string and the last one is not even a number!

Well, a note at the MSDN documentation explains the True result for the ‘$’ sign at Id 8 –

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.

For the strings at Id 6 and 7 with commas, the function behaves that if the string is convertible to a number then the result will be True. Something to remember while writing those data validation routines.

DECLARE @t TABLE
( id int, col1 VARCHAR(10) )

INSERT INTO @t (id, col1)
VALUES (1, '-1001'),
(2, '0'),
(3, '1001.50'),
(4, '1,500'),
(5, 'abc'),
(6, '15,0,1'),
(7, '15,0,1.75'),
(8, '$')

SELECT id,
col1,
Isnumeric(col1) AS [IsNumeric]
FROM @t

TSQL Gotcha – Miss a comma

March 8, 2013Aalam RangiLeave a comment

Some minor omissions can go unnoticed and produce completely unexpected results.

The following code example has two seemingly similar SELECT statements but they produce different results. All because I missed out a comma in the second SELECT statement! SQL Server did not raise any error and quietly assumed that I have tried to use an alias name for the First Name column!

CREATE TABLE employee
(
employee_id INT,
firstname VARCHAR(50),
middleinitial VARCHAR(1),
lastname VARCHAR(50)
)
INSERT INTO employee
(employee_id, firstname, middleinitial, lastname) VALUES
(1001, 'Timothy', 'B', 'Parker'),
(1002, 'Henry', 'F', 'Williams'),
(1003, 'Jeff', NULL, 'Smith'),
(1004, 'Mary', 'M', 'Marsh'),
(1005, 'Miles', 'V', NULL)

--Correct statement
SELECT employee_id, firstname, middleinitial, lastname
FROM employee

-- Wrong statement with a comma missing
-- between the firstname and middleinitial columns.
-- SQL Server thinks it is an alias for firstname.
SELECT employee_id, firstname middleinitial, lastname
FROM employee

TSQL Gotcha – Empty string in INTEGER type column

February 23, 2013Aalam RangiLeave a comment

It is important to sanitize data.

If you try to put an empty string in an INTEGER type (or its cousin) columns, SQL Server converts it implicitly into a zero. This might not be the expected behavior for the application so it is good to be aware.

declare @v char(1) = '' -- this could have been a varchar too with the same results

declare @t table
(
colbigint  bigint,
colint int,
colsmallint smallint,
coltinyint tinyint
)

/* The wrong way that will change all blank strings to zero */
insert into @t
(
colbigint,
colint,
colsmallint,
coltinyint
)
values
(
@v,
@v,
@v,
@v
)

/* The correct way to sanitize */
insert into @t
(
colbigint,
colint,
colsmallint,
coltinyint
)
values
(
nullif(ltrim(@v), ''),
nullif(ltrim(@v), ''),
nullif(ltrim(@v), ''),
nullif(ltrim(@v), '')
)

select * from @t
go

Parent-Child Table Dependency Script

February 21, 2013Aalam RangiLeave a comment

Requirement:

You need to delete and then insert data into tables which have parent-child relationships defined via primary and foreign keys. The delete and insert commands have to be executed in proper sequence.

The sequence for DELETE is Child-before-Parent.

The sequence for INSERT is Parent-before-Child.

I created a quick script in response to a forum post. The script is not the best out there because –

It does not handle circular references in relationships
It takes 30 seconds or more if run on a database with hundreds of tables

I’m sharing it for my future reference and probably others with smaller number of tables would find it useful too.

Download script at –
http://gallery.technet.microsoft.com/Parent-Child-Table-45fa5268

Further reference:
For a faster (but not with all these output columns) script, look at Erland Sommarskog’s response in the forum thread.

Script output would look like the example below –

How to Read MSDN Forums with an NNTP News Reader

January 2, 2013Aalam RangiLeave a comment

If you indulge in Microsoft MSDN Forums (social.msdn.microsoft.com/Forums) frequently then you might have had a thought about using a News reader.

The MSDN Forums Help Wiki mentions an “NNTP bridge” by Microsoft to configure one’s News reader but that project seems to have been off the shelf since the release of the new MSDN forums. It seems there is no other information about the future availability of a new bridge which would be compatible with the current forums.

There is a CodePlex community project at Community Forums NNTP Bridge that provides the functionality to provide this bridge. Download the zip file (approx 1 MB). Extract and run the setup.exe to install. Its documentation is quite explanatory but I’d like to restate and add to it –

The bridge essentially acts as an NNTP server so it has to be running when you plan to use your News Reader. It can be turned off (closed) at other time.
You have to use the IP 127.0.0.1 as the NNTP server in your News Reader configuration while setting up the forums. This is not mentioned clearly in an otherwise good documentation. Screenshots below.
You have to turn on (enable) the NNTP option in your forum settings page before you can use the bridge.
You have to use the same Windows Live login (Hotmail, MSN, Live etc.) account credentials while starting the NNTP bridge server that you use for the forums.
Microsoft Outlook can not be used as a News reader but its cousins viz. Outlook Express, or more recently, Windows Live Mail (WLM) can be used as such. WLM is a part of the free Microsoft Essentials package. If you have other favorite news readers then give them a try too.

Other links:

Community Forums NNTP Bridge page at CodePlex
Ron do Bruin’s page about the bridge settings
Forums Help Wiki

Windows Live Mail Configuration:

Calculate TSQL Stored Procedure Complexity

December 24, 2012Aalam 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.

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

December 23, 2012June 24, 2016Aalam 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

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.
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)

December 4, 2012Aalam 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.

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.

An in-progress message appears briefly.

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.

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

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

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

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

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.

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.

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

October 25, 2012Aalam 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.

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

October 16, 2012Aalam 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.

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

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.

SQLErudition.com

Learning SQL Server

Author: Aalam Rangi