row-by-row operation without cursor

Hi all,
I have a table with customer data. Ik would like to query this table for duplicate entries, based on certain criteria.
My query to find these records:

SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1

So far so good. What I would like is to show some other data from the duplicate records. Example: suppose the above query
finds 2 records having the same criteria (postcode, huisnr, huisnrtv). How can I select other data from the
same table (e.g. name, date of birth) without using a cursor (is too expensive)? I thought of using a derived query but
I can't figure out how to do it. My desired result looks something like this:

postcode huisnr huisnrtv name date of birth
----- --- ---- ----
1111AA 13 a Smith 12/3/70
1111AA 13 a Clinton 10/2/72
2222BB 22 Bloomberg 1/8/61
2222BB 22 Bloomberg 9/2/79
2222BB 22 Pataki 2/4/71
etc.

Thanks in advance.

Diederikselect *
from tblRelatie r
join
(
SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1
) x on r.postcode=x.postcode and r.huisnr=x.huisnr and r.huisnrtv=x.huisnrtv|||Vezi daca merge asta:

select tblRelatie.* from tblRelatie join
(SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1) T1
on tblRelatie.postcode=T1.postcode and tblRelatie.huisnr=T1.huisnr and tblRelatie.huisnrtv=T1.huisnrtv
order by tblRelatie.postcode, tblRelatie.huisnr, tblRelatie.huisnrtv

ionut calin

Row wise operation in MSSQL Server

How can I create a row wise trigger in MSSQL Server .

CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...

For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!Yes, you could put a cursor in the trigger that operates on each row of the INSERTED table. But do yourself a favor and don't do it. Find a set-based solution using the INSERTED table instead.

Please describe what you are trying to do and why you think you need a row-wise operator (cursor). These are generally only needed by dynamic sql procedures or processes where the results of the operation on one record affects the results of the operation on the next record.|||AND...I'll add...anything that would require a cursor should not be done in a trigger...sql

Row operation cannot be reapplied

Hi,

I'm merge replicating between SQL Server 2005 and SQL Compact on mobile devices. This has been running reasonably well, after a lot of setup problems, but recently I've noticed an error when a new subscription synchronizes with the server (creating a new database on the device). After running for several minutes, the synchronization stops with the following error:

"The row operation cannot be reapplied due to an integrity violation. Check the publication filter. [Table = AppUser,Operation = Insert,RowGuid = ..."

There are no filters on the publication. The record causing the problem already exists on the server and is apparently being added to the new database on the subscriber, but it seems the merge agent is trying to add the record twice. I've tried doing a new snapshot and then immediately synchronizing, but still the same error. Has anyone come across this problem when synchronizing, especially with a new database/subscription?

Regards,

Greg

This error is created because of an integrity issue in you table schema. I had the same problem where I had created a foreign key relationship from one table to 2 fields in the joined table. For example:

My replication filtered on employee name (or HOST_NAME())

This employee table field existed in 2 fields of the joined table, ie: records could be sent to 2 subscriptions based on 2 fields in the record table which related to the employee table.

I had to delete the FK relationship in the database, and just kept the relationship in the filtered join in the replication. This solved the error.

|||

I encountered the problem too. Is the limitation of compact edition doesn't support FK relationship in the table?

Anyone had worked around the issue?

Jun

|||No, sql compact database supports fk relationships just fine. Replication, however, is very particular about the schema of the replicated tables. When setting up the replication be sure you know whether relationships are 1 to many, 1 to 1, or many to many. Any deviation from this will cause this or other errors. For example in my case I had 1 table FK relate to 2 fields in another table, although this schema can be fine in a normal production database, when replicated, it makes it hard for the replication to keep track of GUIDs and other replication objects. So I just deleted one of the relationships and kept the other.|||

I am having a very similar issue - how did you get the error message to include the actual table, guid, etc. My error message is as follows:

The row operation cannot be reapplied due to an integrity violation. Check the Publication filter. [,,,Table,Operation,RowGuid] (Source: MSSQLServer, Error number: 28549)

I have a filter using HOST_NAME() as well with your typical FK's 1 field in one table to 1 field in another.

I am under the gun to get this app out and I just can not get enough details on this error to resolve, any help would be greatly appreciated.

Thanks,
Daniel

|||Since your error is not specifying a certain table, you'll have to test it 1 relationship at a time starting with the main filter relationship. Drop the relationship in the production database, re-create snapshot and reinitialize subscriptions until you've removed the errored relationship. Then re-examine your schema to determine why the error happened and see if you can re-design the relationship. Good luck.|||

Wow, there is no way to turn on additional logging that would produce a more detailed error message?

Row operation cannot be reapplied

Hi,

I'm merge replicating between SQL Server 2005 and SQL Compact on mobile devices. This has been running reasonably well, after a lot of setup problems, but recently I've noticed an error when a new subscription synchronizes with the server (creating a new database on the device). After running for several minutes, the synchronization stops with the following error:

"The row operation cannot be reapplied due to an integrity violation. Check the publication filter. [Table = AppUser,Operation = Insert,RowGuid = ..."

There are no filters on the publication. The record causing the problem already exists on the server and is apparently being added to the new database on the subscriber, but it seems the merge agent is trying to add the record twice. I've tried doing a new snapshot and then immediately synchronizing, but still the same error. Has anyone come across this problem when synchronizing, especially with a new database/subscription?

Regards,

Greg

This error is created because of an integrity issue in you table schema. I had the same problem where I had created a foreign key relationship from one table to 2 fields in the joined table. For example:

My replication filtered on employee name (or HOST_NAME())

This employee table field existed in 2 fields of the joined table, ie: records could be sent to 2 subscriptions based on 2 fields in the record table which related to the employee table.

I had to delete the FK relationship in the database, and just kept the relationship in the filtered join in the replication. This solved the error.

|||

I encountered the problem too. Is the limitation of compact edition doesn't support FK relationship in the table?

Anyone had worked around the issue?

Jun

|||No, sql compact database supports fk relationships just fine. Replication, however, is very particular about the schema of the replicated tables. When setting up the replication be sure you know whether relationships are 1 to many, 1 to 1, or many to many. Any deviation from this will cause this or other errors. For example in my case I had 1 table FK relate to 2 fields in another table, although this schema can be fine in a normal production database, when replicated, it makes it hard for the replication to keep track of GUIDs and other replication objects. So I just deleted one of the relationships and kept the other.|||

I am having a very similar issue - how did you get the error message to include the actual table, guid, etc. My error message is as follows:

The row operation cannot be reapplied due to an integrity violation. Check the Publication filter. [,,,Table,Operation,RowGuid] (Source: MSSQLServer, Error number: 28549)

I have a filter using HOST_NAME() as well with your typical FK's 1 field in one table to 1 field in another.

I am under the gun to get this app out and I just can not get enough details on this error to resolve, any help would be greatly appreciated.

Thanks,
Daniel

|||Since your error is not specifying a certain table, you'll have to test it 1 relationship at a time starting with the main filter relationship. Drop the relationship in the production database, re-create snapshot and reinitialize subscriptions until you've removed the errored relationship. Then re-examine your schema to determine why the error happened and see if you can re-design the relationship. Good luck.|||

Wow, there is no way to turn on additional logging that would produce a more detailed error message?

Row failed to retrieve on last operation

Hello,

I am getting the following errors when "inserting" data into my tables using Miscrosoft SQL Server Management Studio with a SQL Server 2005 database...

"This row was successfully committed to the database. However, a problem occurred when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only. To fix this problem, please re-run the query."

In the status bar:

"row failed to retrieve on last operation"

I am hoping I just have some database properties set wrong but have not been able to figure it out.

close cursor on commit is set to False, set it to True, retested, no affect on the error.

other info:

The database is used to supply data to a Microsoft Access ADP application

Are you getting any error number with this?

Anything in the SQL logs?

Does this happen on all inserts or just certain ones?

|||

There is no error number that comes with this error.

There is nothing significant in the logs...

This error occurs when inserting a new record to any table in the database. The record does insert but is not visible until I have followed the instructions of the error msg.

Error Log:

2007-07-04 19:00:42.00 Server (c) 2005 Microsoft Corporation.
2007-07-04 19:00:42.00 Server All rights reserved.
2007-07-04 19:00:42.00 Server Server process ID is xxxx.
2007-07-04 19:00:42.00 Server Authentication mode is xxxxxx.
2007-07-04 19:00:42.00 Server Logging SQL Server messages in file xxxxx.
2007-07-04 19:00:42.00 Server This instance of SQL Server last reported using a process ID of xxxx at 7/4/2007 7:00:00 PM (local) 7/5/2007 2:00:00 AM (UTC). This is an informational message only; no user action is required.
2007-07-04 19:00:42.00 Server Registry startup parameters:
2007-07-04 19:00:42.00 Server xxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.00 Server xxxxxx

2007-07-04 19:00:42.01 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-07-04 19:00:42.01 Server Detected X CPUs. This is an informational message; no user action is required.
2007-07-04 19:00:42.51 Server Using dynamic lock allocation. Initial allocation of xxxx Lock blocks and xxxxx Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-07-04 19:00:42.57 Server Database mirroring has been enabled on this instance of SQL Server.
2007-07-04 19:00:42.57 spidxs Starting up database 'master'.
2007-07-04 19:00:42.95 spidxs Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-07-04 19:00:43.01 spidxs SQL Trace ID 1 was started by login "xxxxxx".
2007-07-04 19:00:43.04 spidxs Starting up database 'xxxxxxxxxxx'.
2007-07-04 19:00:43.06 spidxs The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-07-04 19:00:43.20 spidxs Starting up database 'model'.
2007-07-04 19:00:43.21 spidxs Server name is 'xxxxx\MYDB'. This is an informational message only. No user action is required.
2007-07-04 19:00:43.43 spidxs Clearing tempdb database.
2007-07-04 19:00:43.71 spidxs Starting up database 'tempdb'.
2007-07-04 19:00:43.75 spidxs The Service Broker protocol transport is disabled or not configured.
2007-07-04 19:00:43.75 spidxs The Database Mirroring protocol transport is disabled or not configured.
2007-07-04 19:00:43.76 spidxs Service Broker manager has started.
2007-07-04 19:00:44.23 Server A self-generated certificate was successfully loaded for encryption.
2007-07-04 19:00:44.25 Server Server is listening on [ xxxxxx].
2007-07-04 19:00:44.25 Server Server local connection provider is ready to accept connection on [ xxx].
2007-07-04 19:00:44.25 Server Server named pipe provider is ready to accept connection on [xxxx].
2007-07-04 19:00:44.25 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2007-07-04 19:00:44.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-07-04 19:00:44.43 spidxs Starting up database 'msdb'.

|||

Can you send the DML of the table? And do you have a trigger on the table? I assume you are doing this in the "Open table" tool.

It seems to me that you possibly are changing the key value in a trigger or something and it cannot find the row with the key you edited. I get that error when I edit the following table:

create table fred

(

fredId int primary key,

value varchar(10)

)

go

create trigger fred$insertTrigger

on fred

after insert

as

update fred

set fredId = fredId * 10000

where fredId in (select fredId from inserted)

go

|||There is a constraint for a default in the table and for some reason, when using the open table tool, the default is not being applied automatically and that is what I believe makes the error msg appear.|||That sounds plausible. Can you send the DML for the table? I will test it too.