Reposition Subtotals: experiencing

Showing posts with label experiencing. Show all posts

Friday, March 30, 2012

Resolving deadlock

Hi All,
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBAHi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:
> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I have
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>

Resolving deadlock

Hi All,
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBA
Hi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:

> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I have
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>

Resolving deadlock

Hi All,
Currently I am experiencing a dead lock issue in one of enviornment. I have
identified the dead lock is of type "Deadlocks Involving Threads".
Does any one know how to resolve "Deadlocks Involving Threads".
Regards
Shri.DBAHi
You will need to determine what the threads are doing and resolve the
underlying problem.
Check out http://support.microsoft.com/kb/224453/
http://support.microsoft.com/kb/162361/
http://support.microsoft.com/kb/271509/
John
"Shri.DBA" wrote:

> Hi All,
> Currently I am experiencing a dead lock issue in one of enviornment. I hav
e
> identified the dead lock is of type "Deadlocks Involving Threads".
> Does any one know how to resolve "Deadlocks Involving Threads".
> Regards
> Shri.DBA
>
>sql

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

Tuesday, February 21, 2012

Reportviewer with Multivalue Parameter and FireFox

Hi,

I'm experiencing a problem with the multivalue parameter dropdown in ReportViewer control in FireFox. It works fine in IE. The problem is when I click on dropdown list with multivalue parameters, the list of options appears then quickly disappers before I can select anything. This problem occurs only for multivalue parameters. The dropdown is also grayed-out. Also, the dropdown options are not displayed in the correct position (it is pushed all the way to the left). If a navigate directly to the reportserver and view the report there it works fine so I'm guessing that the problem is with the Reportviewer control in Firefox.

Has anyone else come across this problem?

Thanks!

try deleting the line in the aspx file that sets the doctype to XHTML it worked for us, although did cause some other issues which we then had to rectify.

|||I tried deleting the DOCTYPE element but it causes other issues - the page stysheets and themes didn't render correctly. Also, the page with a reportviewer control is in a master page. :(

|||

we had the same issue and ended up creating a second master page for the reports so all of our other pages could keep the doc type line. As far as I'm aware its a bug with reporting services and this is the only workaround I've been able to find. To be honest the more I use reporting servies the more it feels like an unfinished product.

Friday, March 30, 2012

Resolving deadlock

Resolving deadlock

Resolving deadlock

Saturday, February 25, 2012

Repost: Data not being partitioned properly?

Repost: Data not being partitioned properly?

Repost: Data not being partitioned properly?

Tuesday, February 21, 2012

Reportviewer with Multivalue Parameter and FireFox

Reposition Subtotals

Blog Archive

About Me