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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply