我有个sp是做update stats view的:
BEGIN
set nocount on
DECLARE @execstr VARCHAR (600)
DECLARE @viewname VARCHAR (100)
DECLARE @schemaname VARCHAR (100)
-- Declare cursor
DECLARE views CURSOR LOCAL FAST_FORWARD FOR
select SCHEMA_NAME(v.schema_id),v.name
from sys.views v
order by v.schema_id,v.name
-- Open the cursor
OPEN views
-- Loop through all the tables in the database
FETCH NEXT FROM views INTO @schemaname,@viewname
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the reindex of all indexes of the table
SELECT @execstr=
'UPDATE STATISTICS ['+@schemaname+'].['+@viewname+'] WITH FULLSCAN,NORECOMPUTE'
SELECT @execstr -- For debugging
EXEC (@execstr)
FETCH NEXT FROM views INTO @schemaname,@viewname
END
-- Close and deallocate the cursor
CLOSE views
DEALLOCATE views
set nocount off
END==================================================SQL2005的时候job跑的好好的
upgrade去2008,这个job就fail了
use msdb 跑这个sp的时候才有的,别的database都没问题。
error是 Table 'MSdatatype_mappings' does not exist
有人知道不
BEGIN
set nocount on
DECLARE @execstr VARCHAR (600)
DECLARE @viewname VARCHAR (100)
DECLARE @schemaname VARCHAR (100)
-- Declare cursor
DECLARE views CURSOR LOCAL FAST_FORWARD FOR
select SCHEMA_NAME(v.schema_id),v.name
from sys.views v
order by v.schema_id,v.name
-- Open the cursor
OPEN views
-- Loop through all the tables in the database
FETCH NEXT FROM views INTO @schemaname,@viewname
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the reindex of all indexes of the table
SELECT @execstr=
'UPDATE STATISTICS ['+@schemaname+'].['+@viewname+'] WITH FULLSCAN,NORECOMPUTE'
SELECT @execstr -- For debugging
EXEC (@execstr)
FETCH NEXT FROM views INTO @schemaname,@viewname
END
-- Close and deallocate the cursor
CLOSE views
DEALLOCATE views
set nocount off
END==================================================SQL2005的时候job跑的好好的
upgrade去2008,这个job就fail了
use msdb 跑这个sp的时候才有的,别的database都没问题。
error是 Table 'MSdatatype_mappings' does not exist
有人知道不
这不是说的很清楚了么。找不到那张表。
你少了use msdb
你少了use msdb
msdb.dbo.MSdatatype_mappings 这里 sql2008 r2是有的。
都可以打开看表的内容,应该没有坏跑这个sp前也有use msdb奇怪的
直接用command
use msdb
update statistics dbo.MSdatatype_mappings with fullscan,norecompute
也是一样的error
Table 'MSdatatype_mappings' does not exist