Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

Multi-value parameter cannot be passed to SQL where clause (Oracle db)

Greetings!!

I have a problem with SSRS 2005 (simple rdl) on Oracle10g environment.

PROBLEM Definition

We have multi-valued list box, the selected values of which are being passed as parameters to Oracle 10g (back-end) from the report. I want to be able to pass these values as "WHERE......IN" clause in the main query in SSRS dataset window responsible for loading report. However, when I do that it throws error. I have tried all possible combinations i.e using IN operator with '@.', ':' , '&'. Now when I did the same with SQL server as back end; it worked. Can you please help with the exact syntax of executing this successfully?

Sample Query used in sample SSRS data-set when SQL server was at back end:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN (@.p))

The report worked fine in this case when the user selected certain values in multi-select list.

Thank you,

Karthik

Oracle can not support the query parameter with a name as your case "@.p". You may change your query into:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST

FROM TEST_SSRS WHERE (PRODUCT IN ?)

Then set the parameter ? to one of your report parameter.

It should be work.

|||

Multi-value query parameter rewrite for Oracle only works with the "Oracle" data extension (i.e. data source type = "Oracle").
Note: Oracle uses ":" to mark named parameter - therefore the following query syntax should work:

SELECT PKEY, PRODUCT, TIME, BRANCH, AGEGROUP, REVENUE, PROFIT, COST
FROM TEST_SSRS WHERE PRODUCT IN (:p)

-- Robert

|||Excellent, the : method worked for me. However it only worked in the data view, and not the preview window. In both instances I am asked for the paramters which I specify. In the data view I have to type in the date value as a paramter but in the preview view I can select a date form the calendar. Basically I am getting results in the data view but nothing is retuned in the preview view, no error messages occur, just no data. Can anyone help?

MultiValue Parameter

Hello,

I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".

Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.

Thank you,

Itzhak

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

|||

Thank you, this will solve my issue.

I put the following expression and it works well !!

=iif(CountRows("UsersDataSet")=Parameters!UserID.Count,"ALL",join(Parameters!UserID.Label,","))

Thanks,

Itzhak

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

multiprocessor environment

Folks,
We're running Microsoft SQL-2000 server
in a multiprocessor environment. (quad processor box)
Can we force different jobs that are
scheduled concurrently to use a particular
processor.
What we would like to do is run 4 separate
jobs, scheduled for the same time, and force
each job to use a different processor on a
quad processor database server host.
Is it possible to do this at all with SQL-2000.

Thanks in advanceI think you can only configure which processors will be used by SQL Server. The server itself will assign every thread that runs to a processor based on the priority.sql

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advanceIt will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:
> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

multiple tempdb files and large operation in tempdb.

Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
It will expanded the files in a round robin fashion until its needs are met
or it errors out due to insufficient space.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
Hello,
Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
Each one 15 GB leaving 20 GB free on the device. Large operation comes
along that uses tempdb and needs 40 GB to complete.
What happens?
(1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
Operation fails with no disk space?
(2) Operation uses mutliple tempdb files?
(3) other?
Thanks in advance
|||Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>
|||Yep. Basically, it will try to expand the first file and if that isn't
enough - even for just that txn - it will continue to the next one and so
on.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:9A464240-AAD6-4871-8652-6445A7833C28@.microsoft.com...
Thanks for the reponse.
Just to clarify will it round robin even if it is a single operation?
thanks in advance.
Joe
"Tom Moreau" wrote:

> It will expanded the files in a round robin fashion until its needs are
met
> or it errors out due to insufficient space.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:CDE38DE1-C853-4F4F-BAFF-E482394E3AEC@.microsoft.com...
> Hello,
> Tempdb on it's own device let's say 80 GB. 4 core box so 4 tempdb files.
> Each one 15 GB leaving 20 GB free on the device. Large operation comes
> along that uses tempdb and needs 40 GB to complete.
> What happens?
> (1) Grows one file to 35 GB (15 GB original + 20 GB remaining space).
> Operation fails with no disk space?
> (2) Operation uses mutliple tempdb files?
> (3) other?
> Thanks in advance
>
>

MULTIPLE SQL INSTANCES

SQL 2K / WINDOWS 2003 SERVER
I have a box with 2 SQL INSTANCES and each instances with few databases.
Memory is setup to 'Use a fix memory size' and setup with half and one GB
each .
Almost 250 GB free hard-disk space and both instances using 4 processors.
I have different SQL jobs running on each instances.
My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
hrs and this is not happening always.
Checked for SQL LOCKS and found NONE.
Please advice me , how to trouble-shoot this ?
Rgds
MS-USER
Hi
Without knowing the nature of the jobs it is hard to give detailed advice,
but I would start by looking at performance monitor and try and see if there
is a bottleneck. Your may well have contention on the discs as this is a
common resource that each instance will be competing with, therefore disc
queue lengths, also if your logs and data are on the same set of discs there
potentially there would be problems even with a single instance. Another area
that may cause problems is processor affinity. You may want to change the
affinity mask and see if that helps or use the MAXDOPS hint on your queries.
You may want to read the SQL Server 2000 Performance Tuning Technical
Reference Manual ISBN ISBN 0-7356-1270-6 and Inside SQL Server 2000 by Kalen
Delaney ISBN 0-7356-0998-5
John
"MS User" wrote:

> SQL 2K / WINDOWS 2003 SERVER
> I have a box with 2 SQL INSTANCES and each instances with few databases.
> Memory is setup to 'Use a fix memory size' and setup with half and one GB
> each .
> Almost 250 GB free hard-disk space and both instances using 4 processors.
> I have different SQL jobs running on each instances.
> My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
> hrs and this is not happening always.
> Checked for SQL LOCKS and found NONE.
> Please advice me , how to trouble-shoot this ?
> Rgds
> MS-USER
>
>

MULTIPLE SQL INSTANCES

SQL 2K / WINDOWS 2003 SERVER
I have a box with 2 SQL INSTANCES and each instances with few databases.
Memory is setup to 'Use a fix memory size' and setup with half and one GB
each .
Almost 250 GB free hard-disk space and both instances using 4 processors.
I have different SQL jobs running on each instances.
My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
hrs and this is not happening always.
Checked for SQL LOCKS and found NONE.
Please advice me , how to trouble-shoot this '
Rgds
MS-USERHi
Without knowing the nature of the jobs it is hard to give detailed advice,
but I would start by looking at performance monitor and try and see if there
is a bottleneck. Your may well have contention on the discs as this is a
common resource that each instance will be competing with, therefore disc
queue lengths, also if your logs and data are on the same set of discs there
potentially there would be problems even with a single instance. Another area
that may cause problems is processor affinity. You may want to change the
affinity mask and see if that helps or use the MAXDOPS hint on your queries.
You may want to read the SQL Server 2000 Performance Tuning Technical
Reference Manual ISBN ISBN 0-7356-1270-6 and Inside SQL Server 2000 by Kalen
Delaney ISBN 0-7356-0998-5
John
"MS User" wrote:
> SQL 2K / WINDOWS 2003 SERVER
> I have a box with 2 SQL INSTANCES and each instances with few databases.
> Memory is setup to 'Use a fix memory size' and setup with half and one GB
> each .
> Almost 250 GB free hard-disk space and both instances using 4 processors.
> I have different SQL jobs running on each instances.
> My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
> hrs and this is not happening always.
> Checked for SQL LOCKS and found NONE.
> Please advice me , how to trouble-shoot this '
> Rgds
> MS-USER
>
>

MULTIPLE SQL INSTANCES

SQL 2K / WINDOWS 2003 SERVER
I have a box with 2 SQL INSTANCES and each instances with few databases.
Memory is setup to 'Use a fix memory size' and setup with half and one GB
each .
Almost 250 GB free hard-disk space and both instances using 4 processors.
I have different SQL jobs running on each instances.
My issue is , few jobs which usually runs within 4-5 hrs, running for 12-15
hrs and this is not happening always.
Checked for SQL LOCKS and found NONE.
Please advice me , how to trouble-shoot this '
Rgds
MS-USERHi
Without knowing the nature of the jobs it is hard to give detailed advice,
but I would start by looking at performance monitor and try and see if there
is a bottleneck. Your may well have contention on the discs as this is a
common resource that each instance will be competing with, therefore disc
queue lengths, also if your logs and data are on the same set of discs there
potentially there would be problems even with a single instance. Another are
a
that may cause problems is processor affinity. You may want to change the
affinity mask and see if that helps or use the MAXDOPS hint on your queries.
You may want to read the SQL Server 2000 Performance Tuning Technical
Reference Manual ISBN ISBN 0-7356-1270-6 and Inside SQL Server 2000 by Kalen
Delaney ISBN 0-7356-0998-5
John
"MS User" wrote:

> SQL 2K / WINDOWS 2003 SERVER
> I have a box with 2 SQL INSTANCES and each instances with few databases.
> Memory is setup to 'Use a fix memory size' and setup with half and one GB
> each .
> Almost 250 GB free hard-disk space and both instances using 4 processors.
> I have different SQL jobs running on each instances.
> My issue is , few jobs which usually runs within 4-5 hrs, running for 12-1
5
> hrs and this is not happening always.
> Checked for SQL LOCKS and found NONE.
> Please advice me , how to trouble-shoot this '
> Rgds
> MS-USER
>
>