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?

Multi-user environment

Hi,
I’m working on a project that has MSDE installed on a Server (Windows Small
Business Server 2003 Standard with Veritas Backup V.9.1) for the back-end
database and for the front-end an Access Project (.adp) which I plan to have
on each user's computer.
The Server Administrator is pushing for an update to SBS 2003 Premium with 5
licenses as the only solution. Personally I think MSDE would do the job…
There are few things I’m not so sure:
- How many users can access the server at the same time with MSDE?
- Is there a way to let the user know if a record is being used? Like
displaying a message before any changes are made?
- I’ve created a table for users with username, password and group they
belong to. Is there a way to integrate this with the MSDE Groups and
Permissions or Transact-SQL? So Users, passwords and Groups can be setup from
a form in the front-end.
I’m doing some research on Veritas and see if it would backup the Instance
of MSDE.
Any help on this matter would be greatly appreciated
gaba
hi,
gaba wrote:
> - How many users can access the server at the same time with MSDE?
it's not a matter of users but concurrent batches... a study by Microsoft
indicates a magic number of 25, but it really depends on the application
type/design, database type/design...
of course bad ADO serverside cursors design will be worse then excellent
clientside disconnected design code...
but actually there's no limit, just the buuilt-in Workloads Gevernor that
kicks in when more then 8 concurrent batches are in progress... you can have
a look at
http://msdn.microsoft.com/library/?u...asp?frame=true
fro further info about that governor...

> - Is there a way to let the user know if a record is being used? Like
> displaying a message before any changes are made?
actually not... you have to test saving and trap the relative exception...

> - I've created a table for users with username, password and group
> they belong to. Is there a way to integrate this with the MSDE Groups
> and Permissions or Transact-SQL? So Users, passwords and Groups can
> be setup from a form in the front-end.
I think you've better drop your user tables and rely on the standard SQL
Server users and roles...you are duplicating all and incurring in troubles..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks so much for your answer. I see I need to change directions in few
settings but I'm on the right track. A lot of information to catch up...
gaba
"gaba" wrote:

> Hi,
> I’m working on a project that has MSDE installed on a Server (Windows Small
> Business Server 2003 Standard with Veritas Backup V.9.1) for the back-end
> database and for the front-end an Access Project (.adp) which I plan to have
> on each user's computer.
> The Server Administrator is pushing for an update to SBS 2003 Premium with 5
> licenses as the only solution. Personally I think MSDE would do the job…
> There are few things I’m not so sure:
> - How many users can access the server at the same time with MSDE?
> - Is there a way to let the user know if a record is being used? Like
> displaying a message before any changes are made?
> - I’ve created a table for users with username, password and group they
> belong to. Is there a way to integrate this with the MSDE Groups and
> Permissions or Transact-SQL? So Users, passwords and Groups can be setup from
> a form in the front-end.
> I’m doing some research on Veritas and see if it would backup the Instance
> of MSDE.
> Any help on this matter would be greatly appreciated
> --
> gaba
sql

Multiuser Databinding in SQL Server 2000?

Hi

I am developing one multi user application in windows environment using VC++ .NET with SQL Server 2000. How Can I create dataset for multi user data updating in my SQL Server 2000. Where I can get help and sample code?

Thanks

Jose

Jose,

The DataSet is an off-line data cache. The DataAdapter is used to submit the pending changes stored in the DataSet. In the DataAdapter's updating logic, you can specify your own concurrency options to control whether you want to use a "last in wins" approach with only the primary key column(s) in the WHERE clause, or use other columns in the WHERE clause to ensure you don't overwrite changes made by another user.

I hope this information proves helpful.

David Sceppa
ADO.NET Program Manager
Microsoft|||Hi

Thankyou for the nice explanation. One more question how can I write custom primary key value. for example (50710000001) like this. 5 is for year, 07 is for month, 10 is for place, and remaining is unique automatic generating number. How can I write program in SQL Server 2000.

Regards,
Jose

Multi-team and Multi-Server deployment environment issue

ACK!
So, here I am at my new job, thinking I'm learning RS because they have it
deployed in their environment, only to find out that I'm a member of one of
the teams *expected* to deploy it in the near future...
Due to SOX (Sarbane-Oxley) and other issues, we're going to have 3 servers:
Development, QC and Production.
There are also three teams: Development, QC and Production. I'm a member of
the Dev team. We have to figure out a way to deploy the Reports to the Dev
server for development, then allow the QC team to deploy the reports from the
Dev server to the QC server for testing, and finally to allow the Prod team
to deploy the reports from the QC server to the Production server without the
use of the Visual Studio .Net Deployment tool (since the Production team
doesn't have access, and likely the QC team doesn't either).
One of my co-workers is thinking he'll have to write a whole new app to
import the reports. The other option we can think of is the RS.exe script,
but that will require someone managing that script full time.
Does anyone have any other thoughts?
Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)You may want to check the following tool courtesy of Jasper Smith:
http://www.sqldbatips.com/showarticle.asp?ID=62
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:7AD0D78A-682E-4904-8CF2-EA6AC04F6F7A@.microsoft.com...
> ACK!
> So, here I am at my new job, thinking I'm learning RS because they have it
> deployed in their environment, only to find out that I'm a member of one
> of
> the teams *expected* to deploy it in the near future...
> Due to SOX (Sarbane-Oxley) and other issues, we're going to have 3
> servers:
> Development, QC and Production.
> There are also three teams: Development, QC and Production. I'm a member
> of
> the Dev team. We have to figure out a way to deploy the Reports to the
> Dev
> server for development, then allow the QC team to deploy the reports from
> the
> Dev server to the QC server for testing, and finally to allow the Prod
> team
> to deploy the reports from the QC server to the Production server without
> the
> use of the Visual Studio .Net Deployment tool (since the Production team
> doesn't have access, and likely the QC team doesn't either).
> One of my co-workers is thinking he'll have to write a whole new app to
> import the reports. The other option we can think of is the RS.exe
> script,
> but that will require someone managing that script full time.
> Does anyone have any other thoughts?
> Thanks,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)|||Thank you, Robert. I greatly appreciate the link.
Catadmin
--
MCDBA, MCSA
"When I get back, you're going to argue with me about the things that I want
to argue about!" Josh Lyman, The West Wing
"Robert Bruckner [MSFT]" wrote:
> You may want to check the following tool courtesy of Jasper Smith:
> http://www.sqldbatips.com/showarticle.asp?ID=62
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:7AD0D78A-682E-4904-8CF2-EA6AC04F6F7A@.microsoft.com...
> > ACK!
> >
> > So, here I am at my new job, thinking I'm learning RS because they have it
> > deployed in their environment, only to find out that I'm a member of one
> > of
> > the teams *expected* to deploy it in the near future...
> >
> > Due to SOX (Sarbane-Oxley) and other issues, we're going to have 3
> > servers:
> > Development, QC and Production.
> >
> > There are also three teams: Development, QC and Production. I'm a member
> > of
> > the Dev team. We have to figure out a way to deploy the Reports to the
> > Dev
> > server for development, then allow the QC team to deploy the reports from
> > the
> > Dev server to the QC server for testing, and finally to allow the Prod
> > team
> > to deploy the reports from the QC server to the Production server without
> > the
> > use of the Visual Studio .Net Deployment tool (since the Production team
> > doesn't have access, and likely the QC team doesn't either).
> >
> > One of my co-workers is thinking he'll have to write a whole new app to
> > import the reports. The other option we can think of is the RS.exe
> > script,
> > but that will require someone managing that script full time.
> >
> > Does anyone have any other thoughts?
> >
> > Thanks,
> >
> > Catadmin
> > --
> > MCDBA, MCSA
> > Random Thoughts: If a person is Microsoft Certified, does that mean that
> > Microsoft pays the bills for the funny white jackets that tie in the
> > back?
> > @.=)
>
>sql

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