IDENTITY_INSERT in Dynamic SQL

Recently, my colleague worked with some Dynamic SQL to insert some data in one table. What he tried in that code is inserting some data from one to another table, with the fact he need to preserve all IDs as they are in source table. For that purpose, he tried to use IDENTITY_INSERT attribute to allow inserting specific values in primary key column (IDENTITY column). When he tried to execute code, error informed him that is not allowed inserting values in identity column, though he write code for allowing such action.

Example of what he tried is following:

-- ======================================== --
-- Create table [dbo].[Test]
-- ======================================== --
CREATE TABLE [dbo].[Test]
(
[ID] INT IDENTITY(1, 1) PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL
)

-- ======================================== --
-- Insert data in table using Dynamic SQL
-- ======================================== --
DECLARE
@sqlOn NVARCHAR(MAX),
@sqlMain NVARCHAR(MAX),
@sqlOff NVARCHAR(MAX)

SET @sqlOn = N'SET IDENTITY_INSERT [dbo].[Test] ON;'
SET @SqlMain = N'INSERT INTO [dbo].[Test] ([ID], [Name]) VALUES(1, ''Test'')'
SET @sqlOff = N'SET IDENTITY_INSERT [dbo].[Test] OFF;'

EXEC sys.sp_executesql @sqlOn
EXEC sys.sp_executesql @sqlMain
EXEC sys.sp_executesql @sqlOff

The error he got was:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Test' when IDENTITY_INSERT is set to OFF.

At first glance, everything was OK, if you write code without Dynamic SQL, it will execute without any problems, but if you put it in separate variables to simulate three different Dynamic SQL codes, error raises.

The problem lays in fact that SQL treats every EXEC statement as a different scope, because they are different stored procedures execution, and what is executed in first EXEC statement, is not visible to second and so on.

To resolve this problem, we need to put all code in one variable and execute as single statement. Example is shown below:

DECLARE
	@sqlMain NVARCHAR(MAX)

SET	@SqlMain = N'SET IDENTITY_INSERT [dbo].[Test] ON;
					INSERT INTO [dbo].[Test] ([ID], [Name])
						VALUES(1, ''Test'')
				SET IDENTITY_INSERT [dbo].[Test] OFF;'

EXEC sys.sp_executesql @sqlMain

The conclusion is that we cannot have IDENTITY_INSERT property separated from remaining code, because SQL Server treat it like separate scopes.

Translate labels in SSRS

Many projects are relying on SSRS for reports design, preview and publish, and it is a great platform for reporting solution. But, when you develop projects that need to be localized on desired languages, you need to consider two different aspects: data and report labels localization. Data localization can be achieved through letter conversion or similar mechanism. With report labels localization, situation is quite different.

Connect Microsoft Report Builder to MySQL

Microsoft Report Builder is an easy to use, lightweight tool for report development and publishing. For those who prefer to work on an standalone application for report management, this tool is a perfect choice. It is equipped with many functionalities that has its better equivalent – Visual Studio Report Designer, including several connectors to different standard data sources, report elements (tablix, matrix, graphs and so on). But, to connect to data source, like MySQL, you need to do some workaround.

Set up MySQL linked server to MSSQL server

Last months, we worked on project that consists two database systems, MS SQL server and MySQL server. Those systems worked independently for the most of time, but one task came to us to resolve. Somehow , we needed to make connection between those two systems to enable data from MySQL in MS SQL. I decided to implement that task via Linked Server.