Feeds:
Posts
Comments

Posts Tagged ‘SQL’

SQL SERVER 2012- How to get the current value from a sequence object

Posted in Solutions, SQL SERVER, tagged , , , , on November 6, 2013| Leave a Comment »

Today, I received a query from one of my blog readers asking how to get the current value from a sequence ? He also mentioned to me that he usually uses given below (Database console command) script to get the current value from identity column.

DBCC CHECKIDENT ('Table Name', NORESEED)

So, do we have any DBCC command like this to get the current value from sequence object? Basically we do not have any DBCC command to get the current value from sequence same like identity but we do have work around to find it. Given below are the two methods to get the current value from any sequence object.

METHOD 1 :
In this method, you need to open SQL Server Management Studio and select the particular database and further select the sequence object in which current value is required. Then right click on it and browse its property to view the current value as shown in the image below.

sequence current value.1.1

sequence current value.1.2

METHOD 2 :
In this method, you need to open a NEW query window in SSMS and write the given below script to get the current value of sequence using sys.sequences  (A new system view shipped in SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT current_value FROM sys.sequences
WHERE [name]='Seq_sample'
--OUTPUT

sequence current value.1.3

Read Full Post »

SQL SERVER – How to remove extra spaces from string value

Posted in Solutions, SQL SERVER, tagged , , , on November 4, 2013| 1 Comment »

How to remove extra spaces from string value is a common issue and we usually come across this issue while massaging the data. I used this solution as a part of a solution in one of my earlier solutions.

Let me create a sample to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create table
CREATE TABLE tbl_sample
(
[ID] INT,
[NAME] VARCHAR(100)
)
GO
--Insert few records in the table
--Note : Names have been taken from adventureworks2012 database.
INSERT INTO tbl_sample VALUES (1,'Terri    Lee          Duffy')
INSERT INTO tbl_sample VALUES (2,'Roberto     Tamburello')
INSERT INTO tbl_sample VALUES (3,'Rob   Walters')
INSERT INTO tbl_sample VALUES (4,'Gail   A        Erickson')
INSERT INTO tbl_sample VALUES (5,'Gigi      N      Matthew')
GO
--Browse table
SELECT
[ID]
,[NAME] AS [String with extra spaces]
FROM tbl_sample
--OUTPUT

remove extra spaces.1.2

SOLUTION 1 : Using REPLACE(string) Function
In this solution, we need to use the built-in function REPLACE to remove extra spaces from string value.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[NAME] AS [String with extra spaces]
,REPLACE(REPLACE(REPLACE([NAME]
,CHAR(32),'()'),')(',''),'()',CHAR(32))
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

SOLUTION 2 : Using User Defined Function
In this solution, we need to create a User Defined Function to remove extra spaces from string using XML.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
(
@String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Extra_Space VARCHAR(MAX)
DECLARE @delimiter VARCHAR(5)

SET @delimiter=' '
SET @Xml = CAST(('<a>'+REPLACE(@String,@delimiter,'</a><a>')+'</a>')
AS XML)

;WITH CTE AS (SELECT
A.value('.', 'VARCHAR(MAX)') AS [Column]
FROM @Xml.nodes('A') AS FN(A))

SELECT @Removed_Extra_Space=REPLACE(
Stuff((
SELECT ';' + A.[Column]
FROM CTE A
WHERE ISNULL(A.[Column],'') <>''
FOR XML PATH('')),1,1,''),';',' ')

RETURN (@Removed_Extra_Space)
END
GO

SELECT
[ID]
,[NAME] AS [String with extra spaces]
,dbo.[UDF_Remove_Extra_Space_From_String] ([Name])
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

remove extra spaces.1.1

Read Full Post »

SQL SERVER 2012 – How to get a value from next result row effectively

Posted in Solutions, SQL SERVER, tagged , , , , , on November 3, 2013| Leave a Comment »

Today, I was developing a customer analysis report, basically trying to find out customer’s trend of purchasing. To get the trend I need to get his next row (purchasing) result set in the current row for comparison purposes.  Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution, using LEAD (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] int,
[Levels] varchar(50)
)
GO

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

get the previous row result.1.1

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,B.Levels AS [Next Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID+1=B.ID
ORDER BY A.ID
GO
--OUTPUT

get value from next row

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LEAD function and it will calculate the next result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,LEAD(A.levels,1,0) OVER (ORDER BY A.ID) AS [Next Level]
FROM tbl_sample A
GO
--OUTPUT

get value from next row

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.

Read Full Post »

SQL SERVER – How to find a row that contains a lower case letter

Posted in Solutions, SQL SERVER, tagged , , , on November 1, 2013| 1 Comment »

A few days ago, I was working on list of customers and all the customers’ names were in upper case. Coincidentally, I found one customer having upper and lower case in his name. So, I thought of checking the entire customer list if anyone was having name in upper and lower case.
Note : The database is not case sensitive.

Let me create a sample to demonstrate the solution.

USE tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Name] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RARESQL.COM')
INSERT INTO tbl_sample VALUES (2,'RaReSql.com')
INSERT INTO tbl_sample VALUES (3,'Raresql')
INSERT INTO tbl_sample VALUES (4,'raresql.com')
GO
SELECT * FROM tbl_sample
--OUTPUT

lowercase.1.1

SOLUTION 1 : Using UPPER (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample WHERE
UPPER([NAME]) COLLATE Latin1_General_CS_AS !=[NAME]
--OUTPUT

lowercase.1.2

SOLUTION 2 : Using PATINDEX (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample
WHERE PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
,[NAME] COLLATE Latin1_General_CS_AS)>0
GO
--OUTPUT

lowercase.1.2

SOLUTION 3 : Using ASCII(String) Function
In this solution, it will not only give you the rows having lower case letter but it will also give you what lower characters are there in those rows. Given below is the script.

USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Extract_small_letters_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Extract_small_letters_From_String]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_STRING=ISNULL(@RETURN_STRING,'')
+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String)
AND ASCII(SUBSTRING(@String,Nums.n,1)) BETWEEN 97 AND 122

RETURN @RETURN_STRING
END
GO

SELECT *,dbo.[UDF_Extract_small_letters_From_String]([NAME])
As [Lower cases letters]
FROM tbl_sample
--OUTPUT

lowercase.1.3

Read Full Post »

SQL SERVER – Fix – Error – Agent XPs component is turned off

Posted in Errors, SQL SERVER, tagged , , , on October 30, 2013| 2 Comments »

Today I was working on Maintenance Plans of SQL Server and I had to schedule a database backup on daily basis for a client. But once I right clicked on Management >> Maintenance Plan and selected New Maintenance Plan using SSMS, it gave me given below error.

Let’s discuss this in detail:

Error Message: ‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online. (Microsoft.SqlServer.Management.MaintenancePlanWizard)

Agent Xps component is turned off.1.1

Reason:

Basically, Agent XPs option enables the SQL Server Agent extended stored procedures on the SQL Server and the reason why this error occurs is because Agent XPs is not enabled in SQL Server. Also, if this option is not enabled, SQL Server Agent node will not display in SQL Server Management Studio Object Explorer.

Resolution:
There are two methods to resolve it.

Method 1:
In this method, we will execute the script to enable Agent XPs. You need to execute the given below script in Query window.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Agent Xps component is turned off.1.2

Method 2:
In this method, you do not need to write any script, you just need to open the SQL Server configuration Manager and start the SQL Server Agent as shown in the image below. It will automatically enable Agent XPs.

Agent Xps component is turned off.1.3

Note : There is no need to stop and restart the server, these settings take immediate effect.

Read Full Post »

SQL SERVER – How to remove the trailing characters from number

Posted in Solutions, SQL SERVER, tagged , , , on October 28, 2013| Leave a Comment »

Few days earlier, I was working on a legacy application data and found that some characters were merged with the numbers in a column of a table and the issue was, I had to get only the numbers from this column.
Given below are the samples which were available in the column.
129 mph
550 kmp

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

USE tempdb
GO
--Create a table
DECLARE @tbl_sample TABLE
(
ID INT,
Varchar_col VARCHAR(10)
)
--Insert sample records in the table
INSERT INTO @tbl_sample VALUES (1,'12.9 mph')
INSERT INTO @tbl_sample VALUES (2,NULL)
INSERT INTO @tbl_sample VALUES (3,'45')
INSERT INTO @tbl_sample VALUES (4,'90 mph')

--Browse the table
SELECT * FROM @tbl_sample

