Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
175
questions
0
votes
1answer
21 views
Get availability group and listener of the current DB (dynamic SQL)
I have hundreds of DB on an instance and I want to remove the DB from their AG (each DB is in a separate AG), remove the listener and then delete that AG. I have written the script below to automate ...
0
votes
0answers
18 views
relation “critere1” does not exist error , cursor problem
I have the following error :
relation "critere1" does not exist
CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 43
and here is my PL/pgSQL :
DECLARE
CRITERE1 CURSOR FOR
...
0
votes
0answers
20 views
Is it possible for a FOR LOOP cursor execute operations asynchronously?
I was having a problem in which the number in a table isn't updated between each commit, it was supposed to be incremented 1 by 1 but it was been incremented 2 by 2, I included logs between the ...
0
votes
1answer
20 views
MySQL cursor always exits out of loop
The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop.
Anything wrong here?
I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it ...
2
votes
1answer
93 views
Cursoring over sys.databases skips databases
I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem:
I have the following code to perform database backups.
...
0
votes
2answers
44 views
Overview of leaked/unclosed cursors/statements on DB2 LUW server?
We're encountering a "too many open cursors issue" on DB2/LUW.
Is there any way to get a (database-server-side) list of the corresponding SELECT statements related to these open cursors?
We're doing ...
1
vote
1answer
16 views
Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?
In this example:
DECLARE @KeyField INT, @ValueField INT
DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.ValueField = ...
0
votes
1answer
22 views
Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows
I have a simple database called 'held_toys' (a held toy is a toy which is not on sale yet) which is comprised of a primary key and a foreign key for a Toy. I have a second table called 'toys_on_sale' ...
0
votes
2answers
73 views
How to drop a procedure across a lot of databases (SQL Server 2016)
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 ...
-2
votes
1answer
60 views
SQL Server: Use Implicit or Explicit Cursor? [closed]
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
56 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 (...
7
votes
1answer
173 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
270 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
84 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
168 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
20 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
45 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
988 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
870 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
23 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
24 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
161 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
206 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
42 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
46 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
133 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
60 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
67 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
858 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 ...
6
votes
3answers
958 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
38 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
472 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
685 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
76 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
55 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
673 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
2k 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
6k 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
73 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
47 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
197 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
943 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
556 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
403 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
394 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
72 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
353 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
301 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
...
