Sreek (Sreek@discussions.microsoft.com) writes:
[quoted text, click to view]

There is actually one solution:

sp_dbcmptlevel db, 65

However, this also makes a lot of good features unavailable, so it is not
really a serious option.

In our shop we use our own load tool that performs this checking for us.
The behaviour of SQL Server is entirely unacceptable for enterprise
development, and I very strongly disagree with the gentleman who claimed
this behaviour to be desirable.

Here are two items on Connect that you can vote on:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260762

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127152


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Even with deferred name resolution, you can at least perform cursory
validation of stored procs with SET FMTONLY ON:

SET FMTONLY ON
GO

EXEC dbo.usp_MyProcedure
@MyParameter=NULL
GO

SET FMTONLY OFF
GO

This method is not as thorough as actually executing procs but it will at
least catch DNR issues, barring dynamic SQL. The script below will generate
a script to exercise all procs, views ans functions with SET FMTONLY ON:

SET NOCOUNT ON
--procedures and functions
SELECT
CASE r.ROUTINE_TYPE
WHEN 'PROCEDURE' THEN 'Procedure'
WHEN 'FUNCTION' THEN
CASE
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsTableFunction') = 1
THEN 'TableFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsScalarFunction') = 1
THEN 'ScalarFunction'
WHEN
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsInlineFunction') = 1
THEN 'InlineFunction'
END
END AS ObjectType,
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS ObjectName,
REPLICATE(N'NULL,',
ISNULL((SELECT COUNT(*) AS Parameters
FROM INFORMATION_SCHEMA.PARAMETERS p
WHERE
p.IS_RESULT = 'NO' AND
p.SPECIFIC_SCHEMA = r.ROUTINE_SCHEMA AND
p.SPECIFIC_NAME = r.ROUTINE_NAME), 0)) AS Parameters
INTO #Objects
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE
ROUTINE_TYPE IN ('PROCEDURE', 'FUNCTION') AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)), 'IsMSShipped') = 0
UNION ALL
--views
SELECT
'View' AS ObjectType,
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS ObjectName,
'' AS Parameters
FROM INFORMATION_SCHEMA.TABLES t
WHERE
TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0

--remove trailing comma from parameter list
UPDATE #Objects
SET Parameters = LEFT(Parameters, LEN(Parameters) - 1)
WHERE RIGHT(Parameters, 1) = N','

--generate invocation scripts
SELECT
CASE ObjectType
WHEN 'View' THEN 'SELECT * FROM '
WHEN 'Procedure' THEN 'EXEC '
WHEN 'ScalarFunction' THEN 'SELECT '
WHEN 'InlineFunction' THEN 'SELECT * FROM '
WHEN 'TableFunction' THEN 'SELECT * FROM '
END +
RTRIM(ObjectName) +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ' '
WHEN 'ScalarFunction' THEN '('
WHEN 'InlineFunction' THEN '('
WHEN 'TableFunction' THEN '('
END +
Parameters +
CASE ObjectType
WHEN 'View' THEN ''
WHEN 'Procedure' THEN ''
WHEN 'ScalarFunction' THEN ')'
WHEN 'InlineFunction' THEN ')'
WHEN 'TableFunction' THEN ')'
END
AS InvocationScript
INTO #InvocationScripts
FROM #Objects
ORDER BY ObjectName

DECLARE InvocationScripts CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT InvocationScript
FROM #InvocationScripts
DECLARE @InvocationScript nvarchar(4000)
OPEN InvocationScripts
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InvocationScripts
INTO @InvocationScript
IF @@FETCH_STATUS = -1 BREAK
SET @InvocationScript = 'RAISERROR (' +
QUOTENAME('Testing: ' + @InvocationScript, '''')
+ ', 0, 1) WITH NOWAIT
GO
SET FMTONLY ON
GO
' + @InvocationScript + '
GO
SET FMTONLY OFF
GO'
PRINT @InvocationScript
END
CLOSE InvocationScripts
DEALLOCATE InvocationScripts

DROP TABLE #Objects, #InvocationScripts
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]