Timeout on Refresh Fields in SSRS

This happens when the stored procedure takes more than 30 seconds to run. I’m not sure about all the technical details, but I believe that SSRS is trying to run the stored procedure with NULL parameters to get a response and analyze it’s fields. The easiest way I found to fix this problem is to create a mock stored procedure with the same name that returns a single record with the exact same structure (field names and types) than the original one. Eg

CREATE PROCEDURE MyProcedure (@perf_no INT) AS

SELECT perf_dt = CONVERT(DATETIME, '2013-01-01'), capacity = CONVERT(INT, 1), perc_available = CONVERT(FLOAT, 12.34), amt_sold = CONVERT(MONEY, 1234.56)

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

Hi all,

New problem I've just come across, which I'm still scratching my head over a couple of hours later. I'm working on a new SSRS report, and I've created a Stored Procedure for it. The SP works fine when I'm in SSMS, but in SSRS when I set up the dataset and click Refresh Fields, it spins for 30 seconds or so and then says "Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

When I click the Details button, I also get: "Timeout expired. The timeout period elapsed prior to completeion of the operation or the server is not responding."

Has anybody seen this before and, if so, what did you do about it? Any assistance would be greatly appreciated.

Cheers,

Matt

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This happens when the stored procedure takes more than 30 seconds to run. I’m not sure about all the technical details, but I believe that SSRS is trying to run the stored procedure with NULL parameters to get a response and analyze it’s fields. The easiest way I found to fix this problem is to create a mock stored procedure with the same name that returns a single record with the exact same structure (field names and types) than the original one. Eg

CREATE PROCEDURE MyProcedure (@perf_no INT) AS

SELECT perf_dt = CONVERT(DATETIME, '2013-01-01'), capacity = CONVERT(INT, 1), perc_available = CONVERT(FLOAT, 12.34), amt_sold = CONVERT(MONEY, 1234.56)

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

Hi all,

New problem I've just come across, which I'm still scratching my head over a couple of hours later. I'm working on a new SSRS report, and I've created a Stored Procedure for it. The SP works fine when I'm in SSMS, but in SSRS when I set up the dataset and click Refresh Fields, it spins for 30 seconds or so and then says "Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

When I click the Details button, I also get: "Timeout expired. The timeout period elapsed prior to completeion of the operation or the server is not responding."

Has anybody seen this before and, if so, what did you do about it? Any assistance would be greatly appreciated.

Cheers,

Matt

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!