How to run a command on each online database of a SQL 2000 server ?

• Florent GILAIN

  SSC Enthusiast

  Points: 123

  More actions

  January 2, 2006 at 7:35 am

  #166386

  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),':','-'),' ','-')

• webtekkie

  SSCommitted

  Points: 1901

  More actions

  January 2, 2006 at 3:15 pm

  #612326

  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

   

• vadba

  SSChampion

  Points: 11133

  More actions

  January 3, 2006 at 6:07 am

  #612371

  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).