SQL tips for database administrator RSS 2.0
# Monday, 09 March 2009

/*********************************************************************

The following script shows how to use table partition to archive history data (the first partition)

--1. Create Partition Function to define a partition range

--2. Create Partition Sheme to map the partition to file group(s)

--3. Create a partitioned table using the sheme

--4. Create a partitioned archive table using the same scheme

--5. Populate the partitioned table with test data

--6. Archive the first partition

---a. move the first partition of the partitioned table to the ptable_archive table

---b. Check the data distribution in the partitioned table and the ptable_archive table

 

--Using the following script to check the partition informaiton

 select * from sys.partitions where object_id = Object_id('ptable')

 select * from sys.partition_range_values

**********************************************************************/

 

---------------------------------------------------------------

--1. Create Partition Function to define a partition range

---------------------------------------------------------------

CREATE PARTITION FUNCTION pfunc (int)

AS RANGE LEFT FOR VALUES (50,100,150)

 

---------------------------------------------------------------

--2. Create Partition Sheme to map the partition to file group(s)

---------------------------------------------------------------

CREATE PARTITION SCHEME psche

AS PARTITION pfunc ALL TO ([Primary])

 

---------------------------------------------------------------

--3. Create a table on the sheme

---------------------------------------------------------------

CREATE TABLE ptable(tid INT IDENTITY(1,1) Primary Key Clustered, tval VARCHAR(200)) ON psche(tid)

 

---------------------------------------------------------------

--4. Create a archive table

---------------------------------------------------------------

CREATE TABLE ptable_archive(tid INT Primary Key Clustered, tval VARCHAR(200)) ON psche(tid)

 

 

---------------------------------------------------------------

--5. Populate the partitioned table with test data

---------------------------------------------------------------

INSERT ptable(tval)

SELECT [name] FROM syscolumns

--Check the data distribution in the partitioned table

--SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable GROUP BY $PARTITION.pfunc(tid)

--SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable_archive GROUP BY $PARTITION.pfunc(tid)

 

---------------------------------------------------------------

--6. Archive the first partition

---------------------------------------------------------------

---a. move the first partition of the partitioned table to the temp table

   ALTER TABLE ptable SWITCH PARTITION 1 TO ptable_archive PARTITION 1

 

---b. Check the data distribution in the partitioned table and the ptable_archive table

   SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable GROUP BY $PARTITION.pfunc(tid)

 

   SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable_archive GROUP BY $PARTITION.pfunc(tid)

 

/*********************************************************************

--run the following script to clean it up

       if object_id('ptable') is not null

       begin

               drop table ptable

       end

 

       if object_id('ptable_archive') is not null

       begin

               drop table ptable_archive

       end

 

       if exists(select * from sys.partition_schemes where [name] = 'psche')

       begin

               drop partition scheme psche

       end

 

       if exists(select 1 from sys.partition_functions where [name] = 'pfunc')

       begin

               drop partition function pfunc

       end

       go

**********************************************************************/

Monday, 09 March 2009 00:11:21 UTC  #    Comments [0] -
SQL Server Table Partition
# Friday, 06 March 2009

/*********************************************************************

The following script shows how to use table partition to delete history data (the first partition)

--1. Create Partition Function to define a partition range

--2. Create Partition Sheme to map the partition to file group(s)

--3. Create a table on the sheme

--4. Populate the partitioned table with test data

--5. Check the data distribution in the partitioned table

--6. Delete the first partition

---a. create a temp table with the same structure of the partitioned table

---b. move the first partition of the partitioned table to the temp table

---c. Check the data distribution in the partitioned table and the temp table

---d. drop the temp table

 

--Using the following script to check the partition informaiton

 select * from sys.partitions where object_id = Object_id('ptable')

 select * from sys.partition_range_values

 

**********************************************************************/

 

---------------------------------------------------------------

--1. Create Partition Function to define a partition range

---------------------------------------------------------------

CREATE PARTITION FUNCTION pfunc (int)

AS RANGE LEFT FOR VALUES (50,100,150)

 

---------------------------------------------------------------

--2. Create Partition Sheme to map the partition to file group(s)

---------------------------------------------------------------

CREATE PARTITION SCHEME psche

AS PARTITION pfunc ALL TO ([Primary])

 

---------------------------------------------------------------

--3. Create a table on the sheme

---------------------------------------------------------------

CREATE TABLE ptable(tid INT IDENTITY(1,1), tval VARCHAR(200)) ON psche(tid)

 

---------------------------------------------------------------

--4. Populate the partitioned table

---------------------------------------------------------------

INSERT ptable(tval)

SELECT [name] FROM syscolumns

 

---------------------------------------------------------------

--5. Check the data distribution in the partitioned table

---------------------------------------------------------------

SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable

GROUP BY $PARTITION.pfunc(tid)

 

---------------------------------------------------------------

--6. Delete the first partition

---------------------------------------------------------------

---a. create a temp table with the same structure of the partitioned table

   CREATE TABLE temp(tid INT NOT NULL, tval VARCHAR(200))

 

---b. move the first partition of the partitioned table to the temp table

   ALTER TABLE ptable

    SWITCH PARTITION 1 TO temp

 

---c. Check the data distribution in the partitioned table and the temp table

   SELECT $PARTITION.pfunc(tid), COUNT(*) FROM ptable

   GROUP BY $PARTITION.pfunc(tid)

 

   SELECT count(*) FROM temp

 

 

---d. drop the temp table

   DROP TABLE temp

 

/*********************************************************************

run the following script to clean it up

       if object_id('ptable') is not null

       begin

               drop table ptable

       end

       if exists(select * from sys.partition_schemes where [name] = 'psche')

       begin

               drop partition scheme psche

       end

       if exists(select 1 from sys.partition_functions where [name] = 'pfunc')

       begin

               drop partition function pfunc

       end

       go

       if object_id('temp') is not null

       begin

               drop table temp

       end

**********************************************************************/

Friday, 06 March 2009 18:59:54 UTC  #    Comments [0] -
SQL Server Table Partition
# Thursday, 18 December 2008

1.       Enable Full Text Search

a.       Open the database project

b.      Right Click the database project then select properties

c.       On the Project Settings tab, make sure the Enable full text search option is checked

2.       Create Full Text Catalog

a.       Under Solution Explorer , expand the Database project, expand the Schema Objects -> Storage

b.      Right click the Full Text Catalogs, then select Add -> New Item from the context menu

c.       Select Storage from Categories panel then select Full Text Catalog from Templates panel

d.       Enter the name of the Full Text Catalog you are creating.

e.      Enter the creating Full Text Catalog script in the script window then save.

CREATE FULLTEXT CATALOG {FullTextCatalog_ReplaceThisName}

ON FILEGROUP [PRIMARY]

WITH ACCENT_SENSITIVITY = ON

AS DEFAULT

AUTHORIZATION [dbo]

3.       Create Full Text Index on a table

a.       Under Solution Explorer, expand the database project->Schema Objects -> Table

b.      Right click the Indexes folder and select  Add New Item

c.       Select Tables and Views from Categories panel then select the Full Tex Index from Templates panel

d.       Enter the name of the Full Text Index then enter creating Full Text Index script in the window and save it

CREATE FULLTEXT INDEX ON {TableName} KEY INDEX {IndexKeyName} ON {Full Text Catalog Name}

ALTER FULLTEXT INDEX ON {TableName} ADD ({ColumnName})

GO

ALTER FULLTEXT INDEX ON {TableName} ENABLE

 

Thursday, 18 December 2008 03:03:01 UTC  #    Comments [0] -
TFS Database Project | Full Text Search
# Saturday, 13 December 2008

If you are using SQL authentication for the database connections in your SSIS package you may get the following error message when you schedule your SSIS package.

   Code: 0xC0016016

   Source:

   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

You can do the followings to fix the problem:

1.     1.       Change the Package Security ProtectionLevel from EncryptSensitiveWithUserKey to DontSaveSensitive

2.       2.      Enable package configurations by right click on the package design surface and click "Package Configurations.."  to start Package Configuration Organizer.

3.       Create configuration file using the Package Configuration Organizer.

4.       4.     Modify the configuration file and store the password information of your connection string in the configuration file

5.           5.      Run your package using the configuration file.

 

 


Saturday, 13 December 2008 01:17:25 UTC  #    Comments [0] -
SSIS
# Thursday, 11 December 2008

One of my colleagues came to me and showed me a strange problem on SQL server. When a user uses Windows Login to connect to SQL server the user get the 18456 error message. But if the user is added to SysAdmin role the user is able to login to the SQL server. My colleague has deleted the window login from the SQL server and added the window login back to the SQL server. It does not resolve the problem.

The SQL Server Logs shows the following message:

SQL Error Log Message:

Date  12/11/2008 9:30:16 AM

Log  SQL Server (Current - 12/11/2008 2:50:00 PM)

Source  Logon

Message

Error: 18456, Severity: 14, State: 11.

 

The followings are the steps that we used to solve this problem:

1.       Check if the login has granted access permission to the default database

2.       Check if the login is not disable

3.       Check if the login is denied database engine access

4.       Run the following script:

Exec  xp_logininfo  'domain\user'

 (Replace the domain with your domain name and user with the right user name)

If there is no result returning back, then the window login is denied database engine access through windows group.

5.       Run the following script to get a list of window groups that are denied SQL database engine access

select prin.[name], prin.type_desc

from sys.server_principals prin

JOIN sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id

where perm.state_desc = 'DENY'

6.       Make sure that the window login is not a member of the Window Groups in the list.

Thursday, 11 December 2008 23:39:15 UTC  #    Comments [1] -
SQL Security
Archive
<2016 December>
SunMonTueWedThuFriSat
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Statistics
Total Posts: 6
This Year: 0
This Month: 0
This Week: 0
Comments: 2
All Content © 2016, Full Time DBA