Replicating Record Link Table

LovelyLady

Hi ,
In my code I am replicating the Record link table from Nav to my database.
The Identity is set to true. Identity Management = Manual
I keep etting the error - "Cannot insert explicit value for identity column in table 'Record Link' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)"
when I try to replicate the Link Id column.
It has worked for most of my clients DB replication, only one database replication is missing some thing.
Can anyone please help ???

kine

The error is common for these situations:

You are using column with identity and you are assigning some value to it (differen than 0)
You are not db_owner or you do not have other "higher" permissions

In this situation the system is trying to "change" the counter for the column to specified value, but you do not have permissions.

If you are replicating something from other table, I expect that you want to use the original value. Than you do not need to have the identity on the field. If you want to use the autoincrement, you need to not assign this field or assign 0 instead. The autoincrement will change it automatically.

LovelyLady

below is the script that i am using to replicate the [Record Link] table.Could you tell me how to set the Identity column good to be replicated.

exec sp_addarticle @publication = N''' + @PUBLICATIONNAME + ''', @article = N''Record Link'', @source_owner = N''dbo'', @source_object = N''Record Link'', @type = N''logbased'', @description = N'''', @creation_script = N'''', @pre_creation_cmd = N''drop'', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N''manual'', @destination_table = N''Record Link'', @destination_owner = N''dbo'', @status = 24, @vertical_partition = N''true'', @ins_cmd = N''CALL [dbo].[sp_MSins_dboRecord Link]'', @del_cmd = N''CALL [dbo].[sp_MSdel_dboRecord Link]'', @upd_cmd = N''SCALL [dbo].[sp_MSupd_dboRecord Link]'';
';

/* Adding the article's partition column(s) */
SET @SQL = @SQL + '
exec sp_articlecolumn @publication = N''' + @PUBLICATIONNAME + ''', @article = N''Record Link'', @column = N''timestamp'', @operation = N''add'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;
exec sp_articlecolumn @publication = N''' + @PUBLICATIONNAME + ''', @article = N''Record Link'', @column = N''Link ID'', @operation = N''add'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;
exec sp_articlecolumn @publication = N''' + @PUBLICATIONNAME + ''', @article = N''Record Link'', @column = N''Company'', @operation = N''add'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;
SET @SQL = @SQL + '
exec sp_articleview @publication = N''' + @PUBLICATIONNAME + ''', @article = N''Record Link'', @view_name = N''SYNC_Record Link_1__73'', @filter_clause = N'''', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1;[/color][/color][/color]

bbrown

Turn off the IDENTITY feature for the subscriber's column. Or don't replicated that field and allow the subscriber to generate its own values. Depends on whether or not you need the column values to match between systems.

LovelyLady

I do require to replicate the Id column. How to set the identity to off.
The TSQL set identity OFF does not works as, then next time table is replicated it has the definition of having identity set to true.
how can i set the identity false in the replicated table ?? The Nav table has Id as identity=true.

bbrown

Don't create the column as INDENTITY in the subscriber table. Just create it with the same data type and replicate the values.

LovelyLady

The subscriber table is copying the articles from the nav table where the id is set to identity column. how can i change the id as not identity in the subscriber. it picks up the definition from the publisher i.e. the nav table.
How can I make it a non identity in the subscriber????

bbrown

Manually create the subscriber table. Then setup to replication. What replication type are you using?

LovelyLady

I am using trans replication.
what do you mean by creating the table manually.
The next time I generate a new snapshot, it will delete the table and copy the definition of id field from the Nav database i.e. the publisher.
Please correct me if I am wrong and explain .

FDickschat

Not sure it helps or not. This is from the script I use to copy the contents of DBLive to DBTraining (It is actually coming from this tool http://mibuso.com/blogs/ara3n/2009/12/08/baking-uptransfering-company-specific-data-through-sql-for-dynamics-nav):

DELETE FROM [DBTraining].[dbo].[Record Link]

IF EXISTS (SELECT * from [DBTraining].[dbo].syscolumns where id = Object_ID('[DBTraining].[dbo].[Record Link]') and colstat & 1 = 1) BEGIN
  SET IDENTITY_INSERT [DBTraining].[dbo].[Record Link] ON
END

INSERT INTO [DBTraining].[dbo].[Record Link]
(
    [Link ID]
   ,[Record ID]
   ,[URL1]
   ,[URL2]
   ,[URL3]
   ,[URL4]
   ,[Description]
   ,[Type]
   ,[Note]
   ,[Created]
   ,[User ID]
   ,[Company]
   ,[Notify]
   ,[To User ID]
)
SELECT 
    [Link ID]
   ,[Record ID]
   ,[URL1]
   ,[URL2]
   ,[URL3]
   ,[URL4]
   ,[Description]
   ,[Type]
   ,[Note]
   ,[Created]
   ,[User ID]
   ,[Company]
   ,[Notify]
   ,[To User ID]
FROM [DBLive].[dbo].[Record Link]

IF EXISTS (SELECT * from [DBTraining].[dbo].syscolumns where id = Object_ID('[DBTraining].[dbo].[Record Link]') and colstat & 1 = 1) BEGIN
  SET IDENTITY_INSERT [DBTraining].[dbo].[Record Link] OFF
END