September 14, 2016 at 7:33 am
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 ?
September 14, 2016 at 12:00 pm
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.
September 15, 2016 at 2:11 am
That query returns nothing, I have checked several times and those data types do not exist in the DB.
September 15, 2016 at 2:19 am
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
September 15, 2016 at 2:55 am
Something odd is going on as it won`t even let me script it. The script button is greyed out.
September 15, 2016 at 3:01 am
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
September 15, 2016 at 4:04 am
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.
September 15, 2016 at 4:38 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply