I'm using VB.Net, ADO.Net and MySQL. I want to be able to delete a row in one dataset, sourced from a database table, then detect the deleted row, merge it into a dataset sourced from a table (with identical schema in another (Archive) database. I have no problems otherwise with updating tables but any approach I try to this question doesn't work. I'm sure it's really very simple but.............
It was really a question of how to approach the problem. I didn't see any point in including any of the several versions of code that I tried. In the night, it suddenly came to me what the problem was - I was deleting a row, then trying to add insert that row in another table. The fact that its rowstate was Deleted ensured it could never happen. Having realised this, I have been able to find a viable solution - probably not the most elegant, but it works. Thanks for caring!
I have a need to execute a query that pulls all data from one table regardless of whether matching data (matched by a foreign key) is present in a second table. This would be a classic LEFT JOIN query except that if a matching record is found in the RIGHT table (table2), then I need to pull only the latest such record from that table.
In particular, I need to pull one field, called VE (maybe more in the future) from the RIGHT table, but only from the latest record in the RIGHT table that matches the data in the LEFT table. The basic scenario I have is an object described in the LEFT table and the results of a process performed on that object in the RIGHT table. There may be more than one process result for that object in the right table, but only the latest one matters to me for the purposes of this query.
To solve this problem I used the following query based on the advice of someone from Code Project, but it didn't quite work out as planned. The ID in this query is an autonumber field that I'm using to retrieve the latest record.
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. It appears that the duplications were occuring in the subquery, such that if there was an object that had two (or more) process results with different VE values, then the duplication occurred. For other duplications, this query correctly retrieved only the last record.
Most likely the reason is in your group by section. You group on two columns so you'll get as many rows as there are valid combinations present. But then on your join you use only one column (SerialNumber).
If you want the latest record based on maximum ID and the maximum of id should be fetched based on serialnumber, it may be simpler to use correlated scalar at select portion (there may be typos):
From Results r1
WHERE Details.SN = r1.SerialNumber
AND ID = (SELECT MAX(ID)
FROM Results r2
WHERE Details.SN = r1.SerialNumber)
Thanks. Your explanation about the GROUP BY helped me to understand why I was getting some duplicates, but the solution does not work; the result set was so huge that I had to cancel the attempt to execute the query.
- do you want to get as many rows in the output as there are rows in Details-table (no where conditions)?
- how many rows there are in details table?
- how many rows there are in Results table?
- is SerialNumber indexed in Results-table?
- is ID indexed in results table?
1. Ultimately, no, I want to retrieve rows from the Details table based on certain conditions, say date range or whether one of the fields in the table is equal to (OR LIKE) a certain parameter. But, I thought that retrieving all rows from the Details table (and no more than the total number of rows) was the simplest condition.
2. The sample database I have-and perhaps a small database at that-has 10080 rows in the Details table.
3. 10500 rows. Nearly all objects in the Details table have undergone the process whose results are recorded in the results table (sometimes more than once).
5. Yes, ID is indexed in the results table.
For your information, this is the solution-suggested by someone at another site - that worked:
SELECT Details.*, r1.VE
FROM Details LEFT JOIN [SELECT r1.* FROM Results r1 INNER JOIN (SELECT SerialNumber, Max(ID) As maxID FROM Results GROUP BY SerialNumber) AS r2
ON r1.SerialNumber = r2.SerialNumber AND r1.ID = r2.maxID]. AS r1 ON (Details.SN = r1.SerialNumber);
The actual query I'm using is a bit more complex than this as it involves several more conditions in the LEFT JOIN statement and some more or less simple WHERE queries to limit the information returned from the Details table, but the basic idea of this query does solve the problem I had described.
I've got a stored procedure running across 2 servers which either inserts or updates multiple rows of data into a series of tables from a single source table. I've run into a problem where, if the source table doesn't contain a record for a given ID, MS DTC throws an exception and bombs out of the transaction. e.g.
in the statement, you have a reference to src, but it's not defined. Typo?
I know that the source cannot have more than one row if update is used like this (or at least the results may be incorrect), but could it also be that the source row must have exactly one matching row. But then again, just guessing
I made a simple test case and ran it in both SQL Server 2008 and 2005 without DTC and had no problems. The test was the following:
CREATE TABLE Test1 (
INSERT INTO Test1 (Column1)
CREATE TABLE Test2 (
SET Test1.Column1 = Test2.Column2
SET Test1.Column1 = a.Column2
FROM Test2 a
INNER JOIN Test2 b
ON a.Column2 = b.Column2
In all cases I received 0 rows updated as expected. I was curious since typically if DTC makes a stop it's because an error has occured in some node and it simply rolls back all pending transactions. So I was thinking that there may be another error behind the one you received, but I had no confirmation for that.
One thing you could test is to modify your statement like this:
SET p.Column1 = (SELECT pm.Column1
FROM Property p
inner join Address a
on p.PropertyRef = a.PropertyRef
inner join remotedb.PropertyMaster pm
on pm.PropertyRef = p.PropertyRef)
Perhaps there's a difference in the behaviour or at least you get some other info.
I actually tried something similar on a couple of different machines and only found the problem on the original server so it's almost certainly down to a configuration issue (even though my client's DBA team reckon it's not). I've sent them a copy of DTCPing to do some diagnostics so hopefully that'll find the issue. Failing all else I'll get them to reinstall MSDTC and hopefully that'll mend everything.
If you find out it's a configuration issue and not a broken install, I'd be really glad if you could share the info.
I'm having a hard time to understand what configuration option would modify the behaviour so dramatically since DTC is just a controller and it doesn't have to understand what actually happens under the hood. Just needs to know if everything went as expected or not.
Hopefully you find the solution one way or another without too much pain.
Despite protests from my client's DBA team, the server was upgraded to SP2 (previously SP1) and a hotfix was applied http://support.microsoft.com/kb/937517/en-us[^] - apparently there's a known problem which boils down to an incorrect attention signal being passed to the linked server which forces the linked server to rollback the transaction.
how can i create a transaction so that when ever i update a row of a table no other user can change it or access it..
its like transaction on a account , only one user can operate on an account at a time.
i tried the following sql statement LOCK [ TABLE ] name IN lock_mode
but it gives an error "incorrect syntax near table"
should i create a stored procedure and then use SET TRANSACTION ISOLATION LEVEL SERIALATION after creating a transaction.