Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [cursors]

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

0
votes
2answers
21 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
16 views

why does MySQL Trigger that uses cursor only fetch one value [closed]

I have created a Trigger after INSERT on the table AcademicYearTermLevel which uses a cursor value fetched from another query. I want to use each cursor value in a while loop and insert rows into ...
0
votes
1answer
60 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
799 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
205 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
26 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') ...
3
votes
0answers
129 views

Combine multiple stored procedures into one report?

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
564 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
30 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
38 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
35 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
417 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
715 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
2k 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
52 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
41 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
73 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
364 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
712 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
100 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
202 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
245 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
40 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
198 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
139 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
261 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 ...
2
votes
2answers
965 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
1k 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
437 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
574 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
46 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
227 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
8k 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
784 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
149 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 ...
3
votes
1answer
43 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
265 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), @...
1
vote
1answer
103 views

How to update price where value of rows exist in value of column in sql?

i want to update column p1 but p1 same product. update column p1 where example g6-1se in C1 exist in column name and update value null in column p1 in rows g6-1se by value p1 in row g6-1sr. in rows ...
1
vote
1answer
67 views

From a user table check if one of them exists on a table

I'm developing a database with SQLServer 2012 SP2. I have these tables: CREATE TABLE [dbo].[Code] ( [CodeId] INT IDENTITY (1, 1) NOT NULL, [Serial] NVARCHAR (...
3
votes
3answers
12k views

how to avoid the “An INSERT EXEC statement cannot be nested” exception on this situation?

when I run the following script, it runs fine: declare @temp table ( name varchar(255), field varchar(255), filename varchar(255), filegroup varchar(255), size varchar(255), ...
1
vote
1answer
378 views

Cursor to insert .pdf files into a table

I need to insert all .pdf files from a folder, in a table, according to its number. I could get a list of files ( I have all .pdfs inside a folder ): exec master.sys.Xp_dirtree '...
0
votes
1answer
260 views

Retrieving data from stored procedure's cursor

I want to use the ResultSet in java program to get data from Stored procedure's cursor. Is that possible with SQL Server? My code: CREATE PROCEDURE dbo.SampleProcedure AS DECLARE @FirstName ...
1
vote
1answer
241 views

Set cursor rows command

I'm new to sqlserver and i'm learning about t-sql cursor. I've seen in a book called "t-sql user guide" an example of cursor declaration. There is a command to return more rows for each fetch: ...
0
votes
1answer
62 views

Maintenance plan using cursor statement

I have a cursor which gives parameters to columns to executes the stored procedure in order to update my table. Cursor statement is following: declare @ColumnName nvarchar(30) declare @ColumnID int ...
3
votes
3answers
625 views

Expensive Query not showing in extended events trace

I have a customer who is reporting that a query appears in the activity monitor expensive query list with a very high executions per minute. The query is a FETCH API_CURSOR000000000005268C statement. ...
-2
votes
2answers
2k views

FOR UPDATE clause allowed only for DECLARE CURSOR

Can anyone Please advice what is wrong with this part of my cursor statement: declare @ColumnName nvarchar(30) declare @IsUpdated tinyint declare @UpdateCursor cursor select @ColumnName = ColumnName ...