Opening report takes too long

Hi,

I've created reports using CR v10 Professional developer SP2 and all the latest hotfixes. The reports run fine in most environments except on one computer (XP SP2), where opening some of the reports takes several minutes (normally in a blink of the eye). The problem is that the computer is our report server. The reports reside locally on the computer and database connections etc. work fine. There does not seem to be any logic as to which reports work and which don't.

The problem seems to reside in the .rpt file since opening the problem reports using both the developer and a run time engine take evenly long. We've managed to isolalte the problem to the file opening porcess, before setting any parameter values or database connections logging the runtime engine.

The only work around seems to be rebuilding the reports on the computer creating our concerns. Does anyone know the cause or have a fix?

Thanks in advance,
JukkaMay be virus?
Run Anti Virus and see the performance|||you may try with dao connection taht whold slove your problem

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.

Thanks,

Paul

The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.sql

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.

Thanks,

Paul

The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.