Friday, December 28, 2012 8:06 PM
I have stored procedure that SSDT project which would join 2 table one local to project and another from reference project and based on where clause value of column is set. Normally I would write query as such.
update dd set currentdate = 1 from [ReferencedDB]..DimDate dd join [LocalDB]..DimDate dwsd on dd.BusinessDate = dwsd.BusinessDate
the project itself doesn't have DimDate table as an object because it dynamically is created and could be different depending on runtime configuration. So added to project when redeployed it could create deployment warning for loss of data. I understand that and would expect it to create a warning for referencing an object in stored proc which model isn't aware of and we do this in many places. However in this instead of warning it create error sql 71561 for not being able to resolved [LocalDB]..DimDate.
I have found a work found a work around by formatting update statement differently which is less readable but project build successfully.
update [ReferencedDB]..DimDate set currentdate = 1 from [LocalDB]..DimDate dwsd where [ReferencedDB]..DimDate.BusinessDate = dwsd.BusinessDate
Essentially I have eliminated the need for join in from clause by moving one of the table to update portion. As I understand they should be both compile down to same actions and although I could avoid the situation this time it wouldn't have been the case if I need to join a third table. So I was wondering if I am missing something or is there bug in syntax checker or anyway I can suppress or resolve this error without changing the format sql script