Part

Hi folks,
I posted this somewhere else here, excuse me,,,
I have the following challenge
When I create a stored procedure the selects data from the portioned view,
the execution plan shows that SQL server scans all base tables, while the
select statement after replacing the variable with an actual value scans onl
y
the required table.
Here is a full script:
Execute the commented out statements at the end of the script with show
graphical execution plan option and see the difference.
SET NOCOUNT ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[EMPLOYEES]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_3]
GO
CREATE TABLE [Employees_1] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
) ON [PRIMARY]
CREATE TABLE [Employees_2] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
) ON [PRIMARY]
CREATE TABLE [Employees_3] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
) ON [PRIMARY]
GO
--
CREATE VIEW EMPLOYEES
AS
SELECT *
FROM EMPLOYEES_1
UNION ALL
SELECT *
FROM EMPLOYEES_2
UNION ALL
SELECT *
FROM EMPLOYEES_3
GO
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (1,'aaaaa',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (2,'aaaab',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (3,'baaaa',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (4,'baaab',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (5,'caaaa',3)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (6,'caaab',3)
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp2]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc GetEmp
@.firstchar int
as
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = @.firstchar
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc GetEmp2
@.firstchar int
as
declare @.sql varchar(8000)
set @.sql ='
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = ' + convert(varchar,@.firstchar)
exec (@.sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = 2
exec GetEmp 2
exec GetEmp2 2
*/Use STATISTICS IO and you will see that SQL Server will only access one of t
he table for the GetEmp
procedure. But the plan doesn't show that because the same plan should work
for whatever value you
send in the parameter. So elimination of the other views are deferred until
run-time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
> Hi folks,
> I posted this somewhere else here, excuse me,,,
> I have the following challenge
> When I create a stored procedure the selects data from the portioned view,
> the execution plan shows that SQL server scans all base tables, while the
> select statement after replacing the variable with an actual value scans o
nly
> the required table.
> Here is a full script:
> Execute the commented out statements at the end of the script with show
> graphical execution plan option and see the difference.
>
> SET NOCOUNT ON
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[EMPLOYEES]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_3]
> GO
> CREATE TABLE [Employees_1] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_2] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_3] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
> ) ON [PRIMARY]
> GO
> --
> CREATE VIEW EMPLOYEES
> AS
> SELECT *
> FROM EMPLOYEES_1
> UNION ALL
> SELECT *
> FROM EMPLOYEES_2
> UNION ALL
> SELECT *
> FROM EMPLOYEES_3
> GO
>
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (1,'aaaaa',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (2,'aaaab',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (3,'baaaa',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (4,'baaab',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (5,'caaaa',3)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (6,'caaab',3)
> go
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp2]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> create proc GetEmp
> @.firstchar int
> as
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = @.firstchar
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE proc GetEmp2
> @.firstchar int
> as
> declare @.sql varchar(8000)
> set @.sql ='
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = ' + convert(varchar,@.firstchar)
> exec (@.sql)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> /*
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = 2
> exec GetEmp 2
> exec GetEmp2 2
> */
>|||Karaszi,
the SP takes time much more than the select stmt, this is why i analyzed the
execution plan.
"Tibor Karaszi" wrote:

> Use STATISTICS IO and you will see that SQL Server will only access one of
the table for the GetEmp
> procedure. But the plan doesn't show that because the same plan should wor
k for whatever value you
> send in the parameter. So elimination of the other views are deferred unti
l run-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
>
>|||What does statistics IO say when you execute it? I assume that you have more
data in your tables
than the test script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
> Karaszi,
> the SP takes time much more than the select stmt, this is why i analyzed t
he
> execution plan.
> "Tibor Karaszi" wrote:
>|||I am sorry; there was a bug in my SP that was enforcing the engine to scan
all queries. My issue is closed now.
This posting was useful; you don’t have to rely on the graphical execution
plan always, some time you have to examine other thinks like the SET
STATISTICS IO.
Thanks all
"Tibor Karaszi" wrote:

> What does statistics IO say when you execute it? I assume that you have mo
re data in your tables
> than the test script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
>

Parent-Child-Dimension in SQL Srv 2005 Analysis Services

Hi experts,

having a parent-child-table with the columns child_id, child_name, parent_id
in SQL Server 2005 I just cannot create a parent-child dimension in BI Dev
Studio. Can anyone give me some hints? The Dim Build wizard doesn't create
the hierarchies, manually setting "parent" property to parent_id and "key"
to child_id as well as dragging and dropping the stuff into the hierachy
field haven't just led to success. I also tried to right-click both
parent_id and child_id to create a member property. It just never worked
out.

Any help would be greatly appreciated.

Kind regards,

JoergI realized that there is a special NG for this subject:
http://communities.microsoft.com/ne...er2005&slcid=us

Regards,
Joerg

Parent-Child View without using Cursors

I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:

Parent
{
ID_PK,
Name,
PhoneNum,
Address
}

Child
{
ID_PK,
ParentID_FK,
Name
}

The view would return a dataset like this:

Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward

with an unknown number of children for each parent.

Then I would like to be able to query against this view with something like this:

SELECT * FROM FamilyView Where Child2 = 'Peter'

I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?

Thanks for any advice you all can give me.
BrianWhat Version of SQL Server?

If it's 2005, you can use CTE (Common Table Expreassions)

If it's 2000, you probably need to use a udf that returns a table|||It's SQL Server 2000.

Could you give me an example of how a UDF would be used to solve this please?|||So you want to find where the family tree for a child somewhere in the middle?|||I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?

My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.|||Here you go, either a sproc, or a udf for set based stuff

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie', '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob', '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy', '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don', '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily', '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank', '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry', '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@.Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
RETURN @.rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @.Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
SELECT @.rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO|||You could even do

SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
GO|||Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!

But what I'm looking for is something closer to what this SQL generates.

USE Northwind
GO

SELECT OrderID,
coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.

SELECT * FROM OrderProductView WHERE Product1 = 'Chang'

and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.

The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.|||So you don't care about a tree, just a key and all it's attributive rows?

Maybe something like

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx|||Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
I was beginning to think this could only be executed in code outside the SQL.
I need to play with this a little to fully understand it all, but I think this will give me the results I need.
Thank you Brett for your patience and all your help!|||Just cut and paste the code example to see how it works

Good Luck

...oh, and you can buy me a margarita and we'll call it even|||Next time I'm in the Jersey area I might do just that. I really appreciate it.
And if you're ever in Grand Rapids...|||One more question...
My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?

Partitioning error

Hi,

I am trying to implement partitioning on a table
depending upon the fiscal_month value...

The current values are from 1-6...

Create partition function LoadDataPartitionFunction ( smallint)
as
Range for values (1,2,3,4,5,6)

-- drop partition scheme LoadDataPartitionScheme
create partition scheme LoadDataPartitionScheme
as
Partition LoadDataPartitionFunction ALL to ([PRIMARY])

CREATE TABLE Load_Data_Partition (
[RowID] [int] NOT NULL,
[Fiscal_Month] [smallint] NOT NULL,
[Fiscal_Year] [smallint] NOT NULL,
...

[Service] [nvarchar](100) COLLATE
) ON LoadDataPartitionScheme (Fiscal_Month)

truncate table Load_Data_old -- same schema as load_data_partition
Alter table load_data_partition switch partition 1 to Load_Data_old

-- which month's data to be moved out
alter partition function LoadDataPartitionFunction () merge range (1)

Alter partition scheme LoadDataPartitionScheme next used [primary]

-- which months data to be moved in
alter partition function LoadDataPartitionFunction () split range(7)

Select * from sys.partition_range_values

function_id boundary_id parameter_id value
---- ---- ---- --
65545 1 1 2
65545 2 1 3
65545 3 1 4
65545 4 1 5
65545 5 1 6
65545 6 1 7

Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6

ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.

Values in Load_Data_new for fiscal_month is 7

But when i try

Insert into [Load_Data_partition]
Select * from [Load_Data_new]
where fiscal_month = 7

it works fine...

reference used : http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htmI got the answer..

Alter table Load_Data_new add constraint load_data_new_month check ( fiscal_month =7)

even though the Load_Data_new table has only month = 7 data...
a constraint is mandatory....

partitioning a database

hi all,
before i had a big database more than 90GB and it's growing very sharp so what i did is to create a summary database that gonna hold some aggregation and i create also three tables that gonna hold the data, on will hold the data for the last three months (very detailed information) one between 3 months and a year(less detailed) and the last one older than a year(less less detailed) these are the company requirement i'm working with.
then i transfer the data to the new tables with stored procedures.
now my question is: do i need to create a view for these three tables? and if yes how should i do it?!! cus i do not have the same columns in the three tables.
thanks experts.hi all,
i just posted another question .
titeled optimizing stored procedure
thanks

Partitioned view doesn't allow to query itself while insertion

I created 2 similar tables for different accounts within one database and
partitioned view on them.
create table t
(
[ID] int identity(1,1),
[Name] varchar(15),
[Account] varchar(20) default 'Account1' check(Team = 'Account1'),
PRIMARY KEY CLUSTERED
(
[ID], [Account]
)
)
create view v
as
select * from Account1.t
union all
select * from Account2.t
I run query like this:
"select top 100 * from v where Account = 'Account1'"
and massive insertion from staging table like this at the same time:
"insert into t ( name ) select name from tt under different account"
my query stops until insertion is completed.
FYI: each tables reside in it's own filegroup. Each filgroup has its files
on physically different HDD.
There are 2 multithreaded processors there and parallelism works.
Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so o
n.
Isolation level is Read Commited (when I set read uncommited it works
without locks, of course).
I don't know why my query wait for end of insertion. Query and insertion
don't use common resources. They MUST works independently...Can you cut and paste the exact SQL you're running, or provide
a repro without typos? What you posted here includes a CHECK
constraint that refers to a non-existent column named [Team].
You might try reversing the order of the primary key columns.
Steve Kass
Drew University
OSA wrote:

>I created 2 similar tables for different accounts within one database and
>partitioned view on them.
>create table t
>(
> [ID] int identity(1,1),
> [Name] varchar(15),
> [Account] varchar(20) default 'Account1' check(Team = 'Account1'),
> PRIMARY KEY CLUSTERED
> (
> [ID], [Account]
> )
> )
>create view v
>as
> select * from Account1.t
> union all
> select * from Account2.t
>I run query like this:
>"select top 100 * from v where Account = 'Account1'"
> and massive insertion from staging table like this at the same time:
>"insert into t ( name ) select name from tt under different account"
>my query stops until insertion is completed.
>FYI: each tables reside in it's own filegroup. Each filgroup has its files
>on physically different HDD.
>There are 2 multithreaded processors there and parallelism works.
>Execution plpan shows DIFFERENT CLUSTER KEYS ARE USED for any ss and so
on.
>Isolation level is Read Commited (when I set read uncommited it works
>without locks, of course).
>I don't know why my query wait for end of insertion. Query and insertion
>don't use common resources. They MUST works independently...
>

parent child - get all children for a specific row

Hi,
I am trying to create a bulletinboard app. So I have a thread table
with parent/child relation.
How can I on a specific threadID get all its children ?
Any help/hints will be greatly appreciated :-)
Regards
SayaIf you have SQL Server 2005, check out "Common Table Expressions" in the
BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Saya" <vaqas@.hotmail.com> wrote in message
news:1149257162.442872.321450@.i39g2000cwa.googlegroups.com...
Hi,
I am trying to create a bulletinboard app. So I have a thread table
with parent/child relation.
How can I on a specific threadID get all its children ?
Any help/hints will be greatly appreciated :-)
Regards
Saya

parent child - get all children for a specific row

Hi,
I am trying to create a bulletinboard app. So I have a thread table
with parent/child relation.
How can I on a specific threadID get all its children ?
Any help/hints will be greatly appreciated :-)
Regards
SayaIf you have SQL Server 2005, check out "Common Table Expressions" in the
BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Saya" <vaqas@.hotmail.com> wrote in message
news:1149257162.442872.321450@.i39g2000cwa.googlegroups.com...
Hi,
I am trying to create a bulletinboard app. So I have a thread table
with parent/child relation.
How can I on a specific threadID get all its children ?
Any help/hints will be greatly appreciated :-)
Regards
Saya

Parent Attribute Hierarchy

Does anyone know how to create a parent-child hierarchy that can be expanded in reporting services as well as analysis services? Currently, I have a dimension with three attributes: parent, child, and key. Everything seems to work fine in Analysis Services. However, reporting services doesn't seem to recognize the parent hierarchy as being a true hierarchy.

Any help? Thank you.

Does anyone have an idea or just need some more clarification?

Thanks

|||

There is only sinlge way to create parent-child hierarchy in Analysis Services. You dont have any choice here.

Not sure about Reporting Services and how it is displaying parent-child hierachy. Moving thread to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
Yahya
Yahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>
|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User
defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...een">
> Can I create parameterized view, in other words like stored procedures whe
re
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parameters to SP ?

How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.

- CB

Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.

Parameters that depend on another

I have a situation where I need to create the same graph for 4 "levels". The problem I am facing is that each subsequent level depends on its parent, and I require a parameter (@.Level) to indicate which level of the report to create.

For example, if a user wants to view a report for the 'grouping' level then they would select the grouping from a drop down list and @.level would be set to 1.

Now, if the user wanted to view a report for the 'subgrouping' level, then the user would first need to select a grouping. Once this is done, the subgrouping drop down would become enabled and the user would then select a subgrouping to the grouping (ie. filtering based on the first dropdown menu).

If the user does not select a subgrouping, then the value of @.level should be 1. If the user does select a subgrouping, then the value of @.level should be 2.

The datasource for this report is a stored proced that accepts the @.level paramter as well as the ID of the object choosen from the drop down menu.

Is this possible to do? If so, how would i go about creating the sequence which the parameteres are entered and determin which level is selected?

Thanks for any and all help.

Ben

Ok, I figured out that when one parameter excepts another parameters value as its input, it is disabled untill the required parameters are selected.

I still need help figuring out how to know/set the value of @.level based on which parameters have been chosen.

Thanks.

Ben

Parameters question

I am trying to create a set of parameters that will alow me to select data
either based on criteria from dropdown boxes or from a text box. In my
DataSet I have something like this (simpified)
if @.ItemDesc is null
select citemno, cdescript, 0.0 as price from icitem
where ....
else
select citemno, cdescript, 0.0 as price from icitem
where cdescript like @.ItemDesc
@.ItemDesc is a value from a text box. When I run the report it keeps
prompting me to enter a value into the first dropdown.
Thanks for help.On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am trying to create a set of parameters that will alow me to select data
> either based on criteria from dropdown boxes or from a text box. In my
> DataSet I have something like this (simpified)
> if @.ItemDesc is null
> select citemno, cdescript, 0.0 as price from icitem
> where ....
> else
> select citemno, cdescript, 0.0 as price from icitem
> where cdescript like @.ItemDesc
> @.ItemDesc is a value from a text box. When I run the report it keeps
> prompting me to enter a value into the first dropdown.
> Thanks for help.
You could have a default value in the drop-down box of an empty
string. That should avoid the prompt, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||And to do that I need to select default values non-queried?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am trying to create a set of parameters that will alow me to select
>> data
>> either based on criteria from dropdown boxes or from a text box. In my
>> DataSet I have something like this (simpified)
>> if @.ItemDesc is null
>> select citemno, cdescript, 0.0 as price from icitem
>> where ....
>> else
>> select citemno, cdescript, 0.0 as price from icitem
>> where cdescript like @.ItemDesc
>> @.ItemDesc is a value from a text box. When I run the report it keeps
>> prompting me to enter a value into the first dropdown.
>> Thanks for help.
>
> You could have a default value in the drop-down box of an empty
> string. That should avoid the prompt, etc. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> And to do that I need to select default values non-queried?
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am trying to create a set of parameters that will alow me to select
> >> data
> >> either based on criteria from dropdown boxes or from a text box. In my
> >> DataSet I have something like this (simpified)
> >> if @.ItemDesc is null
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where ....
> >> else
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where cdescript like @.ItemDesc
> >> @.ItemDesc is a value from a text box. When I run the report it keeps
> >> prompting me to enter a value into the first dropdown.
> >> Thanks for help.
> > You could have a default value in the drop-down box of an empty
> > string. That should avoid the prompt, etc. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
If you want to set the default value to an empty string in the stored
procedure/query that sources the parameter (and order it in the
dataset to show the empty string first) then you can set it as 'from
query.' If you want to just use null and check for the null in the
stored procedure/query then you can set it to non-queried. It depends
on where you want to set the default value. Hope this clarifies it
better.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am setting it up to a non-queried value and I am entering ="" into a
field.
Still asks me to enter parameter when I run it.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182954533.677728.17440@.u2g2000hsc.googlegroups.com...
> On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> And to do that I need to select default values non-queried?
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
>> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> >> I am trying to create a set of parameters that will alow me to select
>> >> data
>> >> either based on criteria from dropdown boxes or from a text box. In my
>> >> DataSet I have something like this (simpified)
>> >> if @.ItemDesc is null
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where ....
>> >> else
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where cdescript like @.ItemDesc
>> >> @.ItemDesc is a value from a text box. When I run the report it keeps
>> >> prompting me to enter a value into the first dropdown.
>> >> Thanks for help.
>> > You could have a default value in the drop-down box of an empty
>> > string. That should avoid the prompt, etc. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> If you want to set the default value to an empty string in the stored
> procedure/query that sources the parameter (and order it in the
> dataset to show the empty string first) then you can set it as 'from
> query.' If you want to just use null and check for the null in the
> stored procedure/query then you can set it to non-queried. It depends
> on where you want to set the default value. Hope this clarifies it
> better.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Parameters in URL - Problem

Hi,

I am new to Reporting services and have managed to create some reports. I want to pass some parameters in the URL to the report. I know how to do this but some parameters are not passing to the parameter boxes. They are clearly displayed in the URL but are not populating the boxes. I have one report done before i came and this used to work but now it doesnt work.

Any ideas?

Matt

Hi Matt,

i am unable to understand ur prob completely. But still, if the parameter field is a dropdown list, then the value for that parameter passed in the URL should exists in the dropdown list or else the report will throw an error.

Cheers

Chakri.

|||

Try the sample reports available with your installation of SQL Server. They should show you how to do this:

http://msdn2.microsoft.com/en-us/library/ms161542.aspx

Hope that helps,

-Lukasz

|||

Hi All,

I have the similar kind of problem where i can't see the parameters and their values.

I have a simple report where i am passing employeeid as parameter. When i run it on the server it prompt me for employeeid which after giving displays the report but in the url of browser , i cannot see the parameter and it's value .

Anybody has any idea why this is happenning.

Thanks,

Nagul Shaik

|||

We do not show the parameter in the URL when you're using the report viewer. It does not matter, the report is receiving the parameter.

To see what value the report is receiving you can add a textbox to the report and set it's value to:

=ReportParameters!ParameterName.Value

Hope that helps,

-Lukasz

|||

thanks for the reply but i am not using any report viewer. I am trying access the report thru browser giving the parameters in query string instead of in the prompt.

My requirement is to create hyperlink in my aspx page for that report and when the logged in user clicks on it , i have to pass userid to that report to show the contents of the report.

My sql2005 reporting service is running on WIN2K3.

Please help me with it.

|||

This topic in books online provides you the starting point for how to construct URLs understood by the report server.

http://msdn2.microsoft.com/de-de/library/ms155362.aspx

to pass a particular parameter you defined in the report, you can just append <parametername>=<value> to the end of the query string.

The User!UserId global variable is not a parameter - it is determined by who is logged into the report server. You cannot control it on the URL itself.

Hope that helps,

-Lukasz

|||

http://msdn2.microsoft.com/en-us/library/ms153586(SQL.90).aspx

Parameters in URL - Problem

Hi,

I am new to Reporting services and have managed to create some reports. I want to pass some parameters in the URL to the report. I know how to do this but some parameters are not passing to the parameter boxes. They are clearly displayed in the URL but are not populating the boxes. I have one report done before i came and this used to work but now it doesnt work.

Any ideas?

Matt

Hi Matt,

i am unable to understand ur prob completely. But still, if the parameter field is a dropdown list, then the value for that parameter passed in the URL should exists in the dropdown list or else the report will throw an error.

Cheers

Chakri.

|||

Try the sample reports available with your installation of SQL Server. They should show you how to do this:

http://msdn2.microsoft.com/en-us/library/ms161542.aspx

Hope that helps,

-Lukasz

|||

Hi All,

I have the similar kind of problem where i can't see the parameters and their values.

I have a simple report where i am passing employeeid as parameter. When i run it on the server it prompt me for employeeid which after giving displays the report but in the url of browser , i cannot see the parameter and it's value .

Anybody has any idea why this is happenning.

Thanks,

Nagul Shaik

|||

We do not show the parameter in the URL when you're using the report viewer. It does not matter, the report is receiving the parameter.

To see what value the report is receiving you can add a textbox to the report and set it's value to:

=ReportParameters!ParameterName.Value

Hope that helps,

-Lukasz

|||

thanks for the reply but i am not using any report viewer. I am trying access the report thru browser giving the parameters in query string instead of in the prompt.

My requirement is to create hyperlink in my aspx page for that report and when the logged in user clicks on it , i have to pass userid to that report to show the contents of the report.

My sql2005 reporting service is running on WIN2K3.

Please help me with it.

|||

This topic in books online provides you the starting point for how to construct URLs understood by the report server.

http://msdn2.microsoft.com/de-de/library/ms155362.aspx

to pass a particular parameter you defined in the report, you can just append <parametername>=<value> to the end of the query string.

The User!UserId global variable is not a parameter - it is determined by who is logged into the report server. You cannot control it on the URL itself.

Hope that helps,

-Lukasz

|||

http://msdn2.microsoft.com/en-us/library/ms153586(SQL.90).aspx

parameters in SP

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade = ' + @.rank

EXEC (@.sql)

GO

when i execute this proc without where condintion its working, but when i use where condition its dispalyin invalid column name with the name im passing

eg.

xxx admin,aB

WHEN I TRY TO EXECUTE PROC WITH ABOVE STAT, ITS DIAPLAYIN ERROR AS "INVALID COLUMN NAME ab

but

xxx admin," ' aB ' "

when i try like this its giving result.

how can i avoid second method of executin the proc and use first method for the sake of passing value from frontend

change your sql query as..

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade =''' + @.rank +'''' [...i added 3 single quotes before + @.rank and 4 single quotes after @.rank +...]

it would work..

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade =''' + @.rank +''''

EXEC (@.sql)

GO

Good Luck./.

|||

Hi,

Your Procedure should be like the following

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable wheregrade = ''' + @.rank + ''''

EXEC (@.sql)

GO

Now Execute your query. It will not raise the error.

I hope this is useful to you.

Cheers,
Ganesh.

|||

Thanks for the responses.

But what logic is this? 3 quotes before and 4 quotes after??

any way i will figure it out.

Thankyou again

parameters in reporting services

hello to everybody.

can anyone give me some info (some links)on how to create parameters in RS? my problem is that i pass all the values as selection but when i push the button i get all the results. so the problem must lie in the connection of the parameters with the questioning.

can anyone describe the procedure?

than u in advance

Try searching Reporting Services forum for similar problems.

Here are some for you:

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

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

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi :

If you are using the cube to create the reports and using the usual BI studio interface - then you can create parameters by dragging them onto the "Filter" section on the MDX browser pane - and then checking the "Parameter" option.

However, if you are looking to customise the list you choose therein, then you can individually write specific MDX to create each "DataSet" that makes up the parameter values. You can do this by going to the Report -> Parameters from the Menu.

Is this what you are after ?

Thanks.

Suranjan

|||thnxs. i know that. the problem is that when i switch to mdx to modify the query, the fiels disappear. and i have a prob passing the pqrqmeters in the query.

Parameters in MDX

Is it possible to create an mdx query using parameters? For example, in
a SQL query I'm using in Reporting services, I might do this:
SELECT
*
FROM
dtav_ServiceFacts
WHERE
ServiceInstanceHost = 'BizTalkServerApplication' AND
ServiceInstanceState = @.state
And then SQL Reporting automatically creates the parameter 'state' that
the user can choose. Is there an equivalence in MDX for this? For
example,
SELECT
{ [Dimension D1].[BizTalkServerApplication] } ON COLUMNS,
{ [Dimension D3].members } ON ROWS
FROM
[Cube D]
WHERE
( [Dimension D2].[@.state] )
But when I try this, I get an error saying named parameters are not
supported, so use unnamed. So I put in a "?" in place of "@.state",
which gives me a little value box, but then says command parameters are
not supported by this provider. Is there any way to do this,
particularly in SQL Reporting?
Thanks.It's very possible to create a MDX query with parameters. But the way you
write the query is a bit different than with T-SQL.
You need to let the whole query be on one line (might be several lines in
the designer, but with no line breaks), and the parameter is added with a
slightly different syntax: You also need to add a =" in the beginning and "
in the end.
="SELECT {[Dimension D1].[BizTalkServerApplication] } ON COLUMNS, {
[Dimension D3].members } ON ROWS FROM [Cube D] WHERE ([Dimension D2].[" &
Parameters!State.Value & "])"
Also check out William Pearson's article
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount
and BottomCount Parameters
http://www.databasejournal.com/features/mssql/article.php/10894_3504651_11
On the first page, he writes about parameterized MDX queries, regardless of
TopCount.
Kaisa M. Lindahl Lervik
"Wannabe_Kiwi" <amkessel@.gmail.com> wrote in message
news:1148941733.833408.226390@.g10g2000cwb.googlegroups.com...
> Is it possible to create an mdx query using parameters? For example, in
> a SQL query I'm using in Reporting services, I might do this:
> SELECT
> *
> FROM
> dtav_ServiceFacts
> WHERE
> ServiceInstanceHost = 'BizTalkServerApplication' AND
> ServiceInstanceState = @.state
> And then SQL Reporting automatically creates the parameter 'state' that
> the user can choose. Is there an equivalence in MDX for this? For
> example,
> SELECT
> { [Dimension D1].[BizTalkServerApplication] } ON COLUMNS,
> { [Dimension D3].members } ON ROWS
> FROM
> [Cube D]
> WHERE
> ( [Dimension D2].[@.state] )
> But when I try this, I get an error saying named parameters are not
> supported, so use unnamed. So I put in a "?" in place of "@.state",
> which gives me a little value box, but then says command parameters are
> not supported by this provider. Is there any way to do this,
> particularly in SQL Reporting?
> Thanks.
>|||You'll want to be aware of the differences between 2000 and 2005. In
2000 you could create MDX for your report with the technique shown. In
2005 you pretty much have to let the wizard create the MDX for you. It
creates the parameters for you in the designer, but you're pretty much
restricted (as far as I can see) to what it creates.
Kaisa M. Lindahl Lervik wrote:
> It's very possible to create a MDX query with parameters. But the way you
> write the query is a bit different than with T-SQL.
> You need to let the whole query be on one line (might be several lines in
> the designer, but with no line breaks), and the parameter is added with a
> slightly different syntax: You also need to add a =" in the beginning and "
> in the end.
> ="SELECT {[Dimension D1].[BizTalkServerApplication] } ON COLUMNS, {
> [Dimension D3].members } ON ROWS FROM [Cube D] WHERE ([Dimension D2].[" &
> Parameters!State.Value & "])"
> Also check out William Pearson's article
> MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount
> and BottomCount Parameters
> http://www.databasejournal.com/features/mssql/article.php/10894_3504651_11
> On the first page, he writes about parameterized MDX queries, regardless of
> TopCount.
> Kaisa M. Lindahl Lervik
>
> "Wannabe_Kiwi" <amkessel@.gmail.com> wrote in message
> news:1148941733.833408.226390@.g10g2000cwb.googlegroups.com...
> > Is it possible to create an mdx query using parameters? For example, in
> > a SQL query I'm using in Reporting services, I might do this:
> >
> > SELECT
> > *
> > FROM
> > dtav_ServiceFacts
> > WHERE
> > ServiceInstanceHost = 'BizTalkServerApplication' AND
> > ServiceInstanceState = @.state
> >
> > And then SQL Reporting automatically creates the parameter 'state' that
> > the user can choose. Is there an equivalence in MDX for this? For
> > example,
> >
> > SELECT
> > { [Dimension D1].[BizTalkServerApplication] } ON COLUMNS,
> > { [Dimension D3].members } ON ROWS
> > FROM
> > [Cube D]
> > WHERE
> > ( [Dimension D2].[@.state] )
> >
> > But when I try this, I get an error saying named parameters are not
> > supported, so use unnamed. So I put in a "?" in place of "@.state",
> > which gives me a little value box, but then says command parameters are
> > not supported by this provider. Is there any way to do this,
> > particularly in SQL Reporting?
> >
> > Thanks.
> >