Identity value management

Identity column is a column that uniquely identifies every row. It differs from a primary key in that its values are managed by the server and ( unless you specify it explicitly ) can't be modified. In many cases it is used as a primary key.

Identity value management:

Create Identity:

CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](128) NOT NULL)

Insert Identity values:
You do not have to specify identity value when inserting new record: INSERT INTO Person(Name) values('Oleh'). It will be filled for you using current identity and increment values.
In case you specify one: INSERT INTO Person(PersonID,Name) values(2,'Oleh') you will get an error: Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Person' when IDENTITY_INSERT is set to OFF.
Instead you need to use next snippet:
SET IDENTITY_INSERT Person ON
INSERT INTO Person(PersonID, Name) VALUES(1,'Oleh')
SET IDENTITY_INSERT Person OFF

When you insert identity value explicitly and it's a primary key column then you will get an error:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_Person'. Cannot insert duplicate key in object 'dbo.Person'.
The statement has been terminated.

Get current identity value:

• DBCC CHECKIDENT(TableName, NORESEED) - returns current identity value
• SCOPE_IDENTITY() - returns the last identity value inserted within the same scope(stored procedure, trigger, function or batch)
• @@IDENTITY - returns the last identity value inserted across all scopes
• IDENT_CURRENT(TableName) - returns the last identity value for the specified table regardless of the scope.

Update identity value:

• DBCC CHECKIDENT(TableName, RESEED, NewSeed) - resets the identity value with provided seed
• DBCC CHECKIDENT(TableName) - resets the identity value only if it is required

Get Seed/Increment:

• IDENT_INCR(TableName) - returns original increment value that was specified when identity was created.
• IDENT_SEED(TableName) - returns original seed value that was specified when identity was created.

SELECT TABLE_SCHEMA, TABLE_NAME
, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_SEED
, IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS IDENT_INCR
FROM INFORMATION_SCHEMA.TABLES
WHERE IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) IS NOT NULL

Finding gaps in identity column:

• Here you can find the original post about how you can find gaps in identity column.

Regards,
Oleh

Transaction Log Shipping Copy job update

I need to configure TransactionLogShipping of a database between two database servers. Primary database is on production server that is SQL Server 2008 Standard installed on Windows Server 2003 x32 (used as web server as well). Secondary database is on SQL Server 2008 Developer installed on Windows Server 2008 x64 (I’ll use it as development database with almost up-to-date data for developing and testing heavy statistics reports). And both servers are in DIFFERENT networks.

OK. Let’s configure it!

I would not describe all the steps in detail that are required for TransactionLogShipping (you can find overview here) just step that I had problems with. My problem was copying transaction log over network. Different networks remember. Everything I googled was about having both database servers in the same network and creating user account with the exactly same name on both machines and full access rights for folders used for creating and restoring backups. Creating user accounts on both with full access rights especially on primary server that is a web server as well did not sound like a good idea for me. I tried to reuse something that I have and that is configured, tested and working 24/7 without opening potential security flaw.

I have an FTP server on primary server. And I know that LogShippingCopy job could be adjusted to reuse SSIS package for example that will take care of copying transaction log files from primary database server. Sounds like an idea!

Backup job requires network path for the folder where it will put log backup. It’s compulsory parameter for it. I have created a shared folder with FTP server folder and provided the path to BackupJob configuration dialog.

RestoreJob on secondary server is configured as in every other case.

Copy Job

Final configurations looks like:

FtpCopy package: That was the biggest problem for me.

I have created simple SSIS package with single FtpTask.

I tried it on my development machine locally (Vista x32, BIDS 2008) and it is working ok. Deployed to secondary database server started copy job manually – nothing is copied !? OK. A have BIDS installed on secondary server (since it’s a development one). Opened the same package from there and I’m getting "0xC001602A at FtpCopy, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: The operation timed out" every time. !?!? OK. I’m trying to reuse Scripting Task to copy files over Ftp in order to get detailed exception or at least step through code and see the flow. Wrote the code in C# and placed a breakpoint. I’m pressing F5 hoping that I’m seconds away from the detailed error description thus the solution. But the Output window writes: “Warning: 0x7 at Script Task: Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime.” LLL

