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.
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
When I use Management Studio (SQL Server 2005 SP 1) to set up transactional replication with updateable subscriptions, the snapshot initialization doesn't push the foreign key constraints. If I add a foreing key at the publisher, the change doesn't make it to the subscriber, even though the Publication Properties' Subscription Options panel shows Replicate Schema Changes set True - what am I missing?
Thanks, Liston
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,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
Does anyone have a script that will roll through the tables in a database and identify tables without primary keys defined? I did not see any in the online script database.
Thanks,
Rick
Hello,We imported a bunch of tables from a database and realized that theprimary keys weren't copied to the destination db. In order to re-create the keys, we need to know which tables have them. Is there acommand that I can use (on the source db) to find out which tablescontain primary keys? The db has hundreds of tables and I'd rather notgo through each one to see which has a primary key.Also, for future reference, is there a way to include the primary keyon an import?Thanks,Peps
View 2 Replies View RelatedI've created a table called Employees with a primary key called EmployeeID. Â The table contains EmployeeID, FirstName and LastName columns. Â I now want to create a table called Team which will contain the columns TeamID, EmployeeID (to reference the column EmployeeID from the Employee table) and a column called TeamName. Â Sql won't let me create multiple primary keys in one table (I did think that was the case ) key but yet if I look at the Adventure Works database in the Person.PersonPhone table, I can see three primary keys defined.
View 3 Replies View Related...got tired of looking at them by hand.
Cheers
-b
DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)
Select @vcDB='mydb',@vcSchema='dbo'
DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC
open cLoop
FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'
FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop
Hope this is in the right thread, sorry if not!
I have run into a problem, i need to find out that column(s) in a table that makes the primary key.
I thought that this code did the trick.
***
DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='contact_pmc_contact_relations'
Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@c, 1, Datalength(@c) - 1)
***
This works in most of my cases. But i have encounterd tabels where this code doesn't work.
Here is a dump from one of the tabels where it doesn't work.
SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table
***
id indid colid keyno
933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1
Not sure if that dump made any sense, but i hope it did.
If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).
So, anyone know how i could solve this problem?
Regards
/Anders
Hello All,
Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.
Many thanks in advance for any and all help,
Gurmi
Hi all,
I’m trying to export 120 tables from SQL server 2000 to SQL server 2005 with their Primary and corresponding records.
Is there way to do this?
Thanks for any help.
Abrahim
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 RelatedTable 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
HiI tried posting this query in microsoft.public.sqlserver.programming but gotno response.I am new to replication but I am trying to setup up a transactionalreplication of tables from one database to another in MSSQL 2000 (SP2).My target tables have primary keys defined. Under publication properties Igo to the snapshot tab and for each table I clear the check box that says"Drop the existing table and re-create-it" and "clustered Indexes." On thispage the nothing is checked. for each table.Whenever the subscription is reinitialized it drops the primary keys on mytarget tablesand replaces them with a unique clustered index on the column that used tobethe Primary key.Is this normal? Is there anyway to stop it from doing this?I don't plan to send the snapshot more than once and let transactionalreplication take over for keeping my source and target in sync, but if Iever have to reinitialize the subscription, it would seem that I (orsomeone) willhave to take the a second step of manually dropping these clustered indexesand recreating the primary keys on the target table.Thanks in advance.---Dick Christoph---Dick ChristophJoin Bytes!612-724-9282
View 2 Replies View RelatedI am using SQL 2012 SE and implementing transactional replication. I need to insert the rows from publisher database tables to new tables, drop the old tables and rename the new tables with the old table names.
For example:
Publisher database tables that are being replicated:
Table1
Table2
Table3
and I am going to create new tables in publisher database
Table1_new
Table2_new
Table3_new
Move data from
Table1--->Table1_new
Table2--->Table2_new
Table3--->Table3_new
Drop constraints from and then tables (does this require articles to be removed from replication?)
Table1
Table2
Table3
Rename
Table1_new to Table1
Table2_new to Table2
Table3_new to Table3
Does this require replication to set up from scratch or add the three articles only to replication? Is there a way this can be done without pausing or reinitializing replication or without removing articles and adding them back?
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
Hello again,
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
class_title
VARCHAR(50)
NOT NULL,
class_grade
SMALLINT
NOT NULL
DEFAULT 6,
class_tardies
SMALLINT
NOT NULL
DEFAULT 0,
class_absences
SMALLINT
NOT NULL
DEFAULT 0,
CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,
teacher_id
INT
NOT NULL,
grade_id
INT
NOT NULL,
CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),
CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Thank you for your assistance.
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:
<o:p></o:p>
SQL1 -> SQL2 ->SQL3..
I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
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"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!
what do u think?
hmmm pls help!
Using SQL Svr 7.0. It appears that primary keys are created as nonclustered
unique indexes. Is there a configuration setting I can use to make them be
created as clustered unique indexes?
If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.
Thanks!
I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.
Thanks
Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.
Thanks,
Fady
HI ,
I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:
'table_name' table
- Unable to create index 'PK_tablename'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated.
i.e.
key1 key2
200410 12345
200410 12346
200410 12588
etc for all 200410 there can not be a repeat of any value of key 2
is there a way to have this dual key in MSSQL :confused:
Hi
How can 2 columns be clubbed together to form a primary key , i mean I have 2 columns job & Batch , need to club them together to form the primary key
How is it done I mean in the design , how to define that both the columns togethter form a primary key ,
Cause when I go to the table , it allows me to create a PK through the EM only for one column
Please help
Hi there,
is there any way I can use INSERT so if I try to copy duplicate primary key data, it automatically skips the task and moves on to the next data?
I have declared a table with a single column to be a Primary key. If I write 2 records, the first comprsing "A" and the second "a" then I get an exception about violating the uniqueness of the key. Is there a way that I can define the key to be case sensitive.
Many thanks