SQL2005 Prb1 -Row Cannot Be Located For Updating. Some Values May Have Been Changed
Mar 3, 2006
hi ,
I recently moved from SQL 2000 to SQL 2005.
The client side is vb6 and using Dcom dll's hosted on the db server.
I have a table which has oninsert trigger
When the recordset is updated in the com class it throws the foll error :
"Row cannot be located for updating. Some values may have been changed since it was last read."
The same code was working with SQL 2000 !!!
Any clues.
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
With cmdCategoriesUpdate .Parameters("@Field1Tag").Value = txtFld1.Text .Parameters("@Field2Tag").Value = txtFld2.Text End With cmdCategoriesUpdate.ExecuteNonQuery() SqlConnection1.Close()
Hi,I am getting "Row cannot be located for updating. Some values may havebeen changed" error when I try to update from visual basic with ado.This happens only when set as default locale on the pc other languagethan English.Anybody can help on this¿Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
We are trying to convert our client applications from SQL2000 to SQL2005 back end.
We have noticed by trace that when setting implicit transactions on, and calling sp_set_approle in:
SQL2000 1) connect 2) Set Approle 3) do stuff which invokes an implicit transaction
SQL2005 1) connect 2) implicit transaction is invoked 3) we call set Approle [which fails because it cant be called in transaction].
does anyone know if Sp_set_approle has changed? perhaps it now updates a system table which invokes the start of an implicit transaction where previously it did not ? We have not changed our client code, so this is a change in SQL2005 behaviour or driver incompatibility issue.
Also interested to hear if anyone else is suffering issues. Cheers Iain
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where h.freight_wagon_serial_number = fw.freight_wagon_serial_number and fw.freight_wagon_type = fwt.freight_wagon_type and h.train_number = t.train_number) < (select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
My client wants to minimize concurrency issues by only updating fields that the user actually changed. Whereas most update stored procedures I have created just updated all of the fields WHERE key=value, they want to update individual fields.
What I came up with so far does not look very efficient and I was wondering if anyone has suggestions for improvement.
Hi All,I'm a relatively newbie to SQL Server 2000, having come from a MySQLbackground.I'm creating my first Trigger statement on a table, and I'd like toknow how I go about performing an update on the row that was changedwhen the trigger was fired.To explain, I have 2 columns, one which contains a member number, theother which contains a flag that is supposed to indicate whether ornot the member number in the row has changed since the last time thetable was processed for updates.So, whenever the value in the member number field [memnum] is updated,I want to set the flag [igproc] to true.The best I've been able to do is:CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]FOR UPDATEASdeclare @key as intIF UPDATE (memnum)select @key = recid from insertedUPDATE dd_testtable set igproc=1 where recid=@keyThis seems to work, but I'd like to know if there's a better way ofretrieving the recid value of the changed row to pass to the UPDATEstatement? Also, I read somewhere in passing that using SELECTstatements and variable assignments within triggers can cause problemswhen called from other applications; in this case it will either be aweb site using ASP.or an application developed in FOXPRO. I can't findwhere I read this originally, so it's entirely possible I imagined itor misunderstood it, but I'd very much appreciate it if someone couldconfirm whether or not this is the case?Many, many thanks in advance!Much warmth,Murray
I had a SQL2000 Server with a simple database. I did an "in place" upgrade to SQL2005 expecting the DB Server and databases to upgrade. However, now when I run SQL Server Management Studio and enter the query:-
I'm pretty new to sql server and now I need to create a script that:
Compares the values of the row with the same id as the row with the update table(which has updated values in it) in which the price, description or replacement part have been changed.
If they did change they should be updated and if they haven't been changed nothing should happen...
I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below
update A set df_date1 = max(df_date1) from b where a.account_id = b.account_id
I get the error message Server: Msg 157, Level 15, State 1, Line 3 An aggregate may not appear in the set list of an UPDATE statement.
Is there another way to do this with a subselect and update?
I have a table named 'Personal' that has a field that contains values that represent colors (ex: BLACK, BROWN, BLUE, etc.). How do I run an update statement to update the values to defined set. For example (BLACK = BLK, BLUE = BLU, etc.)?
The above script works OK on a one-off basis but....
Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).
Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?
I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.
ColumnA ColumnB ColumnC ------------------------------- Alice Lukas Alice.Lucas James Redford James.Redford James Redford James.Redford Michael Jackson Michael.Jackson John Brown John.Brown John Brown John.Brown John Brown John.Brown George Gotham George.Gotham
I want to update duplicated values at ColumnC like:
Alice Lukas Alice.Lucas James Redford James.Redford James Redford James.Redford1 Michael Jackson Michael.Jackson John Brown John.Brown John Brown John.Brown1 John Brown John.Brown2 George Gotham George.Gotham
How can i do it?
Thanks in advance!
Note: Table is for creating email aliases from names...
I want to check if the tbaddress.address1 tbaddress_address1 if it is =null or empty then make it = tbaddress.address2 tbaddress_address2, at the same time I make the tbaddress.address2 tbaddress_address2, null. Otherwise leave it as it is.
Actually, I am working with the report and the tbaddress.address1 carrying the apt number. if there is no aprt # then the line print blanks. so that brings the empty line between the street address and the citystate line. That is what i am tryig to cover. There could be a way to do this from the crystal report but I am thinking of doing this way cause I can't get the solution to do it from the crystal report.
Hello, I have been SSIS for a bit now, but really only it its most straight forward simple tasks I have the following issue which for the life of me I can't seem to sort out
1)Excel Source - - -> all data pushed into a initial SQL table
2) Data in initial SQL table gets sorted, certain fields get made UPPER , right trim that sort of thing
3) now I select a set of distinct values of a certain column in the SQL table and get a list of maybe 60 results
these results I want to enter into a table in a different database on same server, the table in the destination db has a identity column and a value column
adding / appending all the distinct value from the import data works fine,
but I want to add only the values which are not already in the destination table what is the best way to update the destination table, should I be using stored proc or is there a easier way in SSIS to do this
Hi, I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement. I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'." The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int) AS BEGIN WITH SearchResults AS( UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price ) SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize END
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0)) FROM facttable FT LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID (In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
I need to get rid of negative values in the same ProductCat by adding surplus values to the negative values in the same ProductCat ie. This is what I ant the table to look like.
ProductCat Product Qty NewQty TypeA ProductA -10 -5 (Add ProductB to ProductA in the same ProductCat to reduce the deficit) TypeA ProductB 5 0 (Due to the fact that I added 5 to ProductA NewQty must be reduced by 5) TypeB ProductX 8 3 TypeB ProductY -5 0 (Deficit only has to be reduced to 0 where possible therefor the balance of 3 for ProductX)
Note the values in Qty can stay as the same. I am interested in the New values. Could someone please help me with the correct method of doing this?
Im trying to update the values int the database and the query is executed correctly but database is not updated and the returned message is one, I really dont know whats causing this here is my code.1 private void UpdatetheDatabase(string connection) 2 { 3 SqlConnection connect = new SqlConnection(connection); 4 SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " + 5 " SET [Name]=@Name,[City]=@City, " + 6 " WHERE [ID]=@ID", connect); 7 update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = txtCustomerName.Text; 8 update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = txtCity.Text; 9 update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ID; 10 connect.Open(); 11 int x = update_customer_details.ExecuteNonQuery(); 12 connect.Close(); 13 StatusLabel.Text = x.ToString(); 14 } your help will be highly appreciated.
HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT
I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.
I have a main table named Office and 4 other tables that have the Office table ID field value in them. I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.
I have only thought of 2 solution and I don’t like either one. 1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.
2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.
Hello! I have a "current" table which users update on a daily basis, it holds forecast data. This table is designed to only hold 7 days of data and is therfore always rolling over by date. What I want to do is have a procedure that copies this "Current" table to a "History" table every day so the historical information is stored. The copy is simple, but how do I check for and over ride values that are already in the "history" table with the newest values from the "current" table because the "current" values have the possibility of changing a few times while the forecast is updated. I can define a primary key but am not sure how to check for and update the necessary values?
hi there, i am new to sql server database.i am doing small projects right now using asp.net and sql to create webpages (very basic webpages) My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course . table1 student id is the primary key refrencing table 2 student id. Now i delete a record in table 1 which will in turn also get deleted in table 2 . so for eg if i have three records 1 ,2 and 3 ....then i delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table 2....now i want 3 to become 2 in both table 1 and table 2...so that i dont have empty space between two student id's 1 and 3. so this is my problem....if any one can help me out with suggestions please do.
I have a task I wrote which does not always update the property value (as seen in the properties pane) Basically, change something on the form, then update the task host property with: this.taskHostValue.Properties["Duration"].SetValue(this.taskHostValue, Convert.ToInt32(spnDuration.Value));
Stepping through this, it does exactly what it is supposed to. Having a look at the property value, it confirms it has changed. Reopening the UI and resetting all the controls returns the expected results.
The package however does not realise it has changed. There is no * next to the package name in the top tabs. As long as the package thinks it is unchanged, SaveXML does not get called either so the tasks do not persist.
Changing the value on the properties pane works fine though.
The frustrating thing is this is slightly random. Slight in the sense that sometimes it works but most of the time it does not.
The sample code I used was the MS download IncrementTask (Which works BTW) so I can't see it as being a VS / SSIS bug but rather something I am / am not doing. 3 tasks I have written all behave the same. I have to "nudge" them before savign the package.
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
Hi! i'm dani, from spain. I´m looking for sombody to help me. The trouble is as i have indicated. I get the next error when my page aspx needs to acced to the SQL Bd: "Error of beginning of session of the user '(null)'. Reason: it is not associate to a connection of confidence SQL Server." I hope a reply. Thanks and greethings.
