January 2, 2006 at 7:35 am
Hi all,
I need to run a command on each database (which are not offline) of my server, but am unable to find the correct syntax :
set quoted_identifier off
declare @query_rapport varchar(1000)
declare @dbname varchar(128)
declare @parameter varchar(128)
print 'Start report - ' + replace(replace(convert (varchar(22),getdate(),120),':','-'),' ','-')
declare DBcursor cursor for select name from sysdatabases where name not in ('tempdb') AND version is not NULL
open DBCursor
Fetch DBCursor into @dbname
while @@fetch_status = 0
begin
set @parameter = '''report'''
set @query_rapport = 'EXEC sp_change_users_login ' + @parameter
print 'Logins in error from database '+ @dbname + ' :'
print @query_rapport
exec (@query_rapport)
-- Loop to Next Database
Fetch DBCursor into @dbname
end
close DBCursor
deallocate DBCursor
print 'End Report - ' + replace(replace(convert (varchar(22),getdate(),120),':','-'),' ','-')
January 2, 2006 at 3:15 pm
Hi Florent,
I think there is a much easier way of doing this. There is a system stored procedure which is undocumented called sp_msForEachDB. It will run the command in the parameter against each database (excluding offline db's). Running the following should give you what you want...
sp_MSForEachDB 'EXEC sp_change_users_login report'
Hope that helps,
Martin
January 3, 2006 at 6:07 am
Two things:
1. Change your SET statement like this (note the space before EXEC):
set @query_rapport = 'USE ' + @dbname + ' EXEC sp_change_users_login ' + @parameter
2. Since sp_change_users_login requires the parameters to be quoted, are the literal strings in @parameter quoted properly? Refer to the example from BOL:
-- EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
SET @parameter = ' ''Auto_Fix'', ''Mary'', NULL, ''B3r12-36'' '
(all quotes are single quotes, the bold areas are single quotes pairs).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply