The object name contains more than the maximum number of prefixes. The maximum is 2.

Question

Hi

I am trying to identity insert to a remote linked server using dynamic sql.

declare @MyStatement NVARCHAR(MAX) = N'
    USE MyDB;

    SET IDENTITY_INSERT LinkedServer.MyDB.dbo.MyTable ON;

    INSERT INTO LinkedServer.EMS_108_HS.dbo.MyTable(ID, <fields list>)
    SELECT               ID, <fields list>
    FROM                 MyTable AS MyTable_1
    WHERE MyTable_1.ModifiedOn = '' + convert(varchar, getdate(), 23) + '';

    SET IDENTITY_INSERT LinkedServer.MyDB.dbo.MyTable OFF'

print @MyStatement
exec master.sys.sp_executesql @statement = @MyStatement

I am getting error 'The object name 'LinkedServer.MyDB.dbo.MyTable' contains more than the maximum number of prefixes. The maximum is 2.' on the two SET IDENTITY_INSERT. How can I get round this to be able to insert identity to remote linked server?

Thanks

Regards

Answer 1

I was trying to do the exact same thing, change identity insert on/off on a linked server.
I solved my problem like this;

I created a stored procedure on the linked server to change the identity on/off

executed that procedure on my local server and it worked.

Answer 2

I would suggest that you run this on the server where the table is located instead.

Then again, why does the table have the IDENTITY property, if you want to insert explicit values? If you absolutely want autonumber, maybe you should use a sequence object instead.

If you absolutely want to work with IDENTITY and insert from the source server, it is doable, but it is complicated. You need to create an XML document of the data with FOR XML. Then you call sp_executesql on the remote server, and send over the XML document and shred it on the remote side into relational format and insert into the table.

Answer 3

SET IDENTITY_INSERT LinkedServer.MyDB.dbo.MyTable ON;

The IDENTITY_INSERT command don't support 4-part qualifier = Linked Server, therefore you get the error message.

See SET IDENTITY_INSERT (Transact-SQL)