Replication Issue

Question

Replication Issue

simonsmithbsa

SSC-Addicted

Points: 461
More actions
September 14, 2016 at 7:33 am

#329874

I am trying to replicate a database on our live server for reporting purposes. We already have several Publications running with no issues.
I am going into Publisher Properties to enable the db for transactional replication. I check the relevant box and hit ok and get an error message :-
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
I have checked all tables, triggers etc and none of the datatypes have been used. Further to that the db was created in 2012 so would have had no access to use those datatypes. I have had a delve on google but can`t see any suggestions as to what is going on / where to look for a solution.
Anyone have any suggestions ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

Those datatypes are still available in SQL 2014 (probably SQL 2016 as well. Can't test at the moment). Just be sure that you don't have one lurking around. This query will tell you what column in which table has one of those three datatypes.

select object_name(object_id), name

from sys.columns

where system_type_id in (34, 35, 99)

This sort of thing comes up a lot with legacy databases that may have been restored from previous versions.

simonsmithbsa SSC-Addicted Points: 461 More actions · Answer 2

That query returns nothing, I have checked several times and those data types do not exist in the DB.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I don't know what's going on, but one thing that may be worth trying is instead of hitting OK, generate a script and run that, one statement at a time. That may give you a better idea of exactly where and why the error is occurring.

John

simonsmithbsa SSC-Addicted Points: 461 More actions · Answer 4

Something odd is going on as it won`t even let me script it. The script button is greyed out.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Is that because you haven't changed anything? Try disabling replication and pressing OK, then open it again, tick the box to enable, and see whether the Script button is available.

John

simonsmithbsa SSC-Addicted Points: 461 More actions · Answer 6

Script falls over here -

-- Adding the transactional publication

use [Gather]

exec sp_addpublication @publication = N'GatherReportingReplication', @description = N'Transactional publication of database ''Gather'' from Publisher ''PRODCLUSTER2''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

GO

With -

Msg 311, Level 16, State 1, Procedure UPDATE_INSERT_DELETE_trig_sysarticles, Line 9

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

Procedure UPDATE_INSERT_DELETE_trig_sysarticle - what is this ? It does not exists anywhere in my database.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

I've never heard of it, but from its name, I'd guess that it's a trigger on the sysarticles table in your published database. Does such a trigger exist? If not, perhaps it's trying to create one? You may need to do a Profiler trace or Extended Events session to find out exactly what's going on behind the scenes.

John