Feeds:
Posts
Comments

Archive for October, 2013

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 »

SQL SERVER 2012 – How to check Sequence has reached its minimum or maximum value

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

Recently I came across an issue related to the sequence object (shipped in SQL Server 2012) because usually we generate the next ID from the sequence object without checking whether it has reached its limit and it cannot generate a new ID. Due to this it generates an error as well. Given below are the error details.

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘Seq_Sample1’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Sequence object maintain its minimum and maximum value by default.

So what is the solution ? But before proceeding with the solution let me create a sample.

SAMPLE :

--This script is compatible with SQL Server 2012 and above.
--Create a sequence object
USE [AdventureWorks2012]
GO
--DROP SEQUENCE [dbo].[Seq_Sample1]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample1]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
GO
--Generate sequence number up to 4 ID
--because it has a maximum limit of 4.
SELECT Next value for [dbo].[Seq_Sample1]
GO 4

SOLUTION 1 : (Not recommended)
In this solution, you need to check the minimum and maximum value of the sequence with the current value and if it matches either of them, it means that sequence has been reached to its minimum or value and you cannot generate a new ID. It is not recommended because it is a lengthy procedure. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @minimum_value As sql_variant
DECLARE @maximum_value As sql_variant
DECLARE @current_value As sql_variant

SELECT
@minimum_value=[minimum_value]
,@maximum_value=[maximum_value]
,@current_value=[current_value]
FROM
sys.sequences AS seq
WHERE name ='Seq_Sample1'

SELECT IIF(@minimum_value=@current_value
OR @maximum_value=@current_value
,'You cannot generate a new ID'
,'You can generate a new ID') AS [Result]
--OUTPUT

sequence limit.1.1

SOLUTION 2 : Using Is_exhausted property (Recommended)
In this solution, we will use a sequence property namely “is_exhausted” and can quickly check whether it reached its limit or not.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT IIF((SELECT is_exhausted FROM sys.sequences AS seq
WHERE name ='Seq_Sample1')=1
,'You cannot generate a new ID'
,'You can generate a new ID')
AS [Result]
--OUTPUT

sequence limit.1.1

CONCLUSION :
As you can see, both solutions generate the same output but solution 2 is recommended because we used a property namely “is_exhausted” and reduced the size of the script.

Read Full Post »

SQL SERVER – How do I get only the numbers before the decimal?

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

I came across this query today, when I was generating some reports and I do not want to show the decimals in the report. I did some research and came up with multiple solutions. Before proceeding with the solution, let me create a sample to demonstrate the solution.

Sample :

USE Tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[Col_ID] INT,
[Col_Decimal] decimal(18,4)
)
GO
INSERT INTO tbl_sample VALUES (1,12345.9876)
INSERT INTO tbl_sample VALUES (2,-12345.9876)
INSERT INTO tbl_sample VALUES (3,123.45)
INSERT INTO tbl_sample VALUES (4,12.00)
GO

Given below are the solutions.

Solution 1 : (Without any function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, [Col_Decimal] - ([Col_Decimal]%1) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.1

Solution 2 : (Using ROUND function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ROUND([Col_Decimal],0,1) As [Col_Number]
FROM tbl_sample
GO
--OUPUT

get the number before decimals.1.2

Solution 3 : (Using CAST function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, CAST([Col_Decimal] AS INT) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.3

Read Full Post »