December 17, 2002 at 10:30 am
I have a very simple job that runs a DTS Package. This DTS Package imports data from an Access Database into tables inside one of my SQL databases (i'm running SQL Server 2000). I also have a couple views that are utilized for reporting purposes from these tables that are updated.
My problem is this, recently when I have people (that use crystal reports to connect to the views) looking at their reports at the same time the update job fires I am getting errors due to the following error:
Executed as user: SECSOURCE\admin_. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: The transaction has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 3618 (E22) Error string: The transaction has been terminated. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
I believe the job is failing because it is timing out due to the locks that are on the tables while people are looking at there reports.
How can I stop this from happening? Keep in mind I want these reports to continue to pull realtime.
Thanks in advance.
December 17, 2002 at 3:29 pm
Looking at records shouldn't prevent an update under the default transaction isolation level of read committed. Unless there is a holdlock, or the transaction isolation level has been changed, I don't think that would cause it. Crystal Reports is great about not holding connections as well. It grabs what it needs and releases the connection, storing the resultset in the report so that you don't even need the connection to look at the report. It could still be timing out, though. Why don't you profile the system and recreate the error. That would give you the complete picture.
For just a quick check to verify its not locking, you could add nolock hints to the crystal reports query. If it is in fact locks causing the timeouts, that would eliminate it.
Edited by - scorpion_66 on 12/17/2002 3:33:42 PM
March 9, 2007 at 4:27 am
Scorpion,
you state crystal is great with resources but we are experiencing similar problems on a larger scale - i.e many reports are running simultaneously all of which are locked up by some application transactions and some of which cause locking of areas trying to start transactions - do you have any way of making crystal the lowest possible isolation level?
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply