T-SQL (SS2K8) :: Use Variable In ALTER / CREATE PARTITION SCHEME Command For Filegroup?

May 9, 2010

Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?

I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.

My code:

declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName

Is failing:

Incorrect syntax near '@fileGroupName'.

Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?

Q: If using a variable is not possible, is there another way to specify the default filegroup?

View 2 Replies


ADVERTISEMENT

SQL 2012 :: How To Add New Filegroup To Existing Partition Scheme

Jul 10, 2014

How to add some more ranges to existing partition schema and function?

Already My table partitioned on date ranges,

6 partitions , each partition contains 6 moths data, so total data is 3 years.

i.e. 1 partition data- from jan2012 to Jun2012
2 partition data- from july2012 to dec2012
3 partition data- from jan2013 to Jun2013
4 partition data- from july2013 to dec2013
5 partition data- from jan2014 to Jun2014
6 partition data- from july2014 to dec2014
After Jan2015 data will go to Primary file group(Default)

Now customer wants to add two more file groups with these partitions ranges, i.e. jan2015 to jun15 and Jul15 to dec15.

File group and ndf file adding is OK, But

how to alter partition scheme and partition function with these additional ranges to existing partition function and scheme?

partitioned table size is 200 GB.

View 1 Replies View Related

Partition Scheme Using A Function

Feb 20, 2007

I can't seem to find a way to do the following:create table part_table (col1 int,col2 datetime) on psX (datename(week,col2))I want to partition based on the week number of a date field.So if I enter in data like the following in my part_table:(1, 1/1/2007) should go into partition 1 for week #1(52, 12/21/2007) should go into partition 52 for week #52 of the yearI tried adding in a computed column, but it says its nondeterministic.

View 4 Replies View Related

T-SQL For Add Existing Table To The Partition Scheme

Sep 4, 2007



Hi ,

I had a table which is going to burst, and of course performance issue is come in to place. and now we thinking to apply to partition method into this table.

So is that possible to create a partition scheme and against the existing table? and how is the T-SQL statement will be look like.

Thanks for anyone for giving some clue...

View 3 Replies View Related

T-SQL (SS2K8) :: HOW To Create Filestream For Filegroup For A DB

Dec 5, 2014

i have a DB in SQL Server 2008. and i wants to store file in DB.how can i create filestream for filegroup for a DB in SQL Server 2008

View 1 Replies View Related

How To Attach The Existing Table To New Partition Function/scheme?

May 26, 2008

Normally we create a new table for tabler partitioning, like:




Code Snippet
CREATE TABLE [dbo].[Sales]
(
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[SalesDate] [datetime] NOT NULL,
[col_01] varchar(50) NULL,
[col_02] varchar(50) NULL,
..
) ON [ps_Sales](SalesDate)
GO







with




Code SnippetCREATE PARTITION SCHEME [ps_Sales]
AS
PARTITION PFN_Sales TO ([FG_20080501], [FG_20080516], [FG_20080601], [FG_20080616],
[FG_20080701], [FG_20080716], [FG_20080801], [FG_20080816],
[FG_20080901], [FG_20080916], [FG_20081001], [FG_20081016])






But what if I have [dbo].[Sales] existed in db, that have attached to [PRIMARY]?

Alter table?

or I need to create a temp table first?

View 1 Replies View Related

Transact SQL :: Apply Partition Scheme To Existing Table In Query Windows

Jun 9, 2015

I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)

BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION

View 2 Replies View Related

T-SQL (SS2K8) :: MASTER And MSDB - Defrag Using Standard Alter Index Command?

Oct 10, 2014

On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.

Is it ok to defrag them using the standard Alter Index command?

View 7 Replies View Related

Find How Much Of Partition Filegroup Contains Data?

Oct 7, 2015

I have partitions that I have filled with data. I am not trying to figure out exactly how much data the partitions contain, and therefore I will be able to see if any of them are close to hitting their autogrow conditions. If I were looking at a single unpartitioned table, then I could maybe look at the table properties to determine data and index sizes, and compare that to the size of the mdf file size, but for partitions, then I am not sure how I would query this information out. Any pointers on how this information could be queried out of the system?

View 3 Replies View Related

How To Change An Existing Table From A Filegroup To A Partition

May 21, 2008

I've create a partition function and a partion scheme for my database.
Now I would like to change an existing table to use these partition.
The table is replicated.
How can I do this?


Thanks
Markus

View 2 Replies View Related

Query For Determining Which Partition Belongs To Which Filegroup?

Oct 13, 2006



Dear guys,

Is there a way to determine which partition belongs to which Filegroup name?

Nabeel

View 1 Replies View Related

Scheme Login Can't Access Tables Owned By Scheme

Dec 4, 2007

Hi,

I've having some baffling problems with a java applicaiton!

I have an application server called WA01 which used to access two tables on an MS SQL 2000 server via a scheme login. The two tables are:

_PurchaseOrderInterface
_PurchaseInvoiceInterface

Both tables were owned by the scheme user, with enough permissions to read & write. The java application on server WA01 could happily read the data within the tables and write a bit flag back to each row.

The MS database has been moved to a new server which no longer allows the java application on server WA01 to access the tables via the scheme login, the only way the java app can view and update the tables is by changing the owner of the tables to dbo. The new server is still MS SQL 2000, with comparible security settings.

The java app keeps complaing of an unknown source when trying to access via scheme, is this a domain trust issue between the two servers? Any ideas would be welcomed. I'm not an SQL expert but have a good grasp of the security structure etc.

Regards,

Alan

View 1 Replies View Related

SQL Server Admin 2014 :: Adding New Partition To Filegroup

Nov 28, 2014

I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?

View 4 Replies View Related

DB Design :: Alter Partition Function In Data Vault Where There Is 4 Table Dependency?

Aug 28, 2015

I'm currently stuck with a table that has 350 mil records. Querying this table is insanely slow so I had a better look at existing yearly partitioning. I already managed to partition on a month level which increased the performance/querrying a lot. I did this on the staging table where I used an alter statement to split the 2015 partition by 12 months.

However, in our project we used Data Vault. This means that we have 4 tables (hub, sathub, link, satlink), all carrying 350 mil records. The problem is that altering the partition function does not work. The server cannot handle this action. What the best way is to do this, without having to drop/reload all tables.

View 17 Replies View Related

T-SQL (SS2K8) :: Partition And Order Records

Oct 8, 2014

I have the below requirement:

1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.

docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: Number Of Rows And Range For Each Partition In A Table?

Mar 13, 2015

Is it possible to show the number of rows and the range for each partition in a table ?

This shows me the range but not the row count per partition

SELECT sprv.value AS [Value], sprv.boundary_id AS [ID] FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv
ON sprv.function_id=spf.function_id
WHERE (spf.name=N'myDateRangePF')
ORDER BY [ID] ASC

View 4 Replies View Related

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

T-SQL (SS2K8) :: Updating Existing Table With Max (value) And Row Number (partition By 2 Columns)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008

View 2 Replies View Related

Need Help With The ALTER Command

Dec 16, 2005

Hello everyone,
I just started learning sql and I came across a slight problem.
Assume I have a customer table composed of the following fields:
Customer_Number,Sname,Street,City,Postcode,credit_lim,balance.

What I want to do is to drop the Street,city,and postcode fields and add the following: fax,email,and phone.

Is it possible to use the following command:

Alter table customer
Drop column street,city,postcode

Or should I do as following:

Alter table customer
Drop column street

Alter table customer
Drop column city

Alter table customer
Drop column postcode

And the same goes to adding the phone,fax,and email field.
Thank You.

NB: I need to use the Alter command and nothing else

View 10 Replies View Related

ALTER Table Command

Jul 20, 2005

Hi,I'm trying to run the ALTER TABLE command using a dynamic string for thetable, like so:DECLARE @TableName CHARSET @TableName = 'Customers'ALTER TABLE @TableNameADD ...blahIs this possible? We know this works:ALTER TABLE Customers ADD ...blahIt looks like I need a way to convert the CHAR value to a literal or perhapseven a table ID?Thanks in advance,Paul

View 3 Replies View Related

Help Me Please I Have A Problem In ALTER Command

Sep 3, 2007



Dear all
I need to add new column in table TranPay , I use ALTER command on PDA on Button Click




Dim SqlStm = "ALTER TABLE TranPay ALTER COLUMN TranKey IDENTITY (1, 1)"

Dim Dcmd As SqlCeCommand = New SqlCeCommand

Dcmd.Connection = Conn

Dcmd.CommandText = SqlStm

Dcmd.ExecuteNonQuery()


But cannot create new Field , Help me please

Brg ,

Tingnong

View 3 Replies View Related

ALTER TABLE SWITCH PARTITION On A Replicated Table

Apr 10, 2007

A customer wants to implement table partitioning on a replicated table.



They want to hold 13 months of data in the table and roll off the earliest/oldest month to an identical archive table. The table has a date field and partitioning by month makes sense all around.



So SWITCH PARTITION is the obvious solution to this, except for the fact that the table is replicated (transactional w/no subscriber updates).



What are his architectural or practical solutions to using table partitioning and replication?











thx

View 5 Replies View Related

How To Create New Partition On A Cube

Jan 12, 2012

I need to create a new partition on a Cube using T-SQL and I am not much aware of either the Cubes or the ActiveX script. I am writing a T-SQL script for creating this partition on a cube.

Select Case iMonth
Case 1,2,3
sQuarter = "1"
Case 4,5,6
sQuarter = "2"
Case 7,8,9
sQuarter = "3"
Case 10,11,12

[Code] .....

View 2 Replies View Related

Adding The Column By Using The Alter Command.

Jun 1, 2008

ALTER TABLE customers
add column active_flg int(1)

but i got error.

quote:Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'column'.

what should i do?

ps. thanks for warning... i won't edit the post after i got the answer anymore.

View 6 Replies View Related

Add A Column Trough Command (ALTER)

Sep 12, 2013

I'm trying to add a column trough command "ALTER" and I've tried in many different ways.

Here is the result of statement..

View 1 Replies View Related

Extend Alter Procedure Command

Jun 27, 2007

Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?

Thanks,

David

View 9 Replies View Related

Can I Create A Filegroup On Existnig Table

Feb 22, 2001

Can I create a filegroup on existnig table?If yes,How?
TIA

View 3 Replies View Related

Simple ALTER Command Setting DEFAULT Help

Apr 21, 2008

Hi all, I am trying to do a very basic ALTER Command and am trying to change its DEFAULT value. Code below is what I currently have:





Code Snippet

ALTER TABLE Table_1

ALTER COLUMN TEST VARCHAR(1000) NULL DEFAULT 2

Thanks, Onam.

*UPDATE* I found this code but are there alternative methods? Additionally, if I was to update its DEFAULT value again how would I go about doing that? Do I first have to remove the CONSTRAINT and then run the command?





Code Snippet

ALTER TABLE Table_1 ADD CONSTRAINT DF_Albumns_Comment DEFAULT 2 FOR TEST

View 8 Replies View Related

SQL Server 2012 :: Create Filestream For Filegroup For A DB?

Dec 5, 2014

i have a DB in SQL Server 2008. and i wants to store file in DB.

how can i create filestream for filegroup for a DB in SQL Server 2008.

View 1 Replies View Related

Should I Create A Non-primary Default Filegroup As A Best Practice?

Nov 7, 2007

This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.


Thanks for your input,
Mike
MCDBA

View 4 Replies View Related

SQL 2012 :: Alter Database Set Partner Command Failed

Jun 23, 2014

Running this query in DR server to start SQL mirroring but encountered an error below.

Query:
use master
go
alter database test set partner= N'TCP://HOSTNAME.DOMAIN.GROUP.INTRANET:5023'
go

Error:
Msg 1452, Level 16, State 6, Line 2

The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.

View 2 Replies View Related

SQL Server 2008 :: Create Partition On Existing Table?

Mar 4, 2015

Can we create the Partition on Existing Table?e.g Create table t ( col1 number(10,0), Col2 Varchar(10)) ;After the table Creation can we alter the table to partition the table.

View 2 Replies View Related

SQL 2005 Won't Start After Moving Model Database With Alter DB Command

Sep 11, 2007

I used the code below to move the Model database and Model log file to new SAN drive locations.
After running the commands I stopped the SQL server and moved the physical files to the correct location.
Now I am unable to start SQL server, I get error 17204 Could not open file:Access is denied

I think the problem maybe that for FILENAME new_path I put "J:SQL ServerMSSQL.1MSSQLData"
but did not include model.mdf in the file path.

How do I correct the path now that SQL service won't start?
I have included the log file below.



ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )


2007-09-11 13:19:31.82 Server Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-09-11 13:19:31.82 Server (c) 2005 Microsoft Corporation.
2007-09-11 13:19:31.82 Server All rights reserved.
2007-09-11 13:19:31.82 Server Server process ID is 3264.
2007-09-11 13:19:31.82 Server Authentication mode is MIXED.
2007-09-11 13:19:31.82 Server Logging SQL Server messages in file 'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2007-09-11 13:19:31.82 Server This instance of SQL Server last reported using a process ID of 2648 at 9/11/2007 1:10:02 PM (local) 9/11/2007 8:10:02 PM (UTC). This is an informational message only; no user action is required.
2007-09-11 13:19:31.82 Server Registry startup parameters:
2007-09-11 13:19:31.82 Server -d D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2007-09-11 13:19:31.82 Server -e D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2007-09-11 13:19:31.82 Server -l D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2007-09-11 13:19:31.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-09-11 13:19:31.84 Server Detected 4 CPUs. This is an informational message; no user action is required.
2007-09-11 13:19:31.96 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-09-11 13:19:31.98 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Database mirroring has been enabled on this instance of SQL Server.
2007-09-11 13:19:32.00 spid5s Starting up database 'master'.
2007-09-11 13:19:32.09 spid5s SQL Trace ID 1 was started by login "sa".
2007-09-11 13:19:32.10 spid5s Starting up database 'mssqlsystemresource'.
2007-09-11 13:19:32.10 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid5s Server name is 'SS02'. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid9s Starting up database 'model'.
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'J:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file J:SQL ServerMSSQL.1MSSQLData for file number 1. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "J:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'I:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file I:SQL ServerMSSQL.1MSSQLData for file number 2. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "I:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s File activation failure. The physical file name "I:SQL ServerMSSQL.1MSSQLData" may be incorrect.
2007-09-11 13:19:32.26 spid9s Error: 945, Severity: 14, State: 2.
2007-09-11 13:19:32.26 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2007-09-11 13:19:32.26 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2007-09-11 13:19:32.26 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved