Our assignment is to create same replica of existing database to new SQL instance. We were also pretty much sure because all our Stored Procedures are also created on the new instance. As soon as we try to execute the stored procedure, we are getting, very simple error like:
Invalid object name 'XXXXXX'.
When we debug that, we come to know that TABLES are not created on the new SQL instance because of one error. Our main question here is: How come SQL SERVER allows to create Stored procedures, if table doesn’t exists?
After review it, we come to know that SSMS 2012 has an option, that we can configure this restriction to allow or not to allow, Check for object existence
By default, "Check for object existence” is configures as FALSE, so SQL Server allows user to create stored procedure without checking dependent object is exists or not. Lets see one simple demo:
--Checking, if table exists or not. If it is exists, delete the table
if object_id('SQLYoga') IS NOT NULL
DROP TABLE SQLYoga
--Creating a Stored procdedure which using the object "SQLYoga" which we have just deleted
CREATE PROC StoredProcedureWithoutObject
As
BEGIN
SELECT * FROM SQLYoga
END
This is very simple example that first we have checked that, if table called “SQLYoga” exists in the database then drop that table and create a stored procedure which uses this table. As Check for object existence configuration is setup as “to allow”, we are able to create the Stored procedure though table is not exists on the table. Stored procedure won’t be executed and will give an error at the time of execution, as table doesn’t exists. To enable this configuration, go to SSMS->Tools->Options->SQL Server Object Explorer and setup “Check for object existence” to TRUE.
Please let me know your comments
Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on June 26, 2015
Posted by Tejas Shah on April 20, 2015