How can I set identity_insert on a tablename passed as a variable

Question

I have the Database name in a @strDBName. I build the SET IDENTITY_INSERT and execute it. There's no error, but a subsequent insert fails. The code below illustrates the problem.

  Declare @Query Varchar(MAX)
  SET @Query = 'SET IDENTITY_INSERT '+ @strDBName +'..TableName ON'
  EXEC(@Query)


  INSERT INTO [TableName] ... (MAX) Value from another table and other applicable record.


  SET @Query = 'SET IDENTITY_INSERT '+ @strDBName +'..TableName OFF'
  EXEC(@Query)

Answer 1

Just to backup Brad's answer given in the comments, here's an MVCE of doing the entire insertion sequence in a single dynamic query. As per Kris' comment, ensure that the database name is white listed, as the query is vulnerable to SqlInjection (unfortunately, database names cannot be parameterized in dynamic sql via sp_executesql)

Given:

CREATE TABLE TableName
(
    ID INT IDENTITY(1,1)
);

A single batch can be executed:

DECLARE @strDBName VARCHAR(100) = 'MyDatabase';
Declare @Query Varchar(MAX)
SET @Query = 'SET IDENTITY_INSERT '+ @strDBName +'..TableName ON; '
SET @Query = @Query + 'INSERT INTO '+ @strDBName 
 +'..[TableName](ID) SELECT COALESCE(MAX(ID), 0)+1 FROM '+ @strDBName +'..TableName; '
SET @Query = @Query + 'SET IDENTITY_INSERT '+ @strDBName +'..TableName OFF;'
EXEC(@Query)