Solved

Running a cross database update during a stored procedure.

Posted on 2014-09-06
4
97 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 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