InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:30:23 GMT20http://www.sqlservercentral.com/Forums/Topic815527-291-1.aspxI had 3 servers 2 transactional servers and one is main server i.e., Multiple Publishers and Single SubscriberA(TRANS SERVER)B(TRANS SERVER)C(MAIN SERVER)I HAD CONFIGURED TRANSACTION REPLICATIONA TO C AND B TO CIn these servers i had taken one table(emp) for testing purposetable:empCreate table emp(empid int primary key identity(1,1),ename varchar(30),sal decimal(18,2))I had created emp table all the above 3 serversCan you suggest meHOW TO HANDLE IDENTITY VALUES WHEN TRANSACTION REPLICATION APPLIESSat, 07 Nov 2009 23:40:50 GMTsatishthotahttp://www.sqlservercentral.com/Forums/Topic814145-291-1.aspxHi EveryoneWhen i try to republish my articles sql server throws following error message.The republisher's republishing range obtained from its publisher is not large enough to allocate the specified @pub_identity_range.Does this comeup when when identity column is replicated?Does anyone have any idea?Any help would be much appreciated.Many thanks.VivekThu, 05 Nov 2009 05:21:45 GMTVivek Shuklahttp://www.sqlservercentral.com/Forums/Topic814577-291-1.aspxCan anyone offer some information about performance issues, latency...etc when using transactional replication? I realize there are many factors which affect performance and latency, but in general, what would one expect to see on the primary database by enabling transactional replication? I understand the concepts and requirements for replication however, just wanted to see if anyone had some real-world experience they could share..Our goal is to setup a secondary server to use for real time reporting.Thu, 05 Nov 2009 14:32:23 GMTCode2Solutionshttp://www.sqlservercentral.com/Forums/Topic814001-291-1.aspxI have a need to pause replication on my published database occasionally. I do not want to drop or remove transactional replication, I only want to [b]pause[/b] it momentarily and then [b]restart[/b] it with minimum number of issues. What is the easiest way of doing this? Stopping/disabling and then restarting/enabling just the distribution jobs on the publisher? Am I missing anything else that needs to be done? I have read other posts concerning this topic, but they seem vague or incomplete..Wed, 04 Nov 2009 22:24:09 GMTtalltop-969015http://www.sqlservercentral.com/Forums/Topic815113-291-1.aspxSuppose a remote distributor is used to create a snapshot on a different server. Rather than use the default local folder on the remote distributor, the snapshot is required to be placed on the original source server. The UNC of the source server is used as the location of the generated snapshot.Box 1 -> Source DBBox 2 -> Remote distributorIs the data from the source DB copied from box 1 to the distributor (box 2), and then copied back to the source DB? Or is the snapshot agent clever enough to realize the source and destination of the snapshot is on the same machine, and can therefore be done locally?Fri, 06 Nov 2009 11:35:43 GMTrsbaxter17http://www.sqlservercentral.com/Forums/Topic815089-291-1.aspxI'm currently leading a huge data migration with other departments.The idea is to design a process that would automate daily extraction of data from the remote/offsite DB2 database on a mainframe to our central SQL server 2000 database.I'm very familiar with the SQL Server Data Tranfer Services but never worked with a mainframe or DB2.I'd really appreciate ideas or input from some of you who have carried out similar initiatives.ZFri, 06 Nov 2009 10:59:58 GMTjayzeddyhttp://www.sqlservercentral.com/Forums/Topic814549-291-1.aspxI've got two servers that use transactional replication to send data to a reporting server. I've been thinking if either publisher crashed that I could point my applications to the subscriber until the publisher was rebuilt or brought back online. It's been brought to my attention that its not quite that simple. Does anybody know of any documentation on how you can turn a subscriber into a writable copy? Thu, 05 Nov 2009 13:49:16 GMTjason.lewis-831465http://www.sqlservercentral.com/Forums/Topic815003-291-1.aspxIn SQL Server 2000 I used to query the sysmergesubscription table in order to determine (within a t-sql script) if the database was a subscriber in a merge replication. The query I use was basically:IF OBJECT_ID('sysmergesubscriptions') IS NOT NULL SELECT @IsSubscriber = CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END FROM sysmergesubscriptions WHERE subid <> pubid AND status IN (0,1) AND subscriber_type IN (2,3)I have run into a problem in SQL Server 2005 in that the publisher now includes the subscriber information in its sysmergesubscription which SQL 2000 did not. So now when I run the query on the publisher it indicates it is a subscriber.Is there an easy way to determine through tsql whether a DB is a merger replication publisher, subscriber or not replicating?Thanks.Mark Fri, 06 Nov 2009 09:23:22 GMTMark Johnson-176476http://www.sqlservercentral.com/Forums/Topic814641-291-1.aspxHello! I have removed replication on the server but log space is not being reclaimed because some of the transactions are still marked for replication:select log_reuse_wait_desc,* from sys.databases --shows REPLICATIONFollowing command doesn't work because database is no longer marked for replicationEXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1Database is in SIMPLE recovery mode. Any ideas how I can remove transactions marked for replication from the log?Thanks,IgorThu, 05 Nov 2009 18:37:39 GMTIgor Marchenkohttp://www.sqlservercentral.com/Forums/Topic814609-291-1.aspxCan somebody guide me through the steps to replicate from SQL 2005 to Oracle 11g?Thanks.Thu, 05 Nov 2009 16:09:38 GMTaron.feldmanhttp://www.sqlservercentral.com/Forums/Topic814089-291-1.aspxI have setup snapshot replication form a sql 2005 to sql 2000 box. This has been running for over a year!We had a few issues with timings of various overnight batch jobs being completed before the snapshot is taken, so to ensure that all rows are copied I created a validate rows proc that counts the rows in all tables on the publisher database then counts rows after the snapshot has been applied on the subscriber - does a match count and emails misssing rows in the subscriber db..Today we have 200,000 rows less in the subscriber 'payments' table and I cannot fathom why..I have created and run the below queries which validate that the rows were created into the 32 BCP files during the snapshot, and another query that tells me that all these rows were applied to the subscriber...There are no errors anywhere - but i am missing 200,000 rows... in a very important table!any ideas..??select * from dbo.MSsnapshot_history where start_time >getdate()-1 and comments like '%Bulk copied snapshot data for article ''PAYMENTS%'order by time descandselect * from dbo.MSdistribution_history where start_time >getdate()-1 and comments like '%Bulk copied data into table ''PAYMENTS''%'order by time desc[b]snapshot history Distribution history[/b]84980 8498085570 85570105168 105168105881 105881108869 108869111851 111851116233 116233122843 122843130299 130299143746 143746154634 154634159505 159505161440 161440164830 164830165513 165513166162 166162170606 170606180374 180374193249 193249223372 223372225147 225147231200 231200237087 237087237593 237593238589 238589245009 245009252761 252761282955 282955284929 284929287993 287993312184 312184356008 356008[b]6046580 6046580[/b]Thu, 05 Nov 2009 02:56:48 GMToraculumhttp://www.sqlservercentral.com/Forums/Topic813865-291-1.aspxI am setting replication between a SQL 2005 server as a publisher and Oracle 11g as a subscriber.Connectivity to the Oracle server works fine.When the subscriber is initialized, table MSREPL7 is created in the Oracle Server.I get the following error:ORA-00932: inconsistent datatypes: expected NUMBER got BINARY (Source: MSSQL_REPL_ORACLE, Error number: 932).My guess is that the problem is when SQL tries to insert data in the MSREPL7 table.By reading some posts in Microsoft, they suggest to execute the following stored procedures:sp_MSrepl_DropDatatypeMappings and sp_MSrepl_CreateDatatypeMappings.I did that, but it didn't work.Other posts state that SQL 2005 doesn;t work with Oracle 11g.Is there somebody out there with the same problem, and who knows what to do to fix?Thanks all for your help. Wed, 04 Nov 2009 14:13:18 GMTaron.feldmanhttp://www.sqlservercentral.com/Forums/Topic811043-291-1.aspxWhen going through the replication setup wizard, I can choose Generate script at the end. The script works fine for the Publication setup, however, when I run the generated script for Subscription setup, I run into issues and the replication doesn't work. If however, I don't generate the script, but simply have the wizard start the replication itself, everything works. I would really like to automate the subscription setup with a script.The generated subscription script is:[code="sql"]-----------------BEGIN: Script to be run at Publisher 'SERVER\INSTANCE'-----------------use [PublisherDatabase]exec sp_addsubscription @publication = N'PublicationName', @subscriber = N'server\instance', @destination_db = N'SubscriberDatabase', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0exec sp_addpushsubscription_agent @publication = N'PublicationName', @subscriber = N'server\instance', @subscriber_db = N'SubscriberDatabase', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @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 = 20091028, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'GO-----------------END: Script to be run at Publisher 'FLANDERS\TESTING'-----------------[/code]When I run the above script, it completes without errors. However, the subscription never shows up in the Sql Server Management Studio Replication->Local Subscriptions tree, and the data is never replicated.Oddly however, running the query:[code="sql"]use distributionselect * from MSSubscriptions[/code]gives the result set:[code="sql"]publisher_database_id publisher_id publisher_db publication_id article_id subscriber_id subscriber_db subscription_type sync_type status subscription_seqno snapshot_seqno_flag independent_agent subscription_time loopback_detection agent_id update_mode publisher_seqno ss_cplt_seqno--------------------- ------------ -------------------------------------------------------------------------------------------------------------------------------- -------------- ----------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------- --------- ------ ---------------------------------- ------------------- ----------------- ----------------------- ------------------ ----------- ----------- ---------------------------------- ----------------------------------3 0 PublisherDatabase 3 1 -1 virtual 0 1 1 0x00000027000001230003 0 1 2009-10-29 10:41:37.540 1 10 0 0x00000027000001230003 0x000000270000012300033 0 PublisherDatabase 3 1 -2 virtual 0 1 2 0x00000027000001230003 0 1 2009-10-29 10:41:37.603 1 11 0 0x00000027000001230009 0x000000270000012300093 0 PublisherDatabase 3 1 0 SubscriberDatabase 0 1 1 0x000000270000013B0008 0 1 2009-10-29 10:54:58.140 1 12 0 0x000000270000013B0008 0x000000270000013B0008[/code]Which would seem to indicate the the subscription is present.In case it matters, this is a push only publication/subscription, and both the publisher and subscriber databases are on the same server.What else do I need to do to get subscriptions working from a sql script?Thu, 29 Oct 2009 11:27:29 GMTNathan Davis-411378http://www.sqlservercentral.com/Forums/Topic813797-291-1.aspxHi allI received a 20598 error (The row was not found at the Subscriber when applying the replicated command) during transactional replication. Per replication help article at http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&ProdVer=09.00.0758.00&EvtSrc=MSSQLServer&EvtID=20598I set the distribution agent to use -SkipErrors 20598 and the replication caught back upNow I would like to: A: remove this parameter as it does not seem like a good thing to have in place because of data inconsistencies. And B: find the offending data. Does anyone know how I can find the data? The help article suggests using tablediff, however I don't even know which table the row(s) was/were in. I suspect that this information found in the error is the key -- Command attempted:if @@trancount > 0 rollback tran(Transaction sequence number: 0x0059565300021DFE000500000000, Command ID: 1) -- but I can't seem to find how to use it to find what data was missing at my subscriber.Anyone have some guidence on how to find it?Thanks in advanceWed, 04 Nov 2009 12:16:18 GMTsean_denneyhttp://www.sqlservercentral.com/Forums/Topic812662-291-1.aspxI recently added a column to an article in transaction replication. I first stopped replication from the subscription from View Synchronization Status, then added the new column in both databases and updated the article. When I attempted to restart replication from the same place, I received a message about needing to re-initialize the snapshot, and I clicked OK without marking the checkbox to create a new snapshot. A little later I noticed that replication wasn't happening, so I repeated the process and did mark the checkbox. That worked, and the replication is working fine. Today I wanted to stop replication for another reason and went to View Synchronization Status again. The Stop button is greyed out, and when I try Start, it gives me an error that it is already running. My questions are:1. How can I get View Synchronization Status back to normal?2. What other way is there to stop replication?Mon, 02 Nov 2009 17:11:38 GMTCarol Jeanhttp://www.sqlservercentral.com/Forums/Topic812302-291-1.aspxHi,I am trying to set a merge replication with "Filter rows" and "Join Filter".my problem is that two of the tables have circular references:First table: "Process" with PK: ProcessId FK: LastStepIdSecond Table: "Step" with PK: StepId FK: ProcessIdThe first table holds foreign key to the last step on the second table.When running the replication, the synchronization completes successfully but the records are not transfered.Mon, 02 Nov 2009 04:31:38 GMTdumbolinohttp://www.sqlservercentral.com/Forums/Topic735653-291-1.aspxDear all,I have configured peer to peer transactional replication with two servers (PRI, BCK)I did the following scenario in testing:1) I have Load Balance point to PRI server, Now when PRI and BCK servers are up, The synchronization is working fine from PRI to BCK 2) when BCK server is disconnected, then make it online, the synchronization return to work good3) when PRI server disconnected the load balance switch to BCK server and theres no problem with this 4) after step (3) when PRI server is up, then when i check the synchronization it give the following error:[b]Error executing a batch of commands. Retrying individual commandsthe row was not found at the subscriber when applying the replicated command.[/b] i have traced the error using [b]browsereplcommand [/b] and i have found there is a command trying to delete record according to ID but there is no records in both databases have this ID the table that give the error contain the following trigger in both databasesset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[SysServicesLog_insert] ON [dbo].[SysServicesLog] FOR INSERT NOT FOR REPLICATIONASDeclare @ServiceType varchar(30)Select @ServiceType = ServiceTypefrom insertedDELETE FROM SysServicesLog WHERE ServiceType=@ServiceType AND SysServicesLogID NOT IN (Select TOP 500 SysServicesLogID FROM SysServicesLog Where ServiceType=@ServiceType ORDER BY LogTimeStamp desc)I don't think there is problem with the trigger because its marked (Not For Replication) but maybe !!!pls need help for this...Thanks, Tue, 16 Jun 2009 07:34:03 GMTahmad.ghazihttp://www.sqlservercentral.com/Forums/Topic811410-291-1.aspxHi All,I have configured Transaction Replication using Updatable Subscription between two sql server 2005 servers. After all transactions are replicated to subscriber I got to see trigger in publisher for one of the table. Triger name begins with sp_mssyn_update_tablenameCan anyone please tell me if system triggers are being created in above scenario ?Fri, 30 Oct 2009 02:59:25 GMTsejal p gudhkahttp://www.sqlservercentral.com/Forums/Topic808646-291-1.aspxHi EveryoneI have been informed by DBA of an organisation from whom we have purchased our product that it is a bad idea to script publication in SQL 2005. When you script up the publication in SQL2005 it doesn’t include all the necessary settings and you will find replication does not work properly.Does anyone know if there is any problem with creating publication scripts in sql server 2005?Any help will be much appreciated.Thanks in advance.VivekMon, 26 Oct 2009 06:52:34 GMTVivek Shuklahttp://www.sqlservercentral.com/Forums/Topic801085-291-1.aspxWe have a transactional publication that fell behind and semed to be hung (could not bring down latency) We decided to recreate the whole publication and subscription. While creating the subscription, I specified – Initialize immediately, with new snapshot, generate snapshot now.The state I am currently in is that when I select the publication and select “view snapshot agent status” I see  “ [100%] A snapshot of 17 article(s) was generated.”But when I select the subscription and select “view synchronization status” I see “The concurrent snapshot for publication 'pubName' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the public”It seems as if the subscription is not talking to the publication. Have you seen this before? This also happens often when we try to reinitialize a subscription.Any help is greatly appreciated.Fri, 09 Oct 2009 14:19:14 GMTsean_denneyhttp://www.sqlservercentral.com/Forums/Topic809834-291-1.aspxWe have a client that currently has a large busy database that they back up using database mirroring. As part of a reporting requirement we need to use some of the data stored in the database to run reports against but we cannot access the data directly. The client also want their data to be as up to date as possible.Is it possible for us to run transaction replication on that server to get our data while it is running database mirroring at the same time? We were planning to use a SQL 2008 database to store this data on. If this is possible would there be any issue with the SQL 2008 subscriber taking data off a SQL 2005 publisher?Wed, 28 Oct 2009 06:06:30 GMTgrowlahttp://www.sqlservercentral.com/Forums/Topic806909-291-1.aspxHello The following is the error i am encoutering.[i]2009-10-12 22:45:19.579 Agent message code 20037. The process could not bulk copyinto table '"dbo"."MY_TABLE"'.2009-10-12 22:45:19.683 Category:NULLSource: Microsoft SQL Server Native Client 10.0Number:Message: Batch send failed2009-10-12 22:45:19.716 Category:NULLSource: Microsoft SQL Server Native Client 10.0Number: 2627Message: Violation of PRIMARY KEY constraint 'MYTABLE_PKEY'. Cannot insert duplicate keyin object 'dbo.MY_TABLE'.2009-10-12 22:45:19.741 Category:NULLSource: Microsoft SQL Server Native Client 10.0Number: 20253Message: To obtain an error file with details on the errors encountered when initializingthe subscribing table, execute the bcp command that appears below. Consult the BOL formore information on the bcp utility and its supported options.2009-10-12 22:45:19.766 Category:NULLSource:Number: 20253Message: bcp "LDTST"."dbo"."MY_TABLE" in"\\025edddb1\SQLREP\unc\SRCSERVER_LDTST_PUB_LDTST\20091012145537\MY_TABLE#1.bcp"-e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SDESTSRVR -T -w[/i]I referred the following URL / website and introduced the [b]-SkipErrors[/b] option. Does not seem to be helping.Skipping Errors in Transactional Replicationhttp://msdn.microsoft.com/en-us/library/ms151331(SQL.90).aspxThe -SkipErrors Parameter[u]Also, when i created the Articles i chose the "keep existing object unchanged" option.[/u]Any help would be appreciated.ThanksWed, 21 Oct 2009 18:41:35 GMTgk-411903http://www.sqlservercentral.com/Forums/Topic804256-291-1.aspxHi!I am replicating a table from one database to another database. If I add a column to the subscriber table, will that mess up replication? It is a transaction publication and I do not want this column on the publisher table. When the publisher adds records to the subscriber table, the column value would just need to be NULL.Thanks in advance for your help!LoriFri, 16 Oct 2009 08:46:14 GMTLori Nevillehttp://www.sqlservercentral.com/Forums/Topic809960-291-1.aspxHi All,I have requirement in Replication.There are 5 different location database and these location database to be replicate to the centralized database with the identification of the data comes from which location name.Say for Example:Source DataBase LocationATable Structure -> TranId, TranDate, Amtsample Data -> 0001,10-10-2009,$299Source DataBase LocationBTable Structure -> TranId, TranDate, Amtsample Data -> 0002,10-10-2009,$399Source DataBase LocationCTable Structure -> TranId, TranDate, Amtsample Data -> 0003,10-10-2009,$199Destination DBTable Structure -> TranId, TranDate, Amt, LocationNamesample Data -> 0001,10-10-2009,$299,'Seatle'sample Data -> 0002,10-10-2009,$399,'Chicago'sample Data -> 0003,10-10-2009,$199,'LA'However, source of the database doesn't have any information about the location details and I am not allowed to change the data structure, but i can edit at the destination database.Anyone can suggest me good database design for this requirement.Thanks for any help and let me know if need more inputs.Regards,Mohan.Wed, 28 Oct 2009 08:11:59 GMTNew2SQL-343122http://www.sqlservercentral.com/Forums/Topic613590-291-1.aspx- I get the following error when I attempt to create a publication from the wizard A exception occurred while attempting to execute a tsql statement or batch Can not find procedure “” Can not find procedure “” Change the database context to “XXX” . (Microsoft sql server :2812) - I tried creating the same from the following script and got an error message some related to the above exec sp_addpublication @publication = N'database', @description = N'Transactional publication of database ''database'' from Publisher ''Server\I01''.', @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'false', @autogen_sync_procs = 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'The Error Msg 2812, Level 16, State 62, Procedure sp_MSrepl_addpublication, Line 1227Could not find stored procedure ''.- I tried removing the database from the publisher property list and got the following error The distributor has not been installed correctly, Could not disable the database for publishing , change the database context to “dabase name” error 20029” The last error message states that the Distributor DB is not setup correctly but there is no way to break the relationship between the Distributor DB and the attempted publication database(as I cant remove the publisher property from the database). I finally tried changing the system table "sysdatabases" value of "category" from 16 to 0 and that failes as well. I loged as DAC with single user mode and still failed to update the sysdatabases table( and yes I did sp_configure 'allow update',1 Reconfigure with Override) Please let me know what I can do to over come this situation Thanks in advanceThu, 04 Dec 2008 06:06:20 GMTjude.pierieshttp://www.sqlservercentral.com/Forums/Topic808852-291-1.aspxI have a server that has a database that runs on a RAM disk. When the server is rebooted, a job executes when SQL Server Agent comes back up which recreates the database that is in RAM, along with assocaited objects and replicated articles. This database publishes a few articles and subscribes to articles on remote servers (which are all on our network).In the first step of the job, subscriptions to remote servers are dropped:[code]begin tryexec MYSERVER01.mydatabase.dbo.sp_dropsubscription @publication = 'Numbers', @article = 'all', @subscriber = 'MYOTHERSERVER01', @destination_db = 'AcctNumbers'end trybegin catch print error_number() print error_message()end catch[/code]This is throwing the following error:[b]18483 [SQLSTATE 01000]Could not connect to server 'MYSERVER' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name. . [SQLSTATE 01000][/b]The job is run as 'sa'. When I manually run each step of the job in a query analyzer browser, the job executes flawlessly. It *seems* to only generate this error when I am trying to execute a stored procedure on a remote machine through the job, as it happens later on in the job when attempting to drop subscriptions on remote servers to the main server.Any ideas?Mon, 26 Oct 2009 10:48:30 GMTM Chapmanhttp://www.sqlservercentral.com/Forums/Topic808082-291-1.aspxHello,I was setting up P2P publication between instances A and B but encountered an error on the last step of setting up the P2P topology (node B's pub was set up but node A couldn't setup the subscription). After repeated retries including deleting the pubs and subs with SSMS and T-SQL (sp_droppublication, sp_removedbreplication and sp_replicationdboption), SSMS doesn't show any pubs and subs on both nodes, but Replication Monitor is showing TWO instances of the pub on node B, one with an error, the other normal. Right-clicking for the context menu and trying validate yields a publication does not exist error. It looks like some high-level metadata didn't get cleared. How can I fix this?Thank you,MattFri, 23 Oct 2009 11:22:57 GMTMatthew Zuberko-454521http://www.sqlservercentral.com/Forums/Topic806752-291-1.aspxHi, I get the following error:[code="other"]OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "Login timeout expired".OLE DB provider "SQLNCLI" for linked server "repl_distributor" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".Msg 53, Level 16, State 1, Line 0Named Pipes Provider: Could not open a connection to SQL Server [53]. [/code]When I try to configure a remote distributor on a dev server using my pc (sql server developer edition) as the distributor[code="sql"]--on distributoruse mastergoexec sp_get_distributorexec sp_adddistributor @distributor = 'mynamepc', @password = 'strongpw'exec sp_adddistributiondb 'Distribution'exec sp_adddistpublisher @publisher ='myserver',@distribution_db = 'Distribution', @working_directory ='\\myserver\ReplData'--on publisherexec sp_adddistributor 'mynamepc', @password = 'strongpw' [/code]My pc/sql server instance is configured to allow remote connections. And it's a linked server in sys.servers although the islink field is zero.Any help would be grrreatly appreciated.Wed, 21 Oct 2009 13:24:35 GMTmcerratohttp://www.sqlservercentral.com/Forums/Topic807726-291-1.aspxHi all - I am new to replication and had a question about the log reader agent for transactional replication. If I use transactional replication on demand i.e. not constabtly running the job, and was to perform an update to my publisher, then perform a transaction log backup then run the replication, would my update appear in the subscriber?My testing has indicated that it does appear in the subscriber, but this seems to be against what I have been reading as the log agent reader reads from the log itself.All input welcome.ThanksFri, 23 Oct 2009 03:56:34 GMTKwisatz78http://www.sqlservercentral.com/Forums/Topic804060-291-1.aspxI've got replication up and running now but when i check subscriber status after setting up subscription it shows me following errors."Error messages:The schema script 'dtblGlobalHisto4ec959fd_86.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)Get help: http://help/MSSQL_REPL-2147201001Cannot DROP TABLE 'dbo.dtblGlobalHistoryTemplate' because it is being referenced by object 'vwTypeHistoryTypeIsUserHistory'. (Source: MSSQLServer, Error number: 3729)Get help: http://help/3729"It seems like it is having trouble applying schema scripts to subscriber and because of this when i look at syncronization status it is showing "The server 'TESTBRANCH1\TestDB' is not a Subscriber. (.Net SqlClient Data Provider)".Any suggestions regarding this.Fri, 16 Oct 2009 04:08:07 GMTVivek Shuklahttp://www.sqlservercentral.com/Forums/Topic806528-291-1.aspxhello everyone,first of all... I'm more of a .net developer than a ms sql server expert. And right now this is a problem ;-)In our company we have serveral MS SQL 2005 database server. Some of them are organized in clusters. Most of them are not. The two sql servers of my concern are even in two different windows domains.Let's call them source and destination. Because it's a one way synchronization I need.[b]My requirement:[/b]In the source environment someone is creating a new entry into a certain table --> the corresponding table in the live database should be synchronized immediately.I had a look at the replication options sql server 2k5 is offering me but I couldn't find anything that seemed to fit. The synchronization we are talking about is obviously a push type communication. But it needs to act immediately upon inserts/updates. Not timer triggered.I hope this isn't too confusing.If I left out some important information please let me know and I'll gladly add that.tiaWed, 21 Oct 2009 08:19:37 GMTnarfhttp://www.sqlservercentral.com/Forums/Topic806930-291-1.aspxI have sql server 2008 as a publisher and sql 2000 as subscriber.SQL Server 2008 has distribution database as well. However I want to move the Distribution database and Distribution jobs to sql 2000 server (also subscriber) to shift the overhead to subscriber.Is there any way to do this without breaking current replication and preferably online. Since the replication is running persisted tables without snapshots.ThanksWed, 21 Oct 2009 21:06:20 GMTSwarndeephttp://www.sqlservercentral.com/Forums/Topic803508-291-1.aspxHi,I have a requirement to identify the last few transactions happened through replication on a specific table/article. Please help me whether it is possible to retrieve in SQL 2005Thanks in Advance.Thu, 15 Oct 2009 08:51:10 GMThaichellshttp://www.sqlservercentral.com/Forums/Topic804104-291-1.aspxHi, I have another question I got the merge replication with pull subscription to work on some some in my tables in my data base and some other tables does not work is there a reason for that? The error that I am getting is "The schema script 'Flight_3.sch' could not be propagated to the subscriber." Also in the Log FIle Viewer it say "MessageThe job failed. The Job was invoked by Schedule 3 (Replication agent schedule.). The last step to run was step 1 (Run agent.)." I have 20 tables with Merge and pull subscription 10 works and 10 of them does not work the same step I been doing for the other 10 that works. I am out of ideas is there a way I can make the other 10 work I am out of ideas please help !!!Fri, 16 Oct 2009 06:06:35 GMTDJ-ROMhttp://www.sqlservercentral.com/Forums/Topic806654-291-1.aspxDoes the distribution DB need to run on the same edition of SQL as the publisher and/or subscriber?Would EE64 at the publisher and subscriber necessitate the use of EE64 at the distributor, or would Standard Edition 64bit be sufficient? (Licensing question, I guess?)Thanks,Wed, 21 Oct 2009 10:29:05 GMTJason Miller-476791http://www.sqlservercentral.com/Forums/Topic805496-291-1.aspxHi,I have configured merge replication via FTP for the snapshot i have took snapshot successfully.but when i run synchronization ..i am getting following errorCan U plz suggest me on this..Thks in advance"The schema script 'xxx.bcp" -hORDER([ID] ASC)' could not bepropagated to the subscriber. Timed OUT errorTue, 20 Oct 2009 02:00:03 GMTSQL Dev-938873http://www.sqlservercentral.com/Forums/Topic800554-291-1.aspxHi,I have a SQL 2000 publishing to a secondary instance on SQL 2005. This SQL 2005 hosts both distribution and the replicated database.Subscription is in PUSH mode. Both publisher and distrib/subscriber are strong configurations (HP Proliant 8x3Ghz, local SAS 15KRPM, etc...). Distributor is running in continuous mode, with a profile which has all values by default except the timeout set to 36000 seconds instead of 1800, because when running against large MSrepl_commands, it fails with a timeout error. On the inbound side, I have 80 clients inserting massively into the publisher in OLTP fashion. Now the MSrepl_commands and MSrepl_transactions queues are filling way too fast for the distrib to catch up. I've set 2 counters last night on logreader cmds/sec and distrib cmds/sec. Logreader reports 300 cmds/sec and distrib 100 cmds/sec, and sometimes even reports nothing and seems to be completely inactive. The MSrepl_commands is now 12 Gb big. I would like to understand why the distributor is so much under the logreader.There are 3 SQL connections on the subscriber related to the distrib.exe: (ie under the same hostprocess)- one doing MSget_repl_commands, very few IOs/sec reported in the sys.sysprocesses physical_io column. - one tracked under profiler session and supposed to apply the transactions at the subscriber, but stucked at the begin tran doing no IO and not waiting for any resource to be freed (last_wait_type is MISC and waittime is always at 0)- one doing sp_MSadd_distribution_history and sometimes blocked for quite a long time (more than 200 seconds average) by the distribution cleanup procedure doing a: EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 120Notice that I set the min_distretention to 0 because I don't want the transactions that have been applied already to stay in the distribution database.I don't know what to do to quicken the distribution process. Is there a way to avoid blocking between the distribution cleanup and the distrib.exe ? I would try to avoid modifying parameters in the distrib agent profile because a restart of the distrib agent costs a lot (it must restart its MS_get_repl_commands from the beginning and the table is getting bigger and bigger every minute, so I fear the situation would get worse every time I would restart the distrib.exe). Thanks for sharing,David B.Fri, 09 Oct 2009 01:50:35 GMTDavid BAFFALEUFhttp://www.sqlservercentral.com/Forums/Topic804827-291-1.aspxThis is sql 2000 question in fact.Have a sql 2000 enterprise server sitting in house. As a backup we will have another server sitting outside and we need to keep it up to date with data. If in house server falls over or the line goes down, we switch to the backup server and the app will directly enter data onto there. Then when the in house server is back or the line is back up we need the in house server to get the new data and continue being the backend....Now I was thinking Merge replication would work best as this would keep the DR server up to date, and then if that one takes over it would then populate the live server once it is available once more - but I can't seem to find any recommendation of using merge for good DR. People seem happier on transactional,. but surely that method requires a good resync routine?!?!Log shipping seems a nice idea, but its only certain tables we are worried about - plus again, I'm not sure how it works when you want to turn the live server back on.Any ideas/thought?Thanks,SharkSun, 18 Oct 2009 16:46:01 GMTShark Energyhttp://www.sqlservercentral.com/Forums/Topic802732-291-1.aspxHi.My transactional replication has a problem. I was adding some items and suddenly gave problem and closed the SSMS. When he returned, got the following message: "The subscription to this publication is not active yet. No user action is required."This is a problem? The number of Undistrubuted Commands keeps increasing, but does not replicate.Thanks!!!Wed, 14 Oct 2009 07:49:32 GMTpatricio.molinahttp://www.sqlservercentral.com/Forums/Topic802937-291-1.aspxHi, I'm using SQL Server 2005 SP2.Is it possible to add a new index to a table which is being merge replicated or do you have to remove replication, add the index and then configure replication again?Thanks in advance.StephenWed, 14 Oct 2009 10:48:35 GMTStephen Burdin