March 4, 2008 at 1:46 pm
Greetings,
Environment:
SQL Server 2005 SP2 installed over Windows 2003.
IE 7.0
Setup:
Stored procedure with @startdate as datetime and @enddate as datetime parameters
The stored procedure is run by the report
The parameters are nullable, when this occurs, the stored procedure used default values
The parameters in the report are set datetime.
Problem:
When I run the stored procedure into MSSMS with null parameters of set parameters, it runs perfectly.
When I run the report with the value of the parameter NULL, it runs perfectly.
When I setup the parameters value into the report by selecting a date with the calendar add-in OR by typing the dates, the report green circle turns forever.
I tried to setup the report parameter as string. Same problem.
I have that issue with multiple different reports.
The problem is not the stored procedure but the report.
I checked the XML with other similar reports and I did not see anything odd.
Anyone has a solution? Anyone got that issue already? Anyone knows of a Microsoft fix?
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 4, 2008 at 1:56 pm
I forgot to say the report throws that error msg:
Execution 'mx5h5h55xwab3p55el5bbv45' cannot be found (rsExecutionNotFound)
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 5, 2008 at 11:01 am
Greetings,
I figured something out. Maybe someone will have an idea where the issue is:
Running Profiler, I saw that between the time the button to generate the report is pressed and that Reporting Services is calling the stored procedure or sending the SQL Query to SQL Server can be many minutes. As soon as it is sent to SQL Server, the report appears within 3 seconds.
In conclusion, it looks like Reporting Services is holding the SQL request to SQL Server. Why?
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 5, 2008 at 11:16 am
Can't help on the Reporting Services thing...
... But that's a cool avatar ...
... and my favorite NRA "saying" is "When they outlaw guns, only outlaws will have them."
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 6:55 am
I have the same problem. I would love to have a solution.
March 6, 2008 at 6:59 am
Greetings,
I noticed a few new things:
- The problem appears like from nowhere. It worked fine then the report began to have issues.
- The CPU hits a steady 25% utilization for sqlserver
- When the report "decides" it will not show up, the cpu hits 100%
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 6, 2008 at 10:38 am
Greetings,
I can't believe there is not much people experiencing that issue.
Sure no one hit that snag before?
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 6, 2008 at 10:45 am
Greetings,
ok, still hoping someone may have a clue. Is it normal to have MANY reset connection? It is where I see most of the time is lost
Here is a cut and paste of part of the trace. I know it is hard to read,but maybe someone will have a clue.
RowNumberEventClassTextDataApplicationNameNTUserNameLoginNameCPUReadsWritesDurationClientProcessIDSPIDStartTimeEndTimeBinaryData
7613 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:43.693NULLNULL
7712 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardis0404194192662008-03-06 12:24:43.6932008-03-06 12:24:43.693NULL
7815NULLReport ServercardisRFSCORP\cardis0234010000004192662008-03-06 12:24:43.6932008-03-06 12:24:44.693NULL
7910exec sp_reset_connection Report ServercardisRFSCORP\cardis000714192662008-03-06 12:24:44.6932008-03-06 12:24:44.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
8014-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:44.693NULLNULL
8113 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:44.693NULLNULL
8212 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardis0704024192662008-03-06 12:24:44.6932008-03-06 12:24:44.693NULL
8315NULLReport ServercardisRFSCORP\cardis02410100000004192662008-03-06 12:24:44.6932008-03-06 12:24:54.693NULL
8410exec sp_reset_connection Report ServercardisRFSCORP\cardis000784192662008-03-06 12:24:54.6932008-03-06 12:24:54.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
8514-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:54.693NULLNULL
8613 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:54.693NULLNULL
8712 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardis0403874192662008-03-06 12:24:54.6932008-03-06 12:24:54.693NULL
8815NULLReport ServercardisRFSCORP\cardis0245010000004192662008-03-06 12:24:54.6932008-03-06 12:24:55.693NULL
8910exec sp_reset_connection Report ServercardisRFSCORP\cardis000774192662008-03-06 12:24:55.6932008-03-06 12:24:55.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
9014-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:55.693NULLNULL
9113 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:24:55.693NULLNULL
9212 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardis0704644192662008-03-06 12:24:55.6932008-03-06 12:24:55.693NULL
9315NULLReport ServercardisRFSCORP\cardis02520100000004192662008-03-06 12:24:55.6932008-03-06 12:25:05.693NULL
9410exec sp_reset_connection Report ServercardisRFSCORP\cardis000764192662008-03-06 12:25:05.6932008-03-06 12:25:05.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
9514-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:05.693NULLNULL
9613 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:05.693NULLNULL
9712 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardis0403854192662008-03-06 12:25:05.6932008-03-06 12:25:05.693NULL
9815NULLReport ServercardisRFSCORP\cardis0256010000004192662008-03-06 12:25:05.6932008-03-06 12:25:06.693NULL
9910exec sp_reset_connection Report ServercardisRFSCORP\cardis000784192662008-03-06 12:25:06.6932008-03-06 12:25:06.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
10014-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:06.693NULLNULL
10113 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:06.693NULLNULL
10212 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardis0704724192662008-03-06 12:25:06.6932008-03-06 12:25:06.693NULL
10315NULLReport ServercardisRFSCORP\cardis02630100000004192662008-03-06 12:25:06.6932008-03-06 12:25:16.693NULL
10410exec sp_reset_connection Report ServercardisRFSCORP\cardis000714192662008-03-06 12:25:16.6932008-03-06 12:25:16.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
10514-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:16.693NULLNULL
10613 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:16.693NULLNULL
10712 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardis0403284192662008-03-06 12:25:16.6932008-03-06 12:25:16.693NULL
10815NULLReport ServercardisRFSCORP\cardis0267010000004192662008-03-06 12:25:16.6932008-03-06 12:25:17.693NULL
10910exec sp_reset_connection Report ServercardisRFSCORP\cardis000704192662008-03-06 12:25:17.6932008-03-06 12:25:17.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
11014-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:17.693NULLNULL
11113 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:17.693NULLNULL
11212 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardis0703984192662008-03-06 12:25:17.6932008-03-06 12:25:17.693NULL
11315NULLReport ServercardisRFSCORP\cardis0274040930004192662008-03-06 12:25:17.6932008-03-06 12:25:21.787NULL
11410exec sp_reset_connection Report ServercardisRFSCORP\cardis000944192662008-03-06 12:25:21.7872008-03-06 12:25:21.7870x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
11514-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:21.787NULLNULL
11610exec GetMyRunningJobs @ComputerName=N'QUAGMIRE',@JobType=1Report ServercardisRFSCORP\cardis0203174192662008-03-06 12:25:21.7872008-03-06 12:25:21.7870x000000000300000020004700650074004D007900520075006E006E0069006E0067004A006F00620073005000000082001600E7306E0076006100720063006800610072002800380029001A00400043006F006D00700075007400650072004E0061006D0065001000000051005500410047004D004900520045002E00000002001000341073006D0061006C006C0069006E007400100040004A006F006200540079007000650001001400000003000600380469006E00740000000000
11715NULLReport ServercardisRFSCORP\cardis0276059060004192662008-03-06 12:25:21.7872008-03-06 12:25:27.693NULL
11810exec sp_reset_connection Report ServercardisRFSCORP\cardis000794192662008-03-06 12:25:27.6932008-03-06 12:25:27.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
11914-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:27.693NULLNULL
12013 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:27.693NULLNULL
12112 declare @BatchID uniqueidentifier set @BatchID = NEWID() UPDATE [Event] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [EventID] FROM [Event] WITH (TABLOCKX) WHERE [ProcessStart] is NULL ORDER BY [TimeEntered] ) AS t1 WHERE [Event].[EventID] = t1.[EventID] select top 4 E.[EventID], E.[EventType], E.[EventData] from [Event] E WITH (TABLOCKX) where [BatchID] = @BatchID ORDER BY [TimeEntered] Report ServercardisRFSCORP\cardis0403814192662008-03-06 12:25:27.6932008-03-06 12:25:27.693NULL
12215NULLReport ServercardisRFSCORP\cardis0280010000004192662008-03-06 12:25:27.6932008-03-06 12:25:28.693NULL
12310exec sp_reset_connection Report ServercardisRFSCORP\cardis000784192662008-03-06 12:25:28.6932008-03-06 12:25:28.6930x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
12414-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:28.693NULLNULL
12513 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardisNULLNULLNULLNULL4192662008-03-06 12:25:28.693NULLNULL
12612 declare @BatchID uniqueidentifier set @BatchID = newid() UPDATE [Notifications] WITH (TABLOCKX) SET [BatchID] = @BatchID, [ProcessStart] = GETUTCDATE(), [ProcessHeartbeat] = GETUTCDATE() FROM ( SELECT TOP 4 [NotificationID] FROM [Notifications] WITH (TABLOCKX) WHERE ProcessStart is NULL and (ProcessAfter is NULL or ProcessAfter < GETUTCDATE()) ORDER BY [NotificationEntered] ) AS t1 WHERE [Notifications].[NotificationID] = t1.[NotificationID] select top 4 -- Notification data N.[NotificationID], N.[SubscriptionID], N.[ActivationID], N.[ReportID], N.[SnapShotDate], N.[DeliveryExtension], N.[ExtensionSettings], N.[Locale], N.[Parameters], N.[SubscriptionLastRunTime], N.[ProcessStart], N.[NotificationEntered], N.[Attempt], N.[IsDataDriven], SUSER_SNAME(Owner.[Sid]), Owner.[UserName], -- Report Data O.[Path], O.[Type], SD.NtSecDescPrimary, N.[Version], Owner.[AuthType] from [Notifications] N with (TABLOCKX) inner join [Catalog] O on O.[ItemID] = N.[ReportID] inner join [Users] Owner on N.SubscriptionOwnerID = Owner.UserID left outer join [SecData] SD on O.[PolicyID] = SD.[PolicyID] AND SD.AuthType = Owner.AuthType where N.[BatchID] = @BatchID ORDER BY [NotificationEntered] Report ServercardisRFSCORP\cardis0704684192662008-03-06 12:25:28.6932008-03-06 12:25:28.693NULL
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 6, 2008 at 6:19 pm
I am not sure is it work for you.
Try this.
Create a temp. table and store the returned record set. The record set has the @date parameters for the reports. For example
Insert into #tmpTable
select * from testtable where date = @date
select * from #tmpTable
March 6, 2008 at 6:23 pm
Greetings,
It will not work because it takes forever for the reporting services after I pressed the button view report to make the call to SQL Server and execute the underlying report stored procedure.
In theory, the reporting services should not spend soo much time between the time I press the button and the time it calls SQL Server. this is a simple one line with 5 columns report. There is no calculations, no graphics, nothing else!
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 6, 2008 at 7:07 pm
I had the same expereince before.
I have checked the parameters with the SQL Trace function and it seems the reporting services successful pass the @date value to the SQLServer yet it cant return the record set.
My fixed for this is using temporary table for storing the record set and it works fine in my case. I am not sure is it work for u as well. Hope it works.
Mike
March 11, 2008 at 4:23 pm
Just for fun, throw a "WITH RECOMPILE" into your stored procedure.
I've had the same problem with a proc working fine within SSMS but when run in Reporting Services the proc would run for 10 minutes!
I used "WITH RECOMPILE" and my the report now runs in just a few seconds.
March 11, 2008 at 5:04 pm
Greetings,
The WITH RECOMPILE solved my problem.
Now the question is why Reporting services delays the call to the underlying stored procedure in the report when there is an already existing explain plan and it reuses it?
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
March 11, 2008 at 5:13 pm
The problem I encountered wasn't that Reporting Services was delaying sending the proc to SQL Server. I also ran a profile and ran an sp_who2 which showed the proc was executing. I looked in the sys.dm_os_waiting_tasks table and the spid was just trying to grab tons of data all the time.
When the report finally finished the amount of CPU and Reads were ridiculous.
My theory was that SQL Server was choosing the absolutely worst query plan ever imagined (for reasons unknown) when the procedure was run from Reporting Services. Using the WITH RECOMPILE stopped it from using that plan....but that's just my theory. 😀
March 11, 2008 at 5:17 pm
Greetings,
I ran a trace and, for example, a 10 minutes execution time, the stored procedure behind the report was executed at 9 minutes 54 seconds, with a 4 seconds execution and 2 seconds to display the data... It is what puzzled me a lot.
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply