Search results matching tag '#sql'

SQL Server - Changing Rows to Columns Using PIVOT

pprajapati — Sun, 19 Feb 2012 04:05:00 GMT

During working with one logic, i got chance to work with PIVOT operation. Sometime we need do require rowdata as column in our custom logic, then we can use some temp table and then populate agreegate data in temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as column.

Before go ahead to run the script of Pivot, we will create database and table objects.

CREATE DATABASE DEMO
GO

USE DEMO
GO

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)

GO

-- Inerting some sample records

INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'

GO

Now we will check the original table data and aggregated data using Pivot.
So we will run both script for the same.

-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated data using Pivot and converted rows to column
SELECT
*
 FROM
 (
    SELECT 
 ItemCode, 
 ItemName, 
 ItemColour
    FROM TblPivot
 ) AS P
PIVOT
(
  Count(ItemName) FOR ItemColour IN (Red, Blue, Green)
) AS pv

GO

You can review here and see how the PIVOT is working. Let me share your experience with PIVOT operation.

SSIS - Delete files older than retention period from folder with parameter using Script Task in SQL Server

pprajapati — Fri, 10 Feb 2012 14:03:00 GMT

After using some of the ways to delete the files from the particular folder like Delete file using Script Task and File System Task in SSIS. We have seen such methods and used logic to get the files from the loop container and process to delete them and same thing applied for the scripts as well. we also looked for the files deletion which are older than some Retention period with Script Task also.

In earlier posts we have not used any parameters in the scripts and directly applied folder path and retention period values there. But here i would like to use parameters for the folder path and retention period and pass through the scripts and delete them as per condition.

1. Let check the files from the target folder.

2. Create parameters and set the values. Here it is going to be delete the files which are older then 3 days from the E:\ImagesBackup folder in this case.

3. Drag and drop File System Task

4. Put the parameters as ReadOnlyVariables

5. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not. Here you can see the parameters used in the script.

Please note here we need to import system.IO namespace.

6. Turn on final step and run package. Files older than specified retention period get deleted.

You can use the script below for the same as mentioned in above image,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");

foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

SSIS - Delete files older than retention period from specified folder using Script Task in SQL Server

pprajapati — Sat, 04 Feb 2012 13:18:00 GMT

Earlier we have seen for the Delete file using Script Task and files deletion using File System Task in SSIS. We have directly wrote the logic to get the files and delete from specified folder in the script for the Script Task. For File System Task we have Used Foreach Loop Container and variables to hold file names which are passed from earlier stage one by one and then finally used with File System Task to delete it.

Here we have same but not to delete all the files inside passed folder but get deleted files which are older than specified days. This is nothing but minor change in script where we pass the folder path and retention period. Lets start the steps as following,

1. Let check the files from the target folder.

2. Drag and drop Script Task.

3. Open script editor from the properties.

4. Apply attached script in editor which have additional logic with condition to check the file lat modified date and check if older than specified retention period or not.

Please note here we need to import system.IO namespace.

5. Turn on final step and run package. Files older than specified retention period get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = 3;
string directoryPath = @"E:\ImagesBackup";
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.png");
     foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Error encountered in SQL Server - Alter failed for Login sa. Cannot set a credential for principal 'sa'.

pprajapati — Sat, 28 Jan 2012 02:19:00 GMT

Recently when i worked with SQL Server security, i encountered with one error while trying to modify 'SA' account properties. The exception details looks following,

Alter failed for Login sa. Cannot set a credential for principal 'sa'.

Also you can see the image below for the same.

The fix for the error is the option "Map to Credential" is checked in the "General" tab of the Login Properties Page as mentioned below,

Working with Application Role - SQL Server

pprajapati — Fri, 20 Jan 2012 14:01:00 GMT

In last post we have seen custom database roles as how can we create it and assign required access to users. We also noticed that we can add multiple members with same role. That was the security with database roles and members comes into the picture. Now here we will study of Application Role. This is the security for the application level and no such members comes into the picture.

Application Role :
As per msdn, An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.

Workaround:
We can implement application role and take into effect with following steps, I am going to here with some of examples, so like create those required objects, so we can set them with application role.

1. Create required objects

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Here we have created same required objects as we created in earlier post for database role.

2. Create an application role

USE demo
GO

-- sp_addapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_addapprole 'AppRole', 'AppPwd'
GO

3. Add permission to this application role

USE demo
GO

GRANT SELECT ON SampleTable1 to AppRole;
GRANT SELECT, INSERT,UPDATE ON SampleTable2 to AppRole;
GRANT EXEC ON SampleSP1 to AppRole;
GO

You can see we have assigned same access to application role as we did in earlier post for the custom database role. Application role is created here now, You can also create/view with expanding Roles inside security tab for particular database and inside it you can find Application Role tab.

4. Connecting database and activating application role
Application roles are enabled/activated by sp_settapprole system stored procedure and it has required password.So application can be connected to SQL Server with this application role with scope of particular session and required password to authenticate it to connect SQL Server. To authenticate application roles and activate it it should be required to use in .Net connection code or other application database connection method code. You can refer the link here to use application role in application code.

sp_settapprole system stored procedure activate the application role for the specific connection while connecting with application and the syntax is as following,

USE demo
GO

-- sp_setapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_setapprole 'AppRole', 'AppPwd'
GO

We can change the password for the application role with following,

USE demo
GO

-- sp_approlepassword [@rolename = ] 'rolename', [@password = ] 'new password'

EXEC sp_approlepassword 'AppRole', 'AppChangedPwd'
GO

Hope these all the steps are enough to implement application role. Stay tuned for more.

SQL Server - Custom Database Role

pprajapati — Fri, 13 Jan 2012 13:53:00 GMT

Recently while working with database security, I learned database roles as how the each role used. Apart from the server level roles if we need to require to assign access/rights to particular databases level then we need to go through database level roles.

Following are the fixed database level roles as per MSDN,
db_owner : Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin : Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.
db_accessadmin : Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator : Members of the db_backupoperator fixed database role can back up the database.
db_ddladmin : Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
db_datawriter : Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_datareader : Members of the db_datareader fixed database role can read all data from all user tables.
db_denydatawriter : Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.
db_denydatareader : Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

You can see the image for all above fixed database roles. Now we will see how can we use the roles and bind with users. Let's create a small demo with example.

Here i am creating required objects used for demo, So let's do that.

USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Now here i want assigned to the access to user as following,
1. User can see the data from SampleTable2 table.
2. User can perform select/insert/update operation on SampleTable1 table, not delete operation.
3. Can execute stored procedure SampleSP1.
4. Can not execute stored procedure SampleSP2.

Workaround 1:
If we assigned fixed database roles to User like db_datareader and db_datawriter for particular database, then user can perform all DML operation on all tables, views for the database. Even if we assigned EXECUTE permission to user then user can execute all the stored procedures.

After all these permission we need to deny permission from the user for some of the tables and stored procedures which are not required to be accessed.

Workaround 2:
Instead of doing above such stuffs, We will create a new custom database level roles and assigned requited access to user for the objects.

USE demo
GO

CREATE LOGIN [SupportUser] 
WITH PASSWORD=N'SupportUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [SupportUser] FOR LOGIN [SupportUser]
GO

CREATE ROLE [SupportRole] 
Go

GRANT SELECT ON SampleTable2 TO [SupportRole];
GRANT SELECT,INSERT,UPDATE ON SampleTable1 to [SupportRole];
GRANT EXEC ON SampleSP1 to [SupportRole]
GO

EXEC sp_addrolemember N'SupportRole', N'SupportUser'
GO

Let's connect the SQL Server instance with this newly created user and see the access rights,

USE demo
GO

PRINT 'Inserting in SampleTable1'
GO
INSERT INTO SampleTable1
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample1'
GO

PRINT 'Inserting in SampleTable2'
GO
INSERT INTO SampleTable2
 (
 Id, 
 Name
 )
SELECT 
 1,
 'Sample2'
GO

PRINT 'Deleting from SampleTable1'
GO
DELETE FROM SampleTable1
GO

PRINT 'Viewing from SampleTable1'
GO
SELECT * FROM SampleTable2
GO
SELECT * FROM SampleTable1
GO


PRINT 'Executing SampleSP11'
GO
EXEC SampleSP1
GO
PRINT 'Executing SampleSP2'
GO
EXEC SampleSP2
GO

You can see the below image to see the access of running user,

Main benefit of the custom database role is role can be assigned to multiple users. You can see below script where i have assigned same role to different user. So once role created it can be assigned to multiple users.

USE demo
GO

CREATE LOGIN [DBAUser] 
WITH PASSWORD=N'DBAUser', 
 DEFAULT_DATABASE=[master], 
 DEFAULT_LANGUAGE=[us_english], 
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

CREATE USER [DBAUser] FOR LOGIN [DBAUser]
GO

EXEC sp_addrolemember N'SupportRole', N'DBAUser'
GO

Hope you liked this post.

SQL Server - Archive old database backup files using TSQL Script

pprajapati — Sat, 07 Jan 2012 13:37:00 GMT

I experienced into one issues for database backups were failed. And this was due to space issues on disk drive. This disk drive is spacific to allocated for the database backups only.

The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all files and continue this activity so many days.If you create a maintenance plans then it have option to delete old backups files. But i have the stored procedure for the database backups. So i do not have option to delete old and unused database backup files.

Finally, I have created a script to clean those old backups. I have created one stored procedure in which you need to pass there parameters, One is Backup type as want to delete full, differential or transaction log backups. Second is From days and third one is End day.

Please make sure XP_CMDSHELL is enabled in database instance as thisis require to enable it to delete database backup files to be deleted physically.
Here id query to enable it.

USE MASTER
GO

EXEC SP_CONFIGURE 'show advanced options',1
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE
GO

I have already told you as i have created script to delete the old DATABASEPROPERTY backups, please find below SP for the same.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteDBBackupOldFiles]
@BackupType char(1) = 'D', -- 'D'-Full, 'I'-Differential, 'L'-Log backup type
@StartDayFromToday int,
@EndDayFromToday int
AS
BEGIN
SET NOCOUNT ON

DECLARE @IsFileExists int
DECLARE @DeletedFile VARCHAR(500)
DECLARE @OldFiles VARCHAR(500)

DECLARE OldFiles CURSOR FAST_FORWARD  FOR
SELECT 
bmf.physical_device_name
from msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE DEVICE_TYPE = 2 
AND TYPE = @BackupType 
AND BACKUP_START_DATE < = GETDATE() - @StartDayFromToday
AND BACKUP_START_DATE > = GETDATE() - @EndDayFromToday
-- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday 

OPEN OldFiles

FETCH NEXT FROM OldFiles INTO @OldFiles

WHILE @@FETCH_STATUS =0
BEGIN

EXEC XP_FILEEXIST @OldFiles,@IsFileExists OUTPUT
IF @IsFileExists=1
BEGIN

PRINT 'Deleted File : ' + @DeletedFile

SET @DeletedFile = 'DEL ' + @OldFiles

EXEC XP_CMDSHELL @DeletedFile

END
FETCH NEXT FROM OldFiles INTO @OldFiles
END
CLOSE OldFiles
DEALLOCATE OldFiles
END

Finally after creating a stored procedures , it is time to run and clean the old database backups. After running it will remove all the old database baclups as per specified parameters. Like we have passed 'D',3 and 10 with SP, So i will delete Full database backups whicl are older then 3 days ago and 10 days before created.

EXEC [DeleteDBBackupOldFiles] 
  @BackupType  = 'D',
  @StartDayFromToday = 3,
  @EndDayFromToday = 10

Which method you are using for old databsee backup maintenance? You can read my earlier posts for delete files using File System Task and Script Task in SSIS.

SSIS - Delete files from specified folder using Script Task in SQL Server

pprajapati — Fri, 30 Dec 2011 14:13:00 GMT

Earlier we have seen for the files deletion using File System Task in SSIS. We have used it with Foreach Loop Container. Used a variable to hold file names which are passed from earlier stage one by one and then finally used with File System Task to delete it.

Now i am going to use Script Task to delete all files from specified folder. Here i have added script to get each files from specified folder and then delete them as you can see in the following steps. Lets start to follow them.

1. Drag and drop Script Task.

2. Open script editor from the properties.

3. Apply attached script in editor and save it.

Please note here we need to import system.IO namespace.

4. Turn on final step and run package. Files get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here

 string directoryPath = @"E:\TestFolder";
            string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.txt");
            foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                currFileInfo.Delete();
              
            }

Hope you liked this post. Stay tuned for more.

SSIS - Delete files from specified folder using File System Task in SQL Server

pprajapati — Fri, 23 Dec 2011 13:51:00 GMT

We know all the various methods to delete the particular or all files from the specified folder using some of the methods like xp_delete_file, Ole Automation Procedures and with xp_cmdshell command line utility which we used for the old files archive or cleanup purpose.

Here i am going to share some of the screens which delete the files with SSIS using File System Task. Let me share them one by one.

1. Folder having some test files.

2. Drag and drop Foreach Loop Container and File System Task. Foreach Loop Container used to get all the files inside that folder one by one and process with File System Task. Open the Foreach Loop Container properties, goto Collection tab and select Foreach File Enumerator as specified in screen below.

3. Select the folder from where need to delete the files. Also apply extension if you need.

4. For the file assignment we need one variable and the values allocated from Foreach Loop Container process. So assign it from variable mapping as per shot taken below.

5. Drag File System Task, select Delete File operation and define Source Connection.

6. Finally run package and files will get deleted.

SQL Server - Database Backup Statistics and History

pprajapati — Fri, 16 Dec 2011 13:56:00 GMT

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at other place and using for the database restore which used for reporting purpose or used in log shipping purpose. Because database backups are most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loses.

You can read my earlier posts for Database Backup Compression, Database Backup files Verification , Automated All Databases Backups Script and Split Database Full Backup to Multiple files.

I would like to share the script which help us to show the database backups status, history of their when they are done based on schedule, at where are taking and when, backup types , backups physical device and size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statistics and status information.

USE MSDB
GO

SELECT 
bs.server_name AS Server, -- Server name
bs.database_name AS DatabseName , -- Database name
CASE bs.compatibility_level 
 WHEN 80 THEN 'SQL Server 2000'
 WHEN 90 THEN 'SQL Server 2005 '
 WHEN 100 THEN 'SQL Server 2008'
 WHEN 110 THEN 'SQL Server 2011'
END AS CompatibilityLevel , -- Return backup compatibility level
recovery_model AS Recoverymodel , -- Database recovery model
CASE bs.type 
 WHEN 'D' THEN 'Full' 
 WHEN 'I' THEN 'Differential' 
 WHEN 'L' THEN 'Log' 
 WHEN 'F' THEN 'File or filegroup' 
 WHEN 'G' THEN 'Differential file' 
 WHEN 'P' THEN 'P' 
 WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
bs.backup_start_date AS BackupstartDate, -- Backup start date
bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
CASE device_type
 WHEN 2 THEN 'Disk - Temporary'
 WHEN 102  THEN 'Disk - Permanent'
 WHEN 5  THEN 'Tape - Temporary'
 WHEN 105  THEN 'Tape - Temporary'
 else 'Other Device'
END AS DeviceType, -- Device type
bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
ORDER BY bs.backup_start_date DESC

GO

The transact-SQL for backupmediafamily and backupset are accordingly are http://msdn.microsoft.com/en-us/library/ms190284.aspx and http://msdn.microsoft.com/en-us/library/ms186299.aspx

Hope you liked this post.