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 prints where it is but it always starts with BEFORE LOOP and then ends with EXIT. CREATE PROCEDURE getTotal() BEGIN DECLARE HOSTID INTEGER; DECLARE cITEMID INT; declare finished bool default false; DECLARE Total INT; declare cur1 cursor for SELECT itemid FROM items WHERE hostid = 10579; declare continue handler for not found set finished = true; open cur1; loop_1: loop fetch cur1 into cITEMID; SELECT "BEFORE LOOP"; if finished then SELECT "EXIT"; leave loop_1; end if; SELECT "IN LOOP"; -- Test query SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1; -- Final select query will look like this. -- SET @Total := @Total + (SELECT value from history_uint WHERE itemid = cITEMID ORDER BY itemid DESC LIMIT 1); -- SELECT @Total; end loop; close cur1; END // DELIMITER ; Queries: SELECT itemid FROM items WHERE hostid = 10579; | itemid | | 12345 | | 12346 | | 12347 | SELECT value from history_uint WHERE itemid = 12345 ORDER BY itemid DESC LIMIT 1; | value | | 1 | SELECT * from history_uint; | itemid | value | clock (unixtimestamp) | | 12345 | 13 | 4364564654654 | | 12346 | 1 | 4364564654657 | | 12347 | 16 | 4364564654654 | | 12345 | 13 | 4364564654756 | | 12346 | 2 | 4364564654753 | | 12347 | 15 | 4364564654756 | Note: The clock column value is just made up.

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. DECLARE @Filename VARCHAR(256) DECLARE @FileDate VARCHAR(15) DECLARE @Path VARCHAR(50) DECLARE @Name VARCHAR(50) -- specify database backup directory SET @Path = '\MyPath' -- specify filename date SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','') DECLARE db_cursor CURSOR FOR SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb') AND [state_desc] = 'ONLINE' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Name WHILE @@FETCH_STATUS = 0 BEGIN SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak' BACKUP DATABASE @Name TO DISK = @Filename WITH CHECKSUM, COMPRESSION FETCH NEXT FROM db_cursor INTO @Name END CLOSE db_cursor DEALLOCATE db_cursor Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be. I am trying to understand *why* this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb') AND [state_desc] = 'ONLINE' are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)

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 example, for the table: provided order_placed column of both table should be null

`
# req 


+--------------------------------------------------+
|                        req                       |
+--------------------------------------------------+
| req_id | order_placed | contact_id | seq_records |
+--------+--------------+------------+-------------+
| 1      |         null |       1000 |        null |
+--------+--------------+------------+-------------+
| 2      |         null |       1002 |        null |
+--------+--------------+------------+-------------+
| 3      |         null |       1003 |        null |
+--------+--------------+------------+-------------+



+-------------------------------------------------------+
|                               contact                 |
+-------------------------------------------------------+
| contact_id | first_name | order_placed | company_name |  
+------------+------------+--------------+--------------+
| 1000       | dirt       |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1002       | dammy      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1003       | samii      |         null |         Asus | 
+------------+------------+--------------+--------------+
| 1004       | xenon      |         null |       Lenova | 
+------------+------------+--------------+--------------+


CREATE TABLE req (
  req_id bigint(20) NOT NULL,
   order_placed char(1) COLLATE utf8_bin DEFAULT NULL,
   contact_id bigint(20) DEFAULT NULL,
   seq_records bigint(2) DEFAULT NULL,
  PRIMARY KEY (req_id),
  KEY contact_id (contact_id),
  CONSTRAINT req_ibfk_10 FOREIGN KEY (contact_id) REFERENCES 
  contact (contact_id)
) 
/*!40101 SET character_set_client = @saved_cs_client */;

# contact

CREATE TABLE contact (
  contact_id bigint(20) NOT NULL,
  first_name varchar(100) COLLATE utf8_bin NOT NULL,
  company_name varchar(100) COLLATE utf8_bin DEFAULT NULL,
  company_id varchar(100) COLLATE utf8_bin DEFAULT NULL,
  order_placed char(1) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (contact_id),
  KEY index_name (contact_id),
)

` # query used

`
DELIMITER $$
DROP procedure IF EXISTS recordsequence $$
CREATE procedure recordsequence ()
BEGIN

declare companyname varchar(250);
declare recordcount integer default 0;
declare duplcount integer default 0;
DECLARE vfinished INTEGER DEFAULT 0;
declare icount int default 0;
DEClARE records_cursor CURSOR FOR
select c.company_name,count(c.company_name),r.opr_id from contact c, request r where c.contact_id=r.contact_id and r.order_placed is null  group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor;
transfer_records: LOOP
FETCH records_cursor INTO companyname,duplcount;
IF vfinished = 1 THEN
LEAVE transfer_records;
END IF;

begin
set recordcount := duplcount;
set icount := 1;
DEClARE records_cursor1 CURSOR FOR
select c.contact_id,c.company_name from contact c, request r where c.company_name = companyname and c.contact_id=r.contact_id and r.order_placed is null group by c.company_name;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET vfinished = 1;
OPEN records_cursor1;
transfer_records1: LOOP
FETCH records_cursor INTO contactid,companyname;
IF vfinished = 1 THEN
LEAVE transfer_records1;
END IF;

begin

UPDATE contact set reorder_sequence = icount where contact_id = contactid;
set icount := icount + 1;
end;

END LOOP transfer_records1;

CLOSE records_cursor1;

if(recordcount == icount) THEN

select concat('company_name Updated successfully', companyname);

else
select concat('company_name count mismatches please check', companyname);
end if

end

END LOOP transfer_records;

CLOSE records_cursor;

End$$
DELIMITER ;

` the above query is to create a procedure for the steps below 1. To fetch records companyname and duplcount of the company names with the cursor. 2. To fetch contact id of each company names and start a loop for a update statement. 3. To update reorder_sequence table with values like the example given below # expected Result

`
Eg:  contact table

+--------------------------------------------------------+
|                         contact                        |
+--------------------------------------------------------+
| order_placed | contact_id | company_name | seq_records |
+--------------+------------+--------------+-------------+
| null         |       1002 |         Asus | 1           |
+--------------+------------+--------------+-------------+
| null         |       1003 |         Asus | 2           |
+--------------+------------+--------------+-------------+
| null         |       1005 |         Asus | 3           |
+--------------+------------+--------------+-------------+
| null         |       1006 |       Lenova | 1           |
+--------------+------------+--------------+-------------+

` Like the above example i have updated seq_records column with values according to the company_name column provided both order_placed column is null # error A syntax error occurred with code 1064 near second select statement.

I have to make multiple UPDATE statements, but I'm looking for the best approach to do so, in terms of readability and performance. I looked for another answers but the only thing I found is that *"Loops are bad and should be the last resource"*, so that's why I'm asking this question. Basically, I have a M:M table, with 'X' and 'Y' FKs and a BOOLEAN column which value is TRUE by default. In the table with the 'X' ID, I have another FK Column, let's name it Z. Now, I want to UPDATE the BOOLEAN value to FALSE in the M:M table, given certain 'Z' (Basically, only change rows where 'X' corresponds to a 'Z' value given by me). Consider that the same value for 'X' repeats several times in the table, with a variation in the 'Y' value. Of course, I can use a Cursor or a WHILE loop, but as I stated before, I'm not looking for the logic of this, but the best option specially in performance, considering it's going to be a frequent operation, and the volume of data is not little; I'm asking for alternatives, so I'll be sure to pick the best one for my case. ### Here's a detailed example ### ------------------------------ Given the following tables:

CREATE TABLE FirstExampleTable (
    Id INT IDENTITY(1,1) PRIMARY KEY,
	ForeignKey INT NOT NULL, -- There might be multiple rows with the same FK
	FOREIGN KEY (ForeignKey) REFERENCES SomeOtherTable(Id)
);

CREATE TABLE SecondExampleTable (
    Id INT IDENTITY(1,1) PRIMARY KEY
);

CREATE TABLE exampleTable (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FirstFK INT NOT NULL,
    SecondFK INT NOT NULL,
    ThisShouldBeFalse BIT DEFAULT 1 NOT NULL,
    FOREIGN KEY (FirstFK) REFERENCES FirstExampleTable(Id),
    FOREIGN KEY (SecondFK) REFERENCES SecondExampleTable(Id)
);

And the following data:

INSERT INTO FirstExampleTable VALUES
(1), -- ID 1
(2), -- ID 2
(1); -- ID 3

INSERT INTO exampleTable VALUES
(1, 1, 1),
(1, 2, 1),
(1, 3, 1),
(1, 4, 1),
(2, 1, 1),
(2, 2, 1),
(2, 3, 1),
(2, 5, 1),
(3, 3, 1),
(3, 4, 1);

Imagining I want to update all values to FALSE where the ID in exampleTables corresponds to a '1' FK, then it should end up like this:

(1, 1, 0)
(1, 2, 0)
(1, 3, 0)
(1, 4, 0)
(2, 1, 1)
(2, 2, 1)
(2, 3, 1)
(2, 5, 1)
(3, 3, 0)
(3, 4, 0)

I have a table "active_t" in a schema called "test", having the following structure:

instance      active
          IN          1
          SI          0

The ask is to loop through this table and for each active schema(i.e., having active=1), the code should query another table "company_t" having the following structure:

code    deldate
 1234     "some date"
 9876     null
 7321     null
 9097     "some date"

The code needs to retrieve those company codes from company_t which have deldate as null. Once picked up, the code should further run an insert statement and insert some data in the another table "rec_t" in the test schema. The basic code that I tried was this:

SET SERVEROUTPUT ON;
declare
v_sql varchar2(200);
v_cntry active_t.instancename%type;
cursor c1 is
select instancename, active from active_t
where active = '1';
begin
for rec in c1
loop
execute IMMEDIATE 'select code from '||rec.instancename||'.company_t where deldate is null' into v_sql;
dbms_output.put_line(v_sql);
end loop;
end;
/

But I get an error as below:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 10
ORA-06512: at line 10
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

Please help what needs to be fixed. If this simple code works then I will try to implement the insert statement. Thanks in advance.

1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values: [a,b,Null], [c,d,Null], [e,f,Null] 2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having fields (id, x, y) with each ORDERS_TABLE.id (aka identity) returned to ***update*** @MULTIPLE_RESULTS_TABLEVAR to make the values: [a,b,1], [c,d,2], [e,f,3] 3.) But using OUTPUT INSERTED.id INTO @MULTIPLE_RESULTS_TABLEVAR ***adds*** new rows to @MULTIPLE_RESULTS_TABLEVAR yielding values: [a,b,Null], [a,b,Null], [a,b,Null], [NULL,NULL,1], [NULL,NULL,2], [NULL,NULL,3] 4.) I can't find a documentation option or non-kludgy strategy to ***UPDATE*** the existing rows. Specifically I don't want to trust a ( LAST_INSERT_@@SCOPE_IDENTITY - count(MULTIPLE_RESULTS_TABLEVAR.id) ) while echoing to a new #temptable or a CURSOR/LOOP to INSERT then UPDATE with @@SCOPE_IDENTITY seems to defeat the whole purpose of **OUTPUT INSERTED**.

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 (v_fromdate) AND (v_todate) 
                AND a.type_id IN (SELECT type_id FROM key2 WHERE   sessionId=v_sessionId)
                AND a.mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                )
     UNION
                (SELECT  b.combo_mag_id FROM B b 
                WHERE
                b.creation_date BETWEEN (v_fromdate) AND (v_todate) 
                AND b.type_id IN (SELECT type_id FROM    key2 WHERE   sessionId=v_sessionId)
                AND b.combo_mag_id IN (SELECT magid FROM    key1 WHERE   sessionId=v_sessionId order by magid)
                );
				
DECLARE
CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET v_cur_time = now();
SET v_cur_query = 'cursor';
OPEN cs;
SELECT timestampdiff(SECOND,v_cur_time,now()) into v_diff;
SELECT CONCAT('Current Query: ', v_cur_query, ' Time taken: ', v_diff);

Both table A and B have millions of records.I have partitions created in both the tables with partition by range on creation_date.For 3 months of a date range the cursor takes almost 4 mins to execute.But when i tried running the same query in MySQL workbench editor,by setting the parameters for the same date range it took only 22 seconds. Can somebody tell me why it executes faster in an SQL editor, and since i have to use it in the stored procedure, is there any way to optimize it ??

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') BEGIN TRAN DECLARE @tablename nvarchar(250) DECLARE @idcolumn nvarchar(250) DECLARE @command nvarchar(500) DECLARE LU CURSOR LOCAL FAST_FORWARD for select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 order by TABLE_NAME OPEN LU WHILE 1=1 BEGIN FETCH NEXT FROM LU INTO @tablename, @idcolumn IF @@FETCH_STATUS0 BEGIN BREAK END SET @command = N' SET IDENTITY_INSERT '+ @tablename+ ' ON INSERT into '+ @tablename+ ' ('+@idcolumn+') VALUES(-1) SET IDENTITY_INSERT ' +@tablename+ ' Off' PRINT @command END CLOSE LU DEALLOCATE LU ROLLBACK tran which outputs something like this: SET IDENTITY_INSERT AccessiblityLU ON INSERT into AccessiblityLU (AccessiblityID) VALUES(-1) SET IDENTITY_INSERT AccessiblityLU Off I am a beginner and realize Cursors shouldn't be used when there are other options available. I tried adding another cursor to grab the second column from each table (the ordinal position of the columns i need is always 2 luckily). I tried this with no luck: BEGIN TRAN --DECLARE VARIABLES DECLARE @tablename nvarchar(250) DECLARE @idcolumn nvarchar(250) DECLARE @command nvarchar(500) DECLARE @secondcol nvarchar(100) --FIRST CURSOR DECLARE LU CURSOR LOCAL FAST_FORWARD for select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 order by TABLE_NAME OPEN LU while 1=1 BEGIN FETCH NEXT from LU into @tablename, @idcolumn if @@fetch_status0 begin break end DECLARE @secondcol nvarchar(100) --SECOND CURSOR DECLARE secondcolumn cursor local fast_forward for select name from sys.columns where OBJECT_NAME(object_id) = @tablename AND column_id=2 OPEN secondcolumn while 1=1 BEGIN FETCH NEXT from secondcolumn into @seondcol if @@fetch_status0 begin break end deallocate LU deallocate secondcolumn SET @command = N' SET IDENTITY_INSERT '+ @tablename+ ' ON INSERT into '+ @tablename+ ' ('+@idcolumn+', '+@secondcol+') VALUES(-1, ''Unknown'') SET IDENTITY_INSERT ' +@tablename+ ' Off' PRINT @command end Any help would be greatly Appreciated!

Is it possible to pass input parameter into Cursor SELECT statement WHERE clause? For some reason I think it isn't working. I'm trying to pass _TAG and _ITEM_NAME into where clause. DECLARE cursor_test cursor for SELECT itemid FROM items WHERE key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" AND STATUS = '0'; Here is the the Stored procedure: DELIMITER // CREATE PROCEDURE getSomething(IN _HOSTNAME VARCHAR(20), _TAG VARCHAR(20), _ITEM_NAME VARCHAR(50)) BEGIN declare FINISHED BOOL default false; DECLARE cursor_test cursor for SELECT itemid FROM items WHERE hostid = @_HOSTID AND key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME" AND STATUS = '0'; DECLARE CONTINUE HANDLER for not found set FINISHED := true; SET @HOSTNAME = _HOSTNAME; PREPARE STMT1 FROM "SELECT hostid INTO @_HOSTID FROM hosts WHERE NAME = ?"; EXECUTE STMT1 USING @HOSTNAME; DEALLOCATE PREPARE STMT1; open cursor_test; SET @TOTAL_VALUE := 0; loop_itemid: loop fetch cursor_test into _ITEMID; SELECT _ITEMID; if FINISHED then leave loop_itemid; end if; SET @TOTAL_VALUE := @TOTAL_VALUE + (SELECT value from history_uint WHERE itemid = _ITEMID ORDER BY clock DESC LIMIT 1); end loop loop_itemid; SELECT @TOTAL_VALUE; close cursor_test; END //

I have an application that is using ODBC to access a SQL Server 2012 database using SQLSetStmtAttr to set the SQL_SOPT_SS_CURSOR_OPTIONS option to SQL_CO_FFO. This means that its reads from the DB are backed by fast forward-only cursor. This is Ok in most of the cases, however sometimes the performance of the fast forward-only cursor is significantly lower that it would be with a "usual" static one (the one that we would get with default SQL_CO_OFF). **Question:** Is there a way to force SQL Server not to use fast-forward cursors? Obviously, the proper way is to change the application, and that process in on the way, however it's going to take time, and meanwhile I am looking for temporary workarounds. So far the only idea that I have is to use this: (from Fast Forward-only Cursors ) > **Implicit Conversion of Fast Forward-only Cursors** > > Fast forward-only cursors are implicitly converted to other cursor types when: > > - If the SELECT statement joins one or more tables with trigger tables (INSERTED/DELETED), the cursor is converted to a static cursor However, that seems a bit too ugly even for a temporary workaround. Are there maybe better approaches? --- _Side note: Main reason why fast forward-only cursors under-perform in this case is that they do not support parallelism ._ For non fast forward *sys.dm_exec_cursors* gives me:

API | Snapshot | Read Only | Global (0)

while fast forward option is:

API | Fast_Forward | Read Only | Global (0)

The snapshot option is 5x times faster than the fast forward one. If I look at the query plans for both of them, they are not so much different except for *DegreeOfParallelism*. The snapshot is 16 while fast forward is 0. The problem is that we are reading from the views (that we do not have much control over), and these views are sub-optimal in their design.

I am trying to transfer a specific amount of film with an specific quantity from one store_id to another. For this I have tried doing a procedure: ~~~pgsql CREATE OR REPLACE PROCEDURE transfer_inventory( p_film_id INTEGER, p_from_store_id INTEGER, p_to_store_id INTEGER, p_quantity INTEGER ) LANGUAGE plpgsql AS $$ DECLARE v_inventory_id INTEGER; v_current_quantity INTEGER; v_transferred_quantity INTEGER := 0; v_remaining_quantity INTEGER; v_total_available_quantity INTEGER := 0; v_cursor CURSOR FOR SELECT inventory_id, COUNT(*) AS film_count FROM inventory WHERE film_id = p_film_id AND store_id = p_from_store_id GROUP BY inventory_id; BEGIN SELECT COALESCE(SUM(film_count), 0) INTO v_total_available_quantity FROM ( SELECT COUNT(*) AS film_count FROM inventory WHERE film_id = p_film_id AND store_id = p_from_store_id GROUP BY inventory_id ) AS temp; RAISE NOTICE 'Se desean transferir % unidades de la película %.', p_quantity, p_film_id; RAISE NOTICE 'Hay % unidades disponibles para transferir.', v_total_available_quantity; IF v_total_available_quantity = v_remaining_quantity THEN RAISE NOTICE 'Transferencia de % unidades de la película % a la tienda %.', v_remaining_quantity, p_film_id, p_to_store_id; EXIT WHEN v_transferred_quantity = p_quantity; ELSE -- No enough units to transfer RAISE NOTICE 'No hay suficientes películas para completar la transferencia.'; ROLLBACK; EXIT; END IF; END LOOP; -- Cursor closure CLOSE v_cursor; -- Verify if all units where transfered IF v_transferred_quantity SQL Error : > ERROR: Cursor "" does not exist > Where: PL/pgSQL function transfer_inventory(integer,integer,integer,integer) on line 72 in CLOSE Apparently, it's a closure issue of my cursor, but it is being closed after de loop ends. I am new to plpgsql so my solutions are limited.

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 them all in one query, which is smart but doesn't work. I've tried: execute sp_msforeachdb 'drop procedure Usages' I got: > Cannot drop the procedure 'Usages', because it does not exist > or you do not have permission. So I thought maybe I create a cursor for it. Thus I wrote: declare @command nvarchar(max) = ' drop procedure Usages '; declare @databaseName nvarchar(100) declare databasesCursor cursor for select [name] from sys.databases where database_id > 5 open databasesCursor fetch next from databasesCursor into @databaseName while @@fetch_status = 0 begin print @databaseName execute sp_executesql @command fetch next from databasesCursor into @databaseName end close databasesCursor deallocate databasesCursor Again, I got that same message. I thought, maybe it doesn't change the context of the database, thus I prefixed the delete command with the name of the given database, so commands would become similar to drop procedure [SomeDatabase].dbo.Usages, but then I received: > 'DROP PROCEDURE' does not allow specifying the database name as a > prefix to the object name. So, I figured out to dynamically execute use @databaseName so that I can drop procedure in the context of that database. But it's not working. What should I do? I'm stuck at the smart method, and I've spent more time than the stupid method now.

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 , @IsUpdated = IsUpdated from MyTable for update of IsUpdated

I am receiving error:

FOR UPDATE clause allowed only for DECLARE CURSOR

This is the first time I am creating cursor, but as far as I know the statement for update looks correct

I have a strange situation. Using sp_whoisactive I can see this: Ok, with this query, I can see what is triggering ( does this word exists in english? ) it: SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text FROM sys.dm_exec_cursors (SPID) c --0 for all cursors running CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t the result: it's a simple select. Why is this using fetch_cursor? Also, I see a lot of "blank" sql_texts too. Does this has something with this "cursor"? DBCC INPUTBUFFER (spid) shows me this: there's this question Here ( made by me ) but i don't know if this is the same thing. _____________________________________________________________________________ **EDIT1:** Using the query provided by kin, I see this: ___________________________________________________________________________ **EDIT2:** Using Activity Monitor, I can see this: It is the most expensive query ( The first one is intentional, we know about it ). And again, I would like to know, why this select * from... is the reason of FETCH CURSOR... ________________________________________________________________________________ **EDIT3:** This "select * from..." is running from another server ( via linked server ). Well, Now i'm having problems to understand what @kin said. This is the execution plan of the query ( running in the same server of the database): this is now, the execution plan, running in the other server, via linked server: Ok, Not a problem too. And NOW! the execution plan , via **activity monitor** ( the same select * from ):

We have a few SQL Server databases that needs to have trustworthy option set to 'ON' to allow for external assemblies to load, but every time there's any security patch, the option sets to off and the application breaks. I don't want to create multiple SQL agent jobs to constantly run the set trustworthy on command, so I've been trying to develop a cursor that will run this command on the five databases. This is what I have right now, but I'm an getting error saying that trustworthy is not a recognized SET option. Could somebody please look and help fix this? Running on SQL Server 2019 Enterprise Edition.

Declare @db_name varchar (100)
Declare @command nvarchar (200)
Declare database_cursor cursor for
Select name from master.sys.sysdatabases
Where name in ('db1', 'db2', 'db3', 'db4', 'db5')

Open database_cursor

Fetch next from database_cursor into @db_name
While @@fetch_status = 0

Begin
     Select @command = 'ALTER DATABASE' + ''''+ @DB_NAME+ '''' + 'SET TRUSTWORTHY ON'
PRINT @COMMAND
EXEC SP_EXECUTESQL @COMMAND

FETCH NEXT FROM DATABASE_CURSOR INTO @DB_NAME
END

CLOSE DATABASE_CURSOR
DEALLOCATE DATABASE_CURSOR

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 know where to continue paginating from where we last left off from the last cursor position. When it comes to a cursor where it is an auto incrementing id value, it's fairly easily to implement: SELECT * FROM users WHERE id <= %cursor // cursor is the auto incrementing id, ex. 100000 ORDER BY id DESC LIMIT %limit What we're not certain about, is if instead of an auto incrementing id cursor, the only unique sequential identifiers for the cursor are uuid and created_at on the table rows. We can certainly query based on the uuid to get the created_at, and then select all users that are <= created_at but the issue is what if there are multiple instances of the same created_at timestamp in the users table? Any idea how to query the userstable based on uuid/created_at cursor combination to ensure we get the correct datasets (just as if we were using auto incrementing id)? Again, the only unique field is uuid since created_at may be duplicate, but their combination would be unique per row.

Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond... > "Msg 6401, Level 16, State 1 ... Cannot roll back SavePoint. No transaction or savepoint of that name was found." ...but I do not get such errors when SAVE TRAN SavePoint; is at location (B)? I can only imagine it's something to do with the context of the cursor loop, but I don't understand why. ``` -- Premable stuff, opening cursor, etc. here... BEGIN TRAN SAVE TRAN SavePoint; -- (A) WHILE @@FETCH_STATUS = 0 BEGIN; -- SAVE TRAN SavePoint; -- (B) BEGIN TRY -- UPDATE statement here which may fail SAVE TRAN SavePoint; END TRY BEGIN CATCH ROLLBACK TRAN SavePoint; END CATCH; FETCH NEXT FROM Cursor INTO @X, @Y, @Z; END; -- CLOSE & DEALLOCATE stuff here IF @DryRun = 1 ROLLBACK TRAN; ELSE COMMIT TRAN;

The SELECT statement to populate my Sql Server CURSOR can (often) return zero rows. Is it advisable to enclose it within an IF check so the CURSOR it is not Declared, Opened, Closed, & Deallocated ? Or will the RDMS automatically know not to create the CURSOR and/or does an IF based on the exact same SELECT infer the query processing twice?

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N'IW') AND ((A.CALCTIMEHOURS0) AND (A.JOBTYPE3))) AND EXISTS (SELECT 'X' FROM PRODROUTE B WHERE ((B.DATAAREAID=N'IW') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N'PR1526157') OR (B.PRODID=N'PR1526157'))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N'GRIJZEN'))) AND NOT EXISTS (SELECT 'X' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N'IW') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS '1900-01-01 00:00:00.000'})) AND ((C.TODATE={TS '1900-01-01 00:00:00.000'})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE The actual execution plan looks like this. Noticing the server wide setting was set to MaxDOP 1 I tried playing around with maxdop settings. Adding OPTION (MAXDOP 0) to the query, or changing the server settings results in much better performance and this query plan. However, the application in question (Dynamics AX) doesn't execute queries like this, it uses cursors. The actual code captured is this. declare @p1 int set @p1=189527589 declare @p3 int set @p3=16 declare @p4 int set @p4=1 declare @p5 int set @p5=2 exec sp_cursoropen @p1 output,N'SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N''IW'') AND ((A.CALCTIMEHOURS0) AND (A.JOBTYPE3))) AND EXISTS (SELECT ''X'' FROM PRODROUTE B WHERE ((B.DATAAREAID=N''IW'') AND (((((B.PRODID=A.PRODID) AND ((B.PROPERTYID=N''PR1526157'') OR (B.PRODID=N''PR1526157''))) AND (B.OPRNUM=A.OPRNUM)) AND (B.OPRPRIORITY=A.OPRPRIORITY)) AND (B.OPRID=N''GRIJZEN''))) AND NOT EXISTS (SELECT ''X'' FROM ADUSHOPFLOORROUTE C WHERE ((C.DATAAREAID=N''IW'') AND ((((((C.WRKCTRID=A.WRKCTRID) AND (C.PRODID=B.PRODID)) AND (C.OPRID=B.OPRID)) AND (C.JOBTYPE=A.JOBTYPE)) AND (C.FROMDATE>{TS ''1900-01-01 00:00:00.000''})) AND ((C.TODATE={TS ''1900-01-01 00:00:00.000''})))))) GROUP BY A.JOBTYPE ORDER BY A.JOBTYPE ',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 resulting in this execution plan (and unfortunately the same multiple-second execution times). I've tried several things such as dropping cached plans, adding options in the query inside the cursor definition, ... But none of them seem to get me a parallel plan. I've also searched google for quite a bit looking for parallelism limitations of cursors but can't seem to find any limitations. Am I missing something obvious here? The actual SQL build is SQL Server 2008 (SP1) - 10.0.2573.0 (X64) which i realise is unsupported, but I cannot upgrade this instance as I see fit. I would need to transfer the database to another server and that would mean pulling a fairly large uncompressed backup over a slow WAN. Trace flag 4199 doesn't make a difference, and neither does OPTION (RECOMPILE). The cursor properties are: API | Fast_Forward | Read Only | Global (0)

When you need to loop a set of data, is there a benefit of using a SQL Server cursor. Or is using a WHILE loop (see below) just the same. I ask because the WHILE loop seems clearer and easier to understand.

-- loop through a table
DROP TABLE IF EXISTS #LoopingSet;
CREATE TABLE #LoopingSet (RowID INT IDENTITY(1,1), DatabaseName sysname);
INSERT INTO #LoopingSet (DatabaseName) SELECT [name] FROM sys.databases WHERE database_id > 4 ORDER BY name;

DECLARE @i INT = (SELECT MIN(RowID) FROM #LoopingSet);
DECLARE @n INT = (SELECT MAX(RowID) FROM #LoopingSet);
DECLARE @DatabaseName sysname = '';

WHILE (@i  @i;
END;