I have a customer who is using vba to pull a result set from an sql server stored procedure into excel. She wants a calculated column added to the result set that gives:
The number days (datediff) between the end date (autend_dte) on one row and the begin date (autbeg_dte) on the next row for each client (clt_num). The rows are to be ordered by client and begin date. The number should be associated with the second row used to calculate the date diff. The first row for each client will have a date diff of 0.
I could do this using a cursor in the stored procedure or a loop in the vba, but I would prefer to do it with the select, but I don't even know where to start.
See expected results below.
CREATE TABLE #testit (
clt_num int NOT NULL ,
autbeg_dte datetime NULL ,
autend_dte datetime NULL)
INSERT INTO #testit (clt_num, autbeg_dte, autend_dte)
SELECT 510, '2004-09-01 00:00:00.000', '2005-09-30 23:59:00.000' UNION ALL
SELECT 510, '2005-10-01 00:00:00.000', '2006-04-06 23:59:00.000' UNION ALL
SELECT 600, '2006-08-01 00:00:00.000', '2006-11-06 23:59:00.000' UNION ALL
SELECT 2529, '2006-01-13 00:00:00.000', '2006-04-11 23:59:00.000' UNION ALL
SELECT 2529, '2005-11-30 00:00:00.000', '2005-12-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-03-29 00:00:00.000', '2006-05-02 23:59:00.000' UNION ALL
SELECT 2602, '2005-11-12 00:00:00.000', '2006-02-27 23:59:00.000' UNION ALL
SELECT 2602, '2006-05-26 00:00:00.000', '2006-06-12 23:59:00.000' UNION ALL
SELECT 2602, '2006-06-18 00:00:00.000', '2006-06-28 23:59:00.000'
Hi All,I want to avoid using cursors and loops in stored procedures.Please suggest alternate solutions with example (if possible).Any suggestion in these regards will be appreciated.Thanks in advance,T.S.Negi
i'm trying to write a batch that will perform a complex task usingset-based selects instead of a row-based cursor. let me know if you canhelp me figure out how.description of what i'm trying to do:there is TABLE1, TABLE2, and TABLE3i want to select each row from TABLE1, do some analysis on the data ofthat row, and then perform an insert of some data into TABLE2, and somedata into TABLE3how do i do this in a T-SQL batch?
I have a long sql batch that does this:1. Gets a list of all tables in user database that start with name COREdeclare@tablenamevarchar(30),@commandvarchar(2000),@cntintegerdeclare GetCOREOids cursor forselect sysobjects.namefrom sysobjectswhere ( OBJECTPROPERTY(sysobjects.id,N'IsUserTable')=1 ) and( sysobjects.name like 'CORE%' or sysobjects.name ='CMNSTRStructGeomBasicPort') and( sysobjects.name not like 'CORESpatialIndex%') and (sysobjects.namenot like 'COREDeletedObjects%') and( sysobjects.name not in('CORERelationOrigin','CORERelationDestination') ) and( sysobjects.id in ( select id from syscolumns where name = 'oid') )for read only2. Populates a temporary table with distinct oids from the list.create table [#tSP3DCoreOid] (oid UNIQUEIDENTIFIER)open GetCOREOidsfetch GetCOREOids into @tablenamewhile ( @@fetch_status = 0)beginselect @command = 'insert into [#tSP3DCoreOid] select distinct oid from' + @tablenameexecute(@command)--print @tablename + ' rows: ' + convert(char,@@rowcount)fetch GetCOREOids into @tablenameend /* while */--Clean upclose GetCOREOidsdeallocate GetCOREOids3. Creates a cursor to get "invalid" oids from 2 other tablesdeclare DanglingRelation cursor forselect oid from [dbo].[CORERelationOrigin] RO where not exists( select oid from [#tSP3DCoreOid] where oid = RO.oid )unionselect oid from [dbo].[CORERelationDestination] RD where not exists( select oid from [#tSP3DCoreOid] where oid = RD.oid )4. Loops thru. the cursor examining each oid and then calls aStoredProc to update another tabledeclare @objectOid uniqueidentifierdeclare @tempOid uniqueidentifieropen DanglingRelationfetch DanglingRelation into @ObjectOidwhile ( @@fetch_status = 0)beginset @tempOid='00000000-0000-0000-0000-000000000000'if left(@ObjectOid,8)='00000002'select @tempOid=oid from COREToDoList where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000003'select @tempOid=oid from COREToDoRecord where Oid=@ObjectOidelse if left(@ObjectOid,8)='00000004'elseselect @tempOid=oid from COREBaseClass where Oid=@ObjectOidif @tempOid = '00000000-0000-0000-0000-000000000000'BEGINexec CORESetObjectIntegrity @ObjectOid, 2ENDfetch DanglingRelation into @ObjectOidendclose DanglingRelationdeallocate DanglingRelationdrop table [#tSP3DCoreOid]Now the #tSP3DCoreOid temp table can sometimes get as many as 7.5million rows and this is making the batch fail on SQLServer as it runsout of memory.I think the problem is the cursor here and would like somefeedback/tips on how to best optimise it.thanks a lotSunit
My problem is basically i need to call a stored proc for each entry in a table, i.e, basically a for loop calling stored procs with parameter coming from the table. I know two ways of doing this .. using cursor and using while loop with temp table. I dont like both approaches. Is there any good practice for this situation..
declare mycur cursor fast_forward for select ID from sometable
open mycur FETCH NEXT FROM mycur INTO @AID
WHILE @@FETCH_STATUS = 0 begin exec dbo.storedproc @AID
Above is the query of the result I want to show this resultset horizontaly for that I have to use the cursor such that @M1=Where Milestone=1 @M2=Where Milestone=2 @M3=Where Milestone=3 and putting this into a temp table with its projectid and taskid. Plz guide me that how to achiev this exactly Swati
I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that. Any thoughts...?
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,
When I use an SSAS cube in Excel the command text is just the name of the cube itself and does not require an MDX query. Is it possible in SSRS to avoid writing an MDX query?
Got a query taking too much time because of lack of cross columns MAX/MIN functions. Consider a similar example where a View is required to reflect distribution of Water among different towns each having four different levels of distribution reservoir tanks of different sizes:In this case the basic table has columns like:
Now suppose I need a query to distribute QuantityPurchased in the Four additional Columns computed on the basis depending on the sizes declared in the last four fields,in the same order of preference.For example: I have to use IIFs to check: whether the quantity purchased is less than Tank_A if yes then Qty Purchased otherwise Tank_A_Size itself for Tank_A_Filled
then again IIF but this time to check:
Whether the quantity purchased less Tank_A_Filled (Which again needs to be calculated as above) is less than Tank_B if yes then Tank_A_Filled (Which again needs to be calculated as above) otherwise Tank_B_Size itself for Tank_B_Filled
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.
