Solved

Running a cross database update during a stored procedure.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

930 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

14 Experts available now in Live!

Get 1:1 Help Now