remove trailing characters.1.1

SOLUTION :
Given below is the solution using string manipulation function.

SELECT
ID
,Varchar_col
,LEFT(Varchar_col
,DATALENGTH(Varchar_col)-
(PATINDEX('%[0-9]%',REVERSE(Varchar_col))-1)) AS [Number]
FROM @tbl_sample

remove trailing characters.1.2

Let me know if you came across this situation and resolved it with the different solution.

Read Full Post »

SQL SERVER 2012 – How to find if a Sequence exist in database

Posted in Solutions, SQL SERVER, tagged , , , , , on October 26, 2013| Leave a Comment »

I came across this query many times whenever I was working on SEQUENCE object (A new object introduced in SQL Server 2012). Because whenever you need to use any sequence object in any piece of code, make sure it exists.

Given below are the two methods, you can use to check the existence of the sequence object.

METHOD 1 :
This is the preferred method because, it will not only check the existence of the sequence object but also it will give you the details about the sequence object.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--Do not forget to change you sequence object name
--in the below query.
SELECT * FROM sys.sequences
WHERE object_id=object_id('Seq_sample')
GO
--OUTPUT

existance of sequence object.1.1

METHOD 2 :
This is the same method we have been using since the earlier version of SQL Server. In this method, we use one of the renowned systems
object catalog view namely sys.objects. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--Do not forget to change you sequence object name
--in the below query.
SELECT * FROM sys.objects
WHERE object_id=object_id('Seq_sample')
AND [TYPE]='SO'
--OUTPUT

existance of sequence object.1.2

Read Full Post »

SQL SERVER 2012 – Fix – Error – 11730- Database name cannot be specified for the sequence object in default constraints.

Posted in Errors, SQL SERVER, tagged , , , , , on October 24, 2013| Leave a Comment »

I usually create the samples and test cases on tempdb database. Today, I came a across an issue while working on Sequence object, a new database feature shipped with SQL Server 2012. I tried to use Sequence object from some other database in tempdb database and it gave me given below error message.

Let’s discuss this in detail:

Message Number: 11730

Severity : 16

Error Message: Database name cannot be specified for the sequence object in default constraints.

Error Generation:

In the given below script, I tried to make sequence object (resides in another database (Adventureworks2012)) as a default value of a column in a table. This table resides in tempdb database. Let me create a sample to demonstrate the issue.

--This script is compatible with SQL Server 2012 and above
--Create a sequence object in AdventureWorks2012 database.
USE [AdventureWorks2012]
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [int]
START WITH 1
INCREMENT BY 1
GO

USE tempdb
GO
CREATE TABLE dbo.tbl_sample
(
[ID] int,
[Name] varchar(100)
)
GO

ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_tbl_sample DEFAULT
(NEXT VALUE FOR AdventureWorks2012.[dbo].[Seq_Sample])
FOR [ID];
GO
--OUTPUT

Msg 11730, Level 15, State 1, Line 2
Database name cannot be specified for the sequence object in default constraints.

Ooopps…… I am unable to make a sequence as a default value in a table if Sequence already resides in another database.

Resolution:
This is the limitiation of a sequence object, that you cannot make any sequence as a default value of a column if it resides in any other database. But there is a way around that you can use to achieve it. So, how can we do it ? You cannot make it as a default but you can still access the sequence even if it is outside your database boundary, using three part name (AdventureWorks2012.[dbo].[Seq_Sample]
). So, using this three part name, you can insert the sequence value in any database. Given below is the sample.

USE tempdb
GO
CREATE TABLE dbo.tbl_sample
(
[ID] int,
[Name] varchar(100)
)
GO
INSERT INTO tbl_sample
VALUES (NEXT VALUE FOR AdventureWorks2012.[dbo].[Seq_Sample],'raresql')
GO

Read Full Post »

How to restore SQL SERVER 2012 backup on earlier versions of SQL Server

Posted in Solutions, SQL SERVER, tagged , , , , , on October 23, 2013| 2 Comments »

‘Can you restore SQL Server 2012 backup in earlier versions of SQL Server?’ I came across this question many times from the community and the simple answer is NO. You cannot restore a new version of SQL Server backup in earlier versions. However, there is a way around that can be followed to achieve it. Basically, you can generate the script of all objects along with the data using SQL Server 2012 with any earlier versions compatibility and run those scripts on earlier versions of SQL Server. This is how you can get the complete data with schema from SQL Server 2012 to earlier versions.

Problem :
Before proceeding with the solution, I would like to show you the error, that happens when you restore a higher versions of
SQL Server database backup to an earlier version of SQL Server. Given below is the image.

erromessage33241.1

The media family on device ‘E:\DBBackup\AdventureWorks2012_Backup\AdventureWorks2012_Backup’ is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

SOLUTION :
Let me proceed with the solution, step by step.

Step 1:
Open SSMS and right click on the database that you want to restore in earlier versions of SQL Server and then select Task and then further click on Generate Script …
Given below is the image.
Restore SQL Server 2012 backup on earlier versions.1.1

Step 2 :
Given below is the informational screen, that will guide you how to generate the scripts along with the steps. Press NEXT button.
Restore SQL Server 2012 backup on earlier versions.1.2

Step 3 :
In this step, you will get two options. Given below are the details.

  1. Script entire database and all database objects
  2. Select specific database objects

Option 1 is preferred because you can generate all database objects altogether from it. However if you upgrade any object using any new features of SQL Server 2012, then you should select Option 2 and exclude that particular object and press NEXT button. Given below is the screen image.

Restore SQL Server 2012 backup on earlier versions.1.3-1

Step 4 :
In this option you need to select the script file location to save all the scripts in a particular file. Rest of the options you can leave as default. Once you are done with the file location, you can see an ADVANCE button. Click on this button. It will open the advance option for the scripting.
Restore SQL Server 2012 backup on earlier versions.1.4

Step 5 :
Once you are inside the Advance option, you need to select further two options. Given below are the details.

  1. Script for SQL Server version
  2. Type of data to script.

In the Option 1 make sure that you have selected the right earlier Version of SQL Server, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.5

In the Option 2, make sure that you have selected Schema and data, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.6-1

Once you configure both options, press OK and press Next in the previous screen, as shown below.

Restore SQL Server 2012 backup on earlier versions.1.7

Step 6
Once you press NEXT button, it will take you to the summary screen where you can see all your configuration altogether.
Again press NEXT button to proceed, as show in the image below.

Restore SQL Server 2012 backup on earlier versions.1.8

Step 7
In this step, SQL Server will automatically script all your selected object along with the data. And will give you the status report, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.9

Restore SQL Server 2012 backup on earlier versions.1.10

Step 8 :
Now you need to browse the file you saved in particular location in the Step 4 and Execute it in selected earlier version of SQL Server.

Let me know if you came across this problem and resolved it in a different manner.

Read Full Post »

SQL SERVER – How to implement conditional where clause

Posted in Solutions, SQL SERVER, tagged , , , on October 8, 2013| 2 Comments »

Few days ago, I received a query from one of my blog readers inquiring how to implement a conditional where clause in a SQL Server query / stored procedure. His requirement was quiet simple. He said that if he passes any filter value to the query / procedure, it should consider that filter otherwise query should ignore that filter and return all records.

Given below is the script.
SAMPLE :

USE AdventureWorks2012
GO
--DROP PROCEDURE Usp_sample
--GO
CREATE PROCEDURE Usp_sample
@City nvarchar(30),
@CountryRegionName nvarchar(50)
AS

SELECT *
FROM
HumanResources.vEmployee
WHERE (1=(CASE WHEN @City IS NULL THEN 1 ELSE 0 END) Or [City]=@City)
AND (1=(CASE WHEN @CountryRegionName IS NULL THEN 1 ELSE 0 END)
Or [CountryRegionName]=@CountryRegionName)
GO

EXAMPLE 1 : (WITHOUT any filter)
In this example, I did not pass any filter to the stored procedure, so the stored procedure ignored all filters and it returned all records as expected.

USE AdventureWorks2012
GO
EXEC Usp_sample @City=NULL,@CountryRegionName=NULL
GO

EXAMPLE 2 : (With ONE filter)
In this example, I passed only one filter to the stored procedure and it returned records on the basis of only one filter and ignored the second filter.

USE AdventureWorks2012
GO
EXEC Usp_sample @City='Renton',@CountryRegionName=NULL
GO

Read Full Post »

« Newer Posts - Older Posts »