I have this problem with this code. I don't know why, because before set values.I declare a variable.

Moreover, How i can execute every results that cursor give

Error message: Msg 137, Level 15, State 1, Line 5 Must declare the scalar variable "@ejecutable".

My code:

declare @Userdb sysname
declare @ejecutable nvarchar(max)
set @ejecutable = ''
set @Userdb = 'SD-A-YF-LG-D-SQL30_ICCORE_reader';
declare cr1_database cursor local forward_only
select 'use ' + d.name + ';
if not exist
   select 1
    from sys.database_principals dp
    where dp.name = ''' + @Userdb + '''
   create user ' + QUOTENAME(@Userdb) + '
   from login ' + QUOTENAME(@Userdb) + '
       with default_schema = dbo;
exec sp_addrolemember @RoleName = ''db_datareader''
   , @MemberName = ''' + @Userdb + ''';
from sys.databases d
where d.database_id > 7
open cr1_database;
fetch next from cr1_database
into @ejecutable;
while @@FETCH_STATUS = 0
   print (@ejecutable);
   fetch next from cr1_database
   into @ejecutable;
close cr1_database;
deallocate E cr1_database;
  • I don't see how the query will gives that error message – Squirrel Oct 25 '19 at 7:21
  • I don't get an error from this code. (I fixed the presumed typo on the final deallocate line.) Are you running this from SSMS? What version of SSMS? My guess is that your client tool parses the GO insind the single quotes as a batch separator. My SSMS doesn't do that, hence my question of what tool you are using. – Tibor Karaszi Oct 25 '19 at 8:14
  • 1
    deallocate E cr1_database; remove the E – McNets Oct 25 '19 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.