February 25, 2014 at 3:14 am
Thanks in Advance 😀
I have been using Dymanic Cursors without any problems until yesterday
example of Dymanic Cursor code
DECLARE @sql nvarchar(1000)
DECLARE @UsersID int
SET @sql = 'DECLARE Dyn_cursor CURSOR FOR SELECT DISTINCT ID FROM tblUsers'
Exec(@SQL)
OPEN Dyn_cursor
FETCH NEXT FROM Dyn_cursor into @UsersID
WHILE (@@FETCH_STATUS = 0)
BEGIN
--- Do Stuff
FETCH NEXT FROM Dyn_cursor INTO @UsersID
END
CLOSE Dyn_cursor
DEALLOCATE Dyn_cursor
now when Stored Procodure runs i get the following error
A cursor with the name 'Dyn_cursor' does not exist.
Yesterday the only thing i did was to install the TFS Plugin for Source Control.... for the plugin to install i have to install team explorer 2010 too..... I havent yet add the databases to TFS.
Would anything have changed in the security to stop cursors from working?
I use the Domain Users group to allow access to databases, with the DB Roles DataReader & DataWriter (Connect, Read & Excute Basically)
February 25, 2014 at 3:30 am
I have search but cant find a reason for this.... seen problems with TSQL Code
Have delete and recreated the Group Account.
My Account DB_Ower does the same thing, the sa account does the same thing
get the same error
A cursor with the name 'Dyn_cursor' does not exist.
I havent yet re-started SQL or Server itself
February 25, 2014 at 4:51 am
All Sorted found the problem.......
The Default Cursor has changed to Local... Needs to Global for Dymanic Cursors to work
February 25, 2014 at 7:27 am
You would do yourself a favor if you stopped using cursors for doing row level modifications. Cursors are horrible for performance and really should only be used for some administration purposes and a few very specialized times. Looping through every row in a table to "Do Stuff" is not one of those times.
Also, why are you creating your cursor using dynamic sql?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply