January 17, 2018 at 4:58 am
Hello All,
I have a simple stored procedure that inserts records into a table.
Create proc Update_Rec_Proc
AS
declare @max_update_date datetime=(select max([MAX_LAST_UPDATE_DATE]) from Target_Table_1)
Begin
---Deleting entire data from target table
TRUNCATE TABLE Target_Table_2
/* Inserting data to the target table */
insert into Target_Table_2
select distinct st1.Column_1, st1.Column_2,
CAST(REPLACE(LEFT(CONVERT(VARCHAR(50),Transaction_Date,120),7),'-','') as int) Transaction_Date_YYYYMM
from
Source_table_1 st1
join Source_Table_2 st2
on st1.Primary_Key_Column = st2.Foreign_Key_Column
join Source_Table_3 st3
on st2.Primary_Key_Column = st3.Foreign_Key_Column
and st3.dw_modified_date>@max_update_date
End
Now the problem occurs when I execute the procedure.
If I give execute procedure via command
exec proc Update_Rec_Proc
I get a set of output. But if I execute the insert statement outside the stored procedure, I am getting different set of output.
declare @max_update_date datetime=(select max([MAX_LAST_UPDATE_DATE]) from Target_Table_1)
insert into Target_Table_2
select distinct st1.Column_1, st1.Column_2,
CAST(REPLACE(LEFT(CONVERT(VARCHAR(50),Transaction_Date,120),7),'-','') as int) Transaction_Date_YYYYMM
from
Source_table_1 st1
join Source_Table_2 st2
on st1.Primary_Key_Column = st2.Foreign_Key_Column
join Source_Table_3 st3
on st2.Primary_Key_Column = st3.Foreign_Key_Column
and st3.dw_modified_date>@max_update_date
I am not sure what is going wrong here. Can anyone please help?
Thank You.
January 17, 2018 at 5:40 am
The stored procedure and the ad hoc statement don't produce any output - they merely insert rows into a table. (Actually, the statement won't event do that - it'll return an error that the variable is undeclared). Please will you show us exactly what you are running in each case and what the difference is in results? Is it possible that MAX_LAST_UPDATE_TIME is changing between executions?
John
January 17, 2018 at 5:57 am
It would almost have to be down to differences in the @max_update_date and how it gets generated inside vs. outside the procedure. Fundamentally, there's nothing all that special about a stored proc versus a batch. Except for parameterization, a query is a query by and large (yes, code reuse, retrieval from cache, etc., etc., for the pedants out there, I'm talking about what data gets returned).
"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
January 17, 2018 at 10:21 am
John Mitchell-245523 - Wednesday, January 17, 2018 5:40 AMThe stored procedure and the ad hoc statement don't produce any output - they merely insert rows into a table. (Actually, the statement won't event do that - it'll return an error that the variable is undeclared). Please will you show us exactly what you are running in each case and what the difference is in results? Is it possible that MAX_LAST_UPDATE_TIME is changing between executions?John
Thanks for your input. Apologies, I forgot to copy the code with the declare statement. I have updated the same in my original post.
Basically the count from the target table (in this instance, Target_Table_2) is mismatching if I execute the procedure manually or schedule the procedure via s SQL Server Agent Job with the ones with the plain Insert statement. (Truncating Target_Table_2 in both instances)
Steps I am trying to do is a basic incremental load:
1. Finding the records from source that got updated after my initial load into the target table (Target_Table_1).
2. Load those records into a secondary table (Target_table_2).
I will try my best to recreate the instance in a local environment as I am not authorized to share live data in forums.
Thanks again and apologies for wasting your time with a silly mistake.. 🙂
January 17, 2018 at 10:27 am
is there a chance that you have row-level security in place? Then the process that is running the automation has different permissions from your login, hence different results? Just trying to figure this out because a query against a table should return the same results unless some other force is in place.
"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
January 17, 2018 at 10:28 am
Grant Fritchey - Wednesday, January 17, 2018 5:57 AMIt would almost have to be down to differences in the @max_update_date and how it gets generated inside vs. outside the procedure. Fundamentally, there's nothing all that special about a stored proc versus a batch. Except for parameterization, a query is a query by and large (yes, code reuse, retrieval from cache, etc., etc., for the pedants out there, I'm talking about what data gets returned).
Hello Grant,
Thanks for your inputs. The max_update_date will not be changed in both the instances as it is derived from a separate Table (Target_Table_1). I am bit new to SQL Server and I am not sure if I am missing any permission issues or DB Settings that can affect the mismatch in counts of the target table (Target_Table_2).
Only difference I feel is, I am running the procedure in a different schema (stage schema procedure to insert into stage schema tables using dbo tables as source) whereas my insert statements are running in dbo (at least I am hoping that is the case). Will that have any impact? I need to test this tomorrow once I reach office. But will be eager to see the opinion from experts here as well.
Thank You.
January 17, 2018 at 10:34 am
Joice - Wednesday, January 17, 2018 10:28 AMHello Grant,
Thanks for your inputs. The max_update_date will not be changed in both the instances as it is derived from a separate Table (Target_Table_1). I am bit new to SQL Server and I am not sure if I am missing any permission issues or DB Settings that can affect the mismatch in counts of the target table (Target_Table_2).Only difference I feel is, I am running the procedure in a different schema (stage schema procedure to insert into stage schema tables using dbo tables as source) whereas my insert statements are running in dbo (at least I am hoping that is the case). Will that have any impact? I need to test this tomorrow once I reach office. But will be eager to see the opinion from experts here as well.
Thank You.
It's possible that the hop across the schema could be causing the issue. I'd look pretty hard at that. Nothing automatic there. Usually if one schema has permissions, then it's all good, but there might be something. Are the queries actually against tables or is that a view? If it's a view, it might be masking security checks.
"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
November 27, 2019 at 2:33 pm
Was a solution ever found for this? I'm experiencing the same issue.
November 27, 2019 at 3:08 pm
They never reported back what the issue was. Usually, it's down to data.
"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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply