November 28, 2016 at 1:38 pm
I have created many packages that use staging tables to implement the incremental loads (adds, changes, deletes) that are detecting change between my SRC and DEST tables. I moved my staging tables into a new DB as was suggested to my by my DBA. So, I went in to change the queries that are in my SQL Task that apply the changes in the staged table to the DEST table, but the package fails now.
UPDATE dest
SET dest.[project_id] = stage.[project_id]
,dest.[cost_center_id] = stage.[cost_center_id]
,dest.[actuals_id] = stage.[actuals_id]
,dest.[quantity] = stage.[quantity]
*****\ lots of other columns \*****
From [ES_RTR].[fact_Actuals_Order] dest
Join [ES_RTR_Staging].[SU_factActualsOrder] stage
ONstage.actuals_id = dest.actuals_id
GO
The error says:
SET dest.[project_id] = stage.[pro..." failed with the following error: "Invalid object name 'ES_RTR.fact_Actuals_Order'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Is it because I am trying to join between two different DBs now? I put the DB name in front of the tables. I don't understand!?!
November 28, 2016 at 3:04 pm
Possibly a dumb question but are you using the database as the schema?
That is, in the UPDATE command it should be:
UPDATE <database>.<schema>.<table>
SET dest.[project_id] = stage.[project_id]
,dest.[cost_center_id] = stage.[cost_center_id]
,dest.[actuals_id] = stage.[actuals_id]
,dest.[quantity] = stage.[quantity]
*****\ lots of other columns \*****
FROM <database>.<schema>.<table/view>
JOIN <database>.<schema>.<table/view>
Looking at your code, my guess is that you are missing the schema. I think your code should be (presuming you are using the dbo schema):
UPDATE dest --This one is OK as you are using the alias
SET dest.[project_id] = stage.[project_id]
,dest.[cost_center_id] = stage.[cost_center_id]
,dest.[actuals_id] = stage.[actuals_id]
,dest.[quantity] = stage.[quantity]
*****\ lots of other columns \*****
From [ES_RTR].[dbo].[fact_Actuals_Order] dest
Join [ES_RTR_Staging].[dbo].[SU_factActualsOrder] stage
ONstage.actuals_id = dest.actuals_id
GO
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 6, 2016 at 11:57 am
Thanks Mr. or Mrs 500. It seems that I had just been staring at the code for too long. I did leave the "[dbo]" (schema) out. Ooof!
Thanks!
December 6, 2016 at 12:08 pm
Not a problem. Everybody has days like that.
Plus it is much nicer to find out you missed something simple than to find out that your query really needs to be rewritten as it doesn't work that way.
But I am glad I could help. Have an excellent day.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply