Dataset error when deployed to Report Manager

Hi
I've got a custom assembly which performs validation on parameters
prior to submitting the query to the database. My dataset which
retrieves the report data is in Text format and calls an appropriate
validation method on free-text parameters, such as dates and numbers.
When validation passes, the code returns a string formatted for use in
the query, and when it fails it returns a string value that the stored
procedure recognises as a dummy value and returns without SELECTing any
data. My custom assembly sets a user-friendly error message as a
property, which is displayed on the report in place of any data.
I've designed a number of reports using this method and it works well
in the Designer environment. However, I've deployed a few of them to
Report Manager and am getting the following error when pressing "View
Report":
An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command text for data set 'Report'.
(rsErrorSettingCommandText)
Error during processing of the CommandText expression of dataset
'Report'. (rsQueryCommandTextProcessingError)
An example of one of the simple Datasets is:
="EXEC spAgentSettlementsDetailedRpt " &
Code.Validator.ResetValidator &
"@.p_StartDate_IN = '" +
Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
" &
"@.p_EndDate_IN= '" +
Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
Parameters!p_EndDate_IN.Value) + "', " &
"@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
&
"@.p_WS_Code_IN = " +
Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
Parameters!p_AgentID_IN.Value, false)
I'm happy to provide the code within the assembly if it would be of any
help in debugging this problem.
In deploying to the server, I have copied the assembly (dll) to the
\bin directory on the server and uploaded the rdl file as per normal.
Any help would be greatly appreciated. Thank you.
AshleyI've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
Ashley.Manos@.gmail.com wrote:
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley|||I've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley

DataBase: Timeout Expired

Hi,

When I am trying to query a table which has got lakhs of records,

it said, Error:Timeout Expired . Timeout Expired prior to completion of operation or server is not responding.

Can anybody help me out

You can change/set the timeout property in the connection string. A more important question is, do you really need to show that many hundreds of thousands of records in the application? There are ways like paging that you can only bring in few hundred records at a time.|||

I dont want to display all these results nut, i need to query these many records to display some 100 records.

I have used Timeout in Connection String also.

Connection is fine.

But Query Execution is taking that much time.

Something similar to http://techielion.blogspot.com

Can you please explain that to me?

|||

If you only need 100 records you could do a SELECT TOP 100 ....

If its still taking too long, check your query. Do you have proper indexes on the columns that are in the WHERE condition? Are the indexes up to date? Is there too much fragmentation?

|||

Hi,

You also need to check the ConnectionTimeout and CommandTimeout property to see if it is too small to return all the results.