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 ...
