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?
Thanks in advance,
Patrick de Jong
View 8 Replies
ADVERTISEMENT
May 28, 2004
Hello:
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()
View 3 Replies
View Related
Jul 23, 2005
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
View 11 Replies
View Related
Sep 13, 2006
Hi,
I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.
The replication share on the distributor has all the generated DDL in it.
I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error
The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)
Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?
The ddl for the table index is as follows ( from the replication folder)
/----
CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)
GO
---/
/------ Keys ddl (.dx)
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go
--------/
/------------
Command attempted:
create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@c1 int,@c2 varchar(80),@c3 char(2),@c4 char(3),@c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @c2
,"ISO 3166-1 ALPHA-2" = @c3
,"ISO 3166-1 ALPHA-3" = @c4
,"ISO 3166-1 NUMERIC-3" = @c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@c2
,@c3
,@c4
(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)
------------/
View 5 Replies
View Related
Feb 6, 2008
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.
Please advice me
thnks for the help in advance
Regards
Arvind L
View 1 Replies
View Related
Jun 25, 2004
I have three items which make up the primary key:
ProjectNumber, Hardware, Date
Problem: The Date is formatted with date and time, how can I add a record and use just the date?
Currently I use GetDate() in my stored proc.
Mike B
View 2 Replies
View Related
Jul 26, 2007
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%%.
View 9 Replies
View Related
Jan 23, 2008
Hi Jonathan Kehayias, Thanks for your valuable response.
I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.
Here is my response to the last code/instruction you gave me:
I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.
I got the following debug error message:
1) A Box appeared and said: String or binary data would be truncated.
The statement has been terminated.
|OK|
2) After I clicked on the |OK| button, the following message appeared:
This "SqlException was unhandled
String or binary data would be truncated.
The statement has been terminated."
is pointing to the "Throw" code statement in the middle of
.......................................
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
..........
Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.
Thanks,
Scott Chang
The code of my Form1.vb is listed below:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7
command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"
command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"
command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"
command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"
command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"
command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"
command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"
Dim resulting As String = command.ExecuteNonQuery
MessageBox.Show("Row inserted: " + resulting)
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
InsertNewFriend()
End Sub
End Class
View 6 Replies
View Related
Jun 19, 2008
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.
View 4 Replies
View Related
Feb 7, 2007
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??
View 1 Replies
View Related
Feb 7, 2007
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??
View 1 Replies
View Related
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
Feb 9, 2015
SQL 2012 Ent SP2
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.
View 1 Replies
View Related
Mar 4, 2007
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.
Thanks in advance
Regards
R Suresh
View 1 Replies
View Related
Mar 25, 1999
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
thanks
Ali
View 3 Replies
View Related
Apr 29, 2015
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)
View 2 Replies
View Related
Feb 28, 2008
Hi guys.
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
Thanks and looking forward.
-malik
View 7 Replies
View Related
Jun 22, 2004
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?
thanks
SV
View 2 Replies
View Related
Jul 7, 2004
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.
View 1 Replies
View Related
May 12, 2004
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 ?
View 3 Replies
View Related
Apr 9, 2007
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.
Please tell me why and how to solve it ? Thanks.
View 6 Replies
View Related
Feb 18, 2006
Hi All
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.
Thanks
View 11 Replies
View Related
Feb 25, 2006
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
Merge Replication Properties
-----------------------------
status : 1
retention : 21
sync_mode : 1
allow_push : 1
allow_pull : 1
allow_anonymous : 1
centralized_conflicts : 1
priority : 100.0
snapshot_ready : 1
publication_type : 1
enabled_for_internet : 0
dynamic_filters : 1
has_subscription : 0
snapshot_in_defaultfolder : 1
alt_snapshot_folder : NULL
Merge Agent Profile:
parameter_name value
-------------------------------- ---------
-BcpBatchSize 100000
-ChangesPerHistory 100
-DestThreads 4
-DownloadGenerationsPerBatch 500
-DownloadReadChangesPerBatch 500
-DownloadWriteChangesPerBatch 500
-FastRowCount 1
-HistoryVerboseLevel 1
-KeepAliveMessageInterval 300
-LoginTimeout 15
-MaxDownloadChanges 0
-MaxUploadChanges 0
-MetadataRetentionCleanup 1
-NumDeadlockRetries 5
-PollingInterval 60
-QueryTimeout 300
-SrcThreads 3
-StartQueueTimeout 300
-UploadGenerationsPerBatch 100
-UploadReadChangesPerBatch 100
-UploadWriteChangesPerBatch 100
-Validate 0
-ValidateInterval 60
View 3 Replies
View Related
Oct 5, 2006
Hi,
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?
Thanks,
Geoff Dupuis
View 1 Replies
View Related
Feb 5, 2003
Hello:
If I would like to do Transactional Replication - do I set the Primary Key in the Table to
Identity = 'No'
Identity = 'Yes' or
Identity = 'Yes (Not for Replication)'
Thanks,
Bianca
View 3 Replies
View Related
Apr 13, 1999
How can I replicate a table when it has NO primary key? It will not show in the Articles in Publication window in Edit Publications....
View 1 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
May 10, 2006
Hello everybody,
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
'"tblSuppContractFee"'.[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_repl_alert(3, 4, 65, 14151, ?, 6, N'01sqlft003', N'EDGE',
N'01SQLFT004',
N'EDGE_REPLICATION', ?)}
ErrorId = 65, SourceTypeId = 1
ErrorCode = ''
ErrorText = ''
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_repl_error(65,
0, 1, ?, N'', ?)}
[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.
Thanks in advance.
View 9 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
May 17, 2001
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
View 1 Replies
View Related
May 29, 2007
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.
Thanks in advance
Satish
View 2 Replies
View Related
