These guys I work with have some sql scripts they run over night and they bog down the server and the machine will be gummed up in the morning etc..
Well, I finally looked at this processing and the culprit is cursors. And cursors within cursors. I would like to just get some opinions about what would be more processor efficient so I can send my boss a link to this thread.
Using a cursors to pull records and update them.
vs
Create script using a scripting language that pulls the records through ADO, loops through them and performs updates as necessary using update statements and the like.
I'm writting a stored procedure to insert new rows into a table, but I need to loop through an exsiting table and create a new record for every row in the old table. So I think what I'm needing to use is a cursor, but I've never worked with cursors before, and I just want to check that I have the right idea on what they are used for and if what I have so far looks ok.
This what I have so far, the StoreFees table only has 5 rows:
DECLARE @StoreFee as smallmoney DECLARE @StoreLineID as int
DECLARE MyStoreFees CURSOR FORWARD_ONLY FOR SELECT LineId, StoreFee FROM StoreFees ORDER BY StoreFee
OPEN MyStoreFees
FETCH NEXT FROM MyStoreFees INTO @StoreLineId, @StoreFee
--Do my Inserts into other tables INSERT INTO OtherTable (...,...,.., @StoreFee) ... ... -- Done working with that row
I am trying to pull the data via fetching rows into a variable.
Begin Fetch Next Into @temp... Select @MainVariable = @MainVariable + @temp < @temp doesn't refresh with next fetch)
Print @temp < this works fine and returns the value for each fetch
End
I can print @temp and the data returns fine, but when I try to cancantenate with the above select, it doesn't refresh @temp wit the column in the table.
I have two procedures. One works well, and the other has a small glitch I cannot figure out. I have placed >>>> at the place where the problem is occuring.
The first procedure, which is working great, is applying a stored procedure to many servers (remote procedure call), but is also polling the local server via a linked server connection. This way, all servers are polled equally.
The second procedure is actually using a SELECT statment to query a system table. This procedure works well on all servers except the local one. I get this error message: Server: Msg 3910, Level 16, State 1, Line 1 Transaction context in use by another session. [OLE/DB provider returned message: Unspecified error]
There seems to be a connection issue. Can someone help me work around this?
Thank you,
Neal
FIRST PROCEDURE (this one works perfectly): truncate table dbidname CREATE TABLE ##dbtemp ( dbname VarChar(50), dbsize varchar (25), dbownervarchar(50), dbid smallint, createdate datetime, statusvarchar(75) ) declare@servernamevarchar(50) declare dbupdate cursor global for select servername from sqlservers where status = 'a' open dbupdate fetch next from dbupdate into @servername while @@fetch_status = 0 begin print @servername EXEC sp_addlinkedserver @servername, 'SQL Server' >>>INSERT INTO ##dbtemp >>>exec (@servername + '.master..sp_helpdb') alter table dbidname alter column sqlservers_id int null insert into dbidname (dbsystemid, dbname) select dbid, dbname from ##dbtemp update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and dbidname.sqlservers_id is null update dbidname set whenupdate = getdate() alter table dbidname alter column sqlservers_id int not null exec sp_dropserver @servername delete ##dbtemp fetch next from dbupdate into @servername end close dbupdate deallocate dbupdate drop table ##dbtemp exec spal_db_files_update
SECOND PROCEDURE (see >>>> to note problem area): truncate table dbidname CREATE TABLE ##dbtemp ( dbname VarChar(50), dbsize varchar (25), dbownervarchar(50), dbid smallint, createdate datetime, statusvarchar(75) ) declare@servernamevarchar(50) declare dbupdate cursor global for select servername from sqlservers where status = 'a' open dbupdate fetch next from dbupdate into @servername while @@fetch_status = 0 begin print @servername EXEC sp_addlinkedserver @servername, 'SQL Server' >>>>INSERT INTO ##dbtemp >>>>exec (@servername + '.master..sp_helpdb') alter table dbidname alter column sqlservers_id int null insert into dbidname (dbsystemid, dbname) select dbid, dbname from ##dbtemp update dbidname set sqlservers_id = sqlservers.sqlservers_id from sqlservers where @servername = sqlservers.servername and dbidname.sqlservers_id is null update dbidname set whenupdate = getdate() alter table dbidname alter column sqlservers_id int not null exec sp_dropserver @servername delete ##dbtemp fetch next from dbupdate into @servername end close dbupdate deallocate dbupdate drop table ##dbtemp
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.
query:
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
I have stored procedure in that I am using a cursor to fetch the row by row. cursor is fetching 75000 records so that the procedure is taking long time. Is there any way to replace the cursor to fetch the records row by row.
We have an app using VB with Access as the front end to SQL. SQL statments were built and sent directly to the server obtaining set-oriented results. Response was fair.
We have a new app developed by an "expert" using VB & Interdev that was to be ported for use on the internet. The app was designed using the same logic to build the SQL statements, but it is also using cursors to retrieve all data. RESPONSE IS TERRIBLE! The designer says that to access SQL over the Web, cursor use is a MUST! True? Not True? If true, and I am stuck with an app using cursors, any basic suggestions on where to look to improve response time?
I am using SQL Server 6.5 and I have a VB routine that updates a field in a tble so it can be used in a primary key. This is run after input of data using bcp. I have noticed on several posts re: using cursors to move through a recordset and was wondering if I could use this functionality to replace my VB routine. I checked the archives and found several messages but I am not sure where to start. My VB routine is:
I first would query the data to return all rows where the value of R08SegmentValue= Null. Then I move through the resultset changing the value of the field R08Segments.
***********************Start of VB Code**************** 'Set initial value for sTReportHeaderIdx With rs1 sTReportHeaderIdx = !ReportIdx & !HeaderIdx sPReportHeaderIdx = sTReportHeaderIdx End With
bCount = 1
'loop through rs and when streportheaderidx changes reset bCount to 1 'otherwise increment bcount by 1 and write to field R08SegmentValue Do While rs1.EOF = False And blnContinue = True DoEvents Do While sPReportHeaderIdx = sTReportHeaderIdx With rs1 .Edit !R08SegmentValue = bCount .Update bCount = bCount + 1 sPReportHeaderIdx = sTReportHeaderIdx .MoveNext If Not rs1.EOF Then sTReportHeaderIdx = !ReportIdx & !HeaderIdx End With Loop sPReportHeaderIdx = sTReportHeaderIdx bCount = 1 Loop
According go textbooks and T-SQL developers experience - using cursors is not recommended, more over they say: avoid cursors where it's possible.
Could someone please recommend any other way to go through the recordset(resultset) forward and backward to perform some search or calculations, if there are a specific requirement for not using front-end tools such as VB or MS Access(please don't ask why), other words - all the work must be performed in T-SQL stored procedure.
Hi I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )
I have a stored proc that merges records from an undeduped table to a deduped table. It is running really slowly. Merging 70 million records against a deduped 70 million is taking 115 hours and counting on decent hardware. Very slow.
I suspect there is significant room for optimization here. Some of my ideas:
- Write/update back to the cursor rather than executing separate UPDATE statements. - Try a dynamic cursor instead of a READ ONLY cursor. - Dump new elements to a separate table or a file and then do a single large INSERT.
Anyone else think any of these ideas will work? Can anyone think of something better?
BTW, I've tried to replaced the procedural cursor code with set based UPDATES/INSERTS but the short version of the story is that that route just didn't pan out. I know that is very common optimization advice.
I've made minor simplifications to the code: - Took out code to handle last line - Took out progress logging code - Removed some DECLARE statements. These are needed to run but it should be obvious what they were supposed to be.
Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.
DECLARE [TM #] CURSOR FOR SELECT * FROM [2004 TERMS];
I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.
I am quite novice in my abilities and I am completely stumped on how to get around using them.
As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?
I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.
Any help or pointers or a link to a good tutorial would be woderful.
declare BadRecords cursor dynamic for select lngZipCodeID from ZipCodes where lngZipCode=@ZipCode and lngZipCodeID<>@NewZipID
/* this is the syntax in Sybase open BadRecords; BadRecordsLoop: loop fetch next BadRecords into BadID; if sqlstate<>0 then leave BadRecordsLoop end if */
I have to convert it to sql , I m just checking whether my syntax is correct or not
open BadRecords while @@fetch_status = 0 begin set @BadId = fetch next BadRecords if @@Error = 0 end
I have doubt in my syntax. Can you help me out in this?
I have a requirement to check a value which MUST be unique forever even if it is removed and readded.I have created a seperate table to maintain this value.
Without using a cursor how would I be able to append a duplicate base value (i.e. smith.j@here.now) with the next sequential value (i.e. smith.j02@here.now)
Any takers?
Oh ya, These values are not manually entered but populated through a DTS script. The existing values are repopulated from historic tables and new entries are added automatically. Initially the values would be populated without a number but a number needs to be generated on duplicates.
I have a Stored Procedure that show RentArrears for a tenant only eg Execute RentArrears '88' where 88 is the tenantID Now I want to execute it for other tenants, I want think of using Cursors to loop thru tenent table , but everybody is frowning at it, what other method can I use to loop thru tenant so as to use my stored procedure Thanks
Can someone tell me what is wrong with this code? I just want to get the account_number field from the accounts table and put it in the results table. Let me clarify, there are over 500 rows in the accounts table and I want to loop through those while setting the accounts.account_number = results.account_number.
declare @account nvarchar
DECLARE Accounts_Cursor CURSOR FOR SELECT account_number FROM accounts OPEN accounts_Cursor FETCH NEXT FROM accounts_Cursor into @account WHILE @@FETCH_STATUS = 0 BEGIN update results set account_number = @account FETCH NEXT FROM accounts_Cursor into @account END
declare @sal char(30), @name char(30), @sal2 char(30), @i int set @i=0
DECLARE Employee_Cursor4 CURSOR for SELECT salary FROM test2 OPEN Employee_Cursor4
fETCH FROM Employee_Cursor4 into @sal while @@fetch_status=0
BEGIN
declare emp10 cursor for select salary, fname from test2 open emp10 fetch from emp10 into @sal2, @name PRINT 'this is NOT the desired output which i wanted from so long :'+@sal while @@fetch_status=0 and @sal2=@sal begin print 'this is the name '+@name fetch next from emp10 into @sal2,@name end close emp10 deallocate emp10
fetch next from employee_cursor4 into @sal print @i set @i=@i+1 end
close employee_cursor4 deallocate employee_cursor4
i HAVE CREATED A TEST TABLE WITH TWO COLUMNS SALARY AND FNAME TABLE STRUCTURE IS LIKE THIS:
salary fname 10 r 20 m 30 n 40 p
when i run the above query it produces this result
this is NOT the desired output which i wanted from so long :10 this is the name r 0 this is NOT the desired output which i wanted from so long :20 1 this is NOT the desired output which i wanted from so long :30 2 this is NOT the desired output which i wanted from so long :40 3
i want it to say the name after every row:
this is NOT the desired output which i wanted from so long :10 this is the name r 0 this is NOT the desired output which i wanted from so long :20 this is the name n 1
this is NOT the desired output which i wanted from so long :30 this is the name m 2
this is NOT the desired output which i wanted from so long :40 this is the name p 3
if any other way to do it please helpppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp
Hi I defined a cursor and executed it...but now i exactly forgot what select statement i had run in the cursor..(forgot the columns that i am extracting )
DECLARE main_Cursor CURSOR STATIC FOR SELECT I.INSTRUMENT_ID, P.PARTY_ID, '(' + CASE P.PARTY_TYPE WHEN 1 THEN 'D' WHEN 4 THEN 'I' ELSE 'N' END + ') ' + P.LAST_NAME + CASE WHEN P.FIRST_NAME IS NOT NULL AND P.FIRST_NAME <> '' THEN ', ' + P.FIRST_NAME ELSE '' END + CASE WHEN P.MIDDLE_NAME IS NOT NULL AND P.MIDDLE_NAME <> '' THEN ' ' + P.MIDDLE_NAME ELSE '' END + CASE WHEN P.NAME_SUFFIX IS NOT NULL AND P.NAME_SUFFIX <> '' THEN ' ' + P.NAME_SUFFIX ELSE '' END AS PARTY_DATA FROM INSTRUMENT I JOIN PARTY P ON I.INSTRUMENT_ID = P.INSTRUMENT_ID WHERE I.RECORDING_DATE BETWEEN @start_date AND @end_date AND I.DELETED_FLAG <> 1
OPEN main_Cursor
FETCH NEXT FROM main_Cursor INTO @instrument_id, @party_id, @party_data
WHILE @@FETCH_STATUS = 0 BEGIN IF @instrument_id != @last_inst_id BEGIN SET @sort_order = 0 END
WHILE LEN(@party_data) > 0 BEGIN SET @sort_order = @sort_order + 1 INSERT INTO ALPHA_INDEX_PARTY_DATA (INSTRUMENT_ID, PARTY_ID, SORT_ORDER, PARTY_DATA) VALUES (@instrument_id, @party_id, @sort_order, LEFT(@party_data, 36)) IF LEN(@party_data) > 36 BEGIN SET @party_data = ' ' + LTRIM(RIGHT(@party_data, LEN(@party_data) - 36)) END ELSE BEGIN SET @party_data = '' END END
SET @last_inst_id = @instrument_id
FETCH NEXT FROM main_Cursor INTO @instrument_id, @party_id, @party_data END
CLOSE main_Cursor DEALLOCATE main_Cursor
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I am new to sql and require some help on cursors?what are they and how and why are they used for???it will be kind enough if anyone helps me in this regards..regardsvishal jain.
We just installed SP3 and the cursor behaviors changed, does anyone knowanything about that? I call this stored procedure from DTS:DECLARE Queue_cursorSCROLL CURSOR FOR SELECT SOURCE_PATH,COMPANY, TABLE_NAME FROMdl.dbo.companiesOPEN Queue_cursorFETCH ABSOLUTE @lineNumber FROM Queue_cursorCLOSE Queue_cursorDEALLOCATE Queue_cursorIf the cursor returns more than one row I get the number of rowsinstead ofthe selection!
During my long SQL-carreer I've managed to steer clear of Cursors. But now I have a project at hand where I do not see a feasable way to NOT use a cursor.
From a pool of available numbers (IMSIs) I need to allocate new ones and add them to a batch table. In total there are three tables affected by the following SP. A batch table storing info on the batch, a table that contains the newly allocated IMSIs and a table holding all IMSIs with a state field to indicate that they are free or not. With in the WHILE loop I need to set the state from 'F'(ree) to 'A'llocated and I have no clue how to update the ImsiCursor. So two questions: Can this be done without a cursor? How to update the State attribute of the current cursor location?
ALTER PROCEDURE dbo.spCreateBatch ( @BatchID AS int , @BatchSize AS int , @ImsiCount as smallint , @PurchaseOrder AS varchar(12) ) AS DECLARE @counter5 AS char(5) DECLARE @FreeIMSIs AS BIGINT SELECT @FreeIMSIs = COUNT(Imsi) FROM tblImsiPool WHERE ImsiRangeID=1 AND STATE='F' IF @FreeIMSIs < @BatchSize BEGIN RAISERROR ('Not enough free IMSIs in range', 16, 1) RETURN END
DECLARE ImsiCursor CURSOR LOCAL FORWARD_ONLY FOR SELECT p.Imsi, p.State FROM tblImsiPool p WHERE p.ImsiRangeID=1 AND p.State='F'
SET NOCOUNT ON BEGIN TRANSACTION CreateBatch DECLARE @BatchID5 AS CHAR(5) SET @BatchID5 = RTRIM(LTRIM(@BatchID)) SET @BatchID5 = REPLICATE('0', 5 - LEN(@BatchID5)) + RTRIM(@BatchID5) INSERT INTO tblBatch (BatchID, Vendor, Creation, Required, BatchSize, ImsiCount, PurchaseOrder, PIN1Default, PIN2Default, MBPINDefault, State) VALUES (@BatchID5, 'GA', DEFAULT, DATEADD(m, 1, GETDATE()), @BatchSize, @ImsiCount, @PurchaseOrder, 'RANDOM', 'RANDOM', 'RANDOM','C')
DECLARE @counter AS int DECLARE @IccIDBase AS VARCHAR(19) DECLARE @IccID AS VARCHAR(20) DECLARE @Imsi AS NUMERIC(15) DECLARE @state AS CHAR(1) SELECT @Imsi = MAX(DefaultImsi) FROM tblSim
OPEN ImsiCursor FETCH NEXT FROM ImsiCursor INTO @Imsi, @State
SET @counter = 0 WHILE( @counter <= @BatchSize ) BEGIN SET @counter5 = @counter SET @counter5 = REPLICATE('0', 5 - LEN(RTRIM(@counter5))) + RTRIM(@counter5) SET @IccIDBase = '89234507' + @BatchID5 + @counter5 SET @IccID = RTRIM(@IccIDBase) + CONVERT( CHAR(1), dbo.luhn( @IccIDBase ) ) IF LEN(@IccID) < 20 SET @IccID = @IccID + 'F'
I am trying to write a stored procedure that will insert rows of data meeting my criteria using an inserted stored procedure that should update each row with a unique id. However, when I run it, all of the rows have the same id. I'm new to cursors so any help is appreciated!!!
--Select accountid, Left(homephone ,3) as areacode Select accountid, Left(homephone ,3) as areacode into #temp From sysdba.contact --Create Activity DECLARE C1 CURSOR FOR SELECT A.AccountID, GetUTCDate() as CreateDate, 'ADMIN' as CreateUser, GetUTCDate() as ModifyDate, 'Admin' as ModifyUser, C.ContactID, '262146' as [Type], 'Welcome Call 1' as Category, 'Welcome Call 20 Queue €“ 02/01' as Description, C.HomePhone as PhoneNumber, 'U6UJ6A0001WB' as userid, 'Welcome Call' as Notes, 'Welcome Call' as LongNotes, 'WC' as userdef2, ac.timezonecode AS TicketNumber from sysdba.ACX_CHECKLIST cl join sysdba.account a on a.accountid = cl.accountid join #temp t on t.accountid = a.accountid join sysdba.areacodexref ac on CAST(ac.areacode AS varchar) = CAST(t.areacode AS Varchar) JOIN sysdba.contact c on c.accountid = a.accountID where cl.SERVFEESCHEDULEDDATE between '12/1/2007' and '1/1/2008'
Open C1 --The stored procedure that puts unique codes in EXEC sysdba.usp_GetNextKeyNested 5, '6UJ6', @ActivityID OUTPUT INSERT INTO sysdba.activity ( ActivityID, Accountid, Createdate, Createuser, Modifydate, Modifyuser, Contactid, [Type], Category, Description, Phonenumber, UserID, Notes, Longnotes, UserDef2, Ticketnumber) VALUES ( @ActivityID, @AccountID, @Createdate, @Createuser, @Modifydate, @Modifyuser, @Contactid, @Type, @Category, @Description, @Phonenumber, @UserID, @Notes, @Longnotes, @UserDef2, @Ticketnumber) FETCH NEXT FROM c1 WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM C1
