Fix issue with certain SQL queries

[markgould]

Use CustomSqlQuery directly when processing data sources, this fixes issues when a command might be like "EXEC Procedure @param1 @Param2" that was not supported before. Validation would fail on such commands and the query would return null.

[k0st1x]

by default, the converter should not pass non select statements (even "select into"), all queries should be read-only. i suggest you keep a new code in your fork. or add a new command line argument to disable custom query validation and update the DataSetToSqlDataSourceConverter.CreateCustomQuery() method so it will skip the validation and just return a new CustomSqlQuery.

[markgould]

DataSetToSqlDataSourceConverter is in the DevExpress libraries and not in this project, so I could not modify that code.

It is read-only, but it's calling a stored procedure in an EXEC manner. I would be happy to change it to add a command line argument though something like /ssrs:SkipCustomQueryValidation=true

Here's an example of the dataset:

    <DataSet Name="AccountListingMonthYear">
      <Query>
        <DataSourceName>VIT</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@MonthFrom">
            <Value>=Parameters!MonthFrom.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@MonthTo">
            <Value>=Parameters!MonthTo.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@YearFrom">
            <Value>=Parameters!YearFrom.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@YearTo">
            <Value>=Parameters!YearTo.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>EXEC Reports.AccountListingMonthYear @MonthFrom, @MonthTo, @YearFrom, @YearTo</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="DealerId">
          <DataField>DealerId</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Name">
          <DataField>Name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="TaxAccount">
          <DataField>TaxAccount</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="PNumber">
          <DataField>PNumber</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Address1">
          <DataField>Address1</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Address2">
          <DataField>Address2</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="City">
          <DataField>City</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="State">
          <DataField>State</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="PostalCode">
          <DataField>PostalCode</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Year">
          <DataField>Year</DataField>
          <rd:TypeName>System.Int16</rd:TypeName>
        </Field>
        <Field Name="Month">
          <DataField>Month</DataField>
          <rd:TypeName>System.Byte</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>

[k0st1x]

DataSetToSqlDataSourceConverter is in the DevExpress libraries and not in this project, so I could not modify that code.

thanks, you are correct. it better to update code only in the converver codebase. so your updated code is right just add some additional logic, like:

if(someArgument.IgnoreQueryValidation && commandTypeEnum == CommandType.Text) {
 query = new CustomSqlQuery(state.DataSetName, command.CommandText.Trim());
} else {
  query = DataSetToSqlDataSourceConverter.CreateSqlQuery(state.DataSetName, command);
}
if(query != null) {
  state.DataSource.Queries.Add(query);
}

[markgould]

You can now use a parameter to ignore the validation "/ssrs:IgnoreQueryValidation=true"

I had to make one method not static so I could access the property off the interface.