Disregard some parameters

Hi,

I am building a search Query which takes 7 parameters:

Lets call them @.P1 .. @.P7 (all Int's)

The query is a simple select query like:

SELECT * FROM MyTable WHERE (Field1 = @.P1) AND (Field2 = @.P2) ...

My problem is that if some parameters are -1 they shall be disregarded.

Is there any way to set a parameter to a value meaning "Anything", or do I
have to
remove the criteria from the select clause?

(In the latter case I cannot use a stored procedure, which is what I prefer)

Cheers
GunnarGunnar,

if your columns do not contain NULLs, then you can use

WHERE Field1 BETWEEN COALESCE(NULLIF(@.P1,-1),-2147483648) AND
COALESCE(NULLIF(@.P1,-1),2147483647)

This WHERE clause assumes that the value "-1" is your indication of a
missing parameter. If you use NULL instead of -1, then you can replace
NULLIF(@.P1,-1) with @.P1. It also assumes the int datatype. If the column
is of a different integer datatype (for example bigint), then the
minimum and maximum value need to be adjusted.

If your column do contain NULLs, then you can use

WHERE CASE WHEN @.P1=-1 THEN 1
CASE WHEN Field1=@.P1 THEN 1
ELSE 0 END = 1

But in general, the first approach will perform better.

Hope this helps,
Gert-Jan

Gunnar Liknes wrote:
> Hi,
> I am building a search Query which takes 7 parameters:
> Lets call them @.P1 .. @.P7 (all Int's)
> The query is a simple select query like:
> SELECT * FROM MyTable WHERE (Field1 = @.P1) AND (Field2 = @.P2) ...
> My problem is that if some parameters are -1 they shall be disregarded.
> Is there any way to set a parameter to a value meaning "Anything", or do I
> have to
> remove the criteria from the select clause?
> (In the latter case I cannot use a stored procedure, which is what I prefer)
> Cheers
> Gunnar

--
(Please reply only to the newsgroup)|||Hi,

One way to do the trick is to write:

SELECT * FROM MyTable WHERE ((@.P1 = -1) OR (Field1 = @.P1)) AND ...

If @.P1 is -1, the OR-clause will simply be true for all rows, effectively
ignoring the comparison with Field1.

-Jrgen

"Gunnar Liknes" <g_liknes.Tabortunderscores@.g_lobal-satcom.com> skrev i en
meddelelse news:4110a27d$1@.news.broadpark.no...
> Hi,
> I am building a search Query which takes 7 parameters:
> Lets call them @.P1 .. @.P7 (all Int's)
> The query is a simple select query like:
> SELECT * FROM MyTable WHERE (Field1 = @.P1) AND (Field2 = @.P2) ...
> My problem is that if some parameters are -1 they shall be disregarded.
> Is there any way to set a parameter to a value meaning "Anything", or do I
> have to
> remove the criteria from the select clause?
> (In the latter case I cannot use a stored procedure, which is what I
prefer)
> Cheers
> Gunnar
>|||"Gert-Jan Strik" wrote

> if your columns do not contain NULLs, then you can use
> WHERE Field1 BETWEEN COALESCE(NULLIF(@.P1,-1),-2147483648) AND
> COALESCE(NULLIF(@.P1,-1),2147483647)
> This WHERE clause assumes that the value "-1" is your indication of a
> missing parameter. If you use NULL instead of -1, then you can replace
> NULLIF(@.P1,-1) with @.P1. It also assumes the int datatype. If the column
> is of a different integer datatype (for example bigint), then the
> minimum and maximum value need to be adjusted.
> If your column do contain NULLs, then you can use
> WHERE CASE WHEN @.P1=-1 THEN 1
> CASE WHEN Field1=@.P1 THEN 1
> ELSE 0 END = 1
> But in general, the first approach will perform better.

Thank you both (Gert-Jan and Jrgen) for two excellent working solutions to
my problem. The COALESCE function was interesting. Will it perform better
than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) "
approach?

Thanks,
Gunnar|||Gunnar Liknes wrote:
> Thank you both (Gert-Jan and Jrgen) for two excellent working solutions to
> my problem. The COALESCE function was interesting. Will it perform better
> than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) "
> approach?
> Thanks,
> Gunnar

Yes, because if the column is indexed, index seeks can be used. The OR
solution needs an index scan.

Gert-Jan
--
(Please reply only to the newsgroup)|||Gunnar Liknes (g_liknes.Tabortunderscores@.g_lobal-satcom.com) writes:
> Thank you both (Gert-Jan and Jrgen) for two excellent working solutions
> to my problem. The COALESCE function was interesting. Will it perform
> better than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) " approach?

Permit me to modify Gert-Jan's enthusiasm a little. It may perform better,
but I have not always be successful with it. And if the columns is not
indexed then it not matter much anyway.

The only way to find out is to benchmark.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Gert-Jan Strik" wrote
> Gunnar Liknes wrote:
> > Thank you both (Gert-Jan and Jrgen) for two excellent working solutions
to
> > my problem. The COALESCE function was interesting. Will it perform
better
> > than the "WHERE ((@.P1 = -1) OR (Field1 = @.P1)) "
> > approach?

> Yes, because if the column is indexed, index seeks can be used. The OR
> solution needs an index scan.

Does MS SQL perform complete boolean evaluations? If (@.P1=-1) it should
not have to check if (Field1 = @.P1) because the result of the statement is
already determined.

Gunnar|||Gunnar Liknes (g_liknes.Tabortunderscores@.g_lobal-satcom.com) writes:
> Does MS SQL perform complete boolean evaluations? If (@.P1=-1) it should
> not have to check if (Field1 = @.P1) because the result of the statement is
> already determined.

The answer is that, yes, SQL Server is able to make logical shortcuts,
but that is not applicable here.

When SQL Server builds a query plan for a stored procedure, it builds
the plan for the entire procedure at once, and is thus blind to what
the actual values of variables and parameters at the time of the statement.
It does take in regard the values of parameter to build the plan, but
since it don't know whether parameter changes value in the procedure or
not, SQL Server can choose a plan which would yield the wrong result if
the parameter is changed. Moreover, since the plan is cached, the procedure
might be called with some other values the next time.

Thus if you have:

SELECT *
FROM tbl
WHERE (field1 = @.p1 OR @.p1 IS NULL)
AND (field2 = @.p2 OR @.p2 IS NULL)

It cannot look at @.p1 and say "Hey @.p1 is NULL, I don't have to test
Field1". So it must pick a plan where it accesses field1. No, once it
comes to the statement it could opt to not actually check field1, but
the cost is not the check - the cost is the access. In this case,
the optimizer will most like to scan the table from left to right.

Here is another example:

SELECT *
FROM tbl
WHERE @.p1 = 0 OR EXISTS (SELECT *
FROM tbl2
WHERE tbl.col = tbl2.col)

Here, if @.p1 is 0 we retrieve all rows from tbl, but if @.p1 is 1 only
rows which has a matching row in tbl2 are to be returned. In this example,
SQL Server is actually able to avoid accessing tbl2 if @.p1 is 0, since
once @.p1 is evaluated, the other branch can be pruned. Note here that
is not your C-style of shortcutting - you get the same result if you
have the condition on @.p1 last.

To read more about this topic, I have an article on my web site:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" wrote.
> Gunnar Liknes writes:

> > Does MS SQL perform complete boolean evaluations? If (@.P1=-1) it should
> > not have to check if (Field1 = @.P1) because the result of the statement
is
> > already determined.

> The answer is that, yes, SQL Server is able to make logical shortcuts,
> but that is not applicable here.

<snip explanation
> To read more about this topic, I have an article on my web site:
> http://www.sommarskog.se/dyn-search.html.

Thank you Erland, your article was very helpful. I also found the topics of
your
other articles very interresting. I'll read the one about Arrays & Lists
when I have
some time.

Regarding search. I'll have to wait until we get more data in our database
before
I decide which search alternative to use. For now I stick to IF / OR.

Cheers
Gunnar

displaying remaining records

Hello all. Quick rundown of what I need to do. I am pulling cartons(or
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets

displaying remaining records

Hello all. Quick rundown of what I need to do. I am pulling cartons(or
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.
I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets
|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets

displaying remaining records

Hello all. Quick rundown of what I need to do. I am pulling cartons(or
we can call them fruits) to make pallets of 25 cartons. Lets say there
is 27 apples in inventory, and i need to make a pallet of apples which
has room for 25. There is a remainder of 2 apples. 55 bananas in I am
doing this with various other fruits. I first need to pull out all all
units in which I can make a pallet(w/one fruit type on each). I then am
going to make pallets of any fruits that are left over
I just need to know the sql code to create a table from the remaining
fruits that cant make a full pallet, and the coding to pull 25 fruits
when available. I plan on doing the coding for the first using a
cursor. I am relatively new to SQL coding, and would appreciate the
help.I hope this is enough to get you started.
declare @.apples int
declare @.pallets int
declare @.cartonsPerPallet int
set @.apples = 127
set @.cartonsPerPallet = 25
set @.pallets = @.apples/@.cartonsPerPallet +
Sign(@.Apples%@.cartonsPerPallet)
print @.pallets|||Modification:
declare @.cartons int
declare @.pallets int
declare @.cartonsPerPallet int
set @.cartons = 125
set @.cartonsPerPallet = 25
set @.pallets = @.cartons/@.cartonsPerPallet +
Sign(@.cartons%@.cartonsPerPallet)
print @.pallets

Displaying images that are accessed through URL

Hi,

I have trouble displaying images that are accessed through URLs in my report.
If I call the URL directly in the Browser the Image can be opened without problems.
The images can be accessed with an anonymous access.

When I deploy the report or in the Preview of Visual Studio I get the following message:

[rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.

In Layout View of Visual Studio the image is showen correctly.

The Images are stored on a Solaris maschine with WebSphere 6.0 as WebServer.

Any ideas?

Thank you.

Frank

You need to configure unattended execution account: http://msdn2.microsoft.com/en-us/library/ms156302.aspx

|||

I configured the unattended execution account, but there I can only use windows user. The images are on a Sun Server with Solaris 9 (Unix) which has different users. Allthough the Images can be accessed without any authorisation, so I think I don't need the unattended execution account.

Any more ideas

Thank you

|||

Have you ever figured this out? I am having the same issue, but the file is on the report server. Actually, it's not an image file, but an ASP.Net web application that creates an image on the fly. Like you, I can get it to come up in the development environment or if I enter the URL directly, but it won't appear in the deployed report.

I have set the unattended execution account, restarted IIS, set permissions to the app's dll everywhere I could think... nothing works.

Interestingly, if you look at the report's source, you can see the images "src" attribute is an empty string, so the report renderer is blocking it.

|||Has anyone found a resolution for this. I have taken the same steps and still only get the RED X in the image space. Account used for unattended execution has admin rights and I can see the image when calling the URL directly from IE.|||

i am also desperately looking for a resolution here

Displaying images that are accessed through URL

Hi,

I have trouble displaying images that are accessed through URLs in my report.
If I call the URL directly in the Browser the Image can be opened without problems.
The images can be accessed with an anonymous access.

When I deploy the report or in the Preview of Visual Studio I get the following message:

[rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.

In Layout View of Visual Studio the image is showen correctly.

The Images are stored on a Solaris maschine with WebSphere 6.0 as WebServer.

Any ideas?

Thank you.

Frank

You need to configure unattended execution account: http://msdn2.microsoft.com/en-us/library/ms156302.aspx

|||

I configured the unattended execution account, but there I can only use windows user. The images are on a Sun Server with Solaris 9 (Unix) which has different users. Allthough the Images can be accessed without any authorisation, so I think I don't need the unattended execution account.

Any more ideas

Thank you

|||

Have you ever figured this out? I am having the same issue, but the file is on the report server. Actually, it's not an image file, but an ASP.Net web application that creates an image on the fly. Like you, I can get it to come up in the development environment or if I enter the URL directly, but it won't appear in the deployed report.

I have set the unattended execution account, restarted IIS, set permissions to the app's dll everywhere I could think... nothing works.

Interestingly, if you look at the report's source, you can see the images "src" attribute is an empty string, so the report renderer is blocking it.

|||Has anyone found a resolution for this. I have taken the same steps and still only get the RED X in the image space. Account used for unattended execution has admin rights and I can see the image when calling the URL directly from IE.|||

i am also desperately looking for a resolution here|||

I enabled anonymous access and that solved my issue.

|||

I found the solution for my problem. The mime type for the images was not set on the WebSphere Server.

After I set the mime type on the WebSpere Server, the images where shown correctly.

I hope this helps.

Frank

Displaying images that are accessed through URL

Hi,

I have trouble displaying images that are accessed through URLs in my report.
If I call the URL directly in the Browser the Image can be opened without problems.
The images can be accessed with an anonymous access.

When I deploy the report or in the Preview of Visual Studio I get the following message:

[rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.

In Layout View of Visual Studio the image is showen correctly.

The Images are stored on a Solaris maschine with WebSphere 6.0 as WebServer.

Any ideas?

Thank you.

Frank

You need to configure unattended execution account: http://msdn2.microsoft.com/en-us/library/ms156302.aspx

|||

I configured the unattended execution account, but there I can only use windows user. The images are on a Sun Server with Solaris 9 (Unix) which has different users. Allthough the Images can be accessed without any authorisation, so I think I don't need the unattended execution account.

Any more ideas

Thank you

|||

Have you ever figured this out? I am having the same issue, but the file is on the report server. Actually, it's not an image file, but an ASP.Net web application that creates an image on the fly. Like you, I can get it to come up in the development environment or if I enter the URL directly, but it won't appear in the deployed report.

I have set the unattended execution account, restarted IIS, set permissions to the app's dll everywhere I could think... nothing works.

Interestingly, if you look at the report's source, you can see the images "src" attribute is an empty string, so the report renderer is blocking it.

|||Has anyone found a resolution for this. I have taken the same steps and still only get the RED X in the image space. Account used for unattended execution has admin rights and I can see the image when calling the URL directly from IE.|||

i am also desperately looking for a resolution here

Displaying images that are accessed through URL

Hi,

I have trouble displaying images that are accessed through URLs in my report.
If I call the URL directly in the Browser the Image can be opened without problems.
The images can be accessed with an anonymous access.

When I deploy the report or in the Preview of Visual Studio I get the following message:

[rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.

In Layout View of Visual Studio the image is showen correctly.

The Images are stored on a Solaris maschine with WebSphere 6.0 as WebServer.

Any ideas?

Thank you.

Frank

You need to configure unattended execution account: http://msdn2.microsoft.com/en-us/library/ms156302.aspx

|||

I configured the unattended execution account, but there I can only use windows user. The images are on a Sun Server with Solaris 9 (Unix) which has different users. Allthough the Images can be accessed without any authorisation, so I think I don't need the unattended execution account.

Any more ideas

Thank you

|||

Have you ever figured this out? I am having the same issue, but the file is on the report server. Actually, it's not an image file, but an ASP.Net web application that creates an image on the fly. Like you, I can get it to come up in the development environment or if I enter the URL directly, but it won't appear in the deployed report.

I have set the unattended execution account, restarted IIS, set permissions to the app's dll everywhere I could think... nothing works.

Interestingly, if you look at the report's source, you can see the images "src" attribute is an empty string, so the report renderer is blocking it.

|||Has anyone found a resolution for this. I have taken the same steps and still only get the RED X in the image space. Account used for unattended execution has admin rights and I can see the image when calling the URL directly from IE.|||

i am also desperately looking for a resolution here

Displaying images that are accessed through URL

Hi,

I have trouble displaying images that are accessed through URLs in my report.
If I call the URL directly in the Browser the Image can be opened without problems.
The images can be accessed with an anonymous access.

When I deploy the report or in the Preview of Visual Studio I get the following message:

[rsWarningFetchingExternalImages] Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access.

In Layout View of Visual Studio the image is showen correctly.

The Images are stored on a Solaris maschine with WebSphere 6.0 as WebServer.

Any ideas?

Thank you.

Frank

You need to configure unattended execution account: http://msdn2.microsoft.com/en-us/library/ms156302.aspx

|||

I configured the unattended execution account, but there I can only use windows user. The images are on a Sun Server with Solaris 9 (Unix) which has different users. Allthough the Images can be accessed without any authorisation, so I think I don't need the unattended execution account.

Any more ideas

Thank you

|||

Have you ever figured this out? I am having the same issue, but the file is on the report server. Actually, it's not an image file, but an ASP.Net web application that creates an image on the fly. Like you, I can get it to come up in the development environment or if I enter the URL directly, but it won't appear in the deployed report.

I have set the unattended execution account, restarted IIS, set permissions to the app's dll everywhere I could think... nothing works.

Interestingly, if you look at the report's source, you can see the images "src" attribute is an empty string, so the report renderer is blocking it.

|||Has anyone found a resolution for this. I have taken the same steps and still only get the RED X in the image space. Account used for unattended execution has admin rights and I can see the image when calling the URL directly from IE.|||i am also desperately looking for a resolution here|||

I enabled anonymous access and that solved my issue.

|||

I found the solution for my problem. The mime type for the images was not set on the WebSphere Server.

After I set the mime type on the WebSpere Server, the images where shown correctly.

I hope this helps.

Frank

Display truncating on SQL Profiler

Greetings,

I am having a problem debugging an XML error we are getting in our production environment because I can't view the entire call to the stored procedure in Profiler. I have successfully traced the error, but when I go to the line with the call to the SP that caused the error, it doesn't show me the entire call. It only shows me the 'exec sproc_name and then the first 16 characters of the XML string parameter that is being passed to the proc. For some reason it's doing this to ONLY the stored procs that have XML parameters...on procs that use standard parameters, it displays the entire call correctly.

I have looked for some type of setting that controls this, but haven't been able to find it. I also have looked through many forums for this issue but to no avail. Does anyone know why this is happening? And, is there a workaround/fix?

Thanks in advance...

SBIs it because it's a text column? Do you see anny other calls using text, ntext or image?

Display report bar and "Report Being Generated" message

We are accessing reporting services from our webserver via the RS
webservice. The RS server is behind our firewall. Currently, when we call
the report, we have a blank page and the browser continues loading the
report without giving any real feedback to the user. At that point, we only
display the report.. ie no toolbar etc. Is there a way via the Web Service
to display the Report toolbar as well as the "Report being generated"
message?
ThanksI have the same problem, i.e. report displayed, but no toolbar or "report
being generated" message. Did you find a solution? I use webrequest.
WebRequest myWebRequest = WebRequest.Create(url);
WebResponse myWebResponse = myWebRequest.GetResponse();
Thanks