Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegro ups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>sql

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name =
(SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Drop table's primary key with knowing the constraint's name

Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?Try:
select
CONSTRAINT_NAME
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where
1 in (objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsClustKey'),
objectproperty (object_id(CONSTRAINT_NAME), 'CnstIsNonclustKey'))
and TABLE_NAME = 't1'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
Drop table's primary key with knowing the constraint name.
Based on some logic, my program needs to create a new primary key.
The problem is when the primary key was created, it was not given a name.
SQL Server assigned a name it to it.
ALTER TABLE t1
ADD PRIMARY KEY (id, name)
go
Contraint name: PK__term__1FCDBCEB
So how can I drop it without knowing the name?|||"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:3EF84A74-A43D-4A7C-AC2A-1FB2FBC85B00@.microsoft.com...
> Drop table's primary key with knowing the constraint name.
> Based on some logic, my program needs to create a new primary key.
> The problem is when the primary key was created, it was not given a name.
> SQL Server assigned a name it to it.
>
> ALTER TABLE t1
> ADD PRIMARY KEY (id, name)
> go
> Contraint name: PK__term__1FCDBCEB
> So how can I drop it without knowing the name?
>
Like this for example:
DECLARE @.pk_name NVARCHAR(256);
SET @.pk_name = (SELECT QUOTENAME(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = 'dbo'
AND table_name = 'table_name') ;
EXEC sp_rename @.pk_name, 'pk_table_name', 'OBJECT' ;
ALTER TABLE table_name DROP CONSTRAINT pk_table_name ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Drop Down Box with edit/input by user

We have a report which is based on Schemes which have pre-defined
start and end dates. I have set up report parameters which are queried
and
the user can select these pre-determined values.. (The user choose a
"Scheme" and both dates are set).
However, there are occasions when the users want to enter a date other
than
one of these values.( by the date control). This I cant get RS to do?
How can I best achieve this switch from selection of a value in a drop
down
list to typing in a value in a text box. If the parameter is queried, a
drop
down list is displayed and the user cannot type in a value. There is no
option to use a "Combo" box and use the display value.
Any help appreciated.
Deepan (This question is takin from another question that was not
answered)I'm looking to do the same thing...
-Ben
Deepan wrote:
> We have a report which is based on Schemes which have pre-defined
> start and end dates. I have set up report parameters which are queried
> and
> the user can select these pre-determined values.. (The user choose a
> "Scheme" and both dates are set).
> However, there are occasions when the users want to enter a date other
> than
> one of these values.( by the date control). This I cant get RS to do?
>
> How can I best achieve this switch from selection of a value in a drop
> down
> list to typing in a value in a text box. If the parameter is queried, a
> drop
> down list is displayed and the user cannot type in a value. There is no
> option to use a "Combo" box and use the display value.
>
> Any help appreciated.
> Deepan (This question is takin from another question that was not
> answered)

DrillThrough Automation

Sorry for repeating myself... I'm still looking for a workaround here...
Based on a User's credentials, I perform a query within our company's Sales
Org to determine which Branches and Sales Reps are displayed in Parent
Report's Table.
Then, when the user clicks on a Sales Rep, a secondary Report gets
"DrillThrough" to (not a sub-report) for the Rep's details... So far, so good.
If however, the User is simply a Sale Rep, I want the Parent report to
automatically DrillThrough to second Report, passing of course, the known
necessary UserID info as the parameter.
Ideally, the Rep wouldn't even see the initial Parent Report.
So I guess my question is...
If a Report Parameter has a specific value, can a DrillThrough Report get
"launched" automatically,
e.g. without the user needing to "click" on the object which DrillThroughs to
the second report with the appropriate parameter ?
Many thanks in advance,Drill through works with action/click event, even if you use conditions still
it waits for the click. So I would suggest you need to create a seperate
report for sales reps , even if you create; it has a back button (arrow) so
that it will go to parent report, which you intend not to show it to the reps.
Amarnath
"Deano" wrote:
> Sorry for repeating myself... I'm still looking for a workaround here...
> Based on a User's credentials, I perform a query within our company's Sales
> Org to determine which Branches and Sales Reps are displayed in Parent
> Report's Table.
> Then, when the user clicks on a Sales Rep, a secondary Report gets
> "DrillThrough" to (not a sub-report) for the Rep's details... So far, so good.
> If however, the User is simply a Sale Rep, I want the Parent report to
> automatically DrillThrough to second Report, passing of course, the known
> necessary UserID info as the parameter.
> Ideally, the Rep wouldn't even see the initial Parent Report.
> So I guess my question is...
> If a Report Parameter has a specific value, can a DrillThrough Report get
> "launched" automatically,
> e.g. without the user needing to "click" on the object which DrillThroughs to
> the second report with the appropriate parameter ?
> Many thanks in advance,

Drill-down using "n" levels using a table?

How should i create a "n" levels drill-down report using a table based on MDX query?

I can only seem to use the visible or invisible based on a know number of levels ("columns in the table") what if i don't know that exact number? Or it is a unbalanced hierarchy?

I cannot use the Matrix because my columns are static and with two levels...

You can use show/hide recursive groups in a table based on parent child hierarchies.|||How do i use that option in SSRS 2005 ?