I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.

Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.

I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.

Snippet that is not working:

declare @db_name varchar(80)

declare @sql_command nvarchar(1500) -- for our dynamic sql command within the cursor loop.

fetch

next

from

settings_cursor

into

@db_name



while

@@fetch_status = 0

begin

print 'CHECKING DBOPTIONS FOR ' + @db_name + ' - ( CHECKSUM, CREATE & UPDATE STATS, FULLRECOVERY)'



set @sql_command =' select'

set @sql_command = @sql_command + ' count(*)'

set @sql_command = @sql_command + ' from'

set @sql_command = @sql_command + ' sys.databases'

set @sql_command = @sql_command + ' where'

set @sql_command = @sql_command + ' name = ''@db_name'''

set @sql_command = @sql_command + ' and'

set @sql_command = @sql_command + ' page_verify_option_desc = ''checksum'''

set @sql_command = @sql_command + ' and'

set @sql_command = @sql_command + ' is_auto_create_stats_on = 1'

set @sql_command = @sql_command + ' and'

set @sql_command = @sql_command + ' is_auto_update_stats_on =1'

set @sql_command = @sql_command + ' and'



-- select recovery model based upon database name.

if @db_name = 'DBAdmin'

or @db_name = 'Master'

or @db_name = 'Model'

or @db_name = 'msdb'

begin

set @sql_command = @sql_command + ' recovery_model_desc = ''simple'''

end

else

begin

set @sql_command = @sql_command + ' recovery_model_desc = ''full'''

end



-- include db chaining for Master database

if @db_name = 'Master'

begin

set @sql_command = @sql_command + ' and'

set @sql_command = @sql_command + ' is_db_chaining_on = 1'

end



-- execute sql command.

--print @sql_command



declare @count int

execute @count = sp_executesql @sql_command, N'@db_name nvarchar(20)',@db_name=@db_name



if @count = 0 -- no records were returned as the settings were wrong.

begin

select 'Issue with settings. altering now'



if @db_name = 'DBAdmin'

or @db_name = 'Master'

or @db_name = 'Model'

or @db_name = 'msdb'

begin

alter database [@db_name] set recovery simple

alter database [@db_name] set page_verify checksum

end

else

begin

alter database [@db_name] set recovery full

alter database [@db_name] set page_verify checksum



end



if @db_name = 'msdb'

begin

alter database [@db_name] set db_chaining on

end



-- all databases get these switched on

alter database [@db_name] set auto_create_statistics on

alter database [@db_name] set auto_update_statistics on

end

else

begin

select 'all settings for ' + @db_name + ' are good'

end



fetch next from settings_cursor into @db_name

end



-- clean up

close settings_cursor

deallocate settings_cursor