June 4, 2013 at 4:03 am
Dear All
I am using sql 2008 . While compiling the procedures it does not give error even if table not present.
I need to trap this error. How to achieve it?
Regards
June 4, 2013 at 6:17 am
Krishna1 (6/4/2013)
Dear AllI am using sql 2008 . While compiling the procedures it does not give error even if table not present.
I need to trap this error. How to achieve it?
Regards
but...that's not an error, it's deferred name resolution.
Now if you want to detect which items are not fully resolved, you can peek at in sys.sql_expression_dependencies after the proc(s) are created
)
SELECT
referenced_entity_name,
*
FROM sys.sql_expression_dependencies
WHERE referenced_id IS NULL --null, because it's either in another databas,e or doesn't exist in this one
Lowell
June 4, 2013 at 10:19 pm
Thanks it worked for the missing tables.
But when i ran it showed me the procedures which are depedent of other procedures. First shot i thought the "called" procedure are missing and hence executed again. After taht rna the script given by you. But it still shows these procedures.
i have added the code below whcih shows the the procedure "a" in the query send by you.
drop procedure a
go
create procedure a as
begin
print 'a'
execute b
execute c
end
go
drop procedure b
go
create procedure b as
print 'b'
go
June 9, 2013 at 10:07 pm
can sombody help
June 10, 2013 at 12:06 am
Not sure how we can help. Deferred name resolution allows you to create an object, like a stored procedure, before other objects used in the procedure are actually created.
If you want to be sure that the objects needed by the procedure exist before you build the procedure you will need to test for their existence before creating the procedure and handle it within the script. Look up EXISTS and NOT EXISTS as well as OBJECT_ID().
June 10, 2013 at 7:46 am
Krishna1 (6/4/2013)
Thanks it worked for the missing tables.But when i ran it showed me the procedures which are depedent of other procedures. First shot i thought the "called" procedure are missing and hence executed again. After taht rna the script given by you. But it still shows these procedures.
sys.sql_expression_dependencies does not get updated when you create the previously missing objects. If it had to do that it would take forever to build any stored procs because the engine would have to search each and every proc to see if there were any missing dependencies every time you create one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 9:41 am
Sean Lange (6/10/2013)
Krishna1 (6/4/2013)
Thanks it worked for the missing tables.But when i ran it showed me the procedures which are depedent of other procedures. First shot i thought the "called" procedure are missing and hence executed again. After taht rna the script given by you. But it still shows these procedures.
sys.sql_expression_dependencies does not get updated when you create the previously missing objects. If it had to do that it would take forever to build any stored procs because the engine would have to search each and every proc to see if there were any missing dependencies every time you create one.
Actually, you should look at the following (I'd do it in an empty database):
--select object_name(object_id), object_name(referenced_major_id), * from sys.sql_dependencies;
select object_name(referencing_id), * from sys.sql_expression_dependencies;
go
create procedure dbo.testproc1 (@Parm1 int)
as
exec testproc2
go
select object_name(referencing_id), * from sys.sql_expression_dependencies;
go
create procedure dbo.testproc2
as
select * from dbo.Customer
go
select object_name(referencing_id), * from sys.sql_expression_dependencies;
go
drop procedure dbo.testproc2;
drop procedure dbo.testproc1;
go
SQL Server 2008 R2 still shows the reference from the outer procedure to the inner procedure even though it is created after the outer procedure. You get an informational message saying that the procedure is created, you just can't execute it until the dependencies are taken care of.
June 10, 2013 at 9:50 am
So I was correct in my verbiage but my understanding of what was happening was incomplete for sure. I didn't realize that it creates the missing dependencies when you create the procedure. Thanks for clarification and the samples Lynn.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 9:53 am
These are the improvements that have been added to SQL Server over the last few releases. IIRC, this was not the behavior in SQL Server versions from 2000 and back. They still had deferred name resolution but the dependencies weren't captured making the system table less usable.
June 10, 2013 at 10:01 am
Lynn Pettis (6/10/2013)
These are the improvements that have been added to SQL Server over the last few releases. IIRC, this was not the behavior in SQL Server versions from 2000 and back. They still had deferred name resolution but the dependencies weren't captured making the system table less usable.
That is how I remember it and somehow missed the changes.
Must be that my memory needs an upgrade from 2000. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply