Solved

Running a cross database update during a stored procedure.

Posted on 2014-09-06
4
91 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now