Hexadecimal condition check and Identity columns

hi , i am using transactional replication between two Sql server 2000 . my
tables have identity columns as the primary keys with the identity property
set to Yes( not for replication). Now when I am generating the replication
intrinsic stored procs for the subscriber , I have a problem with the update
stored procs which are generated at the subscriber side. Each of these Update
command SPs ( which are each for a table) have two update statements in them
and a IF statement which chooses one of the udpate statements to run
depending on a binary hexa decimal field. If the condition satifies , it run
the first update query which unlike the second update query updates the
identity column as well and this update query is always selected by the
Update statement to run. The second if stement is run when the hexadicmal
conditions is not met and the funny part is that it never ever happens. I had
to comment out the part of the first update query which tried to update the
identity column. Now my queston are
1.why is replication trying to update the identity columns when it know
that they can not be updated.
2.What is the binary hexadecimal field used for, why is this the condition
in each and every case select statement in every field?
Please help me with this hexadecimal paradigm
The IF statement checks to see if the PK is being updated, and if so
executes the update statement differently. This is not related to identity
columns. If you have an identity column on the subscriber, compilation of
this procedure may fail. That's because the identity attribute shouldn't be
there - it's implicitly treated as read-only. If you had queued updating
subscribers the logic in these sps is different and there is no update or
insert into the identity columns because the identity attribute is expected
on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Here is a question I cant seem to find the answer to

Hi all. I'm kind of stuck. I may be having a general conceptual error here.

Suppose I have a table with two columns in the primary key, ID and VERSION.

Suppose I have a child table that I want a relationship on ID only and not VERSION.

Is there a way to do this besides creating a check constraint? (ie When I print and ERD it gives the lines, so the "non" db folks can "see" the relationships)

not sure if this is possible or not but any clues you all can give would be most helpful.

Thanks,

bill
s e g fa h l t @. l o n g b o y s . n e tThe conceptual error you have made is mixing up the parent and child. The table with id and version should be the child, and then it can have a foreign key relationship with the parent.

CREATE TABLE parent (
id int NOT NULL ,
string char (10) NULL
)
GO

ALTER TABLE parent ADD CONSTRAINT
PK_parent PRIMARY KEY CLUSTERED
(id)
GO

CREATE TABLE child (
id int NOT NULL ,
version int NOT NULL ,
string char (10) NULL
)
GO

ALTER TABLE child ADD CONSTRAINT
PK_child PRIMARY KEY CLUSTERED
(id, version)
GO

ALTER TABLE child ADD CONSTRAINT
FK_child_parent FOREIGN KEY
(id) REFERENCES parent (id)
GO

Hope this helps.|||Thanks for the feedback. My apologies for not being more clear on the subject. I opted for brevity instead of details.

Here is my situation.
I have a very large table which I want to split up into smaller tables. The tables [should] ideally have a 1-1 relationship between them all.

I also need to implement a transaction tracking solution such that I can determine if any column of any of the tables was changed, when it was changed, and who changed it, and be able to rollback if necessary to any particular point in time.

Finally, I also need to implement a modification approval system, such that any proposed changes must be captured, run through the red-tape corporate pipeline, then when approved, put into the table as actual data. While the changes are in the approval process, I need to be able to show the entire record with the proposed changes to one set of individual, and the entire record with only the approved changes/data to another set of people.

There is non-trivial amount of tables I have to do this with.

I came up with 2 feasible solutions.

solution 1 is : Each table which I need to track will have a copy table(same structure). It will store the proposed changes and any change history.

solution 2 is a bit more abstract. For each of my tables which I need to have transaction auditing and modification approval on, I will add two columns. a version column and a stage column. The version column will be used mainly for reporting and an easy way to see changelog history for any particular item ID. The stage column will be of "proposed", "review", "approved", "rejected", "obsolete". All the data will be contained in one table. so for each unique record(identified by ID) I will actually have multiple records with the same ID. But each successive record will have a higher version number. As new Version Numbers are added, the stage will be moved to "obsolete" when the changes are approved. then that record's stage will be set to "approved" and it will become the official record.

I just came across this yesterday, so i'm still fleshing out my "solution". I like the idea of solution 2 as its compact and fairly easy to code for. However, the more I think about it, the more I feel I will break anykind of RI I can put and still use the DB to enforce it. I don't think I even need a primary key of (ID, Version), but you need a primary key, to have a foreign key. If I could get the relationship, without having a primary key(just have a clustered, non-unique index on ID) then that would be sufficient. However, I'm pretty new to Sql Server, so I don't know if there is a way to do that. Any Ideas?

here is a visual of what my solution 2 would be like.

I have a table
create table table1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key (id, version)
)

I have a child table, which I need a RI check on ID only of the parent table. It too, will have a version column, but does not relate to the parent table. It will however, be part of the primary key of the child table.

create table child1ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)

create table child2ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)

I need the relationship from table1 to child[12]ToTable1 to be a many to many on ID, but I don't care about the version.

Clear as mud? I know. it kind of gives me a headache too.

:)

Thanks for you help.

bill

Help:Select query

I have 3 tables in my database

The first Table is called Customers

The second Table is called Rooms

And the third table is a combination of the primary keys of Customer table and the Rooms Table.

The Room tables consist of 50 rooms and only 30 of these rooms are currently
occupied. I need a query to find the available room numbers that are not
currently in use.

The query below the retrieve the occupied room

SELECT Room.Room, Room.Telefon

FROM ThirdTableINNER JOIN

Room ON ThirdTableINNER .RoomID = Room.RoomID

Thanks in advanceSELECT Room.Room, Room.Telefon

FROM ThirdTable a RIGHT JOIN

Room b ON a .RoomID = b.RoomID

WHERE a.RoomId IS NULL

Are the rooms rented in units of 15 minutes?

:D|||Originally posted by Brett Kaiser
SELECT Room.Room, Room.Telefon

FROM ThirdTable a RIGHT JOIN

Room b ON a .RoomID = b.RoomID

WHERE a.RoomId IS NULL

Are the rooms rented in units of 15 minutes?

:D

Thank u Brett Kaiser for the help. The query was great. Actually all rooms are almost full, but because you did a great job, as the Manager of the Hotel I will offer you 3 nights stay FOC.|||And where might that be?|||Originally posted by Brett Kaiser
And where might that be?

We have branch offices world wide, all you need to do is to specify your location I will send a comfirmation to that branch.