OK. Let’s check FTP logs from the Ftp server. When running either FtpTask or ScriptTask from my development machine (Vista x32) I see log entry code 226 which means “Transfer Complete” but when running the same FtpTask or ScriptTask from secondary server (WS 2008 x64) log entry is 426 Connection closed; transfer aborted. Error: 64.

Let’s google, maybe it’s specific x64 runtime problem. I have founds this MS forum entry that confirmed my suspicions. It’s related to running package on x64 runtime.

My last idea was to reuse ExecuteProcessTask and will start bat file with ftp commands for copying files over ftp.

Now everything is OK. Ftp log entries have 226 code and I see transaction log files in destination folder!

Tried complete TransactionLogShipping sequence – works like a charm! J But almost full working day was spent in order to make it working.

You can see the flow diagram below:

PS:

• Average transaction log file is 2.5MB.
• If you have existing transaction log backup jobs either stop them or re-point them to save transaction log to the same folder as log shipping backup job does as Log Sequence Number(LSN) must me continuous in oder for log shipping restore to be successful.

I hope this post will help someone.

Regards,

Oleh

Report Parameter Default Values

While developing SSRS reports I almost always define default report parameters in case I need to fix some issue with layout or data itself. It saves some time as I don't have to fill report parameters each time I want to preview report with new changes applied.
But after report is deployed to production report server and users start using it default parameters are not that handy there. Especially with heavy statistics reports (that takes more than several seconds to process request) when report engine starts processing request at once using default parameters. Almost in all cases default parameters does not match user's. Nobody wants to wait extra before submitting desired input parameters especially when "extra" is several minutes long.


So don't forget to empty default parameters prior to deploying report or if deployed empty them and redeploy.

PS: And don't forget to update report subscriptions as well in case you have some.


Regards,
Oleh

Alternating background colors for odd and even tablix rows

In this blog post I will describe how you can add alternating background colors to SSRS tablix odd and even rows.

What do you need to know is that expression evaluation rules in RS 2008 have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics.

How we can reuse it to accomplish our goal:

• Create a function that will update _evenRow flag state each time it's called.

Open report properties box -> Code tab -> Add next code:

Public _evenRow As Boolean

Public Function EvenRow() As Boolean

 _evenRow = Not _evenRow

return _evenRow

End Function

• Create a variable within Row group and set the expression to call EvenRow function. Since it will be evaluated one time per each row it's what we need.

• Set row background color depending on variable state.

=iif(Variables!EvenRow.Value, "LightGrey", "Transparent")

• and finally the outcome

Regards,
Oleh

What's new in WCF 4.0, WF 4.0

A Developer’s Introduction to Windows Communication Foundation (WCF) .NET 4 Beta 1 describing upcoming WCF 4.0 changes has been published on MSDN. The basic idea is to simplify developer experience and provide more integration with WF 4.0. Introduction to WF 4.0 changes could be found here.


Enjoy!
Regards,
Oleh

Sysinternals Suite updated

Sysinternals Suite has been refreshed with latest releases and new utilities.

"The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools. This file contains the individual troubleshooting tools and help files. It does not contain non-troubleshooting tools like the BSOD Screen Saver or NotMyFault."

Latest version could be downloaded here.

Enjoy!

Oleh

SQL Server 2008 R2 August CTP

Microsoft released SQL Server 2008 R2 August CTP:

• Start exploring it.
• Follow what's new in August CTP.
• SQL Server 2008 R2: Virtualization for Databases.
• SQL Server 2008 R2: The DAC Pack.
• 
  

  New / changed columns in SQL Server 2008 R2 DMVs.

• Upcoming Reporting Services changes.

Regards,

Oleh