Hi, how can be implemented republisher replication model?
When I first configure and create a publication "P1", and then create pull
subscripton on "RP1 = Publishing subscriber" everything is ok. But whenever I
try to create a new Publication on RP1 I get an error message which says that
before creating publication the subscription shoulfd be dropped, then create
pub and at last create pull subscription again. Unfortunetly this approach
does not work.
Vice versa when I first create publication P1, then another publication RP1
for the subscriber and finally create a pull subscription on RP1 having P1 as
Publisher, EM says that again first drop publication and then create pull
subscription...
So, how can I implement republisher replication model?
The answer is :
In order to use this topology the publishing subscriber must be a global
subscriber for publisher P1.
"Erkan Aygun" wrote:
> Hi, how can be implemented republisher replication model?
> When I first configure and create a publication "P1", and then create pull
> subscripton on "RP1 = Publishing subscriber" everything is ok. But whenever I
> try to create a new Publication on RP1 I get an error message which says that
> before creating publication the subscription shoulfd be dropped, then create
> pub and at last create pull subscription again. Unfortunetly this approach
> does not work.
> Vice versa when I first create publication P1, then another publication RP1
> for the subscriber and finally create a pull subscription on RP1 having P1 as
> Publisher, EM says that again first drop publication and then create pull
> subscription...
> So, how can I implement republisher replication model?
Showing posts with label implemented. Show all posts
Showing posts with label implemented. Show all posts
Friday, March 9, 2012
Saturday, February 25, 2012
Repost: Data not being partitioned properly?
Thanks to David Browne for pointing out my mistake yesterday. But I have
implemented the suggestion and am still experiencing the same behavior. I'm
sorry to to keep posting partioning questions here, but the concept has
sparked a huge interest at my work and I need to answer lots of questions.
Therefore, Im doing lots of different tests/ secenarios and it seems like
each answer brings up more questions. Anyways, below is the DDL and DML,
with explanations of what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000, 10000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
PTPK ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([salary])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([salary])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
The issue here is that "ON [myRangePS2]([salary])" is not used because of
the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
clustered index determines the partitioning of data so all data is
partitioned on PTPK instead of salary as you intended.
If your objective is to use SWITCH as a means to quickly archive data,
you'll want to align the table and index partitions on a date value. See
aligned indexes in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Thanks to David Browne for pointing out my mistake yesterday. But I have
> implemented the suggestion and am still experiencing the same behavior.
> I'm
> sorry to to keep posting partioning questions here, but the concept has
> sparked a huge interest at my work and I need to answer lots of questions.
> Therefore, Im doing lots of different tests/ secenarios and it seems like
> each answer brings up more questions. Anyways, below is the DDL and DML,
> with explanations of what Im trying to accomplish and where my confusion
> is.
>
>
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> 100, 1000, 10000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
> CREATE TABLE [dbo].[PartitionTestArchive](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> /*Now I want to move all the data (value 9999) in partition 4 into my new
> ParitionTestArchive table:*/
>
> alter table PartitionTest
> switch partition 4 to [PartitionTestArchive] partition 4
>
> /*But this did nothing. So I try:*/
>
> alter table PartitionTest
> switch partition 3 to [PartitionTestArchive] partition 3
>
> /*And that did nothing either. So I try:*/
>
> alter table PartitionTest
> switch partition 2 to [PartitionTestArchive] partition 2
>
> /*And that moved every row of data with a value > 1 (99,999,9999) in the
> table to PartitionTestArchive.*/
>
> Again, my goal was just to move the row of data with value 9999 (partition
> 4) into PartitionTestArchive. So what am I not understanding? It seems
> that
> I either don't understand the concept, or data isn't going into the
> partition I think it should?
>
> TIA, ChrisR
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
The issue here is that "ON [myRangePS2]([salary])" is not used because of
the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
clustered index determines the partitioning of data so all data is
partitioned on PTPK instead of salary as you intended.
If your objective is to use SWITCH as a means to quickly archive data,
you'll want to align the table and index partitions on a date value. See
aligned indexes in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Thanks to David Browne for pointing out my mistake yesterday. But I have
> implemented the suggestion and am still experiencing the same behavior.
> I'm
> sorry to to keep posting partioning questions here, but the concept has
> sparked a huge interest at my work and I need to answer lots of questions.
> Therefore, Im doing lots of different tests/ secenarios and it seems like
> each answer brings up more questions. Anyways, below is the DDL and DML,
> with explanations of what Im trying to accomplish and where my confusion
> is.
>
>
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> 100, 1000, 10000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
> CREATE TABLE [dbo].[PartitionTestArchive](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> /*Now I want to move all the data (value 9999) in partition 4 into my new
> ParitionTestArchive table:*/
>
> alter table PartitionTest
> switch partition 4 to [PartitionTestArchive] partition 4
>
> /*But this did nothing. So I try:*/
>
> alter table PartitionTest
> switch partition 3 to [PartitionTestArchive] partition 3
>
> /*And that did nothing either. So I try:*/
>
> alter table PartitionTest
> switch partition 2 to [PartitionTestArchive] partition 2
>
> /*And that moved every row of data with a value > 1 (99,999,9999) in the
> table to PartitionTestArchive.*/
>
> Again, my goal was just to move the row of data with value 9999 (partition
> 4) into PartitionTestArchive. So what am I not understanding? It seems
> that
> I either don't understand the concept, or data isn't going into the
> partition I think it should?
>
> TIA, ChrisR
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||For clarification, are you saying that the placement of the Primary Key
"overrides" where I had placed the partitioning?
Also, if that's the case, and I want to quickly archive data as you
mentioned, then wouldn't I need to have my PK's on the date column (provided
thats the column I wanted to SWITCH, which of course it most likely would
be)?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7B86860D-747C-4D63-99BE-18C6EA3CF498@.microsoft.com...
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > PTPK ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([salary])
> The issue here is that "ON [myRangePS2]([salary])" is not used because of
> the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
> clustered index determines the partitioning of data so all data is
> partitioned on PTPK instead of salary as you intended.
> If your objective is to use SWITCH as a means to quickly archive data,
> you'll want to align the table and index partitions on a date value. See
> aligned indexes in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> > Thanks to David Browne for pointing out my mistake yesterday. But I have
> > implemented the suggestion and am still experiencing the same behavior.
> > I'm
> > sorry to to keep posting partioning questions here, but the concept has
> > sparked a huge interest at my work and I need to answer lots of
questions.
> > Therefore, Im doing lots of different tests/ secenarios and it seems
like
> > each answer brings up more questions. Anyways, below is the DDL and DML,
> > with explanations of what Im trying to accomplish and where my confusion
> > is.
> >
> >
> >
> >
> > USE [AdventureWorks]
> >
> > GO
> >
> > /****** Object: PartitionFunction [myRangePF2] Script Date:
11/17/2006
> > 15:01:28 ******/
> >
> > CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> > 100, 1000, 10000)
> >
> >
> >
> > /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> > 15:10:35 ******/
> >
> > CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> > ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > PTPK ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([salary])
> >
> >
> >
> > insert into PartitionTest (salary) values (1)
> >
> > insert into PartitionTest (salary) values (99)
> >
> > insert into PartitionTest (salary) values (999)
> >
> > insert into PartitionTest (salary) values (9999)
> >
> >
> >
> > /*
> >
> > From BOL:
> >
> > Partition 1 2 3 4
> >
> > Values
> >
> > col1 <= 1
> >
> > col1 > 1 AND col1 <= 100
> >
> > col1 > 100 AND col1 <= 1000
> >
> > col1 > 1000
> >
> >
> >
> > Now if I understand correctly, there should be 1 row of data in each
> > partition?*/
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTestArchive](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > [PTPK] ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([salary])
> >
> >
> >
> > /*Now I want to move all the data (value 9999) in partition 4 into my
new
> > ParitionTestArchive table:*/
> >
> >
> >
> > alter table PartitionTest
> >
> > switch partition 4 to [PartitionTestArchive] partition 4
> >
> >
> >
> > /*But this did nothing. So I try:*/
> >
> >
> >
> > alter table PartitionTest
> >
> > switch partition 3 to [PartitionTestArchive] partition 3
> >
> >
> >
> > /*And that did nothing either. So I try:*/
> >
> >
> >
> > alter table PartitionTest
> >
> > switch partition 2 to [PartitionTestArchive] partition 2
> >
> >
> >
> > /*And that moved every row of data with a value > 1 (99,999,9999) in the
> > table to PartitionTestArchive.*/
> >
> >
> >
> > Again, my goal was just to move the row of data with value 9999
(partition
> > 4) into PartitionTestArchive. So what am I not understanding? It seems
> > that
> > I either don't understand the concept, or data isn't going into the
> > partition I think it should?
> >
> >
> >
> > TIA, ChrisR
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>|||"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:O8aM%23m2CHHA.1224@.TK2MSFTNGP04.phx.gbl...
> For clarification, are you saying that the placement of the Primary Key
> "overrides" where I had placed the partitioning?
> Also, if that's the case, and I want to quickly archive data as you
> mentioned, then wouldn't I need to have my PK's on the date column
(provided
> thats the column I wanted to SWITCH, which of course it most likely would
> be)?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7B86860D-747C-4D63-99BE-18C6EA3CF498@.microsoft.com...
> > > CREATE TABLE [dbo].[PartitionTest](
> > >
> > > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> > >
> > > [salary] [int] NOT NULL,
> > >
> > > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> > >
> > > (
> > >
> > > PTPK ASC
> > >
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> > >
> > > ) ON [myRangePS2]([salary])
> >
> > The issue here is that "ON [myRangePS2]([salary])" is not used because
of
> > the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
> > clustered index determines the partitioning of data so all data is
> > partitioned on PTPK instead of salary as you intended.
> >
> > If your objective is to use SWITCH as a means to quickly archive data,
> > you'll want to align the table and index partitions on a date value.
See
> > aligned indexes in the Books Online for more information.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> > news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> > > Thanks to David Browne for pointing out my mistake yesterday. But I
have
> > > implemented the suggestion and am still experiencing the same
behavior.
> > > I'm
> > > sorry to to keep posting partioning questions here, but the concept
has
> > > sparked a huge interest at my work and I need to answer lots of
> questions.
> > > Therefore, Im doing lots of different tests/ secenarios and it seems
> like
> > > each answer brings up more questions. Anyways, below is the DDL and
DML,
> > > with explanations of what Im trying to accomplish and where my
confusion
> > > is.
> > >
> > >
> > >
> > >
> > > USE [AdventureWorks]
> > >
> > > GO
> > >
> > > /****** Object: PartitionFunction [myRangePF2] Script Date:
> 11/17/2006
> > > 15:01:28 ******/
> > >
> > > CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES
(1,
> > > 100, 1000, 10000)
> > >
> > >
> > >
> > > /****** Object: PartitionScheme [myRangePS2] Script Date:
11/17/2006
> > > 15:10:35 ******/
> > >
> > > CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> > > ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
> > >
> > >
> > >
> > > CREATE TABLE [dbo].[PartitionTest](
> > >
> > > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> > >
> > > [salary] [int] NOT NULL,
> > >
> > > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> > >
> > > (
> > >
> > > PTPK ASC
> > >
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> > >
> > > ) ON [myRangePS2]([salary])
> > >
> > >
> > >
> > > insert into PartitionTest (salary) values (1)
> > >
> > > insert into PartitionTest (salary) values (99)
> > >
> > > insert into PartitionTest (salary) values (999)
> > >
> > > insert into PartitionTest (salary) values (9999)
> > >
> > >
> > >
> > > /*
> > >
> > > From BOL:
> > >
> > > Partition 1 2 3 4
> > >
> > > Values
> > >
> > > col1 <= 1
> > >
> > > col1 > 1 AND col1 <= 100
> > >
> > > col1 > 100 AND col1 <= 1000
> > >
> > > col1 > 1000
> > >
> > >
> > >
> > > Now if I understand correctly, there should be 1 row of data in each
> > > partition?*/
> > >
> > >
> > >
> > > CREATE TABLE [dbo].[PartitionTestArchive](
> > >
> > > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> > >
> > > [salary] [int] NOT NULL,
> > >
> > > CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> > >
> > > (
> > >
> > > [PTPK] ASC
> > >
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> > >
> > > ) ON [myRangePS2]([salary])
> > >
> > >
> > >
> > > /*Now I want to move all the data (value 9999) in partition 4 into my
> new
> > > ParitionTestArchive table:*/
> > >
> > >
> > >
> > > alter table PartitionTest
> > >
> > > switch partition 4 to [PartitionTestArchive] partition 4
> > >
> > >
> > >
> > > /*But this did nothing. So I try:*/
> > >
> > >
> > >
> > > alter table PartitionTest
> > >
> > > switch partition 3 to [PartitionTestArchive] partition 3
> > >
> > >
> > >
> > > /*And that did nothing either. So I try:*/
> > >
> > >
> > >
> > > alter table PartitionTest
> > >
> > > switch partition 2 to [PartitionTestArchive] partition 2
> > >
> > >
> > >
> > > /*And that moved every row of data with a value > 1 (99,999,9999) in
the
> > > table to PartitionTestArchive.*/
> > >
> > >
> > >
> > > Again, my goal was just to move the row of data with value 9999
> (partition
> > > 4) into PartitionTestArchive. So what am I not understanding? It seems
> > > that
> > > I either don't understand the concept, or data isn't going into the
> > > partition I think it should?
> > >
> > >
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
>|||Hi Chris
Inline is your script corrected to show it working. You may want to look at
and try the SQL Server samples for partitioning and sliding window
http://msdn2.microsoft.com/en-us/library/ms160726.aspx
CREATE DATABASE TESTPARTITION
GO
USE TESTPARTITION
GO
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000, 10000)
GO
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL ,
[salary] [int] NOT NULL CONSTRAINT [PK_PartitionTest] PRIMARY KEY
CLUSTERED
) ON [myRangePS2]([salary])
GO
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
GO
/* Show partitions */
SELECT 1 AS Value, $PARTITION.myRangePF2(1) As Partition
UNION ALL SELECT 99, $PARTITION.myRangePF2(99)
UNION ALL SELECT 999, $PARTITION.myRangePF2(999)
UNION ALL SELECT 9999, $PARTITION.myRangePF2(9999)
GO
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL CONSTRAINT [PK_PartitionTestArchive] PRIMARY
KEY CLUSTERED
) ON [myRangePS2]([salary])
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 1 to [PartitionTestArchive] partition 1
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
John
implemented the suggestion and am still experiencing the same behavior. I'm
sorry to to keep posting partioning questions here, but the concept has
sparked a huge interest at my work and I need to answer lots of questions.
Therefore, Im doing lots of different tests/ secenarios and it seems like
each answer brings up more questions. Anyways, below is the DDL and DML,
with explanations of what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000, 10000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
PTPK ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([salary])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([salary])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
The issue here is that "ON [myRangePS2]([salary])" is not used because of
the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
clustered index determines the partitioning of data so all data is
partitioned on PTPK instead of salary as you intended.
If your objective is to use SWITCH as a means to quickly archive data,
you'll want to align the table and index partitions on a date value. See
aligned indexes in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Thanks to David Browne for pointing out my mistake yesterday. But I have
> implemented the suggestion and am still experiencing the same behavior.
> I'm
> sorry to to keep posting partioning questions here, but the concept has
> sparked a huge interest at my work and I need to answer lots of questions.
> Therefore, Im doing lots of different tests/ secenarios and it seems like
> each answer brings up more questions. Anyways, below is the DDL and DML,
> with explanations of what Im trying to accomplish and where my confusion
> is.
>
>
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> 100, 1000, 10000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
> CREATE TABLE [dbo].[PartitionTestArchive](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> /*Now I want to move all the data (value 9999) in partition 4 into my new
> ParitionTestArchive table:*/
>
> alter table PartitionTest
> switch partition 4 to [PartitionTestArchive] partition 4
>
> /*But this did nothing. So I try:*/
>
> alter table PartitionTest
> switch partition 3 to [PartitionTestArchive] partition 3
>
> /*And that did nothing either. So I try:*/
>
> alter table PartitionTest
> switch partition 2 to [PartitionTestArchive] partition 2
>
> /*And that moved every row of data with a value > 1 (99,999,9999) in the
> table to PartitionTestArchive.*/
>
> Again, my goal was just to move the row of data with value 9999 (partition
> 4) into PartitionTestArchive. So what am I not understanding? It seems
> that
> I either don't understand the concept, or data isn't going into the
> partition I think it should?
>
> TIA, ChrisR
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
The issue here is that "ON [myRangePS2]([salary])" is not used because of
the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
clustered index determines the partitioning of data so all data is
partitioned on PTPK instead of salary as you intended.
If your objective is to use SWITCH as a means to quickly archive data,
you'll want to align the table and index partitions on a date value. See
aligned indexes in the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Thanks to David Browne for pointing out my mistake yesterday. But I have
> implemented the suggestion and am still experiencing the same behavior.
> I'm
> sorry to to keep posting partioning questions here, but the concept has
> sparked a huge interest at my work and I need to answer lots of questions.
> Therefore, Im doing lots of different tests/ secenarios and it seems like
> each answer brings up more questions. Anyways, below is the DDL and DML,
> with explanations of what Im trying to accomplish and where my confusion
> is.
>
>
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> 100, 1000, 10000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> PTPK ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
> CREATE TABLE [dbo].[PartitionTestArchive](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([salary])
>
> /*Now I want to move all the data (value 9999) in partition 4 into my new
> ParitionTestArchive table:*/
>
> alter table PartitionTest
> switch partition 4 to [PartitionTestArchive] partition 4
>
> /*But this did nothing. So I try:*/
>
> alter table PartitionTest
> switch partition 3 to [PartitionTestArchive] partition 3
>
> /*And that did nothing either. So I try:*/
>
> alter table PartitionTest
> switch partition 2 to [PartitionTestArchive] partition 2
>
> /*And that moved every row of data with a value > 1 (99,999,9999) in the
> table to PartitionTestArchive.*/
>
> Again, my goal was just to move the row of data with value 9999 (partition
> 4) into PartitionTestArchive. So what am I not understanding? It seems
> that
> I either don't understand the concept, or data isn't going into the
> partition I think it should?
>
> TIA, ChrisR
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||For clarification, are you saying that the placement of the Primary Key
"overrides" where I had placed the partitioning?
Also, if that's the case, and I want to quickly archive data as you
mentioned, then wouldn't I need to have my PK's on the date column (provided
thats the column I wanted to SWITCH, which of course it most likely would
be)?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:7B86860D-747C-4D63-99BE-18C6EA3CF498@.microsoft.com...
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > PTPK ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([salary])
> The issue here is that "ON [myRangePS2]([salary])" is not used because of
> the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
> clustered index determines the partitioning of data so all data is
> partitioned on PTPK instead of salary as you intended.
> If your objective is to use SWITCH as a means to quickly archive data,
> you'll want to align the table and index partitions on a date value. See
> aligned indexes in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> > Thanks to David Browne for pointing out my mistake yesterday. But I have
> > implemented the suggestion and am still experiencing the same behavior.
> > I'm
> > sorry to to keep posting partioning questions here, but the concept has
> > sparked a huge interest at my work and I need to answer lots of
questions.
> > Therefore, Im doing lots of different tests/ secenarios and it seems
like
> > each answer brings up more questions. Anyways, below is the DDL and DML,
> > with explanations of what Im trying to accomplish and where my confusion
> > is.
> >
> >
> >
> >
> > USE [AdventureWorks]
> >
> > GO
> >
> > /****** Object: PartitionFunction [myRangePF2] Script Date:
11/17/2006
> > 15:01:28 ******/
> >
> > CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> > 100, 1000, 10000)
> >
> >
> >
> > /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> > 15:10:35 ******/
> >
> > CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> > ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > PTPK ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([salary])
> >
> >
> >
> > insert into PartitionTest (salary) values (1)
> >
> > insert into PartitionTest (salary) values (99)
> >
> > insert into PartitionTest (salary) values (999)
> >
> > insert into PartitionTest (salary) values (9999)
> >
> >
> >
> > /*
> >
> > From BOL:
> >
> > Partition 1 2 3 4
> >
> > Values
> >
> > col1 <= 1
> >
> > col1 > 1 AND col1 <= 100
> >
> > col1 > 100 AND col1 <= 1000
> >
> > col1 > 1000
> >
> >
> >
> > Now if I understand correctly, there should be 1 row of data in each
> > partition?*/
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTestArchive](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > [PTPK] ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([salary])
> >
> >
> >
> > /*Now I want to move all the data (value 9999) in partition 4 into my
new
> > ParitionTestArchive table:*/
> >
> >
> >
> > alter table PartitionTest
> >
> > switch partition 4 to [PartitionTestArchive] partition 4
> >
> >
> >
> > /*But this did nothing. So I try:*/
> >
> >
> >
> > alter table PartitionTest
> >
> > switch partition 3 to [PartitionTestArchive] partition 3
> >
> >
> >
> > /*And that did nothing either. So I try:*/
> >
> >
> >
> > alter table PartitionTest
> >
> > switch partition 2 to [PartitionTestArchive] partition 2
> >
> >
> >
> > /*And that moved every row of data with a value > 1 (99,999,9999) in the
> > table to PartitionTestArchive.*/
> >
> >
> >
> > Again, my goal was just to move the row of data with value 9999
(partition
> > 4) into PartitionTestArchive. So what am I not understanding? It seems
> > that
> > I either don't understand the concept, or data isn't going into the
> > partition I think it should?
> >
> >
> >
> > TIA, ChrisR
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>|||"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:O8aM%23m2CHHA.1224@.TK2MSFTNGP04.phx.gbl...
> For clarification, are you saying that the placement of the Primary Key
> "overrides" where I had placed the partitioning?
> Also, if that's the case, and I want to quickly archive data as you
> mentioned, then wouldn't I need to have my PK's on the date column
(provided
> thats the column I wanted to SWITCH, which of course it most likely would
> be)?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7B86860D-747C-4D63-99BE-18C6EA3CF498@.microsoft.com...
> > > CREATE TABLE [dbo].[PartitionTest](
> > >
> > > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> > >
> > > [salary] [int] NOT NULL,
> > >
> > > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> > >
> > > (
> > >
> > > PTPK ASC
> > >
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> > >
> > > ) ON [myRangePS2]([salary])
> >
> > The issue here is that "ON [myRangePS2]([salary])" is not used because
of
> > the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
> > clustered index determines the partitioning of data so all data is
> > partitioned on PTPK instead of salary as you intended.
> >
> > If your objective is to use SWITCH as a means to quickly archive data,
> > you'll want to align the table and index partitions on a date value.
See
> > aligned indexes in the Books Online for more information.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> > news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> > > Thanks to David Browne for pointing out my mistake yesterday. But I
have
> > > implemented the suggestion and am still experiencing the same
behavior.
> > > I'm
> > > sorry to to keep posting partioning questions here, but the concept
has
> > > sparked a huge interest at my work and I need to answer lots of
> questions.
> > > Therefore, Im doing lots of different tests/ secenarios and it seems
> like
> > > each answer brings up more questions. Anyways, below is the DDL and
DML,
> > > with explanations of what Im trying to accomplish and where my
confusion
> > > is.
> > >
> > >
> > >
> > >
> > > USE [AdventureWorks]
> > >
> > > GO
> > >
> > > /****** Object: PartitionFunction [myRangePF2] Script Date:
> 11/17/2006
> > > 15:01:28 ******/
> > >
> > > CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES
(1,
> > > 100, 1000, 10000)
> > >
> > >
> > >
> > > /****** Object: PartitionScheme [myRangePS2] Script Date:
11/17/2006
> > > 15:10:35 ******/
> > >
> > > CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> > > ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [primary])
> > >
> > >
> > >
> > > CREATE TABLE [dbo].[PartitionTest](
> > >
> > > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> > >
> > > [salary] [int] NOT NULL,
> > >
> > > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> > >
> > > (
> > >
> > > PTPK ASC
> > >
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> > >
> > > ) ON [myRangePS2]([salary])
> > >
> > >
> > >
> > > insert into PartitionTest (salary) values (1)
> > >
> > > insert into PartitionTest (salary) values (99)
> > >
> > > insert into PartitionTest (salary) values (999)
> > >
> > > insert into PartitionTest (salary) values (9999)
> > >
> > >
> > >
> > > /*
> > >
> > > From BOL:
> > >
> > > Partition 1 2 3 4
> > >
> > > Values
> > >
> > > col1 <= 1
> > >
> > > col1 > 1 AND col1 <= 100
> > >
> > > col1 > 100 AND col1 <= 1000
> > >
> > > col1 > 1000
> > >
> > >
> > >
> > > Now if I understand correctly, there should be 1 row of data in each
> > > partition?*/
> > >
> > >
> > >
> > > CREATE TABLE [dbo].[PartitionTestArchive](
> > >
> > > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> > >
> > > [salary] [int] NOT NULL,
> > >
> > > CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
> > >
> > > (
> > >
> > > [PTPK] ASC
> > >
> > > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> > >
> > > ) ON [myRangePS2]([salary])
> > >
> > >
> > >
> > > /*Now I want to move all the data (value 9999) in partition 4 into my
> new
> > > ParitionTestArchive table:*/
> > >
> > >
> > >
> > > alter table PartitionTest
> > >
> > > switch partition 4 to [PartitionTestArchive] partition 4
> > >
> > >
> > >
> > > /*But this did nothing. So I try:*/
> > >
> > >
> > >
> > > alter table PartitionTest
> > >
> > > switch partition 3 to [PartitionTestArchive] partition 3
> > >
> > >
> > >
> > > /*And that did nothing either. So I try:*/
> > >
> > >
> > >
> > > alter table PartitionTest
> > >
> > > switch partition 2 to [PartitionTestArchive] partition 2
> > >
> > >
> > >
> > > /*And that moved every row of data with a value > 1 (99,999,9999) in
the
> > > table to PartitionTestArchive.*/
> > >
> > >
> > >
> > > Again, my goal was just to move the row of data with value 9999
> (partition
> > > 4) into PartitionTestArchive. So what am I not understanding? It seems
> > > that
> > > I either don't understand the concept, or data isn't going into the
> > > partition I think it should?
> > >
> > >
> > >
> > > TIA, ChrisR
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
>|||Hi Chris
Inline is your script corrected to show it working. You may want to look at
and try the SQL Server samples for partitioning and sliding window
http://msdn2.microsoft.com/en-us/library/ms160726.aspx
CREATE DATABASE TESTPARTITION
GO
USE TESTPARTITION
GO
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000, 10000)
GO
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL ,
[salary] [int] NOT NULL CONSTRAINT [PK_PartitionTest] PRIMARY KEY
CLUSTERED
) ON [myRangePS2]([salary])
GO
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
GO
/* Show partitions */
SELECT 1 AS Value, $PARTITION.myRangePF2(1) As Partition
UNION ALL SELECT 99, $PARTITION.myRangePF2(99)
UNION ALL SELECT 999, $PARTITION.myRangePF2(999)
UNION ALL SELECT 9999, $PARTITION.myRangePF2(9999)
GO
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL CONSTRAINT [PK_PartitionTestArchive] PRIMARY
KEY CLUSTERED
) ON [myRangePS2]([salary])
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 1 to [PartitionTestArchive] partition 1
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
John
Tuesday, February 21, 2012
ReportViewer with Authentication
I'm new to Reporting Services but have built some reports and have implemented them. I have used the report viewer which works great and has a great tool bar that users really like. One problem, I can't figure out how to pass network credentials using the viewer.
If I use the ReportingService class I can pass the credentials and everything is great except that I lose the nice tool bar.
I want the best of both worlds without having th build my own toolbar. This must have been done before but I can't seem to find any samples.
So is there a way to pass credentials (Basic Authentication over SSL) while still using the nivce features from the ReportViewer.
Or
Is there a different ReportViewer that has this capability.
Any help will be greatly appreciated.
ThanksDoes anyone have any info on this? Does a ReportViewer control with the ability to pass credentials exist or do I need to create my own?
Thanks,
|||the short answer is "yes".
the devil is in the details...
are you using rs 2000 or 2005 ?
if 2005 I am also trying to get the details down.
I did some work with rs 2000 and see that a lot is the same but the winforms control is new and I hear they "made things better" but so far found few samples of how to get from "A" to "B".
I do know that there are two calls that deal with auth, one to pass data-source credentials and one to pass in "access" credentials.
the access bits seem to have several options.
when I was doing some hacking throuhg they did not seem to work.
I think that was due to messing with the report server and hosing it's settings.
right now I am building a test-lab with a local server and a Virtual PC to run my client app.
I hope I can get a sample working and share....
|||I'm using RS 2000. We may go to 2005 in the future but the immediate need is for 2000.
|||
mcatet wrote:
I'm using RS 2000. We may go to 2005 in the future but the immediate need is for 2000.
Ok, a few things:
1) you want to use *WINDOWS* credentails
2) look at the docs and as I recall you will see that you needs to create an object and then attach it to your calls.... System.Net.NetworkCredentails
you can create one with user,pass,domain and then pass that to the web services at key points.
I am working 2005 now so I'm not readig the 2000 docs right now but I think that the above will start you in the right direction....|||Ok. So with windows credentials setup on the ReportServer web folder, I just need a windows domain account on the server (LocalServerDomain\RSExec). Then pass those credentials.
I have that part working using the ReportingServices class.
What would be really nice is if I could use the Microsoft ReportViewer control for my reports (because I like the appearance of it) while still passing the credentils to it. The ReprotingServices class has a viewer toolbar but it doesn't look as nice.
Is this possible, and if so, do you have sample code I could look at?
Thanks for your help.
|||Just keeping this up front so people see it.
Anyone else have an additional info pertaining to an RS2000 ReportViewer with authentication being passed?
Thanks,
|||I am using RS2005.
IF i call ReportingService2005 web service, i know how i can use windows credentails. for this i use NetworkCredentails. However, i couldnt find out how i can set reportviewer credentails to windows credentails.
Could u help me?|||
Looks like this has been sitting around for a little while:
for SSL - set the URL of the ReportViewer control to start with https.
for specifying the credentials to use, you should use the IReportServerCredentials Interface
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.ireportservercredentials.aspx
Hope that helps,
-Lukasz
ReportViewer with Authentication
I'm new to Reporting Services but have built some reports and have implemented them. I have used the report viewer which works great and has a great tool bar that users really like. One problem, I can't figure out how to pass network credentials using the viewer.
If I use the ReportingService class I can pass the credentials and everything is great except that I lose the nice tool bar.
I want the best of both worlds without having th build my own toolbar. This must have been done before but I can't seem to find any samples.
So is there a way to pass credentials (Basic Authentication over SSL) while still using the nivce features from the ReportViewer.
Or
Is there a different ReportViewer that has this capability.
Any help will be greatly appreciated.
ThanksDoes anyone have any info on this? Does a ReportViewer control with the ability to pass credentials exist or do I need to create my own?
Thanks,
|||the short answer is "yes".
the devil is in the details...
are you using rs 2000 or 2005 ?
if 2005 I am also trying to get the details down.
I did some work with rs 2000 and see that a lot is the same but the winforms control is new and I hear they "made things better" but so far found few samples of how to get from "A" to "B".
I do know that there are two calls that deal with auth, one to pass data-source credentials and one to pass in "access" credentials.
the access bits seem to have several options.
when I was doing some hacking throuhg they did not seem to work.
I think that was due to messing with the report server and hosing it's settings.
right now I am building a test-lab with a local server and a Virtual PC to run my client app.
I hope I can get a sample working and share....
|||I'm using RS 2000. We may go to 2005 in the future but the immediate need is for 2000.
|||
I'm using RS 2000. We may go to 2005 in the future but the immediate need is for 2000.
Ok, a few things:
1) you want to use *WINDOWS* credentails
2) look at the docs and as I recall you will see that you needs to create an object and then attach it to your calls.... System.Net.NetworkCredentails
you can create one with user,pass,domain and then pass that to the web services at key points.
I am working 2005 now so I'm not readig the 2000 docs right now but I think that the above will start you in the right direction....|||Ok. So with windows credentials setup on the ReportServer web folder, I just need a windows domain account on the server (LocalServerDomain\RSExec). Then pass those credentials.
I have that part working using the ReportingServices class.
What would be really nice is if I could use the Microsoft ReportViewer control for my reports (because I like the appearance of it) while still passing the credentils to it. The ReprotingServices class has a viewer toolbar but it doesn't look as nice.
Is this possible, and if so, do you have sample code I could look at?
Thanks for your help.
|||Just keeping this up front so people see it.
Anyone else have an additional info pertaining to an RS2000 ReportViewer with authentication being passed?
Thanks,
|||I am using RS2005.
IF i call ReportingService2005 web service, i know how i can use windows credentails. for this i use NetworkCredentails. However, i couldnt find out how i can set reportviewer credentails to windows credentails.
Could u help me?|||
Looks like this has been sitting around for a little while:
for SSL - set the URL of the ReportViewer control to start with https.
for specifying the credentials to use, you should use the IReportServerCredentials Interface
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.ireportservercredentials.aspx
Hope that helps,
-Lukasz
ReportViewer with Authentication
I'm new to Reporting Services but have built some reports and have implemented them. I have used the report viewer which works great and has a great tool bar that users really like. One problem, I can't figure out how to pass network credentials using the viewer.
If I use the ReportingService class I can pass the credentials and everything is great except that I lose the nice tool bar.
I want the best of both worlds without having th build my own toolbar. This must have been done before but I can't seem to find any samples.
So is there a way to pass credentials (Basic Authentication over SSL) while still using the nivce features from the ReportViewer.
Or
Is there a different ReportViewer that has this capability.
Any help will be greatly appreciated.
ThanksDoes anyone have any info on this? Does a ReportViewer control with the ability to pass credentials exist or do I need to create my own?
Thanks,
|||the short answer is "yes".
the devil is in the details...
are you using rs 2000 or 2005 ?
if 2005 I am also trying to get the details down.
I did some work with rs 2000 and see that a lot is the same but the winforms control is new and I hear they "made things better" but so far found few samples of how to get from "A" to "B".
I do know that there are two calls that deal with auth, one to pass data-source credentials and one to pass in "access" credentials.
the access bits seem to have several options.
when I was doing some hacking throuhg they did not seem to work.
I think that was due to messing with the report server and hosing it's settings.
right now I am building a test-lab with a local server and a Virtual PC to run my client app.
I hope I can get a sample working and share....
|||I'm using RS 2000. We may go to 2005 in the future but the immediate need is for 2000.
|||
I'm using RS 2000. We may go to 2005 in the future but the immediate need is for 2000.
Ok, a few things:
1) you want to use *WINDOWS* credentails
2) look at the docs and as I recall you will see that you needs to create an object and then attach it to your calls.... System.Net.NetworkCredentails
you can create one with user,pass,domain and then pass that to the web services at key points.
I am working 2005 now so I'm not readig the 2000 docs right now but I think that the above will start you in the right direction....|||Ok. So with windows credentials setup on the ReportServer web folder, I just need a windows domain account on the server (LocalServerDomain\RSExec). Then pass those credentials.
I have that part working using the ReportingServices class.
What would be really nice is if I could use the Microsoft ReportViewer control for my reports (because I like the appearance of it) while still passing the credentils to it. The ReprotingServices class has a viewer toolbar but it doesn't look as nice.
Is this possible, and if so, do you have sample code I could look at?
Thanks for your help.
|||Just keeping this up front so people see it.
Anyone else have an additional info pertaining to an RS2000 ReportViewer with authentication being passed?
Thanks,
|||I am using RS2005.
IF i call ReportingService2005 web service, i know how i can use windows credentails. for this i use NetworkCredentails. However, i couldnt find out how i can set reportviewer credentails to windows credentails.
Could u help me?|||
Looks like this has been sitting around for a little while:
for SSL - set the URL of the ReportViewer control to start with https.
for specifying the credentials to use, you should use the IReportServerCredentials Interface
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.ireportservercredentials.aspx
Hope that helps,
-Lukasz
Subscribe to:
Posts (Atom)