Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Can ALTER statement be used within a cursor?

Posted on 2008-06-19
6
1,115 Views
Last Modified: 2010-04-21
My question is can the ALTER statement be used within a cursor? My situation is I have a database I just transferred over to a new server. I restored the database but now I have over 300 orphaned local SQL accounts. I used SSIS to transfer over the logins from the old server so I wouldn't have to worry about the SID issue. But now I have over 300 local accounts that are disabled! Rather than going to every one manually and enabling them I wanted to script this. I haven't worked much with cursors so I'm in dark territory here. I figured I would use a DMV to give me a list of all the users on the server. From that query I wanted to take each user name and pass it as a variable to an ALTER statement that would enable the account. I've written the cursor below but it won't work. Notice how I commented out the PRINT command? I used that line to see if the cursor was working correctly and sure enough it returns all my names as expected but when I try to pass the same variable to the ALTER statement it shoots back an error "Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@lname'." Any help would be appreciated!
declare @lname as nvarchar(128);
 
DECLARE custom_cursor CURSOR fast_forward FOR
select name from sys.syslogins;
OPEN custom_cursor
FETCH NEXT FROM custom_cursor into @lname;
SET @lname = @lname
 
WHILE @@FETCH_STATUS = 0
BEGIN
ALTER LOGIN @lname ENABLE
--PRINT @lname
FETCH NEXT FROM custom_cursor into @lname;
 
end
 
close custom_cursor;
deallocate custom_cursor;

Open in new window

0
Comment
Question by:CuseMMA
6 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21822592
A few alternate solutions:

1) Try building a statement string and use the EXEC/EXECUTE statement to run it within your loop.
2) Run a simple SQL statement to build ALL of the ALTER statements (don't forget the trailing semicolon) and export them all to a file.  Run all of the statements in the file.  (I use this quite a bit, and it is especially helpful for one-time-only conversions.)
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 65 total points
ID: 21823019
this should do it:

declare @sql nvarchar(2000)
declare @lname as nvarchar(128);
 
DECLARE custom_cursor CURSOR fast_forward FOR
select name from sys.syslogins;
OPEN custom_cursor
FETCH NEXT FROM custom_cursor into @lname;
SET @lname = @lname
 
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'ALTER LOGIN ' + @lname + ' ENABLE'
execute sp_executesql @sql
--PRINT @lname
FETCH NEXT FROM custom_cursor into @lname;
 
end
 
close custom_cursor;
deallocate custom_cursor;
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 60 total points
ID: 21824648
Hey chapmandew - I am back, thought you could use a bit of competition :)  but might wait for the next time -seems you have it nailed already... except... what's with the set @lname=@lname ?


declare @sql nvarchar(2000)
declare @lname as nvarchar(128)
 
DECLARE custom_cursor CURSOR fast_forward FOR select name from sys.syslogins
OPEN custom_cursor
FETCH NEXT FROM custom_cursor into @lname
WHILE @@FETCH_STATUS = 0
BEGIN
   set @sql = 'ALTER LOGIN ' + @lname + ' ENABLE'
   print @sql
   exec (@sql)
   FETCH NEXT FROM custom_cursor into @lname
end

close custom_cursor;
deallocate custom_cursor;
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 60

Expert Comment

by:chapmandew
ID: 21824993
Good catch...I have no idea.  It was something I just copied from the author's post.  :)

Nice to have you back...missed ya.  :)
0
 

Author Closing Comment

by:CuseMMA
ID: 31468786
Thanks guys, got it working! I was close just needed to do the extra step with using the sp_executesql. Oh and for everyone's knowledge, that @lname=@lname was a screw up I forgot to take out!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21825034
There he goes again....geez.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question