sqlIdeas

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

2012-01-28T07:32:00.000+05:30

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,

Hope this help you.

Application Role in SQL Server

2012-01-20T19:29:00.000+05:30

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.

Custom Database Role in SQL Server

2012-01-13T19:31:00.000+05:30

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.

Changing Rows to Columns Using PIVOT - SQL Server

2012-01-07T19:13:00.000+05:30

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.

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

2011-12-30T19:55:00.000+05:30

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

5. Put the parameters as ReadOnlyVariables

6. 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.

7. 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.

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

2011-12-23T19:40:00.000+05:30

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.

3. 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.

4. 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.

Archive old database backup files using TSQL Script - SQL Server

2011-12-16T19:43:00.002+05:30

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.

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

2011-12-14T19:47:00.002+05:30

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.

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

2011-12-10T18:58:00.002+05:30

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.

My recent blog posts and just learned tips - SQL Server

2011-12-02T20:33:00.002+05:30

Recently I have posted some of the articles and some just learned from the month of October. This article just summarize all the posts which recently published. You can find the links below to go in the details.

Blog Posts:

Just Learned tips:

Hope these posts help you.

Backup Statistics and History - SQL Server

2011-11-30T19:46:00.000+05:30

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 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.

NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server

2011-11-25T19:33:00.002+05:30

When we created a new database it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolatino level help for the same as ther are operating same. we have some another options other than this. But i am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

For NOLOCK , we need to put this hint on table level, so it is require to put for every tables level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere tables refers in query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written at top of the query or stored procedure.

Let us look on small demo to elaborate it. First checking here database default isolation level

USE DEMO
GO
DBCC USEROPTIONS

Starting with creating database and table objects.

IF (OBJECT_ID('TrnTable','U') > 0)
DROP TABLE TrnTable

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO

Now for the demo we will run the below script with session 1

-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction

After that we will get the same rows which are updating in above session which are not committed yet in another session. It will be go on waiting to release the lock held by session 1.

We are not closing this transacrion here , and created a new session and run following scripts having NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.

-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

Do not forget to commit or rollback transaction TRAN1
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?

"Can not add a shared registered server with the same name as configuration Server" - Central Management Servers register error in SQL Server 2008

2011-11-19T19:10:00.000+05:30

You may know how can we register SQL Server instances with Central Management Servers (CMS) and also how can we perform multi server query will all of instances of registered SQL Server instances.

There you can see i have registered one shared SQL Server 2011 instance under CMS and named it Denali. Under Shared instance i have registered SQL Server 2008 and same SQL Server instance of Denali which i already registered.

Here SQL server 2008 successfully registered but SQL Server 2011 has encountered error. But how i have registered it which i am going to explain here.

This is because same SQL instance already registered as shared SQL server.

How can i register same SQL Server Denali or 2011 instance again?

I have changed the port and applied static port as following from SQL Server TCP/IP properties which we will available at SQL Server Configuration Monitor.

Then please see the screen shot below as i have register SQL Server Denali instance with port.

Now both SQL Server registered successfully.

Did you get it earlier? How did you resolved?

Database Backup Compression, Amazing feature for DBA - SQL Server 2008

2011-11-16T19:44:00.001+05:30

Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more tome to execute and very CPU, memory and IO consumptive. And important thing is backups activity should be completed with in down time or pick time when more users are not connected with databases.

What is solution?
We have alternative ways to use the some backup tool that can help to use in this matter.
But SQL Server itself provide the best feature and supported SQL Server 2008 or newer version.
That is "Backup Compression". You can read my earlier posts for Automated All Databases Backups, Database Backup files Verification and Details and Split Database Full Backup to Multiple files

Using this feature , we can take a database backups with compression option. And will really reduce time required to backup it, reduce server IO and less CPU and memory consumptive.
It is veryfull feature for the DBA.

Let us look on below example which will clear you the difference between the noncompressed and compressed backups.We will first perform non-compressed backups for the database which have 4 GB size.

Using Management Studio :

Using TSQL:

#1. Performing noncompressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_NonCompressedBackup.bak' 
WITH NAME = N'ReportServer-Full NonCompressed Database Backup',
NO_COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

#2. Performing compressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_CompressedBackup.bak' 
WITH NAME = N'ReportServer-Full Compressed Database Backup',
COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

From the result oputput , you can view the time for the backup execution, CPU usage.
Here you have screen for the both of the backups size.

You can use below query to get the backup statistics,

SELECT 
bs.database_name AS DatabaseName , -- Database name
backup_size/compressed_backup_size as CompressionRatio, 
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
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)
and database_name = 'ReportServer'
ORDER BY bs.backup_start_date DESC

You can set the default backup setting to Compressed as following,

By TSQL :

USE MASTER
GO

EXEC SP_CONFIGURE 'backup compression default', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

From UI :

I hope you liked this feature..

Review of some replication issues and workaround - SQL Server

2011-11-05T18:24:00.000+05:30

After posting some snaps to configure transactional replication in sql server having publication server as distributor, i want to share some of the issue which i got during working with it.

You may also faced the same issue and got resolved as well. You can comment if you faced any other replication issues and the solution for the same. I have shared some of them as Just learned on http://beyondrelational.com.

1."There is insufficient system memory to run this query" - Log reader agent error for subscriber database :

During working with replication this is one of the error which i encountered and get resolved it. This is coming with log reader agent and it stopped working. It's due to the issue memory. i have flushed the unused memory consumed by Adhoc queries and system cache.

The error looks, "Error messages:
There is insufficient system memory to run this query. (Source: MSSQL_REPL, Error number: MSSQL_REPL27453)"

To flush the memory use the below commands,
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')

You can read the same tips here.

2. "Could not find stored procedure 'sp_MSins_TableName' in replication in sql server" - Log reader agent error:

It came after reinitialize all subscription for one publication and it stopped working with error,

"Could not find stored procedure 'sp_MSins_TableName' in replication in sql server"

This is due to replication object was not created for insert and named like 'sp_MSins_TableName'. The solution for this issue, we need to create these missing replication objects using 'sp_scriptpublicationcustomprocs' which generates scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication. Workaround for the solution is,

EXEC sp_scriptpublicationcustomprocs 'PublicationName'

After running generated scripts in subscriber database, log reader agent started to work. You may read same tip here.

3."The row was not found at the Subscriber when applying the replicated command" :

It encountered it with log reader agent and it was stopped due to error as,

"Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000CAB2A00038CB6003B00000000, Command ID: 2)
Error messages:
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)"

Row conflict issue was occurred due to some of the tables which we can get the details with following query to reach towards solution.

SELECT
*
FROM dbo.MSarticles m
WHERE EXISTS
(
SELECT mc.Article_id from MSrepl_commands mc
WHERE mc.xact_seqno = 0x000CAB2A00038CB6003B00000000
and mc.Article_id = m.article_id
)

You can read it in details here. I will share more replication errors if will get in future.

Multi Monitor Support of SSMS - A new feature of SQL Server Denali

2011-11-03T21:18:00.001+05:30

I have drafted all the new features and enhancements introduced by SQL Server denali CTP1 and CTP3. Multi Monitor Support is the new feature among them.

We can do with this feature:
1. Using this feature we can use multi screen of query analyzer or editor.
2. You can monitor separately it and run the output individually.
3. You can resize all the screens.
4. You can drag and drop at the place you want.
5. You can use Registered servers and object explorer and it's details with multi screens.

For more idea, You can see the below screen shows which i have captured during enjoyed with it.

1. This screen shot have multi screens of query analyzer with SSMS.

2. This screen shot captured during docking/undocking the screens.

Hope you liked this post.

"The database owner SID recorded in the master database differs from the database owner SID recorded in database" - SQL CLR DLL Register error in SQL Server

2011-10-27T05:08:00.001+05:30

Recently while working with SQL CLR functionality and created DLL for the SQL CLR.
But while registering this DLL in the database i got one surprised error.

Lt's show you the script so you have more idea. We have a script to register DLL as following,

SP_CONFIGURE 'clr enabled',1
GO
RECONFIGURE
GO

USE SQLCLRDb 
GO

CREATE ASSEMBLY [SQLCLR_ASSEMBLY] 
FROM 'C:\SQLCLR_ASSEMBLY.dll' WITH permission_set = UNSAFE 

GO

And the error is coming like

The database owner SID recorded in the master database differs from the database owner SID recorded in database.
You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement.

The solution for this issue is which we have the script below. This script will change db owner of the running database and make it trust worthy on.

USE SQLCLRDb 
GO

ALTER DATABASE SQLCLRDb SET TRUSTWORTHY ON
go

EXEC SP_CHANGEDBOWNER 'UserName'
GO

After running above query, i come out from the issue and registered SQL CLR DLL successfully.

I think you also suffered same or different issues with SQL CLR. Please comment your issues and the solution for the same.

Database Backup files Verification and Details - SQL Server

2011-10-22T09:31:00.000+05:30

As a best practice, DBA need to verify each database backups are properly done or not, also make sure the backups are OK then is readable and can be restored. Because so many databases are scheduled as FULL and differential backups weekly/daily and transaction log backups on every hours or whatever as per requirement performing to avoing data loss.

You can read my earlier articles for script to automated all types of database backup and split database backup to multiple files.

How can we verify the backup files?
SQL Server provide VERIFYONLY clause and we can use it with Restore command. Please see the below details for the same as how it works.

#1. VERIFYONLY
Verify databse backup integrity and checking backups are currupted or not.
Per SQL Book online, Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable.
However, RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. In Microsoft SQL Server, RESTORE VERIFYONLY has been enhanced to do additional checking on the data to increase the probability of detecting errors. The goal is to be as close to an actual restore operation as practical. For more information, see the Remarks.

If the backup is valid, the SQL Server Database Engine returns a success message.

Let us run the query to verify FULL, Differential and Transactional log backups and will see the output come out from it.

RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE VERIFYONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

How can we get the backup files details?
Using HEADERONLY with Restore command we have details for database backup files.
Let us run the query to verify FULL, Differential and Transactional log backups and will see the output come out from it.

#2. HEADERONLY
Per SQL Book online, Returns a result set containing all the backup header information for all backup sets on a particular backup device.

RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE HEADERONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

Here another command FILELISTONLY which will the logical file and physical files details of backup files.

#3. FILELISTONLY
Per SQL Book online, Returns a result set containing a list of the database and log files contained in the backup set.

RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_182654.bak'
GO
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183348.bak'
GO
RESTORE FILELISTONLY FROM DISK =  'D:\DBBackups\Backups\ReportServer_Backup_20110517_183843.trn'
GO

What you are performing activity with backups?

Grouping Sets vs Native method of Group By - Performance review in SQL Server 2008

2011-10-16T08:56:00.000+05:30

Previously i have posted for the overview and usage of the Grouping Sets as how can we get the aggregate data of different group sets with GROPING SETS vs Native method of group by.

If you have not read my earlier post for the same then please read it before go ahead with this demonstration. In this demo i am going to show the performance of Grouping Sets and native method of group by.

Let us start the demo here.

-- Creatind database and table 
CREATE DATABASE GroupingDB

GO

USE GroupingDB

GO

IF (OBJECT_ID('GroupingTable') > 0)
DROP TABLE GroupingTable

CREATE TABLE GroupingTable
  (
     MainCategoryName VARCHAR(100)
     ,SubCategoryId   VARCHAR(100)
     ,VALUE           BIGINT
  )

GO

Now we are going to insert so many records in table for the presentation of demo.

-- Insert some demo records for diffrent group sets
INSERT INTO GroupingTable
SELECT 'Main-1','Sub-1',100
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-1','Sub-2',200
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-2','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-2',400
GO 500

As i said to check the performance of both of the script using them and check the execution plan.

-- Get aggregate data using native method of group by of different sets
SELECT NULL, NULL,
  SUM(VALUE) as Total
FROM   GroupingTable
Union all
SELECT  MainCategoryName
,NULL
,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY MainCategoryName 
Union all
SELECT  NULL,
  SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY SubCategoryId 
union all
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
  MainCategoryName
  ,SubCategoryId

-- Get aggregate data using Grouping Sets of different sets
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

You can see the data rows of above both scripts are same and given same result set.
Now look for execution plan.

This performance review is totally based on the data and depends on the your business requirement.
Before implementing this new feature please check the execution and decide you view.

Multi server Query with Central Management Servers - SQL Server 2008

2011-10-02T18:01:00.000+05:30

Before SQL Server 2008 , when we need gather all information and details related to server or database level, we must run the script individually by connecting each SQL Server instances.

But SQL Server 2008 came up and easy our work for that. It has introduced a new feature - Central Management Servers (CMS). With Central Management Servers we can configure and register SQL Server instances with shared SQL Server instance. Then we run the query against all the SQL instance and get details for all instances.

Let's you demonstrate the same in details here.

1. How to open Central Management Servers?

Go to View --> Registered Servers
or
press Ctrl + Alt + G.

2. How can register SQL Server instances in CMS?

Expand Database engine from Registered Servers.

Right click on CMS and click on Register SQL Server Management.

The screen will appear like below,

In above i have registered SQL Server Denali instance which will be shared SQL Server instance.

Now I am creating new SQL Server Group under CMS and then register SQL Server 2008 and SQL Server 2011 by right click on group and then go on the link of registration and then go on same way as i did for SQL Server Denali instance.

3. How can we perform multi server query against all SQL Server ?

Go on right click on Shared SQL Server instance under CMS and click on New Query.

Let's do here same and execute the query and see what will be the result?

This feature very help us to run the script against all the registered SQL Server instances. Hope you liked this post.

How to invoke Job thorugh SQL script - SQL Server

2011-09-29T19:15:00.000+05:30

We are mostly create the SQL job and schedule it to run automated. But sometime as per requirement we need to invoke and run on demand basis, not on schedules basis.

I have did the same thing and the following is the small demo ready for you.

Let's create one stored procedure and schedule in job.

USE [Master]
GO

CREATE PROCEDURE JobInvokeProc
AS
BEGIN
SET NOCOUNT ON

WAITFOR DELAY '00:00:15';

END
GO

Now we will create the job for the same and execute this stored procedure in job.

The created script for the job is as following,

USE [msdb]
GO

/****** Object:  Job [Start_JobInvokeProc]    Script Date: 05/05/2011 07:51:53 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/05/2011 07:51:53 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Start_JobInvokeProc', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'[Uncategorized (Local)]', 
  @owner_login_name=N'paresh-PC\paresh', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step_JobInvokeProc]    Script Date: 05/05/2011 07:51:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_JobInvokeProc', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'exec JobInvokeProc', 
  @database_name=N'master', 
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Now i have one more we i get from MSDB database and customize it which is used to get the SQL job status information as running or not.

This is the script to get the Job status.

USE [master]
GO

CREATE PROCEDURE GetJobStatus     
  @job_Name           varchar(max),     
  @status INT OUTPUT    
AS      
BEGIN      
  DECLARE @can_see_all_running_jobs INT ,    
  @job_type           VARCHAR(12)     ,  -- LOCAL or MULTI-SERVER      
  @owner_login_name   sysname         ,      
  @subsystem          NVARCHAR(40)    ,      
  @category_id        INT             ,      
  @enabled            TINYINT         ,      
  @execution_status   INT             ,  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions      
  @date_comparator    CHAR(1)         ,  -- >, < or =      
  @date_created       DATETIME        ,      
  @date_last_modified DATETIME        ,      
  @description        NVARCHAR(512)   ,  -- We do a LIKE on this so it can include wildcards      
  @schedule_id        INT                 
     
  set @job_type           = NULL  -- LOCAL or MULTI-SERVER      
  set  @owner_login_name  = NULL      
  set @subsystem          = NULL      
  set @category_id        = NULL      
  set @enabled            = NULL      
  set @execution_status   = NULL  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions      
  set @date_comparator    = NULL  -- >, < or =      
  set @date_created       = NULL      
  set @date_last_modified = NULL      
  set @description        = NULL  -- We do a LIKE on this so it can include wildcards      
  set @schedule_id        = NULL     
    
  DECLARE @job_owner   sysname      
      
  declare @job_id UNIQUEIDENTIFIER     
  set @job_id = (select job_id from msdb.dbo.sysjobs where name = @job_Name)    
    
  SET NOCOUNT ON      
      
  -- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.      
  -- This proc should only ever be called by sp_help_job, so we don't verify the      
  -- parameters (sp_help_job has already done this).      
      
  -- Step 1: Create intermediate work tables      
  DECLARE @job_execution_state TABLE (job_id                  UNIQUEIDENTIFIER NOT NULL,      
                                     date_started            INT              NOT NULL,      
                                     time_started            INT              NOT NULL,      
                                     execution_job_status    INT              NOT NULL,      
                                     execution_step_id       INT              NULL,      
                                     execution_step_name     sysname          COLLATE database_default NULL,      
                                     execution_retry_attempt INT              NOT NULL,      
                                     next_run_date           INT              NOT NULL,      
                                     next_run_time           INT              NOT NULL,      
                                     next_run_schedule_id    INT              NOT NULL)      
  DECLARE @filtered_jobs TABLE (job_id                   UNIQUEIDENTIFIER NOT NULL,      
                               date_created             DATETIME         NOT NULL,      
                               date_last_modified       DATETIME         NOT NULL,      
                               current_execution_status INT              NULL,      
                               current_execution_step   sysname          COLLATE database_default NULL,      
                               current_retry_attempt    INT              NULL,      
                               last_run_date            INT              NOT NULL,      
                               last_run_time            INT              NOT NULL,      
                               last_run_outcome         INT              NOT NULL,      
                               next_run_date            INT              NULL,      
                               next_run_time            INT              NULL,      
                               next_run_schedule_id     INT           NULL,      
                               type                     INT              NOT NULL)      
  DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,      
                            last_run_date         INT              NOT NULL,      
                            last_run_time         INT              NOT NULL,      
                            next_run_date         INT              NOT NULL,      
                            next_run_time         INT              NOT NULL,      
                            next_run_schedule_id  INT              NOT NULL,      
                            requested_to_run      INT              NOT NULL, -- BOOL      
                            request_source        INT              NOT NULL,      
                            request_source_id     sysname          COLLATE database_default NULL,      
                            running               INT              NOT NULL, -- BOOL      
                            current_step          INT              NOT NULL,      
                            current_retry_attempt INT              NOT NULL,      
                            job_state             INT              NOT NULL)      
      
  -- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)      
  SELECT @can_see_all_running_jobs = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)      
  IF (@can_see_all_running_jobs = 0)      
  BEGIN      
    SELECT @can_see_all_running_jobs = ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0)      
  END      
  SELECT @job_owner = SUSER_SNAME()      
      
  IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater      
    INSERT INTO @xp_results      
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id      
  ELSE      
    INSERT INTO @xp_results      
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner      
      
  INSERT INTO @job_execution_state      
  SELECT xpr.job_id,      
         xpr.last_run_date,      
         xpr.last_run_time,      
         xpr.job_state,      
         sjs.step_id,      
         sjs.step_name,      
         xpr.current_retry_attempt,      
         xpr.next_run_date,      
         xpr.next_run_time,      
         xpr.next_run_schedule_id      
  FROM @xp_results                          xpr      
       LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),      
       msdb.dbo.sysjobs_view                sjv      
  WHERE (sjv.job_id = xpr.job_id)      
      
  -- Step 3: Filter on everything but dates and job_type      
  IF ((@subsystem        IS NULL) AND      
      (@owner_login_name IS NULL) AND      
      (@enabled          IS NULL) AND      
      (@category_id      IS NULL) AND      
      (@execution_status IS NULL) AND      
      (@description      IS NULL) AND      
      (@job_id           IS NULL))      
  BEGIN      
    -- Optimize for the frequently used case...      
    INSERT INTO @filtered_jobs      
    SELECT sjv.job_id,      
           sjv.date_created,      
           sjv.date_modified,      
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)      
           CASE ISNULL(jes.execution_step_id, 0)      
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'      
           END,      
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)      
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)      
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)      
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0   -- type placeholder             (we'll fix it up in step 3.4)      
    FROM msdb.dbo.sysjobs_view                sjv      
         LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)      
    WHERE ((@schedule_id IS NULL)      
      OR   (EXISTS(SELECT *       
                 FROM msdb.dbo.sysjobschedules as js      
                 WHERE (sjv.job_id = js.job_id)      
                   AND (js.schedule_id = @schedule_id))))      
  END      
  ELSE      
  BEGIN      
    INSERT INTO @filtered_jobs      
    SELECT DISTINCT      
           sjv.job_id,      
           sjv.date_created,      
           sjv.date_modified,      
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)      
           CASE ISNULL(jes.execution_step_id, 0)      
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'      
           END,      
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)      
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)      
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)      
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0   -- type placeholder             (we'll fix it up in step 3.4)      
    FROM msdb.dbo.sysjobs_view                sjv      
         LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)      
         LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)      
    WHERE ((@subsystem        IS NULL) OR (sjs.subsystem            = @subsystem))      
      AND ((@owner_login_name IS NULL)       
          OR (sjv.owner_sid            = msdb.dbo.SQLAGENT_SUSER_SID(@owner_login_name)))--force case insensitive comparation for NT users      
      AND ((@enabled          IS NULL) OR (sjv.enabled              = @enabled))      
      AND ((@category_id      IS NULL) OR (sjv.category_id          = @category_id))      
      AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))      
                                       OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))      
      AND ((@description      IS NULL) OR (sjv.description       LIKE @description))      
      AND ((@job_id           IS NULL) OR (sjv.job_id               = @job_id))      
      AND ((@schedule_id IS NULL)      
        OR (EXISTS(SELECT *       
                 FROM msdb.dbo.sysjobschedules as js      
                 WHERE (sjv.job_id = js.job_id)      
                   AND (js.schedule_id = @schedule_id))))      
  END      
      
    
    
  -- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'      
  UPDATE @filtered_jobs      
  SET current_execution_status = NULL      
  WHERE (current_execution_status = 4)      
    AND (job_id IN (SELECT job_id      
                    FROM msdb.dbo.sysjobservers      
                    WHERE (server_id <> 0)))      
      
  -- Step 3.2: Check that if the user asked to see idle jobs that we still have some.      
  --           If we don't have any then the query should return no rows.      
  IF (@execution_status = 4) AND      
     (NOT EXISTS (SELECT *      
                  FROM @filtered_jobs      
                  WHERE (current_execution_status = 4)))      
  BEGIN      
    DELETE FROM @filtered_jobs      
  END      
      
  -- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for      
  --           multi-server jobs there are multiple last run details in sysjobservers, so      
  --           we simply choose the most recent].      
  IF (EXISTS (SELECT *      
              FROM msdb.dbo.systargetservers))      
  BEGIN      
    UPDATE @filtered_jobs      
    SET last_run_date = sjs.last_run_date,      
        last_run_time = sjs.last_run_time,      
        last_run_outcome = sjs.last_run_outcome      
    FROM @filtered_jobs fj,      
         msdb.dbo.sysjobservers sjs      
    WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =      
           (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)      
            FROM msdb.dbo.sysjobservers      
            WHERE (job_id = sjs.job_id))      
      AND (fj.job_id = sjs.job_id)      
  END      
  ELSE      
  BEGIN      
    UPDATE @filtered_jobs      
    SET last_run_date = sjs.last_run_date,      
        last_run_time = sjs.last_run_time,      
        last_run_outcome = sjs.last_run_outcome      
    FROM @filtered_jobs         fj,      
         msdb.dbo.sysjobservers sjs      
    WHERE (fj.job_id = sjs.job_id)      
  END      
      
  -- Step 3.4 : Set the type of the job to local (1) or multi-server (2)      
  --            NOTE: If the job has no jobservers then it wil have a type of 0 meaning      
  --                  unknown.  This is marginally inconsistent with the behaviour of      
  --                  defaulting the category of a new job to [Uncategorized (Local)], but      
  --                  prevents incompletely defined jobs from erroneously showing up as valid      
  --                  local jobs.      
  UPDATE @filtered_jobs      
  SET type = 1 -- LOCAL      
  FROM @filtered_jobs         fj,      
       msdb.dbo.sysjobservers sjs      
  WHERE (fj.job_id = sjs.job_id)      
    AND (server_id = 0)      
  UPDATE @filtered_jobs      
  SET type = 2 -- MULTI-SERVER      
  FROM @filtered_jobs         fj,      
       msdb.dbo.sysjobservers sjs      
  WHERE (fj.job_id = sjs.job_id)      
    AND (server_id <> 0)      
      
  -- Step 4: Filter on job_type      
  IF (@job_type IS NOT NULL)      
  BEGIN      
    IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'LOCAL')      
      DELETE FROM @filtered_jobs      
      WHERE (type <> 1) -- IE. Delete all the non-local jobs      
    IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'MULTI-SERVER')      
      DELETE FROM @filtered_jobs      
      WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs      
  END      
      
  -- Step 5: Filter on dates      
  IF (@date_comparator IS NOT NULL)      
  BEGIN      
    IF (@date_created IS NOT NULL)      
    BEGIN      
      IF (@date_comparator = '=')      
        DELETE FROM @filtered_jobs WHERE (date_created <> @date_created)      
      IF (@date_comparator = '>')      
        DELETE FROM @filtered_jobs WHERE (date_created <= @date_created)      
      IF (@date_comparator = '<')      
        DELETE FROM @filtered_jobs WHERE (date_created >= @date_created)      
    END      
    IF (@date_last_modified IS NOT NULL)      
    BEGIN      
      IF (@date_comparator = '=')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified <> @date_last_modified)      
      IF (@date_comparator = '>')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified <= @date_last_modified)      
      IF (@date_comparator = '<')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified >= @date_last_modified)      
    END      
  END      
      
  -- Return the result set (NOTE: No filtering occurs here)      
  SELECT @status = ISNULL(fj.current_execution_status, 0)           -- This column will be NULL if the job is non-local      
            
  FROM @filtered_jobs fj      
       LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (fj.job_id = sjv.job_id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so1 ON (sjv.notify_email_operator_id = so1.id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so2 ON (sjv.notify_netsend_operator_id = so2.id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so3 ON (sjv.notify_page_operator_id = so3.id)      
       LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sjv.category_id = sc.category_id)      
  ORDER BY sjv.job_id      
      
return @status    
END      
  
GO

Now will run the script to invoke the SQL job through analyzer and will see the SQL job status.

Let's run the below code and see the output

USE [master]
GO

-- Get the job status before invoke the job 
DECLARE @status INT  
DECLARE @JobName VARCHAR(500)  

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 
------------------------------------------
-- Run this query to invoke the job
EXEC msdb.dbo.Sp_start_job @JobName  
------------------------------------------
-- Get the job status after invoke the job
WAITFOR DELAY '00:00:3';

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus  
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 
GO

When you run the script again while executing the job, It will raise the error while invoke it again.

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Start_JobInvokeProc (from User paresh-PC\paresh) refused because the job is already running from a request by User paresh-PC\paresh.

So better option is we need to check the job status first then we can invoke.

The revised script is,

USE [master]
GO

/* Get the job status before invoke the job */
DECLARE @status INT  
DECLARE @JobName VARCHAR(500)  

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 

-- Added condition here to check the job is already running or not.
IF (@status <> 1)
BEGIN
/* Run this query to invoke the job*/
EXEC msdb.dbo.Sp_start_job @JobName  
END
GO

How to reset SQL Server all default settings in SQL Server Denali CTP3?

2011-09-26T19:47:00.000+05:30

Problem:

Before a few days ago i have installed SQL Server Denali CTP3. Then i have querying in query analyzer and found, minimizing result is not working with CTRL+R is not working. Before CTP3 i have already Denali CTP1 installed in my machine and it was working perfectly, So it stopping to work in SQL Server CTP3.The default SQL Server settings was changed during this installation.

One of the solution:

I did not find any option to resolve it. Then i found one solution to reset these setting which are changed during Denali CTP3 installation. Below are some snaps which i performed to recover it back.

Step 1.

Step 2.

Step 3.

Step 4.

Step 5.

After completion of all above steps, i recovered my SQL Server default setting and CTRL+R working perfect now with CTP3.

You may also faced this issue. I have this solution, You have which one?

Stored Procedure Internal Activation in Service Broker - SQL Server

2011-09-16T19:39:00.000+05:30

As we have seen earlier posts for Service Broker for single database and Service Broker between databases, we have implemented it with examples. Hope you liked those posts.

