sp_executesql ¿qué?

[TSQL_Oz]

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

[TSQL_Oz]

Basically, it wouldn't accept an object name as a name for whatever reason. So I hardcoded the db names on the fly. I found that other variables that would be used as select criteria filters (strings and numbers) don't have a problem.

Anyhoo fixed. New code below.

open settings_cursor
go

declare @db_name nvarchar(128)
declare @sql_command nvarchar(1500) -- for our dynamic sql command within the cursor loop.
declare @count int -- the count of records returned for checking DBOptions

fetch
next
from
settings_cursor
into
@db_name

while
@@fetch_status = 0
begin
set @sql_command = N' select'
set @sql_command = @sql_command + N' @count_OUT = count(*)'
set @sql_command = @sql_command + N' from'
set @sql_command = @sql_command + N' sys.databases'
set @sql_command = @sql_command + N' where'
set @sql_command = @sql_command + N' name = ''' + @db_name + ''''
set @sql_command = @sql_command + N' and'
set @sql_command = @sql_command + N' page_verify_option_desc = ''checksum'''
set @sql_command = @sql_command + N' and'
set @sql_command = @sql_command + N' is_auto_create_stats_on = 1'
set @sql_command = @sql_command + N' and'
set @sql_command = @sql_command + N' is_auto_update_stats_on = 1'
set @sql_command = @sql_command + N' 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 + N' recovery_model_desc = ''simple'''
end
else
begin
set @sql_command = @sql_command + N' recovery_model_desc = ''full'''
end

-- include db chaining for Master database
if @db_name = 'Master'
begin
set @sql_command = @sql_command + N' and'
set @sql_command = @sql_command + N' is_db_chaining_on = 1'
end

--Update: 27/02/2008 - Duane Hennessy
-- for whatever reason sp_executesql was ignoring the @db_name argument in
-- the SQL String. It didn't ignore the count argument though. Go figure.
execute sp_executesql @sql_command, N'@count_OUT int output',@count_OUT = @count output

if @count = 0 -- no records were returned as the settings were wrong.
begin
print 'Altering settings for ' + @db_name + char(13) + char(10)

if @db_name = 'DBAdmin'
or @db_name = 'Master'
or @db_name = 'Model'
or @db_name = 'msdb'
begin
set @sql_command = 'alter database ' + @db_name + ' set recovery simple'
execute sp_executesql @sql_command

set @sql_command = 'alter database ' + @db_name + ' set page_verify checksum'
execute sp_executesql @sql_command
end
else
begin
set @sql_command = 'alter database ' + @db_name + ' set recovery full'
execute sp_executesql @sql_command

set @sql_command = 'alter database ' + @db_name + ' set page_verify checksum'
execute sp_executesql @sql_command

end

if @db_name = 'msdb'
begin
set @sql_command = 'alter database ' + @db_name + ' set db_chaining on'
execute sp_executesql @sql_command
end

-- all databases get these switched on
set @sql_command = 'alter database ' + @db_name + ' set auto_create_statistics on'
execute sp_executesql @sql_command
set @sql_command = 'alter database ' + @db_name + ' set auto_update_statistics on'

execute sp_executesql @sql_command
end
else
begin
print char(13) + char(10) + 'Recommended settings for ' + @db_name + ' are OK' + char(13) + char(10)
end

fetch next from settings_cursor into @db_name
end

-- clean up
close settings_cursor
deallocate settings_cursor

[pinktek]

the basic function of the process you accommplished?

[TSQL_Oz]

Sorry for the late response. Work and all that...

Basically, this is a portion of SQL code that checks databases converted from SQL_7 to SQL_2005. It entire script ensures there are monitoring jobs within the Server and they are running, checks the version number and service packs applied and so on.

This portion checks all databases (you can't see the select statement here) and creates a dynamic SQL string that will set the DBOptions for such things as Backup Type, Auto Stats and so on.

Because this covers three plus different build types, we don't know the name of all the databases, but we do know the options we should apply to each, it dynamically creates the SQL string necessary to check the current settings and alter them if needs be.

We possibly do not need to check them but just run an alter script instead, but we want to report on those databases which had settings that complied and those we needed to alter.