Мы обновляем от SQL Server 2005 - 2008. Почти каждая база данных в экземпляре 2005 года установлена на режим эмуляции 2000 года, но мы переходим к 2008. Наше тестирование завершено, но что мы изучили, то, что мы должны стать быстрее в нем.
Я обнаружил некоторые хранимые процедуры, что или ВЫБЕРИТЕ данные из недостающих таблиц или попробуйте к столбцам ORDER BY, которые не существуют.
Обертывание SQL для создания процедур в НАБОРЕ PARSEONLY НА и захват ошибок в попытке/выгоде только ловят недопустимые столбцы в ПОРЯДКЕ BYs. Это не находит ошибку с процедурой, выбирающей данные из недостающей таблицы. intellisense 2008 SSMS, однако, ДЕЙСТВИТЕЛЬНО находит проблему, но я могу все еще идти вперед и успешно запустить ИЗМЕНИТЬ скрипт для процедуры без него жалоба.
Так, почему мне может даже сойти с рук создание процедуры, которая перестала работать, когда оно работает? Есть ли какие-либо инструменты там, которые могут добиться большего успеха, чем, что я попробовал?
Первый инструмент, который я нашел, не был очень полезен: DbValidator от CodeProject, но это находит меньше проблем, чем этот сценарий, который я нашел на SqlServerCentral, который нашел недопустимые ссылки столбца.
-------------------------------------------------------------------------
-- Check Syntax of Database Objects
-- Copyrighted work. Free to use as a tool to check your own code or in
-- any software not sold. All other uses require written permission.
-------------------------------------------------------------------------
-- Turn on ParseOnly so that we don't actually execute anything.
SET PARSEONLY ON
GO
-- Create a table to iterate through
declare @ObjectList table (ID_NUM int NOT NULL IDENTITY (1, 1), OBJ_NAME varchar(255), OBJ_TYPE char(2))
-- Get a list of most of the scriptable objects in the DB.
insert into @ObjectList (OBJ_NAME, OBJ_TYPE)
SELECT name, type
FROM sysobjects WHERE type in ('P', 'FN', 'IF', 'TF', 'TR', 'V')
order by type, name
-- Var to hold the SQL that we will be syntax checking
declare @SQLToCheckSyntaxFor varchar(max)
-- Var to hold the name of the object we are currently checking
declare @ObjectName varchar(255)
-- Var to hold the type of the object we are currently checking
declare @ObjectType char(2)
-- Var to indicate our current location in iterating through the list of objects
declare @IDNum int
-- Var to indicate the max number of objects we need to iterate through
declare @MaxIDNum int
-- Set the inital value and max value
select @IDNum = Min(ID_NUM), @MaxIDNum = Max(ID_NUM)
from @ObjectList
-- Begin iteration
while @IDNum <= @MaxIDNum
begin
-- Load per iteration values here
select @ObjectName = OBJ_NAME, @ObjectType = OBJ_TYPE
from @ObjectList
where ID_NUM = @IDNum
-- Get the text of the db Object (ie create script for the sproc)
SELECT @SQLToCheckSyntaxFor = OBJECT_DEFINITION(OBJECT_ID(@ObjectName, @ObjectType))
begin try
-- Run the create script (remember that PARSEONLY has been turned on)
EXECUTE(@SQLToCheckSyntaxFor)
end try
begin catch
-- See if the object name is the same in the script and the catalog (kind of a special error)
if (ERROR_PROCEDURE() <> @ObjectName)
begin
print 'Error in ' + @ObjectName
print ' The Name in the script is ' + ERROR_PROCEDURE()+ '. (They don''t match)'
end
-- If the error is just that this already exists then we don't want to report that.
else if (ERROR_MESSAGE() <> 'There is already an object named ''' + ERROR_PROCEDURE() + ''' in the database.')
begin
-- Report the error that we got.
print 'Error in ' + ERROR_PROCEDURE()
print ' ERROR TEXT: ' + ERROR_MESSAGE()
end
end catch
-- Setup to iterate to the next item in the table
select @IDNum = case
when Min(ID_NUM) is NULL then @IDNum + 1
else Min(ID_NUM)
end
from @ObjectList
where ID_NUM > @IDNum
end
-- Turn the ParseOnly back off.
SET PARSEONLY OFF
GO
Вы можете выбрать разные способы. Во-первых, SQL SERVER 2008 поддерживает зависимости, которые существуют в БД, включая зависимости STORED PROCEDURE (см. http://msdn.microsoft.com/en-us/library/bb677214%28v=SQL.100%29.aspx, http://msdn.microsoft.com/en-us/library/ms345449.aspx и http://msdn.microsoft.com/en-us/library/cc879246.aspx). Для просмотра и проверки можно использовать sys.sql_expression_dependencies и sys.dm_sql_referenced_entities.
Но самый простой способ проверки всех хранимых процедур следующий:
При обновлении БД существующая хранимая процедура не будет проверена, но если вы создадите новую, процедура будет проверена. Поэтому после экспорта и экспорта всех хранимых процедур вы получите все существующие сообщения об ошибках.
Вы также можете увидеть и экспортировать код хранимой процедуры с кодом, как показано ниже
SELECT definition
FROM sys.sql_modules
WHERE object_id = (OBJECT_ID(N'spMyStoredProcedure'))
UPDATED: Чтобы увидеть объекты (например, таблицы и представления), на которые ссылается хранимая процедура spMyStoredProcedure, вы можете использовать следующее:
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name
,referenced_server_name AS server_name
,referenced_database_name AS database_name
,referenced_schema_name AS schema_name
, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'spMyStoredProcedure');
UPDATED 2: В комментарии к моему ответу Мартин Смит предложил использовать sys.sp_refreshsqlmodule
вместо воссоздания хранимой процедуры. Таким образом, с кодом
SELECT 'EXEC sys.sp_refreshsqlmodule ''' + OBJECT_SCHEMA_NAME(object_id) +
'.' + name + '''' FROM sys.objects WHERE type in (N'P', N'PC')
получается скрипт, который можно использовать для проверки зависимостей хранимых процедур. Вывод будет выглядеть следующим образом (пример с AdventureWorks2008):
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetManagerEmployees'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetWhereUsedProductID'
EXEC sys.sp_refreshsqlmodule 'dbo.uspPrintError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeHireInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspLogError'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeeLogin'
EXEC sys.sp_refreshsqlmodule 'HumanResources.uspUpdateEmployeePersonalInfo'
EXEC sys.sp_refreshsqlmodule 'dbo.uspSearchCandidateResumes'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetBillOfMaterials'
EXEC sys.sp_refreshsqlmodule 'dbo.uspGetEmployeeManagers'
Мне нравится использовать Display Estimated Execution Plan. Это позволяет выявить множество ошибок без необходимости реального запуска процесса.
У меня была такая же проблема в предыдущем проекте, и я написал TSQL checker на SQL2005, а затем Windows-программу, реализующую ту же функциональность.