Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:

>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is no
t
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:
>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

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....

Parameters - A OR B

Probably a pretty easy question: How do I implement 2 parameters in a OR situation? I want the users to be able to either choose a value for A OR a value for B, i.e. select the product name OR the product code. I have 3 datasets defined, 1 for each parameter and 1 for the report output. The last one has ...WHERE A=@.A or B = @.B. When testing, it seems to require both. I want them to be mutually exclusive, the user can only pick one.Hi,

you probably would be able to make one parameter disbled (using a dependency) if the one is chosen, but you won′t be able to do this for bither having circular references. What you can do is to implement both parameters in your report and make them selectable in the GUI and implement the logic for differenting the cases in the query code.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the information. I did some research and I updated my query's WHERE clause: WHERE (D.name = ISNULL(@.name, D.name)) AND (D.tracking_number = ISNULL(@.tracking_number, D.tracking_number))

I also checked the "allow null value" for each of the 2 parameters. I also have the default value to be null. However, if I only select a name, I'm still getting the prompt: Please select a value for the parameter 'Tracking Number'.

Any ideas what else I need to do? I thought the "allow null value" would do it.

|||

I had somewhat-similiar report

User pick a date range, and pick which date column to use (data is coming from a Stored Proc)

I can't think of an easy way to do it in StorProc unless I copy code twice (once for each date column), or dynamic query

so I used the filter in SSRS, on the table

=IIF(Parameters!UseImportedDateRange.Value = "Imported", Fields!ImportedDate.Value, Fields!MostRecentCallDate.Value)

> @.start_date

Parameterized SSIS Packeges

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

did you try using the execute sql task, mapping the parameter to a variable?|||

JSR2005 wrote:

How i can create parameterized sql query .This is my basic idea to implement.

select * from dimemployee

where name = ?

or

exec proc sp_para_employee ?

how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.

How hard did you look?

Execute SQL Task
(http://www.sqlis.com/default.aspx?58)

-Jamie

|||thank you it is working fine