December 7, 2012 at 10:47 am
I have a publisher and a subscriber database under transactional replication. The schemas are identical. One of the tables has 2 columns (BIGINT NOT NULL and CHAR(1) NOT NULL) that i do not want to be replicated.
I've tried applying vertical partitioning by removing the 2 columns after the article has been created, however i'm getting the following error:
Error messages:
The process could not bulk copy into table '"dbo"."TestTable"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
End of file reached, terminator missing or field data incomplete
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "PortalRepTest"."dbo"."TestTable" in "\\CHEEZLYVMAIN642\ReplData\unc\CHEEZLYVMAIN64_PORTALREPTEST_BASE\20121207164942\TestTable_451.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SCHEEZLYVMAIN642 -T -w (Source: MSSQLServer, Error number: 20253)
Running the BCP manually:
SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client] Unexpected EOF encountered in BCP data-file
This doesn't occur if i don't remove the 2 columns from the article. I can see where the error message is coming from but i would expect that during an insert the default values for the 2 unreplicated columns would be taken (i have default constraints defined).
Am i missing something? What's the point of vertical partitioning if this doesn't work?
December 12, 2012 at 9:03 am
Sorry, not sure I understand your question...Are you just wanting to keep from replicating 2 of the columns, or are you trying to implement partitioning on the table as well?
Also, just to be clear...Are publisher and subscriber on 2008?
December 12, 2012 at 9:18 am
I'm trying to prevent replication of 2 of the columns. Both tables have the same schema. It works if the subscriber doesn't have these 2 columns but i need those 2 unreplicated columns on both the publisher and subscriber.
Yes SQL Server 2008.
December 13, 2012 at 4:40 pm
Sorry, for the delay...
How are you trying to filter out the columns you don't want? Did you uncheck from the article, did you apply a filter and leave all the columns? Not sure how you are doing it now?
What methods have you tried to not replicate them?
December 14, 2012 at 2:52 am
Thanks for replying. I've tried via the UI by unticking them from the article, i've tried RMO using the example here (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.replication.transarticle.removereplicatedcolumns.aspx) and the sp_articlecolumn sproc. All essentially do the same.
Previously the columns were in a separate unreplicated table but due to performance problems caused by joining 100,000's rows together and index fragmentation i'm trying to move them into the same table.
December 14, 2012 at 8:27 am
Can you post the table scripts from publisher and subscriber? Also if you can script out the publication and post that or send it to me that would help alot. Also, please make sure nothing sensitive or secure would show up in the scripts.
Thanks
December 14, 2012 at 9:29 am
I've created a test script. The error message has changed slightly:
The process could not bulk copy into table '"dbo"."TEST"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Field size too large
...but the cause of the problem is still fundamentally the same.
USE [master]
GO
/****** Object: Database [TestRepl] Script Date: 12/14/2012 16:12:57 ******/
CREATE DATABASE [TestRepl] ON PRIMARY
( NAME = N'TestRepl', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TestRepl.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestRepl_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\TestRepl_log.ldf' , SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TestRepl] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestRepl].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
USE [TestRepl]
GO
/****** Object: Table [dbo].[TEST] Script Date: 12/14/2012 16:13:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,4) NOT FOR REPLICATION NOT NULL,
[NAME] [nvarchar](50) NOT NULL,
[DESCRIPTION] [nvarchar](50) NULL,
[LOCAL] [bigint] NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TEST] ADD CONSTRAINT [DF_TEST_LOCAL] DEFAULT ((0)) FOR [LOCAL]
ALTER TABLE [dbo].[TEST] ADD CONSTRAINT [DF_TEST_msrepl_tran_version] DEFAULT (NEWID()) FOR [msrepl_tran_version]
GO
INSERT INTO dbo.TEST (NAME, DESCRIPTION, LOCAL)
VALUES ('Tester1', 'Tests things', 3)
INSERT INTO dbo.TEST (NAME, DESCRIPTION, LOCAL)
VALUES ('Tester2', 'Tests more things', 8)
INSERT INTO dbo.TEST (NAME, DESCRIPTION, LOCAL)
VALUES ('Tester3', 'Tests other things', 10)
GO
-------------------------------------------------------------------------------------------------------------------------------------
-- Create Publication
-------------------------------------------------------------------------------------------------------------------------------------
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'TestRepl', @optname = N'publish', @value = N'true'
GO
exec [TestRepl].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [TestRepl].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [TestRepl]
exec sp_addpublication @publication = N'TestPub', @description = N'Transactional publication with updatable subscriptions of database ''TestRepl'' from Publisher ''PUBLISHERBOX''.', @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, @ftp_login = N'anonymous', @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'true', @autogen_sync_procs = N'true', @allow_queued_tran = N'true', @allow_dts = N'false', @conflict_policy = N'pub wins', @centralized_conflicts = N'true', @conflict_retention = 14, @queue_type = N'sql', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'TestPub', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'TestPub', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'PUBLISHERBOX\sql_agent_user'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'WIN-LI1G3M9O0B4\SQLServer2005SQLAgentUser$WIN-LI1G3M9O0B4$MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'WIN-LI1G3M9O0B4\SQLServer2005MSSQLUser$WIN-LI1G3M9O0B4$MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'TestPub', @login = N'distributor_admin'
GO
-- Adding the transactional articles
use [TestRepl]
exec sp_addarticle @publication = N'TestPub', @article = N'TEST', @source_owner = N'dbo', @source_object = N'TEST', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'delete', @schema_option = 0x000000000003008F, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_table = N'TEST', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'true', @ins_cmd = N'CALL [dbo].[sp_MSins_dboTEST]', @del_cmd = N'VCALL [dbo].[sp_MSdel_dboTEST]', @upd_cmd = N'VCALL [dbo].[sp_MSupd_dboTEST]'
-- Adding the article's partition column(s)
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'ID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'DESCRIPTION', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'msrepl_tran_version', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
-- Adding the article synchronization object
exec sp_articleview @publication = N'TestPub', @article = N'TEST', @view_name = N'SYNC_TEST_1__54', @filter_clause = N'', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
-- Adding the transactional subscriptions
use [TestRepl]
exec sp_addsubscription @publication = N'TestPub', @subscriber = N'SUBSCRIBERBOX', @destination_db = N'TestRepl', @subscription_type = N'Pull', @sync_type = N'automatic', @article = N'all', @update_mode = N'queued failover', @subscriber_type = 0
GO
-------------------------------------------------------------------------------------------------------------------------------------
-- Create Subscription
-------------------------------------------------------------------------------------------------------------------------------------
-- Adding the transactional pull subscription
/****** Begin: Script to be run at Subscriber ******/
use [TestRepl]
exec sp_addpullsubscription @publisher = N'PUBLISHERBOX', @publication = N'TestPub', @publisher_db = N'TestRepl', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'queued failover', @immediate_sync = 1
exec sp_link_publication @publisher = N'PUBLISHERBOX', @publication = N'TestPub', @publisher_db = N'TestRepl', @security_mode = -1, @login = N'', @password = null
exec sp_addpullsubscription_agent @publisher = N'PUBLISHERBOX', @publisher_db = N'TestRepl', @publication = N'TestPub', @distributor = N'PUBLISHERBOX', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = N'', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
/****** End: Script to be run at Subscriber ******/
/****** Begin: Script to be run at Publisher ******/
/*use [TestRepl]
-- Parameter @sync_type is scripted as 'automatic', please adjust when appropriate.
exec sp_addsubscription @publication = N'TestPub', @subscriber = N'SUBSCRIBERBOX', @destination_db = N'TestRepl', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'queued failover'
*/
/****** End: Script to be run at Publisher ******/
January 8, 2013 at 7:43 am
Did anyone get anywhere with this?
January 8, 2013 at 8:16 am
sorry, had some personal things come up and was out for awhile. Will try and look at this later this evening
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply