Questions tagged [cursors]
SQL Cursor : Database Object That Provisions Access to Records in a Database
159
questions
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
772 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 ...
0
votes
1answer
47 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
19 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
20 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
21 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 ...
1
vote
1answer
74 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
58 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
26 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
818 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 ...
4
votes
3answers
520 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
272 views
Combine multiple stored procedures into one report? [on hold]
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
583 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
32 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
39 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
44 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
495 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
3k 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
58 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
42 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
93 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
567 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
912 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
189 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
273 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
297 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
57 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
257 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
173 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
428 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
524 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
884 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
47 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
2k 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?
0
votes
3answers
2k views
Dynamic conversion of long data to wide with multiple columns
I'm trying to convert long data:
ID|SchoolID|Section|RepScore|SportsScore|PartyScore
1 |20 |1 |23.2 |70.2 |42.3
2 |20 |6 |65.2 |75.8 |52.3
...
2
votes
2answers
1k views
What to do when you need a cursor and you have a columnstore clustered index
I have a very large SQL Server table with a ColumnStore clustered index. The table is very fast to query, so this is good. However I need to do some updates to a large portion of records in the ...
1
vote
0answers
332 views
Why is Oracle SQL slower than cursors?
I am converting an SQL Server system to run on Oracle (11g). The system has all been set up, data loaded, etc. The Oracle system was loaded using all standard default values.
The amount of data is ...
1
vote
1answer
12k views
Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement
Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched.
OPEN mycursor FOR
SELECT * FROM TABLE;
-- mycursor%ROWCOUNT is ...
9
votes
1answer
867 views
sp_cursorprepexec causing 53 million reads?
We are running a Dynamics AX 2012 installation with SQL Server 2012. I know that cursors should not be used anymore but AX is using it and we can't change this behavior so we have to work with it.
...
3
votes
2answers
153 views
Return batches of rows based on one row
Say I have a table like this in SQL Server 2008:
id | name | qty
-------------------
1 | john | 1
2 | bill | 3
3 | mary | 2
4 | jill | 5
I would like to query this ...
2
votes
1answer
51 views
Cursor That Executes a Proc that Creates An Audit Trigger
So, I've been battling this thing for a couple of hours and figured I would reach out for a little but of help. I need to create an audit trigger on all tables of a database (This audit trigger works ...
1
vote
1answer
310 views
Is it possible to write out images from SQL Server to a mapped drive?
I am using a cursor to write out images stored in a database to text.
Code snippet here:
DECLARE @SOURCEPATH VARBINARY(MAX),
@DESTPATH VARCHAR(MAX),
@ext varchar(MAX),
@...