Remember, in service broker example for single database, we have created stored procedure to process requested data from in target database sent from initiator database. Then we have manually ran the stored procedure to process history data. Please open the link for that example here. We have tested the same there as following,

USE SourceDB
GO

TRUNCATE TABLE TransactionTable
TRUNCATE TABLE TransactionHistory
GO

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

-- Executing stored procedure manualy
EXEC SB_ReceiveData
GO

SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO

Now come out from the link and look what i will do?

Instead of running that stored procedure manually as above, we can set stored procedure self executing as following. Whenever the new incomming requested messages come this stored procedures executed and process the data for history. We will alter target queue here. This is called internal activation of stored procedure. Service broker will active the stored procedure to process incomming message from target queue and will call anytime when message arrive in target queue.

-- Altering queue with self execution of stored procedure
ALTER QUEUE DestinationQueue
    WITH ACTIVATION
    ( STATUS = ON,
      PROCEDURE_NAME = SB_ReceiveData,
      MAX_QUEUE_READERS = 100,
      EXECUTE AS SELF
    )
GO

Retseting :
As resteting the automated self execution of stored procedure, we will do the same but we will not call the stored procedure.

USE SourceDB
GO

-- Truncating table
TRUNCATE TABLE TransactionTable
TRUNCATE TABLE TransactionHistory
GO

-- Inserting a record
INSERT INTO TransactionTable
SELECT 2,'Insert',Getdate()

-- Updating a records
UPDATE TransactionTable
SET TranType = 'Update',
TranDate = GETDATE()
WHERE TranId = 2

-- Deleting a record
DELETE FROM TransactionTable
WHERE TranId = 2

GO

-- Reviewing the data from tables
SELECT 
* 
FROM TransactionTable

SELECT 
* 
FROM TransactionHistory
GO

Finally the output comming for both testing of manually and self execution of stored procedure as captured following,

Hope you liked this post for setting stored procedure self execution with target queue.

New Logical functions coming in SQL Server Denali CTP3

2011-09-13T19:19:00.000+05:30

As i have started to learn new functions arrived by SQL Server Denali CTP3 version and i have posted some of them. You can learn Analytical functions , Conversion functions and String functions. You can also get the list of all the features coming in SQL Server 2011 CTP3.

Now diverting on this post for the new Logical functions which are following,

1. IIF :
It returns one of two arguments, depending on the evaluation of expression. It has require three arguments, first is condition, second and third are the values. Depending on the evaluation of first condition second or third values will be return, means if first condition is ture then it will return second value and if it is false then it will return third value. It returns the data type with the highest precedence from the types in true_value and false_value.

Let us evaluate it to know it better.

DECLARE @IsDone bit
SET @IsDone = 1
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Above code returns "Success". Now les us assign the NULL value to variable then verify.

DECLARE @IsDone bit
SET @IsDone = NULL
SELECT iif(@IsDone = 1,'Success', 'Failed')
GO

Here it returns "Failed" as output. Now elaborate it with more examples.

DECLARE @IsDone bit
DECLARE @FirstVal varchar(10)
DECLARE @SecondVal varchar(10)
SET @IsDone = 1
SET @FirstVal = NULL
SET @SecondVal = NULL

