Feeds:
Posts
Comments

Archive for the ‘SQL Server Solutions’ Category

Recently, I was developing a report for one of my customers, who migrated their database from SQL Server 2005 to SQL Server 2012 Enterprise Edition. The database belongs to a legacy system , so I found couple of conversion issues, however the major issue I faced in developing the report was the date format. The required date format was Mon-yyyy and the data available in the table was in the format of yyyymm.

Before proceeding with the solution, let me create a sample to demonstrate the problem.

USE tempdb
GO
DROP TABLE tbl_Sample
GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] varchar(6)
)
GO
INSERT INTO tbl_Sample VALUES (1,'201401')
INSERT INTO tbl_Sample VALUES (2,'201402')
INSERT INTO tbl_Sample VALUES (3,'201403')
INSERT INTO tbl_Sample VALUES (4,'201404')
INSERT INTO tbl_Sample VALUES (5,'201405')
INSERT INTO tbl_Sample VALUES (6,'201406')
INSERT INTO tbl_Sample VALUES (7,'201407')
INSERT INTO tbl_Sample VALUES (8,'201408')
INSERT INTO tbl_Sample VALUES (9,'201409')
INSERT INTO tbl_Sample VALUES (10,'201410')
INSERT INTO tbl_Sample VALUES (11,'201411')
INSERT INTO tbl_Sample VALUES (12,'201412')
GO
SELECT * FROM tbl_Sample
GO

Convert YYYYmm 1.1

Solution 1 : Traditional Method
This is the old traditional method, which is compatible with SQL Server 2005 and above. In this method, you need to use many functions (LEFT, DATENAME, CONVERT, YEAR) to convert yyyymm date format to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT
LEFT(DATENAME(MM,CONVERT(DATE,[Date]+'01')),3)
+ '-'
+ CONVERT(VARCHAR(4),YEAR(CONVERT(DATE,[Date]+'01'))) As [Mon-Year]
FROM tbl_Sample
GO

Convert YYYYmm 1.2

Solution 2 : New Method
This is the new method, which is compatible with SQL Server 2012 and above. In this script, I will use a newly shipped (in SQL Server 2012) function namely Format to reduce the line of script in order to convert yyyymm to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT FORMAT(CONVERT(DATE,[Date]+'01'),'MMM-yyyy') As [Mon-Year]
FROM tbl_Sample
GO

Convert YYYYmm 1.2

Advertisements

Read Full Post »

Conversion of Select statement result set into Insert statement is a very frequent activity that a DBA/Developer needs to create, mostly when they need to migrate small amount of data from one instance to another or from one environment to another. I recently created one of my customer’s new branch database from other branches database and came across this scenario. Fortunately, we do have a solution since SQL Server 2005 but it was very complicated specially when you need to do it for the tables as they have numerous columns. The reason I am writing this solution is that you can do it in few clicks in SQL Server 2012 and above.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.

Sample :
Given below is a select statement in which I modified the result set to demonstrate.

USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
      ,[Name] + ' Department' As [Name]
      ,[GroupName]
      ,Getdate() As [ModifiedDate]
   FROM [HumanResources].[Department]
GO

Convert select.1.1

Given below are the two solutions, one of them is traditional solution and another one you can use it in SQL Server 2012 and above.

Solution 1 : Using String concatenation (Traditional Method)
In this solution, you need to concatenate the result set of the Select statement in order to convert into Insert statement (with some modifications in the data). You need to make sure that single quotes(‘) are in proper locations. In addition, if the data in the result set does not belong to string data type you must convert into string data type to concatenate. In case, the table is having identity column, you must pass the column name in the INSERT STATEMENT as well with SET IDENTITY_INSERT. The reason why I DO NOT recommend this solution is because if you have more number of columns in the table, it takes more time for the development and debug as well.

USE [AdventureWorks2012]
GO
SELECT
'INSERT INTO tbl_sample (
 [DepartmentID],[Name],[GroupName],[ModifiedDate])
VALUES(' + CONVERT(VARCHAR(50),[DepartmentID])
+ ',''' + [Name] + ' Department'' ,'
+ ''''+ [GroupName] + ''','
+ ''''+ CONVERT(VARCHAR(50),GETDATE(),120) + ''')'
  FROM [HumanResources].[Department]
GO

Convert select.1.2

Solution 2 : Using Generate Script (New Method)
This method is applicable to SQL Server 2012 and above and you will find it quite simple. Let me explain this method using two simple steps.

Step 1 :
First of all, you need to develop a select statement like I did it in the sample based on your requirements and INSERT INTO A TABLE as shown below.

 USE [AdventureWorks2012]
GO
SELECT [DepartmentID]
      ,[Name] + ' Department' As [Name]
      ,[GroupName]
      ,Getdate() As [ModifiedDate]
  INTO [tbl_Department_Sample]  -- Result set inserted in a table
  FROM [HumanResources].[Department]
GO

Convert select.1.3

Step 2 :
Your select statement result set has been inserted into the table([tbl_Department_Sample]). Now, you just need to generate the script (data only) of the table ([tbl_Department_Sample]) using Generate Script feature in SQL Server 2012 and above.

Let me know if you come across these scenarios and their solutions.

Read Full Post »

Whenever we try to take an exclusive access to a database in order to perform any activity like backup restore, it usually fails if any process is still using the database. I received the given below error from my test database while I was restoring the database on it.

kill process.1.1

Opsssss, fortunately, we do have solution by killing the running process to take a lock and perform any activity using a management command namely KILL & a system view sys.sysprocesses. However, what encourages me to write this solution is that sys.sysprocesses will be removed from the future version of SQL Server. So what is the NEW solution ?

Given below is the script, that I developed using sys.dm_exec_sessions.

USE master
GO
DECLARE @SQL_String VARCHAR(MAX)
DECLARE @Database_id INT

--DO NOT forget to change database name in give below statement
SET @Database_id=DB_ID('AdventureWorks2012')
SET @SQL_String=''

SELECT @SQL_String=@SQL_String + 'KILL '
+ COALESCE(CONVERT(VARCHAR(5),[session_id])+ '; ','')
FROM sys.dm_exec_sessions
WHERE database_id =@Database_id

--PRINT @SQL_String
EXEC (@SQL_String)

The above statement kills all the process running on a specific database. But do not forget to CHANGE the database name.
Once the above statement executed successfully, you can perform any activity.

Read Full Post »

Earlier, I have written an article about how to take a backup automatically in place of manual backup. I received very good feedback about this post.

Pre-requisite :

Problem :

The problem is, once automatic backup would be scheduled, it will start piling up the back up in the storage and soon, you will be out of space. In this case, you delete the backup manually or automatically ? I prefer automatically. But how to configure it ?

Solution :
Let me explain the solution step by step :

Step 1 :
It would be the best, if you select the “Maintenance Cleanup Task” in Step 6 in the Pre-requisite article. However if you did not do it there, you can modify the same maintenance plan and do it.
First of all you need to select the same Maintenance plan that you have created in the earlier article and right click on it, in order to modify it, as shown below.

cleanup task.1.1

Step 2 :
Once you select modify, SQL Server Management Studio (SSMS) will take you to the design screen of the maintenance plan. Now you need to select “Maintenance Cleanup Task” from the toolbar available in the left hand and drag and drop it on the right hand design window as shown below.

cleanup task.1.2

Step 3 :
Once you drag and drop the “Maintenance Cleanup Task” in the design window of the maintenance screen on the right hand side, you need to select the backup up task. Once you select it, it will show a green arrow in the bottom. Just drag that green arrow and drop it at the head of “Maintenance Cleanup Task” as shown below. The reason behind to drag and drop the green arrow is, to develop a link between both task. In addition to this, this link will define the sequence as well, so that the backup process will be taken first and then cleanup task, in order to remove the old backup.

cleanup task.1.3

Step 4 :
It seems that configuration is done. However, I can see a red cross sign on “Maintenance Cleanup Task” and the reason behind it that we did not configure the “Maintenance Cleanup Task”.
So lets configure it. In order to configure it, you need to double click on “Maintenance Cleanup Task”, you will get “Maintenance Cleanup Task” property window. Here most of the stuff is self explanatory. However, bear in mind that extension must NOT be with “dot”. Generally it is “bak”, in order to delete backup files ONLY as shown below.

cleanup task.1.4

Step 5 :
Now, you are done with the configuration, just save the maintenance plan and close it. Now it will automatically take the backup and delete the older files defined in step 4.

Let me know, how you clean your older backup files.

Read Full Post »

How to alter an existing computed column in a table is usually a trouble making statement because in computed column case we also try to implement a normal alter statement as shown below and end up with the error message. However, it is a usual perception that you can alter any computed column with the help of SQL Server management Studio (SSMS) without dropping it.

ALTER TABLE dbo.Products
ALTER COLUMN InventoryValue As (QtyAvailable * UnitPrice)
--OUTPUT

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘As’.

Frankly speaking there is no way you can alter any computed column without dropping it. Given below are the two methods to alter the computer column. In addition to this, I will discuss, why you do not need to drop computed column if you alter it through SSMS. Before I embark on the solution, I would like to create a sample to demonstrate the solution.

Sample :

USE tempdb
GO
DROP TABLE dbo.Products
GO
CREATE TABLE dbo.Products
(
    ProductID int IDENTITY (1,1) NOT NULL
  , QtyAvailable smallint
  , UnitPrice money
  , InventoryValue AS QtyAvailable --* UnitPrice
);

-- Insert values into the table.
INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;

--OUTPUT

alter computed column.1.1
As you can see, I deliberately made a mistake in the formula of computed column. Lets correct it using given below methods.

METHOD 1 : USING T-SQL
In this method, we need to first drop the computed column and then add it as a new column to accommodate the altered computed column in any table. Given below is the script.

USE tempdb
GO
ALTER TABLE dbo.Products
DROP COLUMN InventoryValue

ALTER TABLE dbo.Products
ADD InventoryValue AS QtyAvailable * UnitPrice

Once you drop and recreate the computed column, lets browse and check it again.

USE tempdb
GO
-- Display the rows in the table.
SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
FROM dbo.Products;
--OUTPUT

alter computed column.1.2

METHOD 2 : USING SQL SERVER MANAGEMENT STUDIO
As I mentioned above, it is a general perception that if you alter computed column from SSMS, you do not need to drop it, it alters computed column automatically. In this article, I will show you how it works, step by step.

Step 1 :
Browse the table and select the computed column you need to modify using SSMS and right click on it and further select modify as shown below.

alter computed column.1.3

Step 2 :
Once you select modify, SSMS will open the table in the design view. Now you need to go to the column properties (that will be available at the bottom right) and edit the formula as shown below.

alter computed column.1.4

Step 3 :
Now, it seems that we just need to press the save button and SSMS will alter the computed column without dropping it. But hang on a second, instead of pressing the save button, press the generate script button to see what changes SSMS will implement in case of pressing save button. However, once you press generate script button it will pop up save change script window to show you the changes as shown below.

alter computed column.1.5

