-
sp_executesql ¿qué?
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|