Running a cross database update during a stored procedure.
Posted on 2014-09-06
Hi there, I have an sql server 2000 stored procedure which syntax checks just fine. However when a certain update command is present the stored procedure won't run from a trigger which calls it. The strange thing is, the update isn't even reached in the code during testing and if I allow it in but make it impossible to call by entering :
if 1 = 2
around the update command... the stored procedure still fails to run that is until I comment out the update command.
The update command references another database and joins across databases and perhaps this is where my knowledge of certain issues that can arise is weak.
The command is :
update FGDashboard.dbo.KPI SET updated = getdate() FROM (SELECT FGDashboard.dbo.KPI.KPIID FROM dbo.TaskDate INNER JOIN dbo.TaskUsers ON dbo.TaskDate.TaskID = dbo.TaskUsers.TaskId INNER JOIN FGDashboard.dbo.KPI ON dbo.TaskUsers.UserId = FGDashboard.dbo.KPI.ConsultantID WHERE (dbo.TaskDate.CreatedDate BETWEEN FGDashboard.dbo.KPI.KPIStartDate AND FGDashboard.dbo.KPI.KPIEndDate) AND (dbo.TaskDate.TaskID = @CurrentTaskID) ) x INNER JOIN FGDashboard.dbo.kpi ON x.KPIID = FGDashboard.dbo.kpi.KPIID
I have tried swapping the paramater for a fixed value , testing this update command separately and calling the stored procedure from query analyser with the same paramaters and have no problems. It is only when it is called from a trigger there seems to be an issue. My suspicion would be that one of the tables that the update command is reading from is locked at the time of execution except that the stored procedure fails even when the update is never reached so it doesn't make sense to me why this update line could cause any issue at all if its mere presence is enough to cause an error?
Am I missing something? Can the presence of a line in TSQL have an impact even if by process it isn't reached?
Thanks in advance.