Parameter Limitations?

Hi all,
I am calling a SP from RS inwhich there is an optional parameter of string
type. I allow the parameter to be blank and the results returned are
correct. When i try to filter my results by adding a name (ie Tic-Tot's
Daycare) and run the preview again the results are the same as if i left the
parameter blank. When i run the SP in the data view and include the name it
returns the filtered results. When i run the SP in QA i get the filtered
result. My only thought is the odd chars in the string ( single quote and
hyphen) could be messing up the parameter passing. Can some one please
confirm this or give a different suggestion for this issue.
Thanks in advance,
SPOSince the string delimiter in SQL is a single quote, I would suspect that is
your problem... You might try to double up on single quotes in your
parameters ,( which escapes the quote in SQL)... First eliminate the quote
from the parameter and see if it works...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"iamspo" <iamspo@.discussions.microsoft.com> wrote in message
news:5097986A-9711-4802-B839-9BB7A8C22F49@.microsoft.com...
> Hi all,
> I am calling a SP from RS inwhich there is an optional parameter of string
> type. I allow the parameter to be blank and the results returned are
> correct. When i try to filter my results by adding a name (ie Tic-Tot's
> Daycare) and run the preview again the results are the same as if i left
> the
> parameter blank. When i run the SP in the data view and include the name
> it
> returns the filtered results. When i run the SP in QA i get the filtered
> result. My only thought is the odd chars in the string ( single quote and
> hyphen) could be messing up the parameter passing. Can some one please
> confirm this or give a different suggestion for this issue.
> Thanks in advance,
> SPO|||I actually found the error. Since i added the name filter parameter as a
second version to this SP it was not picked up by RS. I went in and added
the parameter (report => report parameters...) but never went back and added
it to the parameter list in the data set. Once i did think the data set knew
which parameter to associate this value to and sent it to the SP. The result
returned were correct, none of the special char's used caused an issue.
Thanks for the help!!!
"iamspo" wrote:
> Hi all,
> I am calling a SP from RS inwhich there is an optional parameter of string
> type. I allow the parameter to be blank and the results returned are
> correct. When i try to filter my results by adding a name (ie Tic-Tot's
> Daycare) and run the preview again the results are the same as if i left the
> parameter blank. When i run the SP in the data view and include the name it
> returns the filtered results. When i run the SP in QA i get the filtered
> result. My only thought is the odd chars in the string ( single quote and
> hyphen) could be messing up the parameter passing. Can some one please
> confirm this or give a different suggestion for this issue.
> Thanks in advance,
> SPO

Paging query bugs out when adding a where clause

I am getting incorrect results from my paging query, where the same results are being returned multiple times.

Here are two queries I have found to bring the same results:

select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 620 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))

select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 640 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))

When I remove the catname_cst where clause it brings back results properly (i.e. records 622-642 and 643-663).

What is wrong with my where clause that is causing identical data to be returned?

I'll try to be a bit more generic with my question.

How would you properly add a where clause in the T-SQL paging query technique I am using?

The template for the paging query I am using shows up in a few tutorials, it's a fairly known technique:

SELECT TOP rows_to_return * FROM table WHERE unique_id NOT IN (SELECT TOP row_to_start_at unique_id FROM table)

Basically it ignores the rows from Row 0 through row_to_start_at and from that starting point it selects rows until rows_to_return is reached.

So how should a where clause (i.e. where catname_cst='mycategoryname') be properly added to this paging query?

|||

Do a quick google search for "custom paging + stored procedure" and you will find some sample code on how to write custom paging which is much more efficient than what you are doing.

|||

You may be suggesting a tutorial (4guysfromrolla.com?) that uses temp tables and variables for current/last records. If you are, is creating a temp table (with potentially 40,000+ rows to be inserted) really more efficient than selecting the top X rows in my technique? I realize that my technique will become progressively slower as the Y (rows to be ignored) value increases, but I don't believe it should ever exceed the time required in creating a temp table that houses every single row.

I will test this out tomorrow and see how it works.

|||

There is no ORDER BY to go with your TOP so you will get just 20 rows in no particular order. You might as well use SET ROWCOUNT 20 which is faster than TOP 20. Also look into using EXISTS instead of IN. IN gets internally converted into OR's and may not result in efficient query plans.

|||

I will use SET ROWCOUNT, but I cannot determine how NOT EXISTS will help with filtering rows. The only result from google on the subject shares my confusion.

I'll test out the temp tables technique, but even if it works perfectly, I'll still want to know why my technique isn't working 100% of the time. Efficiency aside, there just doesn't seem to be anything wrong with the query, maybe it's the data.. the first dozen or so pages will look fine, then suddenly every so often an identical page is found, then they start coming in more frequently, and soon enough you're seeing more identical pages than non. This isn't just something I'm seeing on a site grid, I'm getting the same identical results within SQL Server. I think that based on the response and lack of responses it is not something with the query (as that kind of thing would have been pointed out, considering it should just be some simple logic error)..

The strange part is that if I perform a query such as getting the top 40 and the top 20 rows starting after results 620 and 640 respectively, 1-20 of each query will be the same, and 21-40 of the first query will contain the proper data that should be showing up in the second query..

To sum that up.. because I really want an answer:

Query 1 should get 40 results starting after result 620, so it should return 621-660.

Query 2 should get 20 results starting after result 640, so it should return 641-660.

Query 1 returns results 621-660.

Query 2 returns results 621-640.

|||

Could I get some insight into how I would write a paging query with not exists?

|||

The reason you are not getting the right results, as I mentioned above, is using TOP without ORDER BY. Its like saying "give me the top 20 records that match this criteria". There is every chance the same record may show up in the next set or an expected record may not show up at all. TOP X is incomplete by itself, although syntactically correct.

|||

Thanks, I implemented a version of my technique with Order By as you suggest and it works.

Pagination Issue when Rending to PDF

I am having a pagination issue with a report when I render to PDF. The
report is a patient form that is based on a single record that is returned
from a stored procedure. Most of the records are output fine, but some
records are not paginating properly and leave 3/4 page of blank space. The
field that follows on the next page is short and should definitely fit on
that page. The report renders fine in HTML, but the requirement is a PDF
format.
The body of the report is contained in a table. Each field of the report is
contained in its own row. If the field is blank, the entire row is hidden so
that only populated data is output in the report.
In troubleshooting this, I have removed the logic that hides the rows, but
the pagination is still not correct.
I have also made sure that the data is clean and doesn't have whitespace.
Any suggestions as to how to fix this problem?
TIAOn Dec 18, 8:47 pm, JC <J...@.discussions.microsoft.com> wrote:
> I am having a pagination issue with a report when I render to PDF. The
> report is a patient form that is based on a single record that is returned
> from a stored procedure. Most of the records are output fine, but some
> records are not paginating properly and leave 3/4 page of blank space. The
> field that follows on the next page is short and should definitely fit on
> that page. The report renders fine in HTML, but the requirement is a PDF
> format.
> The body of the report is contained in a table. Each field of the report is
> contained in its own row. If the field is blank, the entire row is hidden so
> that only populated data is output in the report.
> In troubleshooting this, I have removed the logic that hides the rows, but
> the pagination is still not correct.
> I have also made sure that the data is clean and doesn't have whitespace.
> Any suggestions as to how to fix this problem?
> TIA
You will want to check the width of the report. Normally, if it is
wider than 6.5" in design view, it will wrap once exported to PDF.
Also, if you are using a table control, you will want to put it into a
rectangle control, this should shrink the extra space. Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the quick reply. The report is slightly wider than 6.5" in design
mode to incorporate the header (which renders fine). The fields before and
after the blank space are very short (like 1 word and a label).
I did try putting the table in a table control, but that didn't do anything.|||On Dec 18, 9:43 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks for the quick reply. The report is slightly wider than 6.5" in design
> mode to incorporate the header (which renders fine). The fields before and
> after the blank space are very short (like 1 word and a label).
> I did try putting the table in a table control, but that didn't do anything.
You're welcome. Did you mean you put it into a table control or a
rectangle control?
Enrique Martinez
Sr. Software Consultant|||Sorry. I meant that I put the table control inside the rectangle control. I
just removed all code that controls the visibility property for each row, and
the pagination problem went away.
The problem is that the customer only wants rows of the table to appear that
have data. Here's the code on the visiblity property of the tablerow:
=iif(Fields!FirstSignature.Value = string.empty, True, False)
It does work as it is supposed to, but it seems that the space is still
being allocated for the row.
BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
the same results.|||On Dec 18, 11:05 pm, JC <J...@.discussions.microsoft.com> wrote:
> Sorry. I meant that I put the table control inside the rectangle control. I
> just removed all code that controls the visibility property for each row, and
> the pagination problem went away.
> The problem is that the customer only wants rows of the table to appear that
> have data. Here's the code on the visiblity property of the tablerow:
> =iif(Fields!FirstSignature.Value = string.empty, True, False)
> It does work as it is supposed to, but it seems that the space is still
> being allocated for the row.
> BTW, it was initially written in VS 2003, but I have tried with VS 2005 with
> the same results.
You might try =IIF(Fields!FirstSignature.Value = Nothing, True, False)
or =IIF(IsNothing(Fields!FirstSignature.Value), True, False)
Either should do what you want. Make sure you set the property for
the row and not the individual cells/textboxes.|||Thanks, Toolman.
I will try that too. Is using the table + row format to hide empty fields
the best practice to accomplish this? I was also going to try to set the
height to 0 if it was going to be hidden. It just seems that SSRS is taking
the empty rows into consideration when making the pagination decision.|||On Dec 19, 2:32 pm, JC <J...@.discussions.microsoft.com> wrote:
> Thanks, Toolman.
> I will try that too. Is using the table + row format to hide empty fields
> the best practice to accomplish this? I was also going to try to set the
> height to 0 if it was going to be hidden. It just seems that SSRS is taking
> the empty rows into consideration when making the pagination decision.
I believe that it is. If for no other reason than the convenience of
not having to enter the expression for each textbox/cell. Also, my
experience has been that using a table rather than a collection of
boxes just works better in general. I know that trying to hide the
entire row by hiding all the cells doesn't seem to close up the white
space.