مطلبي را روز قبل نوشتم در مورد تعيين اعتبار يك كوئري. اين مورد از آنجايي حائز اهميت مي‌شود كه براي مثال
تغييري در ساختار يكي از جداول حاصل شود. اكنون مي‌خواهيم بررسي كنيم آيا سيستم از كار افتاده يا نه!؟
شما مي‌توانيد نام يك فيلد را تغيير دهيد (حتي اگر اين فيلد در يك رويه ذخيره شده استفاده شده باشد) و هيچ خطايي هم نخواهيد گرفت و اين منشاء دردسرهاي زيادي خواهد بود.
در حالت استفاده از SET NOEXEC ON ، كوئري مورد نظر فقط كامپايل مي‌شود و همچنين از لحاظ نحوي بررسي خواهد شد، اما اين كافي نيست.
مثال زير را در نظر بگيريد:
Create PROCEDURE Test1
AS
SELECT * FROM tblPIDs1
جدول tblPIDs1 در ديتابيس مورد نظر وجود ندارد.
اين كوئري قابل اجرا است. دكمه‌ي F5 را فشار دهيد، بلافاصله رويه ذخيره شده‌ي Test1 براي شما ايجاد خواهد شد.
سپس كوئري زير را اجرا كنيد:
USE testdb
SET NOEXEC ON;
exec test1 ;
SET NOEXEC OFF;
بدون مشكل و بروز خطايي، پيغام زير را نشان مي‌دهد:
Command(s) completed successfully
ايرادي هم وارد نيست چون فقط عمليات parsing و compile صورت گرفته و نه اجراي واقعي رويه ذخيره شده. اينجا از لحاظ دستوري مشكلي وجود ندارد.
در اين نوع موارد مي‌توان از
SET FMTONLY ON استفاده كرد. اين مورد اجراي غير واقعي يك كوئري را سبب مي‌شود (تاثيري روي ديتابيس موجود نخواهد داشت، براي مثال اگر در رويه ذخيره شما عبارت insert وجود داشت، ديتايي insert نخواهد شد) و تنها متاديتاي حاصل را بازگشت مي‌دهد. مثلا نام ستون‌هاي يك كوئري را و همچنين در اين حين اگر خطايي رخ داده باشد، آن‌را نيز ارائه خواهد داد.
USE testdb
SET FMTONLY ON;
exec test1 ;
SET FMTONLY OFF;
با اجراي كوئري فوق خطاي زير ظاهر مي‌شود:
Msg 208, Level 16, State 1, Procedure test1, Line 3
Invalid object name 'tblPIDs1'.
براي اتوماسيون اين توانايي مي‌توان از كوئري زير استفاده كرد:
USE testdb;
SET NOCOUNT ON;
DECLARE @name NVARCHAR(MAX),
@sql NVARCHAR(MAX),
@type CHAR(2), -- object type
@type_desc NVARCHAR(60), -- object type description
@params NVARCHAR(MAX) -- parameters
DECLARE @tblInvalid TABLE (
-- invalid objects
[type_desc] NVARCHAR(60),
[name] NVARCHAR(MAX),
[error_number] INT,
[error_message] NVARCHAR(MAX),
[type] CHAR(2)
);
DECLARE testSPs CURSOR FAST_FORWARD
FOR
SELECT [name] = OBJECT_NAME(SM.[object_id]),
[type] = SO.[type],
SO.[type_desc],
[params] = (
SELECT (
SELECT CONVERT(
XML,
(
SELECT STUFF(
(
SELECT ', ' + [name] +
'=NULL' AS
[text()]
FROM sys.parameters
WHERE [object_id] = SM.[object_id]
FOR XML PATH('')
),
1,
1,
''
)
)
)
FOR XML RAW,
TYPE
).value('/row[1]', 'varchar(max)')
)
FROM sys.sql_modules SM
JOIN sys.objects SO
ON SO.[object_id] = SM.[object_id]
WHERE SO.[is_ms_shipped] = 0
AND SO.[type] = 'P'
OPEN testSPs
FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN TRY
SET @sql = 'SET FMTONLY ON; exec ' + @name + ' ' + @params +
'; SET FMTONLY OFF;'
--PRINT @sql;
EXEC (@sql) ;
END TRY
BEGIN CATCH
PRINT @type_desc + ', ' + @name + ', Error: ' + CAST(ERROR_NUMBER() AS VARCHAR)
+ ', ' + ERROR_MESSAGE();
INSERT INTO @tblInvalid
SELECT @type_desc,
@name,
ERROR_NUMBER(),
ERROR_MESSAGE(),
@type
;
END CATCH
FETCH NEXT FROM testSPs INTO @name, @type, @type_desc, @params
END
CLOSE testSPs
DEALLOCATE testSPs
SELECT [type_desc],
[name],
[error_number],
[error_message]
FROM @tblInvalid
ORDER BY
CHARINDEX([type], ' U V PK UQ F TR FN TF P SQ '),
[name];
توضيحات:
اين كوئري، در ديتابيس جاري كه در قسمت use dbname مشخص مي‌شود، تمامي رويه‌هاي ذخيره شده را به صورت خودكار پيدا مي‌كند. سپس ليست آرگومان‌هاي آن‌ها را نيز يافته و عبارت exec مربوطه را تشكيل مي‌دهد. سپس با استفاده از SET FMTONLY ON سعي در شبيه سازي اجراي تك تك رويه‌هاي ذخيره شده مي‌كند. اگر خطايي در اين بين رخ داد، آن‌ها را در يك جدول موقتي ذخيره كرده و در آخر نتيجه را نمايش مي‌دهد.
ارزش اين كوئري زماني مشخص مي‌شود كه تعداد زيادي رويه ذخيره شده داشته باشيد اما نمي‌دانيد كداميك از آن‌ها بر اساس آخرين تغييرات صورت گرفته، هنوز معتبر هستند يا نه. آيا به قول معروف، سيستم اومد پايين يا خير!؟
نكته:
قسمتي كه از XML استفاده شده جهت
concatenating نتيجه حاصل از كوئري، مورد استفاده قرار گرفته و اين روزها
بحث رايجي است كه در بسياري از سايت‌ها در مورد آن مي‌توان مطالب مفيدي را يافت. راه ديگر انجام آن استفاده از
COALESCE مي‌باشد.
مآخذ:
Check Validity of SQL Server Stored ProceduresWhich of your Stored Procedures are no longer ValidSET FMTONLY ON
