InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 11:36:24 GMT20http://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxsorry, had some personal things come up and was out for awhile. Will try and look at this later this eveningTue, 08 Jan 2013 08:16:51 GMTerichbrinkerhttp://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxDid anyone get anywhere with this?Tue, 08 Jan 2013 07:43:35 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxI'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_REPL20037Field 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%)GOALTER DATABASE [TestRepl] SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [TestRepl].[dbo].[sp_fulltext_database] @action = 'enable'endGOUSE [TestRepl]GO/****** Object: Table [dbo].[TEST] Script Date: 12/14/2012 16:13:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOALTER 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]GOINSERT 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 databaseuse masterexec sp_replicationdboption @dbname = N'TestRepl', @optname = N'publish', @value = N'true'GOexec [TestRepl].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1GOexec [TestRepl].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1GO-- Adding the transactional publicationuse [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'GOexec 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 = 1exec sp_grant_publication_access @publication = N'TestPub', @login = N'sa'GOexec sp_grant_publication_access @publication = N'TestPub', @login = N'NT AUTHORITY\SYSTEM'GOexec sp_grant_publication_access @publication = N'TestPub', @login = N'BUILTIN\Administrators'GOexec sp_grant_publication_access @publication = N'TestPub', @login = N'PUBLISHERBOX\sql_agent_user'GOexec sp_grant_publication_access @publication = N'TestPub', @login = N'WIN-LI1G3M9O0B4\SQLServer2005SQLAgentUser$WIN-LI1G3M9O0B4$MSSQLSERVER'GOexec sp_grant_publication_access @publication = N'TestPub', @login = N'WIN-LI1G3M9O0B4\SQLServer2005MSSQLUser$WIN-LI1G3M9O0B4$MSSQLSERVER'GOexec sp_grant_publication_access @publication = N'TestPub', @login = N'distributor_admin'GO-- Adding the transactional articlesuse [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 = 1exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'NAME', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1exec sp_articlecolumn @publication = N'TestPub', @article = N'TEST', @column = N'DESCRIPTION', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1exec 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 objectexec 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 = 1GO-- Adding the transactional subscriptionsuse [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 = 0GO--------------------------------------------------------------------------------------------------------------------------------------- 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 = 1exec sp_link_publication @publisher = N'PUBLISHERBOX', @publication = N'TestPub', @publisher_db = N'TestRepl', @security_mode = -1, @login = N'', @password = nullexec 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 = 0GO/****** 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 ******/Fri, 14 Dec 2012 09:29:33 GMTpaul.clark 83106http://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxCan 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.ThanksFri, 14 Dec 2012 08:27:33 GMTerichbrinkerhttp://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxThanks 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.Fri, 14 Dec 2012 02:52:00 GMTpaul.clark 83106http://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxSorry, 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?Thu, 13 Dec 2012 16:40:50 GMTerichbrinkerhttp://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxI'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.Wed, 12 Dec 2012 09:18:43 GMTpaul.clark 83106http://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxSorry, 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?Wed, 12 Dec 2012 09:03:21 GMTerichbrinkerhttp://www.sqlservercentral.com/Forums/Topic1394168-1549-1.aspxI 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_REPL20037End of file reached, terminator missing or field data incompleteTo 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/20253bcp "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-fileThis 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?Fri, 07 Dec 2012 10:47:06 GMTpaul.clark 83106