en-UShe-IL
You are here:  Blog

Awared MVP 

Microsoft® Community Contributor 


Microsoft® Community Contributor


Blog Archive:

הגדל
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

הגדל
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

הגדל
* Can be used in order to search for blogs by keywords

TNWikiSummit


הגדל מה בעמוד?

דצמ10

Written by: ronen ariely
10/12/2016 07:42 RssIcon

SET IDENTITY_INSERT doesn't work with 4 parts name, and as such it doesn't work with linked servers unless you execute dynamic SQL. Moreover, "SET IDENTITY_INSERT" is a session level setting, therefore when you connect to another server the setting of current session are not relevant. In this short blog I will show how we can use SET IDENTITY_INSERT on linked server using dynamic SQL.

* This needs raise in the forums from time to time, and I thought it is a good time to put it all in a short post.

Preparation

Open SSMS and connect to the server instance.

For the sake of this post I will create a linked server named Ari_Test01_LS, which linked to the current server. 

USE [master] 
GO
 
EXEC sp_addlinkedserver    
   @server=N'Ari_Test01_LS',  
   @srvproduct=N''
   @provider=N'SQLNCLI',  
   @datasrc=N'.\'; -- Use your instance name here
GO

I will use tempdb database, create a new table, and insert some rows for testing:

USE tempdb
GO
 
drop table if exists T
GO
create table T (id int identity(2,2), txt nvarchar(100))
GO
INSERT T (txt) values ('a'),('b')
GO

Now we can test out table data from the current session and from remote linked server execution. If the linked server was created well then this query should work well. 

SELECT * FROM tempdb.dbo.T -- reading locally
SELECT * FROM Ari_Test01_LS.tempdb.dbo.T -- reading from linked server
GO

* If there is any issue executing the query above then you cannot proceed with the tutorial. You should first confirm that you can create a simple linked server and work with it. This is not in the scope of this post. 

Examine our options using SET IDENTITY_INSERT, locally and remotelly

Our main goal is to execute bellow query on the remote server using our linked server. As first step let's confirm it is executing well locally:

-- Executing locally
SET IDENTITY_INSERT tempdb.dbo.T ON
insert T(id,txt) values (11, 'locally inserted')
SET IDENTITY_INSERT tempdb.dbo.T OFF
GO

Confirm the data inserted

SELECT * FROM tempdb.dbo.T -- reading locally
SELECT * FROM Ari_Test01_LS.tempdb.dbo.T -- reading from linked server
GO

Can we configure the setting of IDENTITY_INSERT using our linked server directly?

SET IDENTITY_INSERT Ari_Test01_LS.tempdb.dbo.T ON
GO

The answer is no! the above query will raise and error: Cannot find the object "Ari_Test01_LS.tempdb.dbo.T" because it does not exist or you do not have permissions.

According to the documentation SET IDENTITY_INSERT support the syntax:

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

It doesn't work with 4 parts name, and as such it doesn't work with linked servers unless you execute dynamic SQL. Moreover, "SET IDENTITY_INSERT" is a session level setting, therefore when you connect to another server the setting of current session are not relevant. In this short blog I will show how we can use SET IDENTITY_INSERT on linked server using dynamic SQL.

Since I want to use dynamic query, let's first confirm that our query work locally. This step is highly recommended every time you design a remote query or a complex dynamic query! always try to built the test first as it might be a bit confusing, especially if your query includes lot of quotation marks:

-- work locally
Declare @SQL NVARCHAR(MAX) = N'
SET IDENTITY_INSERT tempdb.dbo.T ON;
insert T(id,txt) values (21, ''locally inserted'');
SET IDENTITY_INSERT tempdb.dbo.T OFF
'
-- PRINT @SQL
EXECUTE sp_executesql @stmt = @SQL
SELECT * FROM tempdb.dbo.T
GO

* Confirm that the query worked well locally and the data inserted, by executing the select query above.

Let's confirm that we can execute dynamic query on the remote server using our linked server. By default you will probably get an error, which we will deal in the next step.

exec Ari_Test01_LS.master.sys.sp_executesql @statement = N'select 1'
GO

Server is not configured for RPC

If you did not get any error and you can see the result of the above query then you can skeep this section.

ERROR: Server 'Ari_Test01_LS' is not configured for RPC.

By default servers are not configure to execute Remote Procedure Call (RPC). We can change this properties for our linked server using the GUI interface, which I do not recommend as always, or using simple query as I will show in a second.

Using the GUI: Open the "Object Explorer" windows -> Expand the "Server Object" list -> Expand the "Linked server" list and find our new linked server -> Right click on the linked server and chose "properties" -> In the new windows move to the "Server Options" tab as you can see in the image below and change the properties of "RPC Out" to "true"

Change RPC Out property

Much simpler and in my opinion better option is to use simple query as you can see here:

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'Ari_Test01_LS', @optname=N'rpc out', @optvalue=N'true'
GO

Confirm that we can use remote stored procedure again. It should execute well now.

exec Ari_Test01_LS.master.sys.sp_executesql @statement = N'select 1'
GO 


Using dynamic query to insert data into identity column in linked server

Now that all is working well we can use SET IDENTITY_INSERT as part of our dynamic query. The basic idea is that we execute simple stored procedure remotely on our linked server. In our case we simply use the built in sp_executesql stored procedure in order to execute dynamic query. in the dynamic query itself we insert our data after setting the IDENTITY_INSERT to ON.

* The most important issue that we might have here is with quotation marks. For example single quotation mark should be converted into double quotation marks and in our case we had several double quotation marks which should become 4 quotation marks.

exec Ari_Test01_LS.master.sys.sp_executesql @statement = N'
    Declare @SQL NVARCHAR(MAX) = N''
    USE tempdb;
    SET IDENTITY_INSERT tempdb.dbo.T ON;
    insert T(id,txt) values (111, ''''Remotely Inserted'''');
    SET IDENTITY_INSERT tempdb.dbo.T OFF
    ''
    EXECUTE sp_executesql @stmt = @SQL
'
GO

Using external variable

Using external variable (variable that declared in out current server and not in the linked server) inside our linked server query, might be a bit more complex. 

First point that we need to remember is that cannot execute something like:

exec sp_executesql @statement = N'select ' + N'1'

Stored Procedure does not support concatenating two or more string values on the fly. We will need to built the final string first and insert the value into a variable. Next, we will use this variable in the stored procedure execution. For example:

declare @MyStatement NVARCHAR(MAX) = N'select ' + N'1'
exec sp_executesql @statement = @MyStatement

** In production you should use parameters and not concatenating several string values as I demonstrate above!!!

Using parameters the code should look like:

declare @ExternalParm int = 1
DECLARE @MyStatement nvarchar(500) = N'select @InsideParm';
DECLARE @InsideParmDefinition nvarchar(500) = N'@InsideParm tinyint'
DECLARE @max_title varchar(30); 
EXECUTE sp_executesql @MyStatement, @InsideParmDefinition, @InsideParm = @ExternalParm
GO

For more information regarding using sp_executesql stored procedure and parameters you can check the documentation. For the sake of our final example I will use simple concatenating of strings.

Second point that we need to remember is we need to confirm that we use the right number of quotation marks. Each time we use a quotation mark inside a string we need to duplicate it by 2 times.

In my example above we insert the value "Remotely Inserted" into the table in the linked server. In this example I will configure a variable and use it's value in the insert query by concatenating the variable and the main query.

declare @Remotely_Inserted NVARCHAR(100) = N'Remotely Inserted'
declare @MyStatement NVARCHAR(MAX) = N'
    Declare @SQL NVARCHAR(MAX) = N''
    USE tempdb;
    SET IDENTITY_INSERT tempdb.dbo.T ON;
    insert T(id,txt) values (121, ''''' + @Remotely_Inserted + ''''');
    SET IDENTITY_INSERT tempdb.dbo.T OFF
    ''
    EXECUTE sp_executesql @stmt = @SQL
'
print @MyStatement
exec Ari_Test01_LS.master.sys.sp_executesql @statement = @MyStatement
GO

    

That's all we need :-)

   

Conclusions

We can use dynamic query in order to insert data into identity column in linked server, with the use of SET IDENTITY_INSERT ON as part of our remote query. This is not my recommendation as the best procedure to insert data, but this is the question that raise in the forums from time to time. 

* In most cases, if I have one row to insert and if this is one-time-job and if we already configure our linked server to enable RPC then this solution should be great solution, but in other cases I would probably prefer not to use linked server for this need.

    I hope this was useful :-)
    - Ronen

      

    

here are some links that can give you some more to read:


Categories: SQL
Location: Blogs Parent Separator Public blog