Parent Package Variable issue?

I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable.

Is this a know issue? Is it by design?Yep, I see the same thing.|||Good to know I'm not crazy! Thanks.|||Look for a fix in SP3, perhaps.

There is already a bug filed for this issue.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=174510

Partitioning "Alter Table Switch" Statement Failing

Help!! I can't seem to find information on the error that I'm getting anywhere:

ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'DB1.dbo.Table1' is not a subset of range defined by partition 4 in table 'DB1.dbo.Table2'.

Here's some sample code that generates this error

Code Snippet

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE LEFT FOR VALUES (443, 444, 445)

CREATE PARTITION FUNCTION [Table2Range](int) AS RANGE LEFT FOR VALUES (440, 441, 442, 443)

GO

CREATE PARTITION SCHEME [Table1Scheme] AS PARTITION [Table1Range] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE PARTITION SCHEME [Table2Scheme] AS PARTITION [Table2Range] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

GO

CREATE TABLE [dbo].[Table1](

[session_id] [int] NOT NULL,

[ProcessLogID] [int] NOT NULL,

CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

[session_id] ASC,

[ProcessLogID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [Table1Scheme]([ProcessLogID])

) ON [Table1Scheme]([ProcessLogID])

CREATE TABLE [dbo].[Table2](

[session_id] [int] NOT NULL,

[ProcessLogID] [int] NOT NULL,

CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED

(

[session_id] ASC,

[ProcessLogID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [Table2Scheme]([ProcessLogID])

) ON [Table2Scheme]([ProcessLogID])

GO

insert into [Table1]

select 1, 443

insert into [Table1]

select 2, 444

insert into [Table1]

select 3, 445

insert into [Table2]

select 4, 440

insert into [Table2]

select 5, 441

insert into [Table2]

select 6, 442

ALTER TABLE [Table1] SWITCH PARTITION 1 to [Table2] PARTITION 4

I'd really appreciate any advice anyone has! Thanks so much.

Jess

Some further info on this

I discovered some minor changes to the example above that would get this working. I don't understand why, on a conceptual level, these changes would make a difference. Any ideas?

The 2 changes that make my above code work:

1)Declare the partition ranges using RIGHT instead of LEFT:

(i.e.

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE RIGHT FOR VALUES (443, 444, 445)

CREATE PARTITION FUNCTION [Table2Range](int) AS RANGE RIGHT FOR VALUES (440, 441, 442, 443))

2) Run the Alter Table Switch statements on Partition #s 2 & 5 instead of #s 1 & 4

(i.e. "ALTER TABLE [Table1] SWITCH PARTITION 2 to [Table2] PARTITION 5")

It's going to be a ROYAL PITA to switch the partition functions from RIGHT to LEFT in the db I'm working with. Is there anyway I can get this working keeping the LEFT definition?

Thanks!

Jess

|||

Alright- I've answered my own question. I wanted to post in case anyone runs into the same error.

I've got it working using the LEFT boundary condition by adding a Check Constraint on Table1.

ALTER TABLE Table1

ADD CONSTRAINT [CK_ProcessLogID]

CHECK ([ProcessLogID] >= 443)

Basically, when I declared the Partition Function connected to table 1 as:

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE LEFT FOR VALUES (443, 444, 445)

I am declaring Partion Number 1 to store all data where the ProcessLogID<= 443. The key is the LESS THAN or = 443. I was getting confused because in this particular example there was no data that was less than 443 in the table- but there was nothing in the table definition that prohibited it.

Partition Number 4 of Table2 is defined to be all data where the ProcessLogID> = 443 and ProcessLogID < 444 (or ProcessLogID = 443, since it is an integer column). Since a switch statement is actually just altering metadata there can be no data validation, and the definition of the table needs to be representitive that switching one partition to another will follow with the table's partition definition. By adding the check constraint to Table1, you can be assured that all data in Partition #1 of Table1 will be consistant with Partition #4 of Table2. Yippee!

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:
> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
Naty
Hi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:

> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:

> Hi,
> I am having problems with inserting data into a partitioned view that unio
n
> a few tables that have a computed column (defined in the tables themselves
).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains
a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>

Parameters defined in .rdl and how they are applied in Report Manager

One of my reports is defined as follows in .rdl:

<ReportParameter Name="ExplicitProject">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Agile_1029c</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>ExplicitProject</Prompt>
</ReportParameter>

In Report Manager, I have overriden this value by specifying that the default value be an empty string. The report uses the value I specified in Report Manager.

How does this work? Where does Report Server store the overriden definition? There seem to be two places where the rendering is getting its specification. Is there a hierarchy i.e. first check .rdl, then check (wherever the overrides are stored?)

Kind Regards
Jean-Pierre

The overridden parameters are stored in the report catalog. If you override the report parameters in the Report Manager and subsequently deploy the report definition with new default values, you will find that the parameter default values don't change. This is done to favor the report administrators. You can only change default values of a deployed report programatically. The other option is to delete the report and re-deploy.|||

Thank you Teo. THis has been of great help.

Parameters collection

I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.

I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.

How do I access the Parameters collection within my custom code?

Ideas anyone?|||Try...
report.Parameters!parameter_name.Value
Thanks Tomson McCabe :)

Parameters collection

I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.

I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.

How do I access the Parameters collection within my custom code?

Ideas anyone?|||Try...
report.Parameters!parameter_name.Value
Thanks Tomson McCabe :)

Parameters collection

I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?In your custom code, use:
Report.Parameters!parameter1.Value
"Paul H" wrote:
> I have a function defined in Report->Report Properties-> Code Tab which
> I want to access the parameters collection to look for a Debug flag.
> I get the following error:
> c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
> [BC30469] Reference to a non-shared member requires an object
> reference.
> How do I access the Parameters collection within my custom code?
>