Questions tagged [cursors]

SQL Cursor : Database Object That Provisions Access to Records in a Database

Filter by
Sorted by
Tagged with
0
votes
0answers
4 views

How to drop a procedure across a lot of databases

I have a simple procedure that exists in over 150 databases. I can delete them one by one, which is the most stupid work to do in the world of software development. Or I can somehow dynamically delete ...
-1
votes
0answers
26 views

SQL Server: Use Implicit or Explicit Cursor?

SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHILE (1=1) BEGIN EXEC 'drop view ' + Table_Name END or DECLARE my_cursor CURSOR FOR SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES OPEN ...
0
votes
1answer
22 views

Procedure MySQL cursor taking a long time to execute

I have a MySQL procedure, with a cursor in it. The sample cursor query is given below DECLARE cs CURSOR FOR (SELECT a.mag_id FROM A a WHERE a.creation_date BETWEEN (...
6
votes
1answer
163 views

If a stored procedure is altered while it's currently being used in a cursor, does the cursor continue using the old query before it was altered?

If a stored procedure (or view) is currently in use within a long running cursor, and I alter that stored procedure, will the cursor continue using the old instance of the stored procedure until the ...
0
votes
1answer
51 views

Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server

I get this error when I execute this cursor. Msg 16958, Level 16, State 3, Line 56 Could not complete cursor operation because the set options have changed since the cursor was declared. But ...
1
vote
1answer
56 views

SQL Server - Cursors - Missing Afterthought - Safeguard against infinite looping in case of missing FETCH NEXT

It was found that this code mistake caused an infinite loop on PROD: DECLARE @BatchID INT DECLARE MyCursor CURSOR FOR SELECT BatchID = ... OPEN MyCursor FETCH NEXT FROM MyCursor INTO @BatchID ...
0
votes
0answers
57 views

Must declare the scalar variable in cursor SQL SERVER

I have this problem with this code. I don't know why, because before set values.I declare a variable. Moreover, How i can execute every results that cursor give Error message: Msg 137, Level 15, ...
0
votes
0answers
19 views

Calculating order rank after union

I'm trying to run a query such as this: select id, rank() over(order by last_name, age, id) as position from ( select id, first_name, last_name, age from public.user union select '925c26bf-5971-...
0
votes
1answer
44 views

Insert top 253 values where value in select distinct tradernames

I am trying to insert top 253 values for each trader from a temp traders table into temp results table. I am using following code to do it. CREATE TABLE #trad ( Trad varchar(50)) IF @trad is ...
6
votes
2answers
813 views

Infinite loop in CURSOR

I'm trying to use a cursor to clean up temp tables when they are no longer needed. I have a small table which has the names of the temp tables along with an identifier. The cursor is stuck in an ...
-1
votes
1answer
230 views

Facing error 'No data found ' while trying to write a query using 'Cursor' PL/SQL in oracle

Hope you're doing well. I'm new to oracle PL/SQL and I'm trying to write a query just with the aim of practicing 'Cursor's . I have three tables with below structures : Student:(Student_id , ...
0
votes
2answers
22 views

finding duplicates and updating a column with values according with number of duplicates using mysql stored procedure

I am using Mysql 10.1.29-MariaDB Database to create a new table. What I am trying to do is to increment a number for each duplicate appearance of a value of company_name in another column. For ...
0
votes
1answer
22 views

sp_BlitzCache and cursors

I'm running sp_BlitzCache (Brent Ozar) to get some information about database performance. The program that "attacks" this database uses cursors everywhere, so sp_BlitzCache gives me several lines ...
0
votes
1answer
37 views

In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!. The problem is that I have a cursor that is crossed, but believe ...
0
votes
1answer
117 views

Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL

Hi everyone hope you're doing well. I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not ...
0
votes
2answers
40 views

Split the results of a query into parts

I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor: An extra 4% for the first quarter with the highest salary. An extra 6% ...
3
votes
2answers
41 views

How do I extend the character limit of a string in management studio

The SQL below works to an extend however when I view the output in SSMS tables with a lot of columns appear to be getting cut of in that the string that is generated by the cursor only appears to go ...
-1
votes
1answer
76 views

T SQL Cursor is only updating row with same value [closed]

I'm using MS SQL Server and I have the below table 'tmp_AVG_Weekly_Sales': RowID SKU Shop Week Avg_Value LAMBDA PMF Value 1 ABC 200 2 1 2 0.13 ...
0
votes
2answers
32 views

SQL USING CAST in a query inside a stored Procedure

I was trying to use a query inside an Stored procedure which has a cast funtion. But when executing the SP I'm getting an error Conversion failed when converting date and/or time from character ...
0
votes
1answer
65 views

Job with a cursor is abnormally slow [closed]

I have a query that takes more than 9 hours to complete. Looking at the execution plan, I find this high-cost operator (query plan link): How can I improve this job?
4
votes
1answer
855 views

How do database operations (write, update, alter) on particular cell in a table are written to disk without overwriting entire file?

When I want to write to or alter particular cell of an excel file/table using python I'll use pandas read_csv then alter value in particular cell and write back to file with to_csv. But writing back ...
5
votes
3answers
696 views

Cursor Replacement for Newbies

I'd like to know what the general replacement is for a cursor. The general implementation of a cursor I see out and about is DECLARE @variable INT, @sqlstr NVARCHAR(MAX) DECLARE cursor_name CURSOR ...
1
vote
2answers
28 views

Cursor-SQL Help- Need another Column for Dynamic SQL

My company decided to change the value "Unknown" to (-1) in ALL of our lookup tables (tables that end with 'LU'). I am trying to write a script to automatically add a value for VALUES(-1, 'Unknown') ...
4
votes
0answers
297 views

Combine multiple stored procedures into one report? [closed]

This is my first time doing this... For my assignment, I need to combine multiple stored procedures into one big report using a cursor. There is only one table Debt from the database college. Here is ...
1
vote
2answers
618 views

Cursor never stops

I appear to have made an error in this cursor and I can't seem to figure out what I have done wrong. I've confirmed that the select pulls back 2 rows. But when I pass it into the cursor to pick ...
-1
votes
1answer
33 views

SQL Server _ Cursor in Dynamic SQL and UPDATE CASE WHEN THEN

I would like to extract the rows that meet the condition in the CASE WHEN condition and put them in the updated table. But the trick is to create a new row every time the condition is met in one of ...
0
votes
1answer
43 views

SQL Server - Cursor and Dynamic SQL

this query is working but not giving me the output I want. I am expecting to have 'FAILED' when one of the two conditions is met at least one time through the rows in the columns [TVF] or [RPPS]. But ...
0
votes
1answer
47 views

Arithmetic overflow for Variable in Openrowset

I try to create a table to store the used storage Information for every Database. Since theese are hosted on different Cluster / Instances, I created a table with all Instance / Server Information. ...
2
votes
2answers
549 views

Pass data from a table row into a create login statement using dynamicSQL [closed]

I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more. We've done NO work concerning cursors/...
0
votes
1answer
1k views

if exists, update else insert, with cursors in stored procedures only returns 1 row

I have 2 tables api(columns - api , api_context, api_id -->primary key) and api_request_summary(columns - api_context). i need to insert the number of times the api_context relevant to a particular ...
2
votes
2answers
4k views

Using cursor to update if exists and insert if not

I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the ...
0
votes
1answer
65 views

SQL job ignoring IF condition

I have a job with a few steps(that run reports information) and i added a query i wrote to the last step. Sometimes the job fails because of factors i can not control (application issues) and my query ...
1
vote
1answer
43 views

How to select info about indexes with specified columns from all databases in the instance using TSQL?

This query selects all the indexes - its names, fragmentation, and some other parameters from the current database. select idx.[name] as [index_name], sc.[name] as [schema_name], obj.[...
1
vote
0answers
126 views

Oracle Max Open Cursors - What does table_ mean?

We're encountering a lot of maximum open cursors recently so we ran a query to check for the maximum open cursors. The query to check for open cursors looks something like this select * from v$...
1
vote
0answers
674 views

HANA SQL procedure Iteration

Here is my requirement for a SAP HANA 2.0 system. 1) Identify users with not having SAML flag enabled from "SYS."USERS" table. Fields that I need from this table are USER_NAME,EXTERNAL_IDENTITY,...
3
votes
2answers
1k views

Cursor causes SSMS to crash

I have a cursor that generates one record of JSON text from a group of tables. The cursor has been making SSMS crash. The script runs for a time then SSMS fails. Below is the code that I have ...
0
votes
1answer
316 views

Getting two or more column values through a Cursor SQL Server

I have two tables like this: Table Experiment (I will just use one roadID (rID) for now) rID | latBegin |longBegin |latEnd |longEnd 041 | 23.875464|-80.456798|30.549879|-83.5465521 041 | 33....
1
vote
2answers
313 views

Stored Procedure return unwanted cursor result

Context I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one. ...
2
votes
1answer
324 views

Can't reduce cost of query plan and get rid of Key Lookup because of cursor

I have tried to create a non-clustered index on the fields that are in the output list which are created_by and Chk1002 .I don't have a column called Chk1002 anywhere . I have read here that it has ...
1
vote
1answer
63 views

Severity 20 - Fatal Error in LOCAL CURSOR after FETCH

This week our database on a SQL2014 SP2 CU11 server started throwing fatal errors (severity 20), on the FETCH statement of code, which could be reduced to: DECLARE @dummy int DECLARE CurName CURSOR ...
1
vote
0answers
285 views

Why does this SQL Server execution plan contain a snapshot cursor for a select statement?

Here is the situation. I have a fairly simple query. It returns ~22k rows: select * from tbl_A When I run this query in SSMS (2017 7.6), I get a fairly simple plan: SELECT <= Clustered Index ...
3
votes
0answers
206 views

In SQL Server is a FAST_FORWARD cursor DYNAMIC by default? [duplicate]

I've been reading the DECLARE CURSOR (Transact-SQL) Books Online article. It states: FAST_FORWARD: Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. and ...
2
votes
2answers
556 views

Need to run select statement across all databases

I am trying to run a query like the one below across multiple databases select ParamValue from Table1 where paramname = 'SYSTEM_LICENSE' Each DB is named something like the one below and some DB ...
3
votes
2answers
2k views

MySQL - UUID/created_at cursor based pagination?

For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we ...
1
vote
2answers
2k views

script all triggers in a database

I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications. Every time I need to reinitialise those database I need to script ...
1
vote
0answers
28 views

The actual utilization of Ref Cursor is at Database level not at the Database Client level. Is this statement correct?

From my understanding, the purpose of a ref cursor is to pass a Result Set among procedures/functions(at the DB level) through a "pointer" without passing the actual Result Set which can consume a lot ...
2
votes
2answers
575 views

sp_cursoropen chooses terrible execution plan

If I execute my (simple) query directly in SQL Server Management Studio... SELECT auftrag_prod_soll.ID FROM auftrag_prod_soll WHERE auftrag_prod_soll.auftrag_produktion = 51621 AND ...
0
votes
1answer
1k views

MySQL Error 1329 - Empty Cursor Issue

I am experiencing an issue with a stored procedure I wrote some time ago that has recently stopped working. The SP populates a cursor and runs through a loop processing the data in another SP. The ...
0
votes
1answer
48 views

Mysql database table cursor

i have written the function below containing a cursor while executing it an error appears . what' wrong please CREATE PROCEDURE processorders() BEGIN DECLARE ordernumbers CURSOR FOR SELECT * ...
1
vote
2answers
3k views

What's the difference between a cursor and a trigger? [closed]

I'm triying to understand why use a Cursosr or really how they work (I'm a newbie). I don't really find a clear purpose for using it. How does it compare to a trigger?