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
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.
RowNumber EventClass TextData ApplicationName NTUserName LoginName CPU Reads Writes Duration ClientProcessID SPID StartTime EndTime BinaryData
76 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:43.693 NULL NULL
77 12 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 Server cardis RFSCORP\cardis 0 4 0 419 4192 66 2008-03-06 12:24:43.693 2008-03-06 12:24:43.693 NULL
78 15 NULL Report Server cardis RFSCORP\cardis 0 234 0 1000000 4192 66 2008-03-06 12:24:43.693 2008-03-06 12:24:44.693 NULL
79 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 71 4192 66 2008-03-06 12:24:44.693 2008-03-06 12:24:44.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
80 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:44.693 NULL NULL
81 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:44.693 NULL NULL
82 12 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 Server cardis RFSCORP\cardis 0 7 0 402 4192 66 2008-03-06 12:24:44.693 2008-03-06 12:24:44.693 NULL
83 15 NULL Report Server cardis RFSCORP\cardis 0 241 0 10000000 4192 66 2008-03-06 12:24:44.693 2008-03-06 12:24:54.693 NULL
84 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 78 4192 66 2008-03-06 12:24:54.693 2008-03-06 12:24:54.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
85 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:54.693 NULL NULL
86 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:54.693 NULL NULL
87 12 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 Server cardis RFSCORP\cardis 0 4 0 387 4192 66 2008-03-06 12:24:54.693 2008-03-06 12:24:54.693 NULL
88 15 NULL Report Server cardis RFSCORP\cardis 0 245 0 1000000 4192 66 2008-03-06 12:24:54.693 2008-03-06 12:24:55.693 NULL
89 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 77 4192 66 2008-03-06 12:24:55.693 2008-03-06 12:24:55.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
90 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:55.693 NULL NULL
91 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:24:55.693 NULL NULL
92 12 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 Server cardis RFSCORP\cardis 0 7 0 464 4192 66 2008-03-06 12:24:55.693 2008-03-06 12:24:55.693 NULL
93 15 NULL Report Server cardis RFSCORP\cardis 0 252 0 10000000 4192 66 2008-03-06 12:24:55.693 2008-03-06 12:25:05.693 NULL
94 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 76 4192 66 2008-03-06 12:25:05.693 2008-03-06 12:25:05.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
95 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:05.693 NULL NULL
96 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:05.693 NULL NULL
97 12 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 Server cardis RFSCORP\cardis 0 4 0 385 4192 66 2008-03-06 12:25:05.693 2008-03-06 12:25:05.693 NULL
98 15 NULL Report Server cardis RFSCORP\cardis 0 256 0 1000000 4192 66 2008-03-06 12:25:05.693 2008-03-06 12:25:06.693 NULL
99 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 78 4192 66 2008-03-06 12:25:06.693 2008-03-06 12:25:06.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
100 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:06.693 NULL NULL
101 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:06.693 NULL NULL
102 12 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 Server cardis RFSCORP\cardis 0 7 0 472 4192 66 2008-03-06 12:25:06.693 2008-03-06 12:25:06.693 NULL
103 15 NULL Report Server cardis RFSCORP\cardis 0 263 0 10000000 4192 66 2008-03-06 12:25:06.693 2008-03-06 12:25:16.693 NULL
104 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 71 4192 66 2008-03-06 12:25:16.693 2008-03-06 12:25:16.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
105 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:16.693 NULL NULL
106 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:16.693 NULL NULL
107 12 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 Server cardis RFSCORP\cardis 0 4 0 328 4192 66 2008-03-06 12:25:16.693 2008-03-06 12:25:16.693 NULL
108 15 NULL Report Server cardis RFSCORP\cardis 0 267 0 1000000 4192 66 2008-03-06 12:25:16.693 2008-03-06 12:25:17.693 NULL
109 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 70 4192 66 2008-03-06 12:25:17.693 2008-03-06 12:25:17.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
110 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:17.693 NULL NULL
111 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:17.693 NULL NULL
112 12 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 Server cardis RFSCORP\cardis 0 7 0 398 4192 66 2008-03-06 12:25:17.693 2008-03-06 12:25:17.693 NULL
113 15 NULL Report Server cardis RFSCORP\cardis 0 274 0 4093000 4192 66 2008-03-06 12:25:17.693 2008-03-06 12:25:21.787 NULL
114 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 94 4192 66 2008-03-06 12:25:21.787 2008-03-06 12:25:21.787 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
115 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:21.787 NULL NULL
116 10 exec GetMyRunningJobs @ComputerName=N'QUAGMIRE',@JobType=1 Report Server cardis RFSCORP\cardis 0 2 0 317 4192 66 2008-03-06 12:25:21.787 2008-03-06 12:25:21.787 0x000000000300000020004700650074004D007900520075006E006E0069006E0067004A006F00620073005000000082001600E7306E0076006100720063006800610072002800380029001A00400043006F006D00700075007400650072004E0061006D0065001000000051005500410047004D004900520045002E00000002001000341073006D0061006C006C0069006E007400100040004A006F006200540079007000650001001400000003000600380469006E00740000000000
117 15 NULL Report Server cardis RFSCORP\cardis 0 276 0 5906000 4192 66 2008-03-06 12:25:21.787 2008-03-06 12:25:27.693 NULL
118 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 79 4192 66 2008-03-06 12:25:27.693 2008-03-06 12:25:27.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
119 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:27.693 NULL NULL
120 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:27.693 NULL NULL
121 12 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 Server cardis RFSCORP\cardis 0 4 0 381 4192 66 2008-03-06 12:25:27.693 2008-03-06 12:25:27.693 NULL
122 15 NULL Report Server cardis RFSCORP\cardis 0 280 0 1000000 4192 66 2008-03-06 12:25:27.693 2008-03-06 12:25:28.693 NULL
123 10 exec sp_reset_connection Report Server cardis RFSCORP\cardis 0 0 0 78 4192 66 2008-03-06 12:25:28.693 2008-03-06 12:25:28.693 0x00000000000000002600730070005F00720065007300650074005F0063006F006E006E0065006300740069006F006E00
124 14 -- 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:28.693 NULL NULL
125 13 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 Server cardis RFSCORP\cardis NULL NULL NULL NULL 4192 66 2008-03-06 12:25:28.693 NULL NULL
126 12 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 Server cardis RFSCORP\cardis 0 7 0 468 4192 66 2008-03-06 12:25:28.693 2008-03-06 12:25:28.693 NULL
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