Is Is Necessary For A Table Which Will Be Part Of The Replication Process To Have A And Primary Key Defined On It?
May 17, 2007
Hi there,
We're going to use replication on our database. There are tables without any primary key.
Is a primary key necessary for replication?
I have also antoher question: We want to replicate the whole OLTP database (size 30 GB).
Is snapshot replication a suitable mechanisme for this? I thougt snapshot replication overwrites all of the data, not only changed data. Or should we use another replication method?
I have a database table where a primary key is defined. When I enter data that is the same as another table, it does not allow and throws an error which I do want. What happens is that a Server Error in Application error type is thrown with the following message:
Violation of PRIMARY KEY constraint 'PK_cs_sc'. Cannot insert duplicate key in object 'cs_sc'. The statement has been terminated.
How can I detect the error in my own asp.net page code so that it does not forward my users to the asp.net server error page? Heres my code below... how can I check for success or failure within this code?
Dim conSqlConnect As SqlConnection Dim strInsert As String Dim cmdInsert As Sqlcommand conSqlConnect = New SqlConnection( "Server=localhost;uid=var;pwd=var;database=var" ) strInsert = "Insert cs_sc ( [name] ) Values ( @name )" cmdInsert = New SqlCommand ( strInsert, conSqlConnect ) conSqlConnect.Open() cmdInsert.ExecuteNonQuery() conSqlConnect.Close()
Hello,I want to delete duplicate rows in a table when no primary key isdefined.For eg: If we have table1 with data as below,Suma 23 100Suma 23 100I want to delete a row from this table and retain only one row.I tried deleting self joins and exists operator. But it is deletingboth the rows. I want to retain one row.Can anybody help me out.Thanks in advance,Suma--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict221110.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520
Hi We are planning to setup log shipping model being setup between two sqlserver 2005 enterprise edition. Our transaction log backup sizes are not consistent through out the day.The following is the scenario.
T1 at 8.00 -- 315MB T2 at 8.30 -- 152MB T3 at 9.00 -- 2.5GB T4 at 10.00 -- 500MB
The bandwidth available is 25MB/minute. We are taking the backups of Tlogs at 30mins interval.Now at 'T3' log transfer we want to zip the file on the primary server,copy the file over the WAN and unzip it standby server. Now i wanted to know whether to perform this kind of action(Zip,copy,unzip), can i create a separate job on primary ?? which will put the unzipped file on standby and allow the log shipping job to restore the log file (T3) Also i wish to carry my T3,T4 etc backup -- copy-- restores through my Log shipping activity. I cannot take 15mins backup on primary server to reduce the transaction log backup size.Thts another constraint i have. Also tell me how to zip and unzip thru command line if u hve any link.
On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?
Error: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.
Thanks everyone for being helpful. I am new to SQL Server and don't have many coworkers so I am relying heavily on this forum's help.
It seems the previous poster on this issue was able to work it out but I am still not able to get it to work. I have tried using the server URL, the server machine name, the ipaddress with alias, but still cannot connect with the replication over vpn. Any ideas?
Also, how can I set the subscriber from the server? How do I refer to the subscriber machine? Let's say it is my pc that I want to make changes in and have them replicate to the server? When I specify a pull subscription I am not show how to refer to my computer.
I setup replication (merge) for a test db with 11 tables. Deleted the publication and want to delete the database itself. Sql server says it can't be deleted becuase it's has replication setup! Bug??
I had a publication (merge) setup for a database. Deleted the publication and tried to delete the database. Sql server says it can't be deleted because it's has replication setup. Bug??
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?
Database is in simple recovery mode, and published with transaction replication push subscription, just one subscriber but the database is huge. I don't want to overwrite the schema at the subscriber either.
I had to run an alter database command on a published database, it created so many logs that an extra drive had to be added along with an extra log file to accommodate all the logs.
The problem I have is I'd like to know clear the file of logs so I can drop the temporary log file, and give the drive back, but I cannot.
I have tried dbcc shrinkfile with the emptyfile option but it never clears, I have also tried it with notruncate and truncateonly options (mainly out of desperation).
I do not need to worry about point in time restore as a full backup is taken before and after the operation. After which the database will be put back into Full recovery mode.
I have looked at log_reuse_wait_desc and the file says 'Replication', so I am now thinking the file cannot empty because replication is keeping one of the VLFs active. I tried dropping and recreating the subscription hoping it might free something up and I could get somewhere, but it made no difference.
Do I have to remove replication completely to get round this? Surely not.
I have also tried putting the database back into full recovery mode, doing a full DB backup, and a transaction log backup, but its made no difference, which is also what makes me think a portion of the log is still active because of replication, and perhaps the transactions have not gone through to the subscriber, which raises another question, why not?
I have not tried restarting SQL server, as I'd like to know a way out of this without having to do that, plus I do not think it would make any difference anyway.
I am about to apply DBCC DBREINDEX to a large database that is part of transactional replication and synchronised every 3 minutes. What are the likely implication and what precautions I must take.
hi, I have not done any replication process before . but I am trying to learn it . so I have the sql server reside in a window NT4.0 (first NT server )and I am using window workstation which has a client utility to manage the database. What I am thinking to do is to have a nother pc that has a window NT4.0 (second NT server) and sql server 6.5 installed. so what is my question.
Can I control the replication process from the workstation by using the utility tools to replicate the database from the first NT server ( source database ) into the second NT server
The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011).The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {001317bf:0000f736:0008}. (Source:
MSSQLServer, Error number: 18768).The process could not set the last distributed transaction. (Source: MSSQL_REPL, The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
i had configured a merge replication betwen 2 server and i was running fine. but due to network problems my network admin has changed the ip address and name of the publisher, distributor and subscriber.
i just want to delete all the publication and subscription.. but i cannt' do this.. i m getting some sql server error.. is there any command that i can execute to reset the replication process that will delete all the publication and subcription. and then i create a new publication and subscription
Hi, I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min. It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation. Also, Whether to use row-level or column-level tracking?
I am getting this error on replication and don't know why. Any ideas?
The process could not read file '\NJRARSVR00E9d$sqldatasystemMSSQL$P001ReplD atauncNJRARSVR00E9$P001_PTR_PTR20040707104224s napshot.pre' due to OS error 5.
One of our users tried to set replication solution in a sqlserver (the idea was given up). SQL Server added in each table a column related to replication. We want to remove theses columns and I used the following script :
select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO' +'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO' from sysconstraints where object_name(constid) like '%msrep%'
Question: 1. I want to know how to introduce a carraige return in order to have some thing like this :
... ALTER TABLE dbo.T_CommandCopyFile DROP CONSTRAINT DF__T_Command__msrep__44AB0736 GO ALTER TABLE dbo.T_CommandCopyFile DROP COLUMN msrepl_tran_version ... 2. Is there any other solution to do this more simply ?
Hi.everyone.I use two computer in workgroup.I use A to be a publisher and distributor ,B to a Subscrible.When i create a snop in A computer and Create snopshot in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL epldataunc",when I use "view Synchronization Status" in B computer, the show "job already success start",but in A computer sqlserver error log show "Message Replication-: agent LIUQINASERVER2005-TestTran-Tran-LIUQIANHOMEHOME-6 failed. The process could not read file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLReplDatauncLIUQINA$SERVER2005_TESTTRAN_TRAN20070409150285ame_2.pre' due to OS error 3.". The publisher snapshot agent is servernameadministrator.
I find a lot of message in internet ,but no one can help me .I try use same account and password in two computer.but no help.
I am getting this error during Merge Replication "Unable to synchronize the row because the row was updated by a different process outside of replication."
I could not reproduce this error in my development enviornment, can anyone tell me what is cause of the above error and how do I reproduce this error in my development machine? I am able to get which is outside process?
The Conflict Type is 10 for above error which is logged into Conflict Table.
Everyday between 18:00 and 20:00 nearly 1000 PDA Subsriber anonymously synchronise via Merge Replication and at least two time he have the error :
IIS Worker Process Faulting application w3wp.exe, version 6.0.3790.1830, faulting module sscerp20.dll, version 2.0.7331.0, fault address 0x000110f4.
And subscriber which synchronising meanwhile becomes suspect.
Can someone offer a suggestion as to the cause of and correction for this error?
Thanks,
Hakan G
Here is some details about our system:
Client Side OS: Windows Mobile 2003 4.21.1088 DB: SQL CE 2.0 Microsoft SQL Server CE (ssce20.dll) 2.00.4415.0 Microsoft SQL Server CE Client Agent (ssceca20.dll) 2.00.4415.0 Development Tools: VB.NET 2003 Service Pack: .NET Compact Framework 1.0 SP3
Server Side OS: Microsoft 2003 SP1 Internet Information Services (INETINFO.EXE) 6.0.3790.1830 (srv03_sp1_rtm.050324-1447) IIS Worker Process (w3wp.exe) 6.0.3790.1830 (srv03_sp1_rtm.050324-1447) HW:IBM XSERIES_346 Intel(R) Xeon(TM) CPU 3.60GHZ (2CPU) 5,00 GB RAM DB: SQL CE 2.0 DB:SQL Server Standart Edition 8.00.2039(SP4)
SQL CE Server 2.0 Microsoft SQL Server CE Server Agent (sscesa20.dll) 2.00.7331.0 Microsoft SQL Server CE Replication Provider (sscerp20.dll) 2.00.7331.0
I need information on adjusting the priority of the replication thread(s) within SQL Server 2000. All I've been able to find is how-to's on adjusting the priority of the SQL Server process itself, as well as information on how to adjust the relative priority of changes made to the database by subscribers.
Here's the situation, from time to time our replication system goes down, it starts itself up after waiting 5 minutes but this causes a problem. When its starting up it seems to be taking up far too many CPU cycles. As such the server is unable to process SQL queries, which forces its subscribers to time out. This is a bad thing as the subscribers are control systems on an assembly line. What I want to do is lower the priority of replication so that the server can still process queries while the replication agent is restarting itself. Is this possible? If so how can it be done?
I'm trying to setup transaction replication between 2 servers. This is a one-way replication: Server A to Server B, not Server B to Server A.
I am able to replicate all the tables except one. I added commands to the agent so that it would create an output file, possibly with more or better information.
Here is a portion of the error causing the failure
Agent message code 20037. The process could not bulk copy into table '"tblSuppContractFee"'. [5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01, 0x01)} Adding alert to msdb..sysreplicationalerts: ErrorId = 65, Transaction Seqno = 000075400000ff9b000b00000002, Command ID = 6 Message: Replication-Replication Distribution Subsystem: agent 01sqlft003-EDGE-01SQLFT004-4 failed. The process could not bulk copy into table
[5/5/2006 8:02:10 PM]01SQLFT004.EDGE_REPLICATION: exec dbo.sp_MSupdatelastsyncinfo N'01sqlft003',N'EDGE', N'', 0, 6, N'The process could not bulk copy into table ''"tblSuppContractFee"''.'
Can somebody help me in finding a solution for this error? I don't see any Error Text and there are no resources available for the error code throwing up in the log file.
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
Hi, Just a continuation to my earlier queries on replication. I have a db which I want to replicate - it do not have pk? I do not want to create pk on existing columns - so I thought of creating one more column in all the tables and make them pk . Any one has any idea if this will work fine or may give any problem which I should be prepare for. Any thought appreciated - pvd
Hi , Plz help me. The scenario is as follows I have a db on sql server 2005.I have replicated the db on sql server 2000.All the data counts seems to be correct.But the online websites are not able to execute queries generating error(Microsoft OLE DB Provider for sql server error '80040e21'.Multiple step OLE DB operation generated errors.Check each OLE DB status value,if available.No work was done.)Same web aspcode is working for other db (which were replicated from sql server 2000). Also one more thing that was observed is as follows: 1)Prmary key was replicated for some of the tables.When primary keys were removed manually report executed fine.
