-- declare cursor to return the from lat and lon for all segments that do not have address point 109. DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109) OPEN c1 FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId WHILE @@FETCH_STATUS = 0 BEGIN -- insert into table the segId, from lat, from lon and returned segment id from function. INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId END
CLOSE c1 DEALLOCATE c1
As you can see here I am using a Cursor, which in turn calls a function with each row that is processed in the recordset. The function that is called is as follows...
ALTER FUNCTION points_test(@x INTEGER, @y INTEGER) RETURNS INTEGER AS BEGIN -- function to find the closed segment point with address point 109 to the segment specified in procedure. DECLARE @tempDistance FLOAT(4) SET @tempDistance = 1000000 DECLARE @id, @seg, lat, lon INTEGER DECLARE @distance, @xd, @yd FLOAT DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109) OPEN c1 FETCH NEXT FROM c1 INTO @lat, @lon, @id WHILE @@FETCH_STATUS = 0 BEGIN -- calucations to get distance. SET @xd = (@lat-@x) SET @yd = (@lon-@y) SET @distance = SQRT((@xd*@xd) + (@yd*@yd))
-- test if you have shortest distance. IF (@distance < @tempDistance) BEGIN SET @tempDistance = @distance SET @seg = @id END FETCH NEXT FROM c1 INTO @lat,@lon, @id END CLOSE c1; DEALLOCATE c1; RETURN @seg END
(This function works out an equation to get the shortest distance from two parameters passed to the function calculated with data from each row returned within the cursor) As you can see here, this function contains ANOTHER cursor!! Ouch. The fact that their is an SQL query in a function is a killer, but having another embedded cursor there is also a killer - this has virtually killer the application.
So, how best is it for me to correct this. Should I turn the function into a stored procudure? But even if I do this, the nested cursor still remains. I was thinking maybe to have the SQRT equations within the SELECT expression and then wrapped in a MIN() to maybe get the lowest value.
I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:
CREATE PROCEDURE uspSeeAllViews AS SET NOCOUNT ON DECLARE @strMessageVARCHAR(100) DECLARE @strColumnVARCHAR(100) DECLARE @strViewVARCHAR(100) DECLARE @strCommandVARCHAR(250)
DECLARE crsViews CURSOR FOR
SELECT name AS strView FROM sysobjects WHERE type = 'U'
OPEN crsViews FETCH NEXT FROM crsViews INTO @strView WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE crsColumns CURSOR FOR
SELECT name AS strColumn FROM syscolumns WHERE name = @strView
OPEN crsColumns FETCH NEXT FROM crsColumns INTO @strColumn WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @strView + ':' + @strColumn FETCH NEXT FROM crsColumns INTO @strColumn END
100 2006-01-20 84936 1 Sent a reminder letter 105 2006-01-23 134502 1 Called, but no reply 100 2006 01-24 104532 1 Asked to call again later 100 2006-01-24 104532 2 when it is more convenient 100 2006-01-24 104532 3 and the person I require is available
I wish to concantenate the History_text fields together for records with the same Converted_Mortgage_Number, Activity_Date and Activity_Time so:
100 2006-10-24 104532 Asked to call again later when it is moreconvenient and the person I require is available.
To do this I amusing nested cursors so:
USE ar GO DECLARE @mortgage INT, @date datetime, @time INT, @getMortgage CURSOR, @notes varchar(MAX), @notesComplete varchar(MAX), @getDetail CURSOR
SET @getMortgage = CURSOR FOR SELECT Converted_Mortgage_Number, Activity_Date, Activity_Time FROM format_history GROUP BY Converted_Mortgage_Number, Activity_Date, Activity_Time ORDER BY Converted_Mortgage_Number ASC OPEN @getMortgage FETCH NEXT FROM @getMortgage INTO @mortgage, @date, @time WHILE @@FETCH_STATUS = 0 BEGIN SET @notes = '' SET @getDetail = CURSOR FOR SELECT History_Text FROM format_history WHERE Converted_Mortgage_Number = @mortgage AND Activity_Date = @date AND Activity_Time = @time ORDER BY Sequence_Number OPEN @getDetail SET @notesComplete = '' FETCH NEXT FROM @getDetail INTO @notes WHILE (@@FETCH_STATUS = 0) BEGIN @notesComplete = @notesComplete + @notes + ' ' FETCH NEXT FROM @getDetail INTO @notes END PRINT @notesComplete CLOSE @getDetail DEALLOCATE @getDetail FETCH NEXT FROM @getMortgage INTO @mortgage, @date, @time END CLOSE @getMortgage DEALLOCATE @getMortgage GO
But I am getting the error: 'Incorrect syntax near the keyword CLOSE, incorrect syntax near @notesComplete'.
This code does not seem to be returning me all of the data.
Code Snippet DECLARE element_Cursor CURSOR FOR SELECT ElementTypeRecNo FROM dbo.tblTemplateElementType where TemplateRecno = @TemplateRecNo OPEN element_cursor FETCH NEXT FROM Element_Cursor into @ElementTypeRecno --delete from tblElementCPO WHILE @@FETCH_STATUS = 0 BEGIN select @Count = count (*) from tblProjTypeSet where ProjRecno = @ProjRecNo if @Count > 0 begin select @ProjTypeRecno = ProjTypeRecno from tblProjTypeSet where ProjRecno = @ProjRecNo select @Count = count (*) FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo and ProjTypeRecno = @ProjTypeRecNo if @Count > 0 begin DECLARE ElementTypeDep_Cursor CURSOR FOR SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates, Description FROM tblElementTypeDep WHERE (TemplateRecNo = @TemplateRecNo) AND (ProjTypeRecNo = @ProjTypeRecno) AND (PreElementTypeRecNo = @ElementTypeRecno) OPEN ElementTypeDep_cursor FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementTypeRecNo, @PostElementTypeRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates, @Description WHILE @@FETCH_STATUS = 0 BEGIN select @PreElementRecNo = ElementRecno from tblElementCPO where ProjRecNo = @ProjRecNo and IssueRecno = @IssueRecNo and ElementTypeRecno = @PreElementTypeRecno if @PreElementRecno is not null begin select @PostElementRecNo = ElementRecno from tblElementCPO where ProjRecNo = @ProjRecNo and IssueRecno = @IssueRecNo and ElementTypeRecno = @PostElementTypeRecno if @PostElementRecno is not null begin select @Count = count (*) from tblElementDepCPO where ElementTypeDepRecno = @ElementTypeDepRecno and PreElementRecNo = @PreElementRecNo and PostElementRecno = @PostElementRecno if @Count = 0 begin INSERT INTO tblElementDepCPO (ElementTypeDepRecNo, PreElementRecNo, PostElementRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates, Description, ChangeDate, ChangePerson) VALUES (@ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates, @Description, GETDATE(), CURRENT_USER) end
select @Count = count (*) from tblElementAttemptCPO where ElementRecNo = @PostElementRecNo
if @Count = 0 begin
select @Count = count (*) from tblElementAttemptCPO where ElementRecNo = @PostElementRecNo
if @Count = 0 begin select @NextPlanDate = ProjectedCompletionDate, @NextDueDate = RequiredCompletionDate from tblElementAttemptCPO where ElementRecno = @PreElementRecNo end else begin select @NextPlanDate = @StartDate select @NextDueDate = @StartDate end
insert into tblElementAttemptCPO (ElementRecno, ProjectedCompletionDate, RequiredCompletionDate, ProjectedStartDate, RequiredStartDate, ActualStartDate, ActualCompletionDate, AttemptNum, IsCompleted, IsStarted, ResponsibleRoleTypeRecno, ChangeDate, ChangePerson) values (@PostElementRecno, @NextPlanDate, @NextDueDate, '1/11/1900', '1/11/1900', '1/11/1900', '1/11/1900', 0, 0, 0, 0, GETDATE(), CURRENT_USER) end end end FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementTypeRecNo, @PostElementTypeRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates, @Description END
CLOSE elementTypeDep_Cursor DEALLOCATE elementTypeDep_Cursor end FETCH NEXT FROM element_Cursor into @ElementTypeRecno END CLOSE element_Cursor DEALLOCATE element_Cursor end
All, I have a question on a nested Cursor statement, I am having problem getting the @@Fetch_Status to work correctly...
I can get the first cursor to fetch and the the second cursor to fetch all of it rows but when it reaches the end. It should fetch the first cursors next row and @@fetch_Status should be 0.
Is there a way around this???
Thanks, ~Lee
Alter Procedure Summary As Declare @DateID nvarchar(50) Declare @ShortDate nvarchar(30) Declare @SourceIPID nvarchar(50) Declare @SourceIP nvarchar(30) Declare @Packets int
Declare lead_cur CURSOR FOR SELECT tbl_Date.DateID, tbl_Date.ShortDate FROM Tbl_Date WHERE NOT tbl_date.shortdate = convert(char(11),GetDate()) FOR READ ONLY
Declare lead_cur2 CURSOR FOR SELECT SourceIPID, SourceIP FROM tbl_SrcIP FOR READ ONLY
Open lead_cur Open lead_cur2
FETCH NEXT FROM lead_cur INTO @DateID,@ShortDate
While @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM lead_cur2 INTO @SourceIPID, @SourceIP While @@FETCH_STATUS = 0 BEGIN
Set @Packets = (Select ISNULL(Sum(CAST(strNumPackets As Int)),0) FROM tbl_All_SysDATA Where DateID = @DateID and SourceIPID = @SourceIPID) If @packets > 0 Insert tbl_SysIPSummary (DateID,IPID,Packets) VALUES (@DateID,@SourceIPID,@Packets)
FETCH NEXT FROM lead_cur2 INTO @SourceIPID, @SourceIP END FETCH NEXT FROM lead_cur INTO @DateID,@ShortDate END
Close lead_cur Close lead_cur2 DEALLOCATE lead_Cur DEALLOCATE lead_Cur2 GO
I have a Master/Detail table setup - let's call the master "Account" and the detail "Amount". I also have a "black box" stored procedure (BlackBox_sp) which carries out a lot of complex processing.
What I need to do is, for each Account, I need to iterate thtough it's Amount records and call the black box each time. Once I've finished going through all the Amount records, I need to call the black box again once for the Account. This must be done with the Account & Amount rows in a specific order.
So I have something along the lines of
Code Block
DECLARE Total int
DECLARE Account_cur OPEN Account_cur FETCH NEXT FROM Account_cur WHILE FETCH_STATUS = 0 BEGIN
SET Total = 0
DECLARE Amount_cur OPEN Amount_cur FETCH NEXT FROM Amount_cur WHILE FETCH_STATUS = 0 BEGIN
SET Total = Total + Amount
EXEC BlackBox_sp (Amount) END CLOSE Amount_cur
EXEC BlackBox_sp (Total)
END CLOSE Account_cur
Any tips on another approach would be appreciated given the contraints I have.
I need to use a nested cursor in SQL server 2005. I have never done this before. The problem here is that I need to use "@@FETCH_STATUS' twice.Once for my inner loop and once for my outer loop. If there is a way to use two different @@FETCH_STATUS variables at once, I would like to know how. Here is my code. The inner loop is currently pseudo code and I just need to figure out how to code it. DECLARE @mycur1 CURSORDECLARE @mycur2 CURSORDECLARE @InMarketId INTSET @InMarketId=575 -- Hard code for now. Just for testing.DECLARE @test VARCHAR(10)SET @mycur1 = CURSORSET FORSELECT SubDivisionId FROM SubDivision WHERE MarketId=@InMarketIdOPEN @mycur1FETCH NEXT FROM @mycur1 INTO @testWHILE @@FETCH_STATUS = 0 BEGIN PRINT @test-- Begin Inner Nested Loop-- FOR EACH SubDivisionId in SubDivisionSubMarket SDSM delete from SubMarket SM Where SDSM.SubMarketId=SM.SubMarketId -- End of Inner nested Loop FETCH NEXT FROM @mycur1 INTO @test ENDDEALLOCATE @mycur1------------------------------------------Here are my three tables I am scripting agains and a description of each. SubDivision-----------SubDivisionIdMarketIdNameDescriptionCreatedBy SubDivisionSubMarket--------------------SubDivisionIdSubMarketId SubMarket--------------------SubMarketIdCodeDescriptionLastUpdateDate
I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:
DECLARE @LookUpTerm VARCHAR(25) , @SearchCol VARCHAR(255) , @LogonIDToProcess VARCHAR(50) , @Matched CHAR , @Cycle INT = 1 IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees
I'm migrating a fu... dts to SSIS. That's fine. I've got a derived column task for stuff as cdbl(DTSSource("Col014") /100).
But how the source plain file have a lot of columns suddenly I find this:
Function Main()
Fecha=Right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4) If IsDate(Fecha) then DTSDestination("FechaOp") =Fecha Else DTSDestination("FechaOp")=null End IF Main = DTSTransformStat_OK End Function
So that 'Derived Column' is useless at all for to encompass this rule unless to anyone be able to tell me how to implement that conditional snippet inside...
I'll be to use a Script Component Task only for that dammed column when the rest ones already defined.
Does anybody know of some utility or code or script which I could use to rapidly change the server name in the connections of a fairly large number of DTS packages?
I'm convinced that this must be possible from code using the DTS libraries, but I just don't have time to sit down and figure it all out.
I have just started in Sql 6 months ago. I was an IT major in graduate school and am now in my late 30's (career change). It took me 4 years to finally get what I thought was my dream job. Well, I was promised training which I have yet to receive and bascially went from doing basic queries that I did in class in college to doing advance Sql - stored procs, etc. I completely hate it. I do not know if it is from the lack of training or if it just not for me. I am completely bored out of mind. I am also tired of struggling everyday to complete assignments. The first 2 assignments I received were way out of my league. I was told this by a project mgr who did a similiar assignment and they have years of sql experience and it took her 3 months with assistance to complete it. I have been with the company for almost 6 years. I am not sure if I should just complete my current assignments and ask if they can reassign me to a different IT department, look for a new job or just continue to struggle and pull my hair out. Please give me some feedback as to how you would handle this situation.
I really dislike the Management Studio in SQL Server 2005. Having tabsis nice but everything else about it really sucks. What don't you likeabout it? Maybe someone at M$ will read this and actually make somechanges --- for the better (sigh, I wish but I won't hold my breath).For all the $$$$$ Microsoft spends you think they could do better. Itseems every 'new and improved' version has a worse UI than the onebefore. I can hardly stand to use Visio any more. Word has so manydamned annoying features that I spend most of time trying to figure outhow to turn them off rather than getting real work down.Living in the shadow of the evil empire I run into M$ dweebs all thetime. One of the biggest problems M$ has is arrogance. They 'knowbetter' than the whole user community.Microsoft get a clue. Trying listening once in a while.
...and it is because... when i drag and drop my script file into query window unlike query analyzer which selects the current database, new MSSMS selects the users 'default' database.
we maintain 1 script file for each sp/function and ddl/views groupe related stuff in 1 or more files
in query analyzer I just have to select DB where i want to deploy scripts and drag and drop the files from explorer and then then 1) press F5 (to run) 2) press CTL + F4 (to close window) 3) repeat till all scripts are deployed
and now in MSSMS
1) select db (this step is really painful especially when i have to deploy more than 1 script/file) 2) press F5 3) press CTL + F4 4) repeat
is there any settings that could change the behavior of SMS2K5 like old QA2K?
well what i cant do here is change default db to the tartget db before deploying and switch back after deploying (but not a true solution)
I added a maintenance CleanUp task pointing to the right folder I checked the include first-level subfolder box, and delete files based on the age of the file at task run time
I specified "delete files older than " 1 day
But the cleanup task is not working, I have to manually go a delete the files myself.
Am I missing something? should I run the maintenance task first? and then the backup task?
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];
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 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.
