Hi,
I am trying to use parameters in MDX Queries From one data set to other data
Set I created a data set which gives me Time values.
I am using this as parameter value as I do in SQL Queries.
Ex -
SELECT {[Measures].[Unit Sales]} on columns,
ORDER([Promotion Media].[Media Type].members,[Measures].[Unit Sales],DESC)
on rows FROM SALES where @.Time
When run It does'nt populate the value for the @.Time parameter
When i preview the report it populates the parameter values but when the
report view button is clicked it fails to build the Query because it cannot
populate the string.
Is there any other way to use the parameters and pass the parameters in MDX
Queries?
if possible with Examples
Thanks,Hi!
In MDX, this is not so simple. Checkt the article at
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
for the procedure.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
news:413ED68C-5AD5-4143-B079-1ADDE5DB6EF2@.microsoft.com...
> Hi,
> I am trying to use parameters in MDX Queries From one data set to other
data
> Set I created a data set which gives me Time values.
> I am using this as parameter value as I do in SQL Queries.
> Ex -
> SELECT {[Measures].[Unit Sales]} on columns,
> ORDER([Promotion Media].[Media Type].members,[Measures].[Unit Sales],DESC)
> on rows FROM SALES where @.Time
> When run It does'nt populate the value for the @.Time parameter
> When i preview the report it populates the parameter values but when the
> report view button is clicked it fails to build the Query because it
cannot
> populate the string.
>
> Is there any other way to use the parameters and pass the parameters in
MDX
> Queries?
> if possible with Examples
> Thanks,
>
>
>
>
>|||The OleDB provider for AS 2000 does not support parameterized MDX queries.
This MSDN article explains how to achieve parameterized MDX in RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
In addition, you may want to download this sample:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
BTW: RS 2005 Beta 2 contains graphical and text-based query designers for
MDX and DMX. They also support single-value parameters at this point.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:e6eUiDqrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> Hi!
> In MDX, this is not so simple. Checkt the article at
> http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
> for the procedure.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
> news:413ED68C-5AD5-4143-B079-1ADDE5DB6EF2@.microsoft.com...
> > Hi,
> >
> > I am trying to use parameters in MDX Queries From one data set to other
> data
> > Set I created a data set which gives me Time values.
> >
> > I am using this as parameter value as I do in SQL Queries.
> >
> > Ex -
> > SELECT {[Measures].[Unit Sales]} on columns,
> > ORDER([Promotion Media].[Media Type].members,[Measures].[Unit
Sales],DESC)
> > on rows FROM SALES where @.Time
> >
> > When run It does'nt populate the value for the @.Time parameter
> >
> > When i preview the report it populates the parameter values but when the
> > report view button is clicked it fails to build the Query because it
> cannot
> > populate the string.
> >
> >
> > Is there any other way to use the parameters and pass the parameters in
> MDX
> > Queries?
> > if possible with Examples
> >
> > Thanks,
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Referencing tables on a remote server
I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?
Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>
Referencing tables on a remote server
I have multiple stored procedures which include queries that reference tables
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>
on a remote, linked server. For example,
select id, name from remoteserver.db.dbo.table1
In order to speed up my queries and avoids lockouts while data is being
inserted into those remote tables, I would like to use the WITH (NOLOCK)
clause. However, i cannot use that on remote tables. I thought of creating
local views for each of those remote tables, for example,
create view v_table1 as select id, name from remoteserver.db.dbo.table1
and then using that view instead of the original table in my queries. Would
this be faster? Most of my queries involve multiple tables. The remote
tables do not have primary keys, though they do have multiple indexes.
I should add that I do not have permission to change anything on the remote
server. That includes adding stored procedures to that remote database or
adding indexes or keys.
Also, the remote database is massive, multi-terrabytes massive. The tables
I am interested in have millions of rows.
In a related question, when you create a view that references tables on a
remote server, how quickly/often does it get updated when the data in those
tables gets changed? Am I creating a CPU burden on the remote server or only
my own? My application does not insert/update/delete data on the remote
server, only queries it. On the remote server, however, there is a great
deal of data insertion going on throughout the day and night.
Any suggestions?Hi
A view does not copy data between servers, it is just a way at looking at
the data in table/tables.
If you create a view, the data, meeting the criteria, still has to be pulled
from the other server to be joined on the local server.
Think of a view as a Window to the other data.
Cheers
Mike
"speegee" wrote:
> I have multiple stored procedures which include queries that reference tables
> on a remote, linked server. For example,
> select id, name from remoteserver.db.dbo.table1
> In order to speed up my queries and avoids lockouts while data is being
> inserted into those remote tables, I would like to use the WITH (NOLOCK)
> clause. However, i cannot use that on remote tables. I thought of creating
> local views for each of those remote tables, for example,
> create view v_table1 as select id, name from remoteserver.db.dbo.table1
> and then using that view instead of the original table in my queries. Would
> this be faster? Most of my queries involve multiple tables. The remote
> tables do not have primary keys, though they do have multiple indexes.
> I should add that I do not have permission to change anything on the remote
> server. That includes adding stored procedures to that remote database or
> adding indexes or keys.
> Also, the remote database is massive, multi-terrabytes massive. The tables
> I am interested in have millions of rows.
> In a related question, when you create a view that references tables on a
> remote server, how quickly/often does it get updated when the data in those
> tables gets changed? Am I creating a CPU burden on the remote server or only
> my own? My application does not insert/update/delete data on the remote
> server, only queries it. On the remote server, however, there is a great
> deal of data insertion going on throughout the day and night.
> Any suggestions?
>|||OPENQUERY will solve your problem. Examples:
--This will fail:
select * from server1.bb01_db.dbo.bb01 (nolock)
where process_dt = '2002-12-02' and mid = '03301001' and
tran_dt = '2002-12-02'
--This will work if you have your linked servers set up --
correctly:
select * from openquery(server1, 'select * from
bb01_db.dbo.bb01 (nolock)
where process_dt = ''2002-12-02'' and mid = ''03301001''
and tran_dt = ''2002-12-02''')
.
>--Original Message--
>I have multiple stored procedures which include queries
that reference tables
>on a remote, linked server. For example,
>select id, name from remoteserver.db.dbo.table1
>In order to speed up my queries and avoids lockouts while
data is being
>inserted into those remote tables, I would like to use
the WITH (NOLOCK)
>clause. However, i cannot use that on remote tables. I
thought of creating
>local views for each of those remote tables, for example,
>create view v_table1 as select id, name from
remoteserver.db.dbo.table1
>and then using that view instead of the original table in
my queries. Would
>this be faster? Most of my queries involve multiple
tables. The remote
>tables do not have primary keys, though they do have
multiple indexes.
>I should add that I do not have permission to change
anything on the remote
>server. That includes adding stored procedures to that
remote database or
>adding indexes or keys.
>Also, the remote database is massive, multi-terrabytes
massive. The tables
>I am interested in have millions of rows.
>In a related question, when you create a view that
references tables on a
>remote server, how quickly/often does it get updated when
the data in those
>tables gets changed? Am I creating a CPU burden on the
remote server or only
>my own? My application does not insert/update/delete
data on the remote
>server, only queries it. On the remote server, however,
there is a great
>deal of data insertion going on throughout the day and
night.
>Any suggestions?
>.
>
Referencing Multi-value parameters in SQL code.
Hi everyone,
I have a report with 2 parameters - Account Manager and Company.
They are both populated from SQL Queries.
The Company query uses the value of the Account Manager parameter in the Where clause so that if an Account Manager is slected only the companies they are in charge of are displayed in the Company parameter.
My Select statement for the Company parameter list is as follows:
SELECT C.Company_Name
FROM Company C INNER JOIN
Employee E ON C.Account_Manager_Id = E.Employee_Id
WHERE (C.Type LIKE ('Customer%') OR C.Type = ('Partner - Customer'))
AND (E._Full_Name = @.Account_Manager)
How can I get this to work if I change the Account Manager parameter to be a Multi-value parameter?
I have tried replacing the "E._Full_Name = @.Account_Manager" with "E._Full_Name IN @.Account_Manager" but this does not work - is there another way to get this working?
Thanks,
Paul.
The IN clause requires the use of brackets.
Try E._Full_Name IN (@.Account_Manager)
|||That did the trick, thanks!
Subscribe to:
Posts (Atom)