April 28, 2015 at 9:24 am
I am getting error [[Msg 16924, Level 16, State 1, Line 13
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.
Any suggestions please.
=====================================================
Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)
DECLARE Dropuser_Cursor CURSOR FOR
Select dbname,username from FinalDBleverluser Order by dbname
OPEN Dropuser_Cursor
FETCH NEXT FROM Dropuser_Cursor INTO @dropuser
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' use ' + @dbName+'; Drop user ' + @username+''
Execute (@mSql1)
FETCH NEXT FROM Dropuser_Cursor INTO @dropuser
END
CLOSE Dropuser_Cursor
DEALLOCATE Dropuser_Cursor
=============================================================
April 28, 2015 at 9:32 am
Look carefully at the code below. You have two columns in the select list in the declaration of your cursor.
DECLARE Dropuser_Cursor CURSOR FOR
Select
dbname,
username
from
FinalDBleverluser
Order by
dbname
OPEN Dropuser_Cursor
In the fetch you only have one variable listed to accept values from your cursor and you need two.
FETCH NEXT FROM Dropuser_Cursor INTO @dropuser
Like this:
FETCH NEXT FROM Dropuser_Cursor INTO @dbname, @username;
April 28, 2015 at 10:08 am
Thank You !!
April 28, 2015 at 10:17 am
Just because I'm someone who doesn't like cursors, I feel compelled to offer an alternative.
DECLARE @strSQL Varchar(MAX) = '';
WITH FinalDBleverluser(dbname, username) AS (
SELECT 'db1', 'user1' UNION ALL
SELECT 'db2', 'user2' UNION ALL
SELECT 'db3', 'user3'
)
SELECT @strSQL = @strSQL + 'use ' + QUOTENAME(dbname) + '; drop user ' + QUOTENAME(username) + '; '
FROM FinalDBleverluser;
SELECT @strSQL;
You can then execute your @strSQL all at once. You don't have any error checking in your OP, so I presume you know that all the databases and users that you want to drop have already been checked, so this since execution should suffice.
April 28, 2015 at 10:37 am
Hi Ed Wagner,
i want to delete all the users from the table.But you mentioned only 3 users.
can you provide the code to drop all the users from that table.
April 28, 2015 at 10:37 am
I'd do it using the FOR XML PATH method. I would post the code, but for some reason I can't. Something in the code is causing a post error from my current location.
April 28, 2015 at 10:42 am
Like this using Ed's code.
WITH FinalDBUser(dbname, username) AS (
Select
dbname,
username
from
dbo.FinalDBleverluser
Order by
dbname
)
SELECT @strSQL = @strSQL + 'use ' + QUOTENAME(dbname) + '; drop user ' + QUOTENAME(username) + '; '
FROM FinalDBUser;
SELECT @strSQL;
April 28, 2015 at 10:44 am
Sorry, without having DDL, I just created a CTE to simulate the table. If you substitute it like Lynn did, you'll be all set.
April 28, 2015 at 10:55 am
its working ....thanks Lynn Pettis !!!
April 28, 2015 at 2:16 pm
This is the FOR XML version that Lynn previously commented.
You can find more information in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
DECLARE @strSQL Varchar(MAX) = '';
WITH FinalDBleverluser(dbname, username) AS (
Select
dbname,
username
from
dbo.FinalDBleverluser
Order by
dbname
)
SELECT @strSQL = (SELECT 'use ' + QUOTENAME(dbname) + '; drop user ' + QUOTENAME(username) + '; ' + CHAR(10) + CHAR(13)
FROM FinalDBleverluser
FOR XML PATH(''),TYPE).value('.', 'varchar(max)');
SELECT @strSQL;
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy