Records don't lock-one user receives error

I created a MSAccess application with the forms in one db (front end) and the tables in another db (back end). The network admin migrated the back end to SQL Server. With a few minor changes (changing -1 to 1 in queries, etc.) the migration seemed to go pretty smoothly and everything works fine except....

The MSAccess backend used to lock records when someone was editing them. The status of the record was indicated in the record selector on each form, a right pointing arrow to indicate the record was free, a pencil with three dots indicating the record was locked by the current user, and a circle with a diagonal line to indicate the record was locked by another user. When 2 users attempted to simultaneously edit a record, the first user got the lock on the record. The second user received a message as soon as they attempted to edit the locked record to the effect that the record was locked.

Now (since the migration to SQL Server) the record selectors no longer give any indication as to the locking status of the records. When 2 users attempt to simultaneously edit a record, both are able to enter information in their forms, but when the second user attempts to save the record they receive the following message:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

Please help me find the cause of this behavior. Is it something I need to change in the MSAccess front end or something that needs to be set in the SQL Server back end by the network admin?

I am moving this question to the SQL Server Database Engine forum.

Records dont get deleted from the database!

I am using this code to delete records from a table in the database that has two fields (both are primary keys) I am not getting any error, but nothing gets deleted. Thanks for the help.

Code:

protectedvoid btnRemove_Click(object sender,EventArgs e)

{

Remove();

Response.Redirect("RegisteredCompanies.aspx");

}

protectedvoid Remove()

{

//update database with filename

//Save Changes

connection.Open();

using (SqlTransaction trans = connection.BeginTransaction())

{

try

{

SqlHelper.ExecuteNonQuery(trans,CommandType.StoredProcedure,"spRemoveCompany",newSqlParameter("@.Userid",getGdUserID(Profile.UserName)),newSqlParameter("@.BusinessID",getBusinessID());

}

catch (Exception err)

{

// throw exception

trans.Rollback();

lblStatus.Text ="An error has occurred in the delete process. Please try again.";

}

}

Here is the stored procedure:

ALTERPROCEDURE[dbo].[spRemoveCompany]

@.Useriduniqueidentifier OUTPUT,

@.BusinessID uniqueidentifier OUTPUT

AS

DELETEFROMAssociations2WHEREUserid = @.Userid AND BusinessID = @.BusinessID

RETURN

You must commit the transaction in your code.

|||

Also, why are you using OUTPUT on your parameters?

|||

yes, I used output in the stored procedure. How do i commit the transaction? Thanks!

|||

Got it working now!! Thanks a lot!!

Records disappearing Data/Preview tabs.

On "Data" tab I run the data. 3 records are returned. i.e.
Part# 12345 Philadelphia PA Qty 60
Part# 11223 Philadelphia PA Qty 100
Part# 11223 Bronx NY Qty 500
On "Preview" tab I see the following:
Part# 12345 Philadelphia PA 60
Part# 11223 Philadelphia PA 100
Customer break SUM 660
So, I loose the 3rd record in the detail section of the report
yet the totals sum up all 3 records.
I realize the 2nd & 3rd records have the same Part#.
None of the textboxes have "Hide Duplicates" checked off.
Can somebody explain to me what's happening?You arent grouping it by the Part# are you?
"BillB" <BillB@.discussions.microsoft.com> wrote in message
news:F9A6FA80-4F67-40D6-B9A8-1A9196B83CA4@.microsoft.com...
> On "Data" tab I run the data. 3 records are returned. i.e.
> Part# 12345 Philadelphia PA Qty 60
> Part# 11223 Philadelphia PA Qty 100
> Part# 11223 Bronx NY Qty 500
> On "Preview" tab I see the following:
> Part# 12345 Philadelphia PA 60
> Part# 11223 Philadelphia PA 100
>
> Customer break SUM 660
> So, I loose the 3rd record in the detail section of the report
> yet the totals sum up all 3 records.
> I realize the 2nd & 3rd records have the same Part#.
> None of the textboxes have "Hide Duplicates" checked off.
> Can somebody explain to me what's happening?
>|||Thanks for the reply.
Yes I am grouping it by the part #
the detail line is in a list and the customer total is an another list.
I'd expect it to total the Qty's on the detail line for the two records
with the identical Part#'s.
so part # 11223 would show 600 on the detail line not 100!
"Ben Watts" wrote:
> You arent grouping it by the Part# are you?
> "BillB" <BillB@.discussions.microsoft.com> wrote in message
> news:F9A6FA80-4F67-40D6-B9A8-1A9196B83CA4@.microsoft.com...
> > On "Data" tab I run the data. 3 records are returned. i.e.
> >
> > Part# 12345 Philadelphia PA Qty 60
> > Part# 11223 Philadelphia PA Qty 100
> > Part# 11223 Bronx NY Qty 500
> >
> > On "Preview" tab I see the following:
> >
> > Part# 12345 Philadelphia PA 60
> > Part# 11223 Philadelphia PA 100
> >
> >
> > Customer break SUM 660
> >
> > So, I loose the 3rd record in the detail section of the report
> > yet the totals sum up all 3 records.
> >
> > I realize the 2nd & 3rd records have the same Part#.
> >
> > None of the textboxes have "Hide Duplicates" checked off.
> >
> > Can somebody explain to me what's happening?
> >
> >
>
>|||I may be understanding it wrong, but it sounds like it is only showing one
because of the group. It will still total everything it finds because of
the detail, but if you are showing your group then it will only bring back
one record. Are you showing any detail in the group. Or is it all showing
in the details?
"BillB" <BillB@.discussions.microsoft.com> wrote in message
news:A43A1A2D-2F40-4B17-A01D-D4E289428D6A@.microsoft.com...
> Thanks for the reply.
> Yes I am grouping it by the part #
> the detail line is in a list and the customer total is an another list.
> I'd expect it to total the Qty's on the detail line for the two records
> with the identical Part#'s.
> so part # 11223 would show 600 on the detail line not 100!
> "Ben Watts" wrote:
>> You arent grouping it by the Part# are you?
>> "BillB" <BillB@.discussions.microsoft.com> wrote in message
>> news:F9A6FA80-4F67-40D6-B9A8-1A9196B83CA4@.microsoft.com...
>> > On "Data" tab I run the data. 3 records are returned. i.e.
>> >
>> > Part# 12345 Philadelphia PA Qty 60
>> > Part# 11223 Philadelphia PA Qty 100
>> > Part# 11223 Bronx NY Qty 500
>> >
>> > On "Preview" tab I see the following:
>> >
>> > Part# 12345 Philadelphia PA 60
>> > Part# 11223 Philadelphia PA 100
>> >
>> >
>> > Customer break SUM 660
>> >
>> > So, I loose the 3rd record in the detail section of the report
>> > yet the totals sum up all 3 records.
>> >
>> > I realize the 2nd & 3rd records have the same Part#.
>> >
>> > None of the textboxes have "Hide Duplicates" checked off.
>> >
>> > Can somebody explain to me what's happening?
>> >
>> >
>>|||Hi Ben
In the detail list I show
part# 12345 Qty= 60 this is correct
part#11223 Qty is showing 100 which is not correct.
remember the 2nd record is part # 11223 with qty = 100
the third record does not show in the detail at all but the 3rd
record
has a qty of 500.
so i should see in the detail:
Part # 11223 qty=600 because it should combine the 2nd record of 100 &
the 3rd record with the same Part# of 500.
the customer total shows correctly qty 660.
hope this makes it clearer & thanks fro the help.
"Ben Watts" wrote:
> I may be understanding it wrong, but it sounds like it is only showing one
> because of the group. It will still total everything it finds because of
> the detail, but if you are showing your group then it will only bring back
> one record. Are you showing any detail in the group. Or is it all showing
> in the details?
> "BillB" <BillB@.discussions.microsoft.com> wrote in message
> news:A43A1A2D-2F40-4B17-A01D-D4E289428D6A@.microsoft.com...
> > Thanks for the reply.
> > Yes I am grouping it by the part #
> > the detail line is in a list and the customer total is an another list.
> > I'd expect it to total the Qty's on the detail line for the two records
> > with the identical Part#'s.
> > so part # 11223 would show 600 on the detail line not 100!
> >
> > "Ben Watts" wrote:
> >
> >> You arent grouping it by the Part# are you?
> >>
> >> "BillB" <BillB@.discussions.microsoft.com> wrote in message
> >> news:F9A6FA80-4F67-40D6-B9A8-1A9196B83CA4@.microsoft.com...
> >> > On "Data" tab I run the data. 3 records are returned. i.e.
> >> >
> >> > Part# 12345 Philadelphia PA Qty 60
> >> > Part# 11223 Philadelphia PA Qty 100
> >> > Part# 11223 Bronx NY Qty 500
> >> >
> >> > On "Preview" tab I see the following:
> >> >
> >> > Part# 12345 Philadelphia PA 60
> >> > Part# 11223 Philadelphia PA 100
> >> >
> >> >
> >> > Customer break SUM 660
> >> >
> >> > So, I loose the 3rd record in the detail section of the report
> >> > yet the totals sum up all 3 records.
> >> >
> >> > I realize the 2nd & 3rd records have the same Part#.
> >> >
> >> > None of the textboxes have "Hide Duplicates" checked off.
> >> >
> >> > Can somebody explain to me what's happening?
> >> >
> >> >
> >>
> >>
> >>
>
>|||Go to your detail line and right click on the three lines.then click edit
group. Even in the detail you can group it and it will hide records. Make
sure there is nothing in "Group On" Expression. I am betting you have
something in that. If so just delete it and leave it blank. I just
recreated that with one of my own reports and when I put something in the
"group on" in the details line it hid alot of records.
"BillB" <BillB@.discussions.microsoft.com> wrote in message
news:A3A0CAAD-7B02-4E76-A03A-802C0D3A34EF@.microsoft.com...
> Hi Ben
> In the detail list I show
> part# 12345 Qty= 60 this is correct
> part#11223 Qty is showing 100 which is not correct.
> remember the 2nd record is part # 11223 with qty = 100
> the third record does not show in the detail at all but the 3rd
> record
> has a qty of 500.
> so i should see in the detail:
> Part # 11223 qty=600 because it should combine the 2nd record of 100 &
> the 3rd record with the same Part# of 500.
> the customer total shows correctly qty 660.
> hope this makes it clearer & thanks fro the help.
> "Ben Watts" wrote:
>> I may be understanding it wrong, but it sounds like it is only showing
>> one
>> because of the group. It will still total everything it finds because of
>> the detail, but if you are showing your group then it will only bring
>> back
>> one record. Are you showing any detail in the group. Or is it all
>> showing
>> in the details?
>> "BillB" <BillB@.discussions.microsoft.com> wrote in message
>> news:A43A1A2D-2F40-4B17-A01D-D4E289428D6A@.microsoft.com...
>> > Thanks for the reply.
>> > Yes I am grouping it by the part #
>> > the detail line is in a list and the customer total is an another list.
>> > I'd expect it to total the Qty's on the detail line for the two records
>> > with the identical Part#'s.
>> > so part # 11223 would show 600 on the detail line not 100!
>> >
>> > "Ben Watts" wrote:
>> >
>> >> You arent grouping it by the Part# are you?
>> >>
>> >> "BillB" <BillB@.discussions.microsoft.com> wrote in message
>> >> news:F9A6FA80-4F67-40D6-B9A8-1A9196B83CA4@.microsoft.com...
>> >> > On "Data" tab I run the data. 3 records are returned. i.e.
>> >> >
>> >> > Part# 12345 Philadelphia PA Qty 60
>> >> > Part# 11223 Philadelphia PA Qty 100
>> >> > Part# 11223 Bronx NY Qty 500
>> >> >
>> >> > On "Preview" tab I see the following:
>> >> >
>> >> > Part# 12345 Philadelphia PA 60
>> >> > Part# 11223 Philadelphia PA 100
>> >> >
>> >> >
>> >> > Customer break SUM 660
>> >> >
>> >> > So, I loose the 3rd record in the detail section of the report
>> >> > yet the totals sum up all 3 records.
>> >> >
>> >> > I realize the 2nd & 3rd records have the same Part#.
>> >> >
>> >> > None of the textboxes have "Hide Duplicates" checked off.
>> >> >
>> >> > Can somebody explain to me what's happening?
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Hi Ben
That's it . Thanks for all the help and sticking with me!
"Ben Watts" wrote:
> Go to your detail line and right click on the three lines.then click edit
> group. Even in the detail you can group it and it will hide records. Make
> sure there is nothing in "Group On" Expression. I am betting you have
> something in that. If so just delete it and leave it blank. I just
> recreated that with one of my own reports and when I put something in the
> "group on" in the details line it hid alot of records.
> "BillB" <BillB@.discussions.microsoft.com> wrote in message
> news:A3A0CAAD-7B02-4E76-A03A-802C0D3A34EF@.microsoft.com...
> > Hi Ben
> > In the detail list I show
> > part# 12345 Qty= 60 this is correct
> > part#11223 Qty is showing 100 which is not correct.
> > remember the 2nd record is part # 11223 with qty = 100
> > the third record does not show in the detail at all but the 3rd
> > record
> > has a qty of 500.
> >
> > so i should see in the detail:
> > Part # 11223 qty=600 because it should combine the 2nd record of 100 &
> > the 3rd record with the same Part# of 500.
> >
> > the customer total shows correctly qty 660.
> > hope this makes it clearer & thanks fro the help.
> >
> > "Ben Watts" wrote:
> >
> >> I may be understanding it wrong, but it sounds like it is only showing
> >> one
> >> because of the group. It will still total everything it finds because of
> >> the detail, but if you are showing your group then it will only bring
> >> back
> >> one record. Are you showing any detail in the group. Or is it all
> >> showing
> >> in the details?
> >>
> >> "BillB" <BillB@.discussions.microsoft.com> wrote in message
> >> news:A43A1A2D-2F40-4B17-A01D-D4E289428D6A@.microsoft.com...
> >> > Thanks for the reply.
> >> > Yes I am grouping it by the part #
> >> > the detail line is in a list and the customer total is an another list.
> >> > I'd expect it to total the Qty's on the detail line for the two records
> >> > with the identical Part#'s.
> >> > so part # 11223 would show 600 on the detail line not 100!
> >> >
> >> > "Ben Watts" wrote:
> >> >
> >> >> You arent grouping it by the Part# are you?
> >> >>
> >> >> "BillB" <BillB@.discussions.microsoft.com> wrote in message
> >> >> news:F9A6FA80-4F67-40D6-B9A8-1A9196B83CA4@.microsoft.com...
> >> >> > On "Data" tab I run the data. 3 records are returned. i.e.
> >> >> >
> >> >> > Part# 12345 Philadelphia PA Qty 60
> >> >> > Part# 11223 Philadelphia PA Qty 100
> >> >> > Part# 11223 Bronx NY Qty 500
> >> >> >
> >> >> > On "Preview" tab I see the following:
> >> >> >
> >> >> > Part# 12345 Philadelphia PA 60
> >> >> > Part# 11223 Philadelphia PA 100
> >> >> >
> >> >> >
> >> >> > Customer break SUM 660
> >> >> >
> >> >> > So, I loose the 3rd record in the detail section of the report
> >> >> > yet the totals sum up all 3 records.
> >> >> >
> >> >> > I realize the 2nd & 3rd records have the same Part#.
> >> >> >
> >> >> > None of the textboxes have "Hide Duplicates" checked off.
> >> >> >
> >> >> > Can somebody explain to me what's happening?
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Records disappeared

Hi,
I have lost approximateley 1,000 records from a 1200 record database.
When I check the properties it says 1200 but there are only 200 rcords in
the table.
Help!
John Stevenswhen you click on properties the ROWS details are picked up from the table
sysindexes which may not always show you accurate value. Specifically after
bulk operation.
"Select count(*) from table" will give you the correct result set.
To update the values of sysindexes system table run
DBCC UPDATEUSAGE ... WITH COUNT_ROWS
--
-Vishal
"John Stevens" <yourpal@.mad.co.uk> wrote in message
news:bil1np$6tg$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have lost approximateley 1,000 records from a 1200 record database.
> When I check the properties it says 1200 but there are only 200 rcords in
> the table.
> Help!
> John Stevens
>

Records Deleted after merge

PKs and FKs are all uniquidentifiers.
(the db will eventually have 100's of subscribers)
The FK validation errors only occur sometimes - this the odd thing.
And when it does happen it is on new records added by one subscriber, so
there shouldn't be any conflicts.
Thx
You may get the behavior you describe if your foriegn keys are missing the
"NOT FOR REPLICATION" option.
|||Thanks Jim.
Any idea where can I get more details on this behaviour?
Darren
|||I haven't seen any documentation on this yet but basically you cannot
guarantee that replication will apply your changes in the order that they
originally occured.
"Darren Wallace" wrote:

> Thanks Jim.
> Any idea where can I get more details on this behaviour?
> Darren
>
>
|||Thanks Jim.
Pity about that.
So it sounds like all foreign keys should have the Not For Replication
option.
Cheers
Darren

Records comparsion

Hi,

I have a table of say 5 columns containing 100 records. The records are extracted from DB2. Now records need to be fetched on a nightly basis to the table. Only those records that are new should be fetched. If the records are the same then do not fetch them. What is the simplest way of implementing this?

Thanks.You may add a column to set a flag to inducate which record is new.|||Set up a second work table that looks like the first...

You'll then have to deal with 3 potential actions,

INSERT: New records on the file
DELETES: Records that don't exists
UPDATES: Records that are on the file, but attributes have changed

Do you have a PK on the table now?|||For most cases, datetime field is a good candidate to determine a record is old or new.|||Yes I have a primary key (It's a composite key made up of 2 columns)
[]
QUOTE]Originally posted by Brett Kaiser
Set up a second work table that looks like the first...

You'll then have to deal with 3 potential actions,

INSERT: New records on the file
DELETES: Records that don't exists
UPDATES: Records that are on the file, but attributes have changed

Do you have a PK on the table now? [/QUOTE]|||You only insert records that do not already exist - Could you be more specific as to what you need ? Also, what are you using now to import the records from db2 ?|||I use ODBC driver to get records from DB2. The records have a PK and attributes. Say attribute1,2,3...5. Now I only need to call insert a record if the PK has changed OR if attribute 1 for the records has changed. If attribute 2,3,4,5 have changed, then that does not matter. I do not call that records New.|||What programming language are you using ?|||T-SQL.|||Ummm, don't you want to INSERT when the PK changes, but UPDATE when column 1 changes? Otherwise, you get relational integrity issues. This should be handled in two different steps after the data is loaded into a staging table.

blindman|||He wants this...

USE Northwind
GO

CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

--DO DELETES FIRST

DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- INSERT

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- UPDATE

UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO|||Brett . Thanks a lot. I am getting close. But this is how I need it.

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','d','z' UNION ALL --NO CHANGE

Col3,4,5 are attributes. If PK changes then insert new record. If for same PK, attribute 2,3 changes but attribute 1 is unchanged then do not INSERT (No change). I need a history of the records and so I do not do updates. I just insert new records.|||OK...I wouldn't do ot this way...

I wouldn't keep all the history in 1 table...I would use a trigger and a history table...

Hey, but they're the reqs...you're going to have to find the PK with the max timestamp every time for the current row..

EDIT: And what, prey tell, does the concept of "DELETE" mean anymore to this process?

USE Northwind
GO

CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, Col6 datetime DEFAULT GetDate()
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2,col6))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','a','b','c' --INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

-- INSERT

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- "UPDATE"

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT b.Col1, b.Col2, b.Col3, b.Col4, b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO|||...and what, prey tell, does the concept of "PRIMARY KEY" mean anymore to this process?

blindman|||Hey..it got extended to include the "property" (if you will) of time...

smoke'em if you got 'em...

I still think you should go with a historical table and triggers though...If you stll need to see all of the history, just create a view...|||I understand that I need to do the Deletes first. I could do either an Insert or an Update after that,correct? There is no order for Inserts and Updates,correct?

Also can I execute this code in one stored procedure? I generally execute one query in one stored proc and then check for error condition. Here is my error condition -

SELECT @.ERRORNUM = @.@.ERROR, @.LOCALROWCOUNT = @.@.ROWCOUNT
IF @.ERRORNUM = 0
BEGIN
IF @.LOCALROWCOUNT >= 1
BEGIN
SELECT @.RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 0
RAISERROR ('FETCH FAILS: No row matching the specified criteria is found.',16, 1)
END
END
ELSE
BEGIN
SELECT @.RETURNVALUE = 1
END
RETURN @.RETURNVALUE

--

If I executes multiple queries, like the Delete/Insert/Update in one stored proc then how does this error condition change. Should I have the @.error checking after every query?

Records comparsion

Please look at the code template below for my problem. I am comparing two tables. Table 99 is the current state of the table. I get in a fresh extract of Table 99 everyday from my source - DB2 and I insert that in Table 00. Then I execute the code template below so that Table 99 stays current with the source DB2 on a daily basis.
--
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','j','b','c' --INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

--DO DELETES FIRST

DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- INSERT

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- UPDATE

UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

--
I want to process new and updated records.
Let me explain the problem -

The last row for myTable99 is - 1','4','j','k','l'

The last row for myTable00 is - '2','3','j','b','c'

Col1 & Col2 are the PK's. So the last row from Table 99 gets deleted and the one from Table00 gets inserted as a 'New' record.Now I want to do processing only on those records that are new. So I should process record '2','3','j','b','c' .

My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

How do I do that with the Insert new records query? The same case is for updates as well.

Any help is appreciated.

Thank you.My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

You lost me....

Huh?

Understand....Whatever is in Col3 attribute BELONGS to the composite key Col1+Col2...it does not transcend the row to some other key?

It belongs to that row...if it so happens that the same value is on another row that's fine...but it belongs to that other key...

Can you give the explanation another shot?|||Why so complicated?

Can you not flag the affected records after the import and use the EXISTS / NOT EXISTS clause to target those which have been actioned as you translate them from import source to stored data?|||I think he's saying, that if I messed with an "attribute" (bad choice of words here) of a value of 5 in column 3, then I don't want to touch ANY of the rows...across keys...right?|||Here is the processing deal. I get the value of col 3 and check to see if it exists in this other table Z.

So now when I get a new record and if I have already checked that col3 value in Table Z, then I don't really need to check it again?|||Clear as mud...what does that have to do with adding and updating rows?

And what are you checking col3 for?|||what does that have to do with adding and updating rows?
Well every row that is added/updated has the status of a new record. And all new records have to be processed.

And what are you checking col3 for?
I am checking if the col3 value of the new record exist in this other table called Table Z. If it exist then I spit out the contents of Table 99 and Table Z into another table say X. Now my client looks to see the degree of similarity in col3 and the column in Table Z. For instance - col3 value is 'j' and the column in Table Z is 'jo'

Now for a new record which has the same value of Col3 as an old/delted record that was spit out then I have many duplicate values of Col3 (but different primary key) in X. Now the client has to check the same value of Col3 again for degree of similarity with Table Z.

I know it's a bit crazy but that's how the data is. Let me know what you think.|||I;m thinking I'm glad I'm not you...

But a previous post by someone a good idea...

When you add or update a record, tag it with an indicator NEW_IND for example, the do you're comparison step where the ind = 'Y' or whatever...then when you're donme set them all bacl to N

What'chyou think?|||Well if I tag it with IND = 'Y' and do the comparison then isn't it possible that Col3 of the record with IND = 'Y' would have been compared before?|||You cannot delete the old records AND retain knowledge of the old record's values. You must retain a knowledge of what the old the old values were.

You probably need an intermediate table indicating which of the possible values you have already processed, or implement some ki9nd of flagging system such that you identify the records to be deleted, insert the new records, compare the col3 values and then delete the flagged records.|||I appologize for the tone of my previous message. I did not intend for it to sound aggressive.

I had to address a similar problem to create change files (LDIFs) for changes to an X500 directory. Our solution was to mirror the table structure and do a comparison of changes based off of the key values for the appropriate tables.

Prior to import (or live data manipulation) the two instances are identical. A series of EXISTS / NOT EXISTS comparisons returns the newly added or deleted records. These changses are passed to the ADD/DELETE tables.

At the field level we passed the field names with an INNER JOIN fo the PK to look for field level changes. These changes were passed to a MODIFY table.

I am sure that you can use a variation of this logic to solve your problem.