SELECT iif(@IsDone = 1,@FirstVal, @SecondVal)
GO

Above code returns NULL as resulted output. What happen if we pass directly NULL in both first and second values?

DECLARE @IsDone bit
SET @IsDone = 1

SELECT iif(@IsDone = 1,NULL, NULL)
GO

It comes with following error,

"Msg 8133, Level 16, State 1, Line 4
At lease one of the result expressions in a CASE specification must be an expression other than the NULL constant."

Now second turns come for CHOOSE function.

2. CHOOSE :
It returns the value at the specified index from among the lists. It has require first argument as Index and hen we can pass mutiple parameters for the values. It returns the data type with the highest precedence from the set of types passed to the function

Let elaborate it with sample examples.

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,'First','Second','Third')
GO

It returns "Second" value becuase it belong to second index.

DECLARE @Index int
SET @Index = 0

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Above query returns NULL as output.

DECLARE @Index int
SET @Index = NULL

SELECT CHOOSE (@Index,'First','Second','Third')
GO

Same as earlier query it also returns NULL as resulted output. What happen if we pass all the values with NULL ?

DECLARE @Index int
SET @Index = 2

SELECT CHOOSE (@Index,NULL,NULL,NULL)
GO

It also come up with NULL.

Hope you liked these functions. stay tuned for more posts.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" - Error encountered in SQL Server

2011-09-10T10:51:00.000+05:30

During development of stored procedures, i ran into one issue and this was due to not applied best practice of stored procedures practice and this issue comes mostly when we call stored procedures inside another one and so on, which are used in nested in transactions.

So i would like to write next post for the stored procedures development , as how we should write it, It will be a just a template of best practice. In this post i will explain the details of the issue which i suffered and how can we resolve it.

Let's for the demonstration we will created one table and stored procedures.

-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
  (
     TranId    INT NOT NULL PRIMARY KEY
     ,TranName VARCHAR(10)
  )

GO


-- Creating first stored procedure.
CREATE PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION

    -- Inserting records in table,
    -- Due to this insert error will be raised.
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION
      END TRY

      BEGIN CATCH
          ROLLBACK TRANSACTION

          PRINT 'Rollback Tran1'
      END CATCH
  END

GO


-- Creating Second stored procedure
CREATE PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

    -- Calling first created stored procedure here.
          EXEC Firttranproc

          COMMIT TRANSACTION
      END TRY

      BEGIN CATCH
          ROLLBACK TRANSACTION

          PRINT 'Rollback Tran2'
      END CATCH
  END

GO



-- Executing second stored procedure here and see what happen.
EXEC Secondtranproc

GO

In first stored we have written a code to raise error and due to transaction issue while running second stored procedure it raised error.

This is not best practice as we have write only "Begin Transaction". We should specify the name of the stored procedure.

# 1 Solution :

Let's specify the name of transactions in both of the stored procedures and altering stored procedures here and execute it again with same.

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON
   
   -- Here we have specified Tran1 as transaction name
 
   BEGIN TRY
          BEGIN TRANSACTION Tran1

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
   
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION Tran1
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran1'

          ROLLBACK TRANSACTION Tran1
      END CATCH
  END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

  -- Here we have specified Tran2 as transaction name

      BEGIN TRY
          BEGIN TRANSACTION Tran2

    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

          -- Calling first stored procedure here
          EXEC Firttranproc

          COMMIT TRANSACTION Tran2
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran2'

          ROLLBACK TRANSACTION Tran2
      END CATCH
  END

GO


-- Executing second stored procedure which will also call of SP1 
EXEC Secondtranproc

GO

You can see here the error is not raised which is raided in first case. Here stored procedure find and commit/rollback correct and their transactions only.

# 2 Solution :

Le's look another alternative method which can also help to come out from issue.

The rewritten stored procedures,

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
      SET NOCOUNT ON
   
   -- Here we have specified Tran1 as transaction name
 
   BEGIN TRY
          BEGIN TRANSACTION 

          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
   
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'

          COMMIT TRANSACTION 
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran1'

   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION 
      END CATCH
  END

GO

-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON

      BEGIN TRY
          BEGIN TRANSACTION 

    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'

          -- Calling first stored procedure here
          EXEC Firttranproc

          COMMIT TRANSACTION 
      END TRY

      BEGIN CATCH
          PRINT 'Rollback Tran2'

    
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION 
      END CATCH
  END

GO


-- Executing second stored procedure which will also call of SP1 
EXEC Secondtranproc

GO

Here is same output as second case , it is also not raised error.

Let me know what you think over here. You also encountered this issue in past, Please share you experience and alternation solution.