[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Running a cross database update during a stored procedure.

Posted on 2014-09-06
4
Medium Priority
?
106 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

591 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