Primary Key Columns Issues In Replication
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
ADVERTISEMENT
Sep 16, 2006
Hi,
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
Any help would be appreciated.
Thanks,
-Niraj
View 1 Replies
View Related
Dec 7, 2001
Hi Guys,
When I create a table with a composit primary key with 18columns. I get this error
"Cannot specify more than 16 column names for index key list. 17 specified."
I really have to have these columns as primary key.
Is there any workaround to create this table;
Thanks
View 3 Replies
View Related
Apr 15, 2007
How can I create a primary key on two columns? I could not find any sensible code sample or description how it should be done. It is clear from MSDN that it is possible but there are no examples.
There is a textbox "Included Columns" in one of the tools for setting primary keys for one column but it does not allow me to enter anything.
Thanks.
View 3 Replies
View Related
Jan 12, 2007
Hello all. I need to extract the column names that form the primary key group on a table in SQL Server. I have a table called Account and it contains ten columns. The primary key consists of two columns - MasterAccountID, AccountID. This primary key is a unique constraint and is clustered (it acts as an index as well as a primary key group). I have tried the following to no avail:exec sp_pkeys Account -> returns no rowsexec sp_helpindex Account -> throws an error stating that the object 'Account' does not existIf I run the following SQL statement, I can see all of the PK_* constraints in the database, so I know they are there:select * from information_schema.table_constraintswhere constraInt_type IN ('PRIMARY KEY','FOREIGN KEY') Again, I need to be able to specify a table name and have it return the columns (don't care if it returns extra fields) that make up the primary key fields for that table. Thanks!
View 2 Replies
View Related
Jan 5, 2006
I want to get the Primary Key Columns in Arrays by sending a tablename. I am using SQL Server 2000 and I want to make a find utility in VB.net whichwill work for all the forms; I have tables with one Primary key and some tables with composite Primary keys. I used to do this in VB 6 by making a function which fills the Primary Keys inList Box (I require to fill in list box), now I need to get in array. Can some one tell me the migration of the following VB 6 Code? This was written for the MS Access, I need same for SQL Server, I cannot find Table Def and Index Object in VB.net 2003. Public Sub GetFieldsFromDatabase (ldbDatabase As Database, lsTableName AsString) Dim lttabDef As TableDef Dim liCounter As Integer Dim liLoop As Integer Dim idxLoop As Index Dim fldLoop As Field
With ldbDatabase For Each lttabDef In .TableDefs If lttabDef.Name = lsTableName Then liCounter = lttabDef.Fields.Count For liLoop = 0 To liCounter - 1 cboFieldLists.List(liLoop) = lttabDef.Fields(liLoop).Name Next liLoop For Each idxLoop In lttabDef.Indexes With idxLoop lblIndexName = .Name If .Primary Then liCounter = 0 For Each fldLoop In .Fields cboPrimaryKeys.List(liCounter) = fldLoop.Name liCounter = liCounter + 1 Next fldLoop End If End With Next cboFieldLists.ListIndex = 0 If cboPrimaryKeys.ListCount > 0 Then cboPrimaryKeys.ListIndex = 0 End If Exit For End If Next End WithEnd Sub
View 2 Replies
View Related
Oct 17, 2005
I am currently undertaking a review of the primary keys in a SQL Server2000 database with a view to improving performance of queries.I have heard that, in the case of compound primary keys, it isimportant to select the correct order for the columns within the key.For instance, imagine a table called OrderLine which has primary keycolumns as follows-LedgerOrderNumberOrderLineNumberThe theory I have heard is that columns with the most distinct valuesshould come first. In this case, Ledger is likely to have a maximum of6 distinct values, OrderNumber a maximum of 10 million and OrderLine upto 99. Based on this supposition I believe the best order would be-OrderNumberOrderLineNumberLedgerI have performed a few rudimentary tests that appear to bear this out.I was wondering if anyone else has tried something similar and if sowhat was the result?Thanks,Ross
View 14 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
Feb 23, 2006
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
View 5 Replies
View Related
Jun 12, 2006
Hi everyone.
I have two tables: the catalog table and the detail table.
The two tables are joined by a two-columns key.
I want to make a Delete sentence for delete all the rows in the Catalog table that aren't in the detail table, in SQL Server.
The only problem is that the key is composed by two colums.
If the Key was maded of one column, that would be easy, like this:
DELETE FROM CATALOG
WHERE CATALOGKEY NOT IN (SELECT CATALOGKEY FROM DETAIL)
But it is possible to make a delete sentence if the key has two columns ?
Thanks
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 23, 2015
Can a Primary Key column also be a Identity column? The reason I am asking this question is because I have created a table and each time I insert data into the Address Table I am also inserting the AddressID, how do I get the Primary Key (AddressID column) to self generate ID values.
View 9 Replies
View Related
Jul 31, 2007
Hello!
I got a problem with merge replication. I got a central sql server 2005 database on which i got a publication. Also there are 2 sql servers CE which are subscribers to that publication. I need to add some records on both mobile servers indepently but i'm using primary key as a user id in one table.
So when i add a user on one PDA i use next available number in column ID. In the same time I add a user on other PDA with the same ID, because I don't know that there is such user with the same ID.
Ok then I do synchronization. First PDA synchronizes with server but second tells me that there is a record with the same PK. And my question is. Can these be resolved writing a custom resolver or maybe you know others resolutions, because I think that is a typicall problem but couldn't find any solution other than using ie. HOST_NAME() function.
Thanks for help!
MZ
View 1 Replies
View Related
Jul 26, 2007
I have a Merge Replication Problem and I'm not sure how to proceed.
Given the following:
Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue
The replication is configured to resolve to the greater integer value.
Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)
One of the inserts will get to the Central Server first.
The other will form a conflict.
A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.
So now I have two different value fields and a conflict that cannot be resolved.
Is this fixable?
Do I need to make changes at athe Architechture level?
View 2 Replies
View Related
Sep 24, 2014
I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.
View 0 Replies
View Related
Apr 17, 2014
how to find all primary key columns & foreign key columns in all database tables?
View 1 Replies
View Related
Jun 7, 1999
I have a publication that has been working great for over eight months. I have on occassion had to stop publishing and stop subscribing in normal operation without problems.
Yesterday, I ran into a problem that I am not sure how to solve.
I keep getting a distribution task failure "Violation of Primary Key constraint 'aaaashipm_pk'. Attempt to insert duplicate key in object shipm failed while bulk copying into shipm
Anyone run across this or have any suggestions -- do I have a transaction log problem, distribution data base/log problem. I have checked the identity column to make sure its not out of sync, run a check on the table to make sure its not corrupted. I have a couple of other things I am trying but was curious if anyone else had come across this?
Will let you know if I find a solution but it doesn't look good.
View 1 Replies
View Related
Jul 23, 2007
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.
What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e
On Server 1 - Values start from 1 then 2,3,4 etc etc
Server 2 - 24001, 24002, 24003 etc etc
Server 3 - 46001, 46002, 46003 etc etc
Server 4 - 68001, 68002, 68003
My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?
Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.
cheers
View 1 Replies
View Related
Oct 22, 2007
Hi, everyone,
I have problems to setup peer-to-peer replication. After I setup, I have an error message as below.
Violation of PRIMARY KEY constraint 'XPKSYS_SRTY_ADT'. Cannot insert duplicate key in object 'dbo.SYS_SRTY_ADT'. (Source: MSSQLServer, Error number: 2627)
Event ID 14151 in vent viewer.
Does anyone have a solution to resolve the problem?
Thanks a lot.
View 3 Replies
View Related
Jul 23, 2005
How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you
View 1 Replies
View Related
May 16, 2007
Hi All
I am a newbie to replication. I just want to know Is there any way to publish tables (articles) without primary keys in transactional replication.
Early Thanks,
Salman Shehbaz.
View 1 Replies
View Related
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
View Related
Oct 2, 2015
I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
View 2 Replies
View Related
Jul 7, 2006
I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?
View 2 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
Aug 17, 2006
Dear all,
In my current database design, there is one table (PState) which has a Primary Key (int) and a few other fields.
During development, a pattern started to arise; for certain rows in PState, I wanted to specify an additional set of columns (over 10 of them with quite large lengths) for each row in PState. However, as these additional columns would only be required in approximately 20% of the rows of PState, there would be plenty of NULL values in PState if I would make this table wider than necessary. So, I decided to create a separate table with those optional columns (PStateWFI). In order to attach these additional columns in PStateWFI to PState in the cases they were needed, I would obviously have to create a Foreign Key constraint on the Primary Key of PStateWFI so that these optional rows would know which row in PState they would belong to.
However, the problem with this approach is that one could define multiple rows in PStateWFI referring to the same row in PState, which would not make sense. Thus, a UNIQUE index constraint added to the constrained ID column in PStateWFI would make sense to ensure that there could only be one set of optional columns added to each row in PState. But now, when adding the UNIQUE index, the FK constraint started to appear as a bidirectional key link in the Diagram; hence, new entries in PState would have to meet a FK constraint based on PStateWFI, which was not intended.
Hence, I had to create a quite awkward design to enforce the constraints:
1. The PState table has a Primary Key (PState.ParticleID, int, Identity Specification: Yes)
2. The PStateWFI table has a Primary Key (PstateWFIID, int, Identity Specification: Yes)
3. PStateWFI has field "PStateID" which has a FK constraint to PState.ParticleID (which is a one-way constraint operating in the correct way and does not constrain insertions in PState)
4. PStateWFI has an additional column ParticleIDIndex which has a UNIQUE Index attached to it.
5. There is a check constraint on PStateWFI enforcing PStateWFI.ParticleIDIndex = PStateWFI.ParticleID.
Although this structure does the job, it makes it necessary to add a redundant column in PStateWFI by duplicating the PStateWFI.ParticleID into PStateWFI.ParticleIDIndex, since I can't create a UNIQUE index on PStateWFI.ParticleID without constraining the PState table as well. So, insertions into this table would have to insert the same value into two columns. Not a big deal, but appears slightly ugly.
Basically I'd hope someone could explain why a bidirectional FK constraint has to be enforced on the primary key table in a relationship when the constrained column in the primary key table has a UNIQUE index attached on it. I have a few other cases where the above approach would benefit from a more clear structure.
Thanks in advance for any advice.
View 6 Replies
View Related
Aug 1, 2007
Hi,
I am trying to replication between a SQL Server 2005 on a desktop and SQL Mobile Edition on a mobile device. I followed the step-by-step guide and used the code here: http://msdn2.microsoft.com/en-US/library/aa454892.aspx
After replication, the sdf on my device has new columns which the original sdf does not have. The new columns seem to be associated with replication, and are as follows:
rowguid
_sysIG
_sysCG
_sysCD
_sysP1
_sysMC
_sysMCS
_sysSR
All columns contain null except for rowguid and _sysCD, and are making my sdf quite big. Is this to be expected? If yes, any references to explain what are these columns for?
Thanks for any help.
View 1 Replies
View Related
Jan 16, 2001
(Long post)
I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).
Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).
The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.
The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)
My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.
Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?
NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.
Thanks in advance for any help you can shed on this issue.
Dave
View 3 Replies
View Related
Sep 8, 1999
We are trying to run replication from one server to another. Most of the tables in the publishing database have identity columns set. At one point the subscribing database had a match of identity columns and primary keys matching the publishing database. Obviously with a primary key set on the subscriber there would be conflicts with duplicate key inserts. We disabled the identity columms and droped the indexes and the data was able to replicate over. However, we discovered that the identity columns, which are used to generate id's on many of our tables, were not replicating over to the subscriber. In fact a null value was being inserted into the subsriber database.
Anyone seen this before? What, if there is one, is the solution?
Thanks,
Brad
View 1 Replies
View Related
Jan 17, 2000
hi there.
I was wondering is there any way that you can use an Identity Column on both a subcribing and Publishing table in Replication,
I am receiving errors when I run Replication with Identity Columns,
Thanks in advance
Fin
View 1 Replies
View Related
Jun 26, 2007
I have an interesting server occuring. We have transactional replication running with 1 pub and 6 subscribers. On nearly all the tables we have some sort of date column that's a smalldatetime. If I look at the pub the data will appear like:
2006-11-13 09:59:00
However, on all the subs it appears like:
2006-11-13 09:59:00.000
It's the same column and record but for some reason it's become a datetime. I checked the column types and they are both smalldatetime's.
Why is this happening? What can be done?
Thanks,
Phil
View 3 Replies
View Related