Given below is the complete script I copied from the generate script window for your reference that will be implemented once you press the save button. As you can see, SSMS  will create a temporary table with the correct computed column formula, then migrate all the data and drop the old table and rename the temporary table to the old table name. And in this case, it drops the computed column and table as well.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Products
	(
	ProductID int NOT NULL IDENTITY (1, 1),
	QtyAvailable smallint NULL,
	UnitPrice money NULL,
	InventoryValue  AS ([QtyAvailable]* [UnitPrice])
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Products SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_Products ON
GO
IF EXISTS(SELECT * FROM dbo.Products)
	 EXEC('INSERT INTO dbo.Tmp_Products (ProductID, QtyAvailable, UnitPrice)
		SELECT ProductID, QtyAvailable, UnitPrice FROM dbo.Products WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Products OFF
GO
DROP TABLE dbo.Products
GO
EXECUTE sp_rename N'dbo.Tmp_Products', N'Products', 'OBJECT'
GO
COMMIT

Conclusion :
As you can see in both methods, you need to drop and recreate the computed column in order to alter it. In addition to this, the perception that “if you alter the computed column via SSMS, it alters it without dropping it” was proved wrong.

Read Full Post »

It is a very common scenario when you need to match a source table and a target table to find the missing (mismatch) rows across the result sets. We usually do it for multiple purposes specially at the time of audit or data migration etc.

Problem :
We usually use logical operators (NOT IN, Exists) to find the difference between two result sets. However, if you need to match each row by each row & each column by each column, it would be very difficult to achieve it via above mentioned logical operators.

Sample :
Let me create a sample to demonstrate the problem and solution.

--This script is compatible with SQL Server 2005
USE [AdventureWorks2012]
GO
--Create a sample table
CREATE TABLE [HumanResources].[Department_Sample](
	[DepartmentID] [smallint] NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[GroupName] [dbo].[Name] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
) ON [PRIMARY]
GO

--Insert records into the sample table from actual table
INSERT INTO [HumanResources].[Department_Sample]
SELECT * FROM [HumanResources].[Department]
GO

--Update a single record in the sample table
--to differentiate it from actual
UPDATE [HumanResources].[Department_Sample]
SET [Name] = 'Business Development'
WHERE DepartmentID=2

--Insert a record in the original table
INSERT INTO [HumanResources].[Department]
           ([Name]
           ,[GroupName]
           ,[ModifiedDate])
     VALUES
           ('IT'
           ,'Executive General and Administration'
           ,GETDATE())
GO
--Delete a record from the sample table
DELETE FROM [HumanResources].[Department_Sample]
WHERE [DepartmentID]=11
GO
SELECT * FROM [HumanResources].[Department]
GO
--OUTPUT

find missing result set.1.1

Solution :
As mentioned above, I created a sample table, copied data from original table in it and then modified & inserted the data in the sample table & original table respectively. Now, in order to find the the missing (mismatched) records across the result set, we need to use EXCEPT operator. Given below is the script that will ONLY show the missing rows in the sample table (Target Table).

--This script is compatible with SQL Server 2005
USE AdventureWorks2012
GO
--Source Table
SELECT * FROM [HumanResources].[Department]
EXCEPT
--Target table
SELECT * FROM [HumanResources].[Department_Sample]
GO
--OUTPUT

find missing result set.1.2

Conclusion :
Whenever you need to find the missing (mismatch) row across the result sets, always use EXCEPT operator to make your life easier.

Read Full Post »

Last Sunday, my support team received a backup from one of our customers and customer requested some activities for their backup. Once we saved the backup it was named testing backup.  The problem was, we deployed lots of databases for different applications at this client and we did not know that which database backup is he referring to. First of all it is not a best practice to name a backup like this, it must be proper naming conversion. However if you come across situation like this, there is a workaround to find the details about the backup without restoring it. But how ?

Given below is the script that will give you the details about the backup without restoring it to any database.

--This script is compatible with SQL Server 2005 and above.
USE master
GO
RESTORE FILELISTONLY
FROM DISK = N'C:\data\Testing Backup.bak'
GO

metadata of backup.1.1

In addition to the above information, if you need further details about the backup without restoring, you can use RESTORE HEADERONLY & RESTORE LABELONLY

Reference : MSDN

Read Full Post »

Older Posts »