Error sql 71561 appear depending on how update statement is writtent


  • 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

    Friday, December 28, 2012 8:06 PM