Replication - How I Update Only Tabelstructure Without The Tabledata?

Mar 12, 2003

Hi,
I use a Snapshot Replication. I want to update the tablestructure (add a column or delete a relation), but without
update the data in the table.

I use the following Properties:
- Article - propertie: "Delete Data in the existing table that matches the row filter statement"
- Row - Filter: "... WHERE id < 0" (no rows returns with this condition)

I create an Initial Snapshot and apply it to my Subscriber, but the table - structur dont change.

Has the Replication a other way to do this?

Ch. Reinke

View 5 Replies


ADVERTISEMENT

Backup Without Tabledata

Aug 2, 2006

Hello.

I am using SQL express and we have developed our app. til now.

Now we have to move the database to the another server and go live. It is fine just to backup the database but how to do it without tabledata? We have many test posts there and when going live we would like to start from beginning.

Is there any way to do it or do we have to delete all data before backing up?

View 3 Replies View Related

Merge Replication &&amp; Update On Keys

Feb 21, 2006

Hi, I'am using several SQL 2000 servers with different working merge-replications. Actually I wrote a .NET assembly which makes updates to key values in one of the replicated tables:

Ex:
update ReplTable set key = -key where key between (1 and 100)

Loop
......
update ReplTable set key = NewKey where key = ....
end of loop

Intention is to resort the datarows. So I made two updates to every keyvalue in a very short time. At the ConflictViewer I got messages like:

"The row was inserted at 'ServerX' but could not be inserted at 'ServerY'. Violation of PRIMARY KEY constraint 'PK_xxxx. Cannot insert duplicate key in object 'ReplTable'. "

The replication did only deliver one (the last) update - this makes the violation. Is there a possibility to make the mergeagent known that I wanted to deliver every transaction? Is there a parameter in the agents profile?



View 1 Replies View Related

Replication 2 Part Update In SQL2005?

Jul 13, 2007

SQL2000 used to send a 2 part update using sp_msdel followed by sp_msins instead of just calling sp_msupd. I thought this used to happen on tables with compostie primary keys. Does any know know if this still occurs in SQL2005 and maybe why?

View 1 Replies View Related

Transactional Replication - UPDATE (Delete/Insert)

Feb 14, 2001

Hi,

Any updates on tables at publisher are being converted to Delete/Insert pair
at subscriber end with transactional replication. Our business requirement is not to delete data from few tables and also to get rid of Foreing Key
Constraint error.

How to avoid this Deletes during Update? Any ideas about writing custom stored procs?

Thanks

View 1 Replies View Related

Update Query Is Slow With Merge Replication?

Dec 4, 2014

I have a database with enabling merge replication.

Then the problem is update query is taking more time.

But when I disable the merge triggers then it'll update quickly.

View 3 Replies View Related

SQL 2012 :: Update Query Is Slow With Merge Replication?

Dec 3, 2014

I have a database with enabling merge replication.

Then the problem is update query is taking more time.But when I disable the merge triggers then it'll update quickly.

View 1 Replies View Related

How To Create Merge Replication With Latest Update Always From Subscribers

May 13, 2006

Hi ,

I am trying to create Replication Topology (Merge Replication) like below.

Subscriber1 --> Publisher <-- Subscriber2.

I have created both subscribers with Subscription Type as Server with Priority as 75. I am updating the Column A of Row_10 in Subscriber1 on time say 11 am. After i am running the Starting synchronizing agent from Subscriber1. The value propagated to Publisher now publisher contains the latest value in Column A. Uptonow the Subscriber2 is not synchronized with Publisher.

Now in Subscriber2 also Column A of Row_10 is updated say at 11.10 am. Actually now Publisher contains the value from Subscriber1 for that Column and in Subscriber2 we have the same column updated.

Now i am running the Synchronization in Subscriber2, i am getting the result which is not expected. Here Publisher's value is propagated to Subscriber2. But as per real scenario Subscriber2 has the Latest value which is updated on 11.10 AM.

I don't know what am i missing here. Actually merge replication should see the time stamp and it has to decide winner. But here it always considers publisher as a winner and puts the data to Subscriber.

Can anyone help ?

Thanks in advance

View 4 Replies View Related

Merge Replication – Bug In Business Logic Update Handler

Nov 17, 2006

I have got a business logic update conflict handler working, but I have had to work round what appears to be a bug.

Please can someone confirm if this is indeed a bug €“ and if it is a known bug?

My conflict handler needs to take some columns from the publisher row and some from the subscriber row in the event of conflict.

I can quite happily generate a custom dataset which contains the winning row that I want €“ I can see that because I can step through the conflict handler with debug when a conflict occurs.

However, just returning ActionOnUpdateConflict.AcceptCustomConflictData from the UpdateConflictsHandler method does not set the publisher and subscriber columns correctly. I end up with different values on the two databases.

I have found that the only way to get the correct rows on both publisher and subscriber is to create a new ADO connection to the publisher and actually perform an update €“ updating all the modified columns. This now works reliably in my testing.

Fortunately, due to business rules the frequency of update conflicts are likely to be very infrequent, but I would very much like to avoid having to do the €˜unnecessary€™ update.

Notes:

I am using column level tracking €“ but I have seen the problem with row level tracking too
I have mainly been using SP1 but have repeated the test on a configuration using the SP2 CTP €“ and the problem occurs there too
The problem is not due to complex logic in my code. If the method just sets customDataSet = publisherDataSet.Copy and then returns ActionOnUpdateConflict.AcceptCustomConflictData, the changed and winning publisher values are not sent to the subscriber

Any thoughts would be much appreciated

View 5 Replies View Related

Large SQL Update - Effect On SQL 2005 Transactional Replication

Feb 1, 2007

I'm a newbie to Replication and recently setup the following.

Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.

Any feedback greatly appreciated.

Thanks

View 1 Replies View Related

Does The Actual Update Statement Of A Table Affect Merge Replication?

Dec 8, 2006

Suppose your using Merge Replication.

2 Users in 2 locations issue updates to the same table. 1 updating 1 column and the other updating another column. Now in reality the actual Stored Procedure issuing the update statement is passed in all the possible columns that could change and builds an update statement that updates all columns even the ones that havent changed.

Will this break Merge Replications conflict tracking? Or does SQL Server 2005 Merge Replication pickup that in reality the 2 updates only in reality changed the values in 2 columns.

View 1 Replies View Related

Problem : Extreme Activity - Slow Merge Replication - From A Schema Update

Jul 4, 2007

Hi there,



We have a big problem here with merge replication, specifically whenever a schema change occurs. We are replicating schema changes, and triggers/stored procedures. The example is that we changed about 150 stored procedures and about 30 triggers. This is then replicated to the subscriber database (which is also a merge publisher for further remote systems that were offline at the time) over a 10Mb link - hardly low bandwidth. However, the replication process takes about an hour and a half - considering the SQL on the primary server took less than a minute to run this is a big suprise.



We've run a trace to see if we can identitfy what is going on. There seems to be a great number of calls to sp_MSunmarkschemaobject - we are still waiting for a trace to complete to fully analyse this however it looks like it calls this repeatedly for every stored procedure in the database. We are currently re-testing to one of the remote servers with the merge agent set to the slow profile (not much hope that this will alter the poor performance).



This task looks to be excessive - and certainly does not seem to function in a sensible manner. Has anyone else had similar issues or have any suggestions. This is very infuriating as it means that the servers are effectively offline for a minimum of and hour and a half (in fact the remoter servers take over 4 hours !).



Thanks.

View 2 Replies View Related

Replication Performance Degrade In Unidirectional Direction And Lock Time Out (Update Are High Than Inserts)

Feb 21, 2007

 

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1)   One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

 

on subscriber database  response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

 

looking for any experieced solution/suggestion.

Thanks in advance. 

View 3 Replies View Related

SQL 2005 Error: Replication-Replication Distribution Subsystem: Agent (null) Failed.

Jun 15, 2007

I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.

View 3 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Adding New Table In Replication And Changing One Column Replication Database

Jan 17, 2002

Hi,

In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don

View 1 Replies View Related

DBCC OPENTRAN Shows REPLICATION On A Server That Is Not Configured For Replication

Aug 22, 2007

Hello,

I have this problem on a Production database.

DBCC OPENTRAN shows "REPLICATION" on a server that is not configured for replication. The transaction log is almost as large as the database (40GB) with a Simple recovery model. I would like to find out how the log can be truncated in such a situation.

Thank you.

View 4 Replies View Related

Identity Range Managed By Replication Is Full And Must Be Updated By A Replication Agent. Error Message Makes NO SENSE.

Mar 6, 2007

Hello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm

View 1 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

SQL Express Replication And Problems Accessing Data After Replication

Jul 28, 2006

Hi,

I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows

- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem

- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine

however.......

- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error

- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.

I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.

I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc

Any advice appreciated !

Thanks
Ronan





View 2 Replies View Related

Does Replication Affect Tempdb GROWTH Replication

Jun 28, 2007

Hi all,

I have recently setup a transactional replication in MS SQL 2000. After setting up the replication the clients TempDB grew by almost 60GB. Now the client is Blaming me for the TempDB GROWTH and saying that its because of the replication being setup i tried to convince them but they are not satisfied yet. Can anybody please tell me does replication cause the tempdb to grow. If yes then how. can u suggest any good link for getting to know the internal working of SQL Server replication????



Thanks in advance

Jacx

View 3 Replies View Related

How Do We Add A New Column To A Merge Replication Article, But Specify It As Not For Replication?

Aug 30, 2007

Hi all,

I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.

I am using SQLSERVER 2005 (SP1).

View 3 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 

SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 Replies View Related

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages

Nov 11, 2007

I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000.  I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process.   Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue.  In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. 
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically).  When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense.  There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks 
 

View 2 Replies View Related

How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?

Feb 16, 2006

Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza

View 8 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

The Folowing code is not working anymore. (500 error)

Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close

The .execute Method works fine

strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email


i use FUNCTION i get on this forum to use split from multi update

how to loop for evry update send an single eamil to evry employee ID send one email

i update like this


Code Snippet
:

DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3

now
how to send an EMAIL for evry ROW update but "personal email" to the employee



Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'


TNX

View 2 Replies View Related

DB Replication Or Table Replication Via Triggers?

Apr 17, 2007

Hello everyone,I am involved in a scenario where there is a huge (SQL Server 2005)production database containing tables that are updated multiple timesper second. End-user reports need to be generated against the data inthis database, and so the powers-that-be came to the conclusion that areporting database is necessary in order to offload report processingfrom production; of course, this means that data will have to bereplicated to the reporting database. However, we do not need all ofthe data in the production database, and perhaps a filtering criteriacan be established where only certain rows are replicated over to thereporting database as they're inserted (and possibly updated/deleted).The current though process is that the programmers designing thequeries/reports will know exactly what data they need from productionand be able to modify the replication criteria as needed. For example,programmer A might write a report where the data he needs can beexpressed in a simple replication criteria for table T where column X= "WOOD" and column Y = "MAHOGANY". Programmer B might come along amonth later and write a report whose relies on the same table T wherecolumn X = "METAL" and column Z in (12, 24, 36). Programmer B willhave to modify Programmer A's replication criteria in such a way as toaccomodate both reports, in this case something like "Copy rows fromtable T where (col X = "WOOD" and col Y = "MAHOGANY") or (col X ="METAL" and col Z in (12, 24, 36))". The example I gave is reallytrivial of course but is sufficient to give you an idea of what thecurrent thought-process is.I assume that this is a requirement that many of you may haveencountered in the past and I am wondering what solutions you wereable to come up with. Personally, I believe that the above method isprone to error (in this case the use of triggers to specifyreplication criteria) and I'd much rather use replication services tocopy tables in their entirety. However, this does not seem to be anoption in my case due to the sheer size of certain tables. Is thereanything out there that performs replication based on complexprogrammer defined criteria? Are triggers a viable alternative? Anyalternative out-of-the-box solutions?Any feedback would be appreciated.Regards!Anthony

View 11 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View 1 Replies View Related

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved