Error during processing of the CommandText expression of dataset

Question

Hello

I have created a SSRS report that queries MYSQL and uses an expression to query the database.

All works fine in development, but when I deploy to the SSRS server some queries do not work.

Please note on some searchers it works OK on the SSRS server.

The error I get is the following;

• An error has occurred during report processing. (rsProcessingAborted)
  • Cannot set the command text for dataset 'DataSet1'. (rsErrorSettingCommandText)
    • Error during processing of the CommandText expression of dataset ‘DataSet1’. (rsQueryCommandTextProcessingError)
      

The query dataset1 is running is this;

="SELECT D.ref as loc_ref, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',2),'|',-1)) AS cat1,  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',3),'|',-1)) AS cat2,  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',4),'|',-1)) AS cat3,  Stk_products.name AS cat4, Sl_orders.unit_gross_paid as price_paid, Sl_orders.total_gross_paid as TTL,Sl_orders.type_lkp as status,Sales_qty as Sales_qty,Case When Sl_orders.type_lkp = 4 Then 'refund' When Sl_orders.type_lkp = 1 Then 'sold' Else Sl_orders.type_lkp End as CS_Transaction_Type, Case When TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',4),'|',-1)) = 'Ladies' Then 1 When TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',4),'|',-1)) = 'Mens' Then 2 When TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',4),'|',-1)) = 'Childrens' Then 3 When TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',4),'|',-1)) = 'Media' Then 4 When TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',4),'|',-1)) = 'Home' Then 5 Else 6 End as OrderBY FROM sl_transaction t inner join data_location D on D.id = t.location_id inner join sl_order Sl_orders on Sl_orders.transaction_id = t.ID inner join stk_item Stk_item on Sl_orders.stk_item_ID = Stk_item.ID inner join stk_product Stk_products on Stk_products.ID = Stk_item.stk_product_id inner join stk_cat sc on Stk_products.stk_cat_id = sc.id Left join sl_order_note on Sl_orders.id = sl_order_note.sl_order_id WHERE D.id >=1 and  D.id <=99999 AND Sl_orders.line_status = 1 And TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(concat(path_string, '|||'),'|',2),'|',-1)) in ('New Donated','DONATED GOODS') and Date(t.updated_at) between '" & Format(CDate(Parameters!RW_START_DATE.Value), "yyyy/MM/dd") & "' and '"& Format(CDate(Parameters!RW_END_DATE.Value), "yyyy/MM/dd") & "' and D.Ref in ('" + join(Parameters!Store.Value,"','") + "') order by t.updated_at"

What can I try so all searchers work on the SSRS Server?

Regards

SQLSearcher99

Answer 1

what kind of datasource are you using for connecting to MySQL? ODBC?

If yes you should be using ? placeholders for the parameters and map the Parameter1,Parameter2 etc it generates against corresponding report parameters

see

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/binding-parameters-odbc?view=sql-server-2017

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Hi SQLSearcher99

According to your expression for the dataset query, you could check your issue in the following aspects.

1 Check if you could connect to the mysql data source , try to use a simple sql query to check the connection.

2 Try to split your query into pieces and see if each piece is work or not. (for example ,remove the where condition to check if it works)

3 Check the expression for the dataset query is fine. There would be syntax error when you use expression for dataset query. Try to put the query expression into textbox , and then get the query . run the query in mysql to check if there is syntax issue or not.

Hope it can help you.

Best Regards,

Eric Liu

---

Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

Answer 3

Hello

I made a stupid mistake my SSRS date parameters were set to Text and should have been set to Date/Time.

Once I made this change it work with all searchers.

Thank you for your comments.

Regards

SQLSearcher99