Solved

Running a cross database update during a stored procedure.

Posted on 2014-09-06
4
95 Views
Last Modified: 2014-10-04
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
Begin
End
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.
0
Comment
Question by:dgloveruk
  • 3
4 Comments
 

Author Comment

by:dgloveruk
ID: 40307488
Further to this I have determined that
update FGDashboard.dbo.KPI SET updated = getdate() where ConsultantID = @Lastvisitor and getdate() between kpistartdate and kpienddate
which would achieve the same thing also doesn't work.. so I am moving towards thinking this is something to do with updating a table that is in another database?
0
 

Accepted Solution

by:
dgloveruk earned 0 total points
ID: 40307525
I have determined it is  permissions issue with accessing another database.... I also see that Execute AS is a sql 2005 command that might have done the job, alas I can't use that in sql 2000.
Short of changing permissions on all users I am not sure what to use so at the moment I will go for updating some data on the user database and then querying it from the other database instead.
Any other ideas appreciated.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40307674
Is cross-database ownership chaining enabled?
0
 

Author Closing Comment

by:dgloveruk
ID: 40360994
My solution was to rewrite the application so it no longer needed to write from one database to the other but could do what it needed via reads instead.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question