SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtering by date crashes report\query


Filtering by date crashes report\query

Author
Message
Shabbaranks
Shabbaranks
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 169
Hi,
I have the following sql query

SELECT DISTINCT 
scheme.ABC.users_text_field, scheme.efg.PAL_efg, scheme.efg.PAL_oqty, scheme.efg.PAL_product, scheme.efg.PAL_date,
CASE WHEN scheme.efg.PAL_product = '000' THEN 'Test 1' WHEN scheme.efg.PAL_product = '999' THEN 'Test 2' WHEN scheme.efg.PAL_product = '888' THEN 'Test 3' WHEN scheme.efg.PAL_product
= '111' THEN 'Test 4' WHEN scheme.efg.PAL_product = '222' THEN 'Test 5' WHEN scheme.efg.PAL_product = '333' THEN 'Test 6' END AS ProductName, scheme.efg.PAL_porder,
scheme.efg.PAL_bin, scheme.xyz.users_text01, scheme.xyz.users_text02, scheme.xyz.users_text03, scheme.xyz.users_text04, scheme.xyz.users_text05, scheme.efg.PAL_insp_name,
scheme.LMN.alpha
FROM scheme.efg INNER JOIN
scheme.ABC ON scheme.efg.PAL_efg = scheme.ABC.users_text_field INNER JOIN
scheme.xyz ON scheme.ABC.lot_number = scheme.xyz.lot_number INNER JOIN
scheme.LMN ON scheme.efg.PAL_porder = scheme.LMN.order_no
WHERE (scheme.efg.PAL_product LIKE 'R00%')

Which runs fine, if I try and filter on the date column as below
WHERE        (scheme.efg.PAL_product LIKE 'R00%') AND (scheme.efg.PAL_date = CONVERT(DATETIME, '2019-04-03 00:00:00', 102))

It errors with a time out. If I leave it without the date filter and try to filter within SQL Server Report Builder it also times out.

Any ideas how to get around this?
Thanks

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)SSC Guru (434K reputation)

Group: General Forum Members
Points: 434972 Visits: 44160
Shabbaranks - Thursday, March 7, 2019 4:57 AM
Hi,
I have the following sql query

SELECT DISTINCT 
scheme.ABC.users_text_field, scheme.efg.PAL_efg, scheme.efg.PAL_oqty, scheme.efg.PAL_product, scheme.efg.PAL_date,
CASE WHEN scheme.efg.PAL_product = '000' THEN 'Test 1' WHEN scheme.efg.PAL_product = '999' THEN 'Test 2' WHEN scheme.efg.PAL_product = '888' THEN 'Test 3' WHEN scheme.efg.PAL_product
= '111' THEN 'Test 4' WHEN scheme.efg.PAL_product = '222' THEN 'Test 5' WHEN scheme.efg.PAL_product = '333' THEN 'Test 6' END AS ProductName, scheme.efg.PAL_porder,
scheme.efg.PAL_bin, scheme.xyz.users_text01, scheme.xyz.users_text02, scheme.xyz.users_text03, scheme.xyz.users_text04, scheme.xyz.users_text05, scheme.efg.PAL_insp_name,
scheme.LMN.alpha
FROM scheme.efg INNER JOIN
scheme.ABC ON scheme.efg.PAL_efg = scheme.ABC.users_text_field INNER JOIN
scheme.xyz ON scheme.ABC.lot_number = scheme.xyz.lot_number INNER JOIN
scheme.LMN ON scheme.efg.PAL_porder = scheme.LMN.order_no
WHERE (scheme.efg.PAL_product LIKE 'R00%')

Which runs fine, if I try and filter on the date column as below
WHERE        (scheme.efg.PAL_product LIKE 'R00%') AND (scheme.efg.PAL_date = CONVERT(DATETIME, '2019-04-03 00:00:00', 102))

It errors with a time out. If I leave it without the date filter and try to filter within SQL Server Report Builder it also times out.

Any ideas how to get around this?
Thanks

Saying "it errors out" doesn't tell us a thing. What is the full and complete error message that you receive?


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Shabbaranks
Shabbaranks
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 169
Sorry the query was timing out - I have managed to get it working within SSMS by adding

Declare @theDateDATETIME

Set @TheDate ='2019-03-04'


and then using this in my select statement

WHERE (scheme.efg.PAL_product LIKE 'R00%') AND scheme.pallet.PAL_date= @theDate

But I was wondering how I would call this in my Report builder dataset as a Parameter instead of setting a specific date as per the query?
Thanks

Sue_H
Sue_H
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87317 Visits: 17501
Shabbaranks - Thursday, March 7, 2019 7:44 AM
Sorry the query was timing out - I have managed to get it working within SSMS by adding

Declare @theDateDATETIME

Set @TheDate ='2019-03-04'


and then using this in my select statement

WHERE (scheme.efg.PAL_product LIKE 'R00%') AND scheme.pallet.PAL_date= @theDate

But I was wondering how I would call this in my Report builder dataset as a Parameter instead of setting a specific date as per the query?
Thanks


Create a report parameter for PAL_date. There is a tutorial for report parameters - make note of step 3 in the tutorial:
Tutorial: Add a Parameter to Your Report (Report Builder)

Sue



Shabbaranks
Shabbaranks
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 169
Thanks Sue but I may be confusing my original question. I was struggling with my original question (which I was developing in SSMS and saving it as a view to link with SSRS). Although I was explicitly setting the scheme.pallet.PAL_date in SSMS I want to be able to use a parameter so the date can be queried (in SSRS). So creating a parameter isn't a problem but I think Im correct in saying you cant use the declare, set and then referencing the declared as @theDate for example given SSRS creates a parameter based on the @xxx if you see what I mean?
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95781 Visits: 10301
Would have to see the query plan to be sure, but it seems like SQL may be doing an implicit conversion on the date column, which would cause big performance issues.

The safest way to code it with a literal would be this:
(scheme.efg.PAL_date = '20190403')
assuming that PAL_date is any type of date column: smalldatetime, date, datetime, etc.. Then SQL will convert the literal to match the table column and never vice versa.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Sue_H
Sue_H
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87317 Visits: 17501
Shabbaranks - Thursday, March 7, 2019 9:14 AM
Thanks Sue but I may be confusing my original question. I was struggling with my original question (which I was developing in SSMS and saving it as a view to link with SSRS). Although I was explicitly setting the scheme.pallet.PAL_date in SSMS I want to be able to use a parameter so the date can be queried (in SSRS). So creating a parameter isn't a problem but I think Im correct in saying you cant use the declare, set and then referencing the declared as @theDate for example given SSRS creates a parameter based on the @xxx if you see what I mean?

If you want it so the date can be queried in SSRS I would guess you mean that the user can enter a date in the report?
If that's what you are trying to do then yes you create a parameter for the report. You don't declare it like you do in SSMS. In the query for the dataset, you create the parameter there without using declare. When the user runs the report they enter a date and the date is passed to the query as a report parameter.

Sue



Shabbaranks
Shabbaranks
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 169
I think the reason for the loading loop and SQL Report Builder crashing when I run this report is something to do with the date selection format I have set up or it needs changing. Within SMSS the datatype of the scheme.pallet.PAL_date is datetime, I have formatted the text box on the report as Date DD/MM/YY (and set the parameter as date selection) but it doesn't seem to make any difference. As said the query works fine in SSMS but just not as a report in SSRS?
sgmunson
sgmunson
SSC Guru
SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)SSC Guru (107K reputation)

Group: General Forum Members
Points: 107975 Visits: 7500
This would be a really good reason to create a stored procedure, which can be easily paremeterized, and from within SSRS.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Shabbaranks
Shabbaranks
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 169
sgmunson - Wednesday, March 13, 2019 10:05 AM
This would be a really good reason to create a stored procedure, which can be easily paremeterized, and from within SSRS.


Hi - wouldn't you say the view is doing the same as the stored procedure? The problem lies somewhere within the report builder as I can run the report in no time within SSMS. I can save it as a stored procedure and then add that as a dataset and see if that makes a difference?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search