Sorry for the odd format but that is what happens when you paste here! This is data from our Job History for a "Replication Distributor" Job Step Type. Here is how I interprete it: The job step started on the 19th. It restarted itself twice on the 20th. Then it restarted itself twice on the 21st. Then it restarted itself 3 times on the 22nd. And finally it restarted itself on the 23rd. Notice the progression of Retries_Attempted. When Retries_Attempted exceeds 10, the step will fail and the service will shut-down, which is what I have seen in the past. I conclude the Advanced Settings of Retry Attempts is not per "Event" but "total tries no matter how many successful retries are encountered." I thought a successful retry would reset the Retries_Attempted. But it does not. Is this correct? This implies that to keep the distributor up, we should set the Retry Attempts" setting to its max!
Status Run_Date Run_Time Run_Duration Retries_attempted
We are using a SQL Server 2000 Replication. I'm using the Merge Agent History Screen to retrive Informacion about replication sessions, is there any other screen to know exaclty which datawas replicated on each session?Or at least to know the script generated on each session?
I want to backup an important table every week in creating some history tables.
I would like to create a Dts job or script to create every week a table with the day and month in its name. ( like : [important_table_09-07] , [important_table_09-14],... )
I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables. Is there a solution for the version 2000?
When creating history tables that are appended to whenever a record isupdated, should one append the corresponding child table records to theirhistory tables at the same time (so as to have a complete set for eachedit); or should one append the child tables only when those particulartables are edited? I have been doing the latter, but I thought I'd ask forothers' opinions.Thanks!Neil
I've got a large and growing database in SQL Server 7.0. I'd like to utilize a monthly stored procedure that will search various tables for records that are older than 3 months and copy this data out to corresponding history tables. Typically, most production queries are run only on the new data, but occasionally (like at year-end, for example), we will need to run some queries on data extending back 12 months -- that is, on data in the production tables AND in the history tables. Notwithstanding the fact that I've never done stored procedures*, I would like to know if it is possible to run a query that can search for data in both production and history tables at the same time. I know this sounds like a stupid question, but I read somewhere that doing this qould require some kind of Joining function that is complex...?
Thanks, Whill96205
* Also, any good resources I could use for developing stored procedures?
For my company, we have made it a standard to create history tables and triggers for the majority of our production tables. I recently grew tired of consistently spending the time needed to create these tables and triggers so I invested some time in creating a script that would auto generate these.
We recently launched a project which required nearly 100 history tables & triggers to be created. This would have normally taken a good day or two to complete. However, with this script it took a near 10 seconds. Here are some details about the script.
The code below creates a stored procedure that receives two input parameters (@TableName & @CreateTrigger) and performs the following actions:
1) Queries system tables to retrieve table schema for @TableName parameter
2) Creates a History table ("History_" + @TableName) to mimic the original table, plus includes additional history columns.
3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table.
/************************************************************************************************************ Created By: Bryan Massey Created On: 3/11/2007 Comments: Stored proc performs the following actions: 1) Queries system tables to retrieve table schema for @TableName parameter 2) Creates a History table ("History_" + @TableName) to mimic the original table, plus include additional history columns. 3) If @CreateTrigger = 'Y' then it creates an Update/Delete trigger on the @TableName table, which is used to populate the History table. ******************************************* MODIFICATIONS ************************************************** MM/DD/YYYY - Modified By - Description of Changes ************************************************************************************************************/ CREATE PROCEDURE DBO.History_Bat_AutoGenerateHistoryTableAndTrigger @TableName VARCHAR(200), @CreateTrigger CHAR(1) = 'Y' -- optional parameter; defaults to "Y" AS
-- query system tables to get table schema SELECT CONVERT(VARCHAR(500), SP2.value) AS TableDescription, CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType, CONVERT(VARCHAR(10),SC.length) AS FieldLength, CONVERT(VARCHAR(10), SC.XPrec) AS FieldPrecision, CONVERT(VARCHAR(10), SC.XScale) AS FieldScale, CASE SC.IsNullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls FROM SysObjects SO INNER JOIN SysColumns SC ON SO.ID = SC.ID INNER JOIN SysTypes ST ON SC.xtype = ST.xtype LEFT OUTER JOIN SysProperties SP ON SC.ID = SP.ID AND SC.ColID = SP.SmallID LEFT OUTER JOIN SysProperties SP2 ON SC.ID = SP2.ID AND SP2.SmallID = 0 WHERE SO.xtype = 'u' AND SO.Name = @TableName ORDER BY SO.[name], SC.ColOrder
OPEN CurHistoryTable
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls
WHILE @@FETCH_STATUS = 0 BEGIN
-- create list of table columns IF LEN(@FieldList) = 0 BEGIN SET @FieldList = @FieldName SET @FirstField = @FieldName END ELSE BEGIN SET @FieldList = @FieldList + ', ' + @FieldName END
IF LEN(@SQLTable) = 0 BEGIN SET @SQLTable = 'CREATE TABLE [DBO].[History_' + @TableName + '] (' + @CRLF SET @SQLTable = @SQLTable + @TAB + '[History' + @FieldName + '] [INT] IDENTITY(1,1) NOT NULL,' + @CRLF END
IF UPPER(@DataType) IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'BINARY') BEGIN SET @SQLTable = @SQLTable + '(' + @FieldLength + ')' END ELSE IF UPPER(@DataType) IN ('DECIMAL', 'NUMERIC') BEGIN SET @SQLTable = @SQLTable + '(' + @Precision + ', ' + @Scale + ')' END
IF @AllowNulls = 'Y' BEGIN SET @SQLTable = @SQLTable + ' NULL' END ELSE BEGIN SET @SQLTable = @SQLTable + ' NOT NULL' END
SET @SQLTable = @SQLTable + ',' + @CRLF
FETCH NEXT FROM CurHistoryTable INTO @TableDescr, @FieldName, @DataType, @FieldLength, @Precision, @Scale, @AllowNulls END
CLOSE CurHistoryTable DEALLOCATE CurHistoryTable
-- finish history table script with standard history columns SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedOn] [DATETIME] NULL,' + @CRLF SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserID] [SMALLINT] NULL,' + @CRLF
SET @SQLTable = @SQLTable + @TAB + '[HistoryCreatedByUserName] [VARCHAR](30) NULL,' + @CRLF SET @SQLTable = @SQLTable + @TAB + '[HistoryAction] [CHAR](1) NOT NULL' + @CRLF SET @SQLTable = @SQLTable + ' )'
PRINT @SQLTable
-- execute sql script to create history table EXEC(@SQLTable)
IF @@ERROR <> 0 BEGIN PRINT '******************** ERROR CREATING HISTORY TABLE FOR TABLE: ' + @TableName + ' **************************************' RETURN -1 END
IF @CreateTrigger = 'Y' BEGIN -- create history trigger SET @SQLTrigger = '/************************************************************************************************************' + @CRLF SET @SQLTrigger = @SQLTrigger + 'Created By: ' + SUSER_SNAME() + @CRLF SET @SQLTrigger = @SQLTrigger + 'Created On: ' + @Date + @CRLF SET @SQLTrigger = @SQLTrigger + 'Comments: Auto generated trigger' + @CRLF SET @SQLTrigger = @SQLTrigger + '***********************************************************************************************/' + @CRLF SET @SQLTrigger = @SQLTrigger + 'CREATE TRIGGER [Trigger_' + @TableName + '_UpdateDelete] ON DBO.' + @TableName + @CRLF SET @SQLTrigger = @SQLTrigger + 'FOR UPDATE, DELETE' + @CRLF SET @SQLTrigger = @SQLTrigger + 'AS' + @CRLF + @CRLF SET @SQLTrigger = @SQLTrigger + 'DECLARE @Action CHAR(1)' + @CRLF + @CRLF SET @SQLTrigger = @SQLTrigger + 'IF EXISTS (SELECT ' + @FirstField + ' FROM Inserted)' + @CRLF SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''U''' + @CRLF SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF SET @SQLTrigger = @SQLTrigger + 'ELSE' + @CRLF SET @SQLTrigger = @SQLTrigger + 'BEGIN' + @CRLF SET @SQLTrigger = @SQLTrigger + @TAB + 'SET @Action = ''D''' + @CRLF SET @SQLTrigger = @SQLTrigger + 'END' + @CRLF + @CRLF SET @SQLTrigger = @SQLTrigger + 'INSERT INTO History_' + @TableName + @CRLF SET @SQLTrigger = @SQLTrigger + @TAB + '(' + @FieldList + ', HistoryCreatedOn, HistoryCreatedByUserName, HistoryAction)' + @CRLF SET @SQLTrigger = @SQLTrigger + 'SELECT ' + @FieldList + ', GETDATE(), SUSER_SNAME(), @Action' + @CRLF SET @SQLTrigger = @SQLTrigger + 'FROM DELETED'
--PRINT @SQLTrigger
-- execute sql script to create update/delete trigger EXEC(@SQLTrigger)
IF @@ERROR <> 0 BEGIN PRINT '******************** ERROR CREATING HISTORY TRIGGER FOR TABLE: ' + @TableName + ' **************************************' RETURN -1 END
Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication, -stop the subscription, -make our modifications to the publisher, -then create a backup of the database, -push the backup to all of the subscribers, -restore the database to the subscriber, -reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot
the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.
this is incredibly cumbersome and time consuming and i know there must be a better way to do this.
i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?
I have some rogue replication conflict tables that I can't delete because they are system tables. They are names aonflict_<tablename> and bonflict_<tablename>. They are definitely not needed anymore. How can I delete them ? Does anyone know how to delete a system table ?
i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites. i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites. this 4 table have forignkey between themselves and other15 tables i planed to transactional replication but i cant becuse forign keys occure erros if needed i can send my DB digram to you.
I have setup a transactional replication from oracle to MSSQL2005. One of the publication includes 26 articles but I found that there are only 24 articles replicated to the subscriber DB. I found that the repldata folder includes only files for 24 articles only. Besides, I double check the publication properties, totally, there 26 articles. Please advise. Thanks in advise.
Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***
Hi, eveyone.. I have a problem. One of developer deleted Merge Replication Job. However there is no doc for that replication. What I want to know is like this: which tables are in that replication? Is there any system or user tables track down these replication as well as configuration in that replication?
A set of tables are published for replication, when I have to unpublish the tables in order to update their structures (i.e. drop and recreate), an error comes up to say that the tables are still marked for replication. I check sysarticles and no tables are listed and even if I completely take the database off of replication, still there are problems. I have looked at the stored procedures that publish/unpublish the selected articles and see that a bit in sysobjects.category is set/unset by a constant of 32. But when unpublishing, it is like this does not work. I have to go into the sysobjects table renumber the category number and try again to drop/recreate and still the problem occurs, so I have to do this once more and then it will work. VERY FRUSTRATING and TIME CONSUMING. I find it hard to believe that this is how to maintain this. Does anyone have any ideas? I unpublish the articles through the replication interface, so it should work.
HiI tried posting this query in microsoft.public.sqlserver.programming but gotno response.I am new to replication but I am trying to setup up a transactionalreplication of tables from one database to another in MSSQL 2000 (SP2).My target tables have primary keys defined. Under publication properties Igo to the snapshot tab and for each table I clear the check box that says"Drop the existing table and re-create-it" and "clustered Indexes." On thispage the nothing is checked. for each table.Whenever the subscription is reinitialized it drops the primary keys on mytarget tablesand replaces them with a unique clustered index on the column that used tobethe Primary key.Is this normal? Is there anyway to stop it from doing this?I don't plan to send the snapshot more than once and let transactionalreplication take over for keeping my source and target in sync, but if Iever have to reinitialize the subscription, it would seem that I (orsomeone) willhave to take the a second step of manually dropping these clustered indexesand recreating the primary keys on the target table.Thanks in advance.---Dick Christoph---Dick ChristophJoin Bytes!612-724-9282
Does anyone know if there's an SQL command i can run that will listthe tables in a database that have an identity column set to NOT FORREPLICATION?Many thanksDan Williams.
There is an error in one of my merge publication. The error is,
The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.
When i checked my tables I got following values in rowguid column
I am using SQL 2012 SE and implementing transactional replication. I need to insert the rows from publisher database tables to new tables, drop the old tables and rename the new tables with the old table names.
For example:
Publisher database tables that are being replicated:
Table1 Table2 Table3
and I am going to create new tables in publisher database
Drop constraints from and then tables (does this require articles to be removed from replication?)
Table1 Table2 Table3
Rename
Table1_new to Table1 Table2_new to Table2 Table3_new to Table3
Does this require replication to set up from scratch or add the three articles only to replication? Is there a way this can be done without pausing or reinitializing replication or without removing articles and adding them back?
Apparently the dependencies are not calculated correctly because I'm using a temporary table.
My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.
Do you have any idea on how I can force the dependencies to be calculated correctly ?
We have merge replication running with anamous subscribers We have generested lots of views tables and stored procedures like sp_ins_C435D35DDEC04FE2517CCD52A9024EC4
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):
Code Snippet
CREATE VIEW [dbo].[ViewREPL_PublishedObjects]
AS
SELECT TOP 100 PERCENT
CASE [xtype]
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE NULL END AS [Object Type],
[name] AS [Object Name],
CASE WHEN [replinfo] = 0
THEN 0 ELSE 1
END AS [Replicated]
FROM [sysobjects]
WHERE
[xtype] in ('U', 'V')
AND [status] > 0
ORDER BY
(CASE [xtype]
WHEN 'U' THEN 1
WHEN 'V' THEN 2
ELSE 10
END),
[name]
Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].
So, I need a view with same functionality, that will work under SQL-2005 and 2008.
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.
The 'view job history' on Enterprise Manager is showing me only 4 or 5 run histories. I want to see more history on each of the jobs.Even I changed the limit to 10000 lones from 1000 lines on the server properties in the job system option, I am still seeing only the last 4 or 5 histories for each job(there are a total of 70 jobs on the server).Any ideas??Thanks. Reddy.
I have set up a job as sa.After that I changed the sa password.Now I am not able to view the job history since then.Do i need to delete the job and reschedule it?
