OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Try to do:

DISTRIBUTED TRANSACTION

Error message:

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Solution:

In order to enable the distributed transactions set:

SET XACT_ABORT ON;

Run dynamic sql from the linked server to the current one

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

DECLARE @SQL nvarchar(max)

BEGIN TRY

       set @SQL = 'REVERT; EXECUTE AS LOGIN = ''SA''

              EXECUTE(''

       UPDATE / INSERT / .....

       FROM [LinkedServerDataBaseName].[LinkedServerSchemaName].[LinkedServerTableName] b

       JOIN [MyServerNameAsLinkedServerName].[DataBaseName].[SchemaName].[TableName] ns  ON b.BRN_ID = ns.BRN_ID

       WHERE .......

              '') AT [LinkedServerName]';

       exec sp_executesql @SQL

       COMMIT TRANSACTION

END TRY

BEGIN CATCH

       -- ROLLBACK TRANSACTION

       -- do something....

END CATCH

SET XACT_ABORT OFF;

LinkedServerDataBaseName - database name in the linked server

MyServerNameAsLinkedServerName - my server has to be declared as linked server in the other server, this is it's linked server name there.

LinkedServerName - the linked server name in my server

TRANSACTION between servers - DISTRIBUTED TRANSACTION

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

-- do something

ROLLBACK/COMMIT TRANSACTION

SET XACT_ABORT OFF;

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

Error message:

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.

Problem:

As written...  INSERT statement exceeds the maximum allowed number of 1000 row values.

Surprised, but INSERT statement using 'VALUES' (not insert with a select) - have limitation of 1000 inserted records.

Solution:

Anything that will use INSERTs with maximum 1000 records: separate to few INSERTs, insert the new data to temp table and insert using select from the temp table and so on.

exec query on other database

DECLARE @statement NVARCHAR(MAX) = N'select * from dbo.MyTable;

DECLARE @sql NVARCHAR(MAX) = QUOTENAME('OtherDbName') + '.sys.sp_executesql';

EXEC @sql @statement;

Merge with two options for MATCHED case

Remainder for MERGE syntax:

http://copypastenet.blogspot.co.il/2011/03/synchronize-two-tables-using-merge.html

If there are 2 options what to do when we have a MATCHED between the source and he target tables - most of the times it will be deletion and update - we can do that like this:

MERGE <target_table> [AS TARGET]

.....

WHEN MATCHED AND <condition>

THEN DELETE

WHEN MATCHED  -- all the other cases of MATCHED other then the condition above

THEN UPDATE 

SET ColumnA = s.SomeValue

.....

FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')

An encoding issue with “FOR XML PATH”:

DECLARE @str NVARCHAR(MAX)= N'h&s_dor, Zoe&lee88AB'

select stuff(( select ',' + t.Alias from split ( @str, ',') t

                           FOR XML PATH(''))

                     ,1,1,'') EncStr

Solution:

FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')

select stuff((( select ',' + t.Alias from split ( @str, ',') t

                           FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)'))

                     ,1,1,'') EncStr