April 5, 2018 at 1:14 am
I have a procedure inside a schema 'ABC'. inside this procedure in an if else block using left outer join am getting certain column values to variables. Later am doing some DML operations. In the left outer join , the second table i didnt mention the schema name (table is not related with default schema. Default schema is dbo) in both if and else block. The procedure is able to create and modify finely without errors and not at all showing any errors.
During runtime also its working fine. But the same piece of code is not able to run separately when am executing it outside from SP.
So, what is the intended behaviour of this ? It will cause any deadlock or any other issue?
I am using Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)
Please Help me.... Thanks in advance
Thanks
April 5, 2018 at 1:34 am
deepzzzz - Thursday, April 5, 2018 1:14 AMI have a procedure inside a schema 'ABC'. inside this procedure in an if else block using left outer join am getting certain column values to variables. Later am doing some DML operations. In the left outer join , the second table i didnt mention the schema name (table is not related with default schema. Default schema is dbo) in both if and else block. The procedure is able to create and modify finely without errors and not at all showing any errors.During runtime also its working fine. But the same piece of code is not able to run separately when am executing it outside from SP.
So, what is the intended behaviour of this ? It will cause any deadlock or any other issue?I am using Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64)
Please Help me.... Thanks in advance
Way too vague for anything other than a stab in the dark. Can you post up the code?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 5, 2018 at 1:40 am
Further on Chris's answer, have you tried to fully schema qualify all object references within the code?
😎
April 5, 2018 at 2:13 am
for example
create or alter abc.user_sp1(id int)
begin
select @var1=col1, @var2=col2
from abc.table1 a join table2 b on a.id=b.id
where condition;
------
Some DML statements
----
end
1) Table2 I didnt give any schema name. I dont have table2 in dbo schema, which is by default
2) create or alter working fine
3) During run time its working.. Not throwing any error.
Certain cases its throwing deadlock issues. Not always.
My question is if this will cause any issue during runtime? If the same piece of code, am running outside SP its throwing error saying "Invalid Object". So why this error is not coming while running in SP or modifying the SP?
Hope its clear... Let me know if you need any further info
Thanks
April 5, 2018 at 2:17 am
Yes, I agree. Qualify all objects with schema names. If that doesn't work, post the code.
My guess is that the schema name you've missed from your code is ABC. Since the procedure is also in ABC, that's where it looks for the object - and finds it. When you execute from outside the procedure, it looks in your default schema - and doesn't find the table.
John
April 5, 2018 at 6:09 am
When you create a stored procedure, it doesn't validate the existence of objects, just syntax. It's when you execute the code that you hit the problems. So yeah, CREATE or ALTER are going to work fine. This is an old article on the concept, but what you're looking at is called deferred name resolution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply