Feeds:
Posts
Comments

Posts Tagged ‘SQL Server’

SQL SERVER – Fix – Error – 10920 – Cannot %S_MSG user-defined function ‘%.*ls’. It is being used as a resource governor classifier.

Posted in Errors, SQL SERVER, tagged , , , , on December 22, 2013| Leave a Comment »

A Few days ago, I was working on resource governor implementation on my test server. Once I implemented & tested, I had to delete the resource governor settings from the test server. But Unfortunately, I got stuck because of the given below error. It did not allow me to delete the resource governor properly. Let me explain the error and its solution in details.

Message Number: 10920

Severity : 16

Error Message: Cannot %S_MSG user-defined function ‘%.*ls’. It is being used as a resource governor classifier.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

 DROP FUNCTION dbo.rgclassifier_MAX_CPU

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function ‘rgclassifier_MAX_CPU’. It is being used as a resource governor classifier.

Ooopps…… I am unable to drop the above classifier function.

Resolution:
If you look at the error description, it seems that classifier function is in use of resource governor, so you cannot drop this function. Then I thought of disabling it and tried to drop after that but I could not :(.

So what is the solution? Basically, you need to assign all new sessions to the default workload by eliminating the classifier function from the resource governor configuration of your server. When there is no user defined classifier function assigned as a classifier function, you can easily drop the classifier function. Given below is the script.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Now, you can easily drop the classifier function.


DROP FUNCTION dbo.rgclassifier_MAX_CPU

Read Full Post »

SQL SERVER – How to get only the numbers after the decimal?

Posted in Solutions, SQL SERVER, tagged , , , , , on December 16, 2013| 2 Comments »

I was working on an SQL server tool and got stuck in a place where I had to split the the numbers into two parts, first should be before the decimal part and second should be after the decimal part. In my earlier articles, I already wrote how to get the numbers before the decimal. In this article, I will share, how to get the numbers after the decimals.

Let me create a sample, before proceeding with 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.90)
GO

Given below are the solutions.

Solution 1 : (Without any function)

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

number after decimal.1.1

Solution 2 : (Using CAST & ABS function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ABS([Col_Decimal]) - CAST(ABS([Col_Decimal]) AS INT)
As [Col_After_decimal]
FROM tbl_sample
GO
--OUPUT

number after decimal.1.1

Read Full Post »

SQL SERVER – How to use a temp table in a table-valued function

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

Local and global temporary tables play a vital role in the SQL Server scripting. We generally use it to store temporary values for further manipulation. But unfortunately, you cannot use it inside the user defined function.

PROBLEM :
If you use temporary tables in the function, you will get the below error message.

Msg 2772, Level 16, State 1, Procedure ufnGetEmployeeInformation, Line 18
Cannot access temporary tables from within a function.

So, what is the solution?

SOLUTION :
To resolve this, you need to use table variables instead of temporary tables. You can easily accommodate it in the user defined function for further manipulation.
Given below is a sample.

--This script is compatible with SQL Server 2005 and above.
--DROP FUNCTION [dbo].[ufnGetEmployeeInformation]
--GO
USE AdventureWorks2012
GO
CREATE FUNCTION [dbo].[ufnGetEmployeeInformation]
(@BusinessEntityID int)
RETURNS @EmployeeInformation TABLE
(
[BusinessEntityID] int,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[JobTitle] [nvarchar](50) NULL,
[City] [nvarchar](30) NULL,
[Return Time] [datetime] NULL
)
AS
BEGIN
INSERT INTO @EmployeeInformation
SELECT
[BusinessEntityID],
[FirstName],
[LastName],
[JobTitle],
[City],
getdate()
FROM [HumanResources].[vEmployee]
WHERE
BusinessEntityID=@BusinessEntityID

RETURN;
END
GO

EXAMPLE :

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT * FROM [dbo].[ufnGetEmployeeInformation](124)
--OUTPUT

use temporary table in function.1.1

Read Full Post »

SQL SERVER – How to select minimum value from group in a table

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

Sometimes, you need to define window frame within the table on the basis of certain criteria, to pick up some specific data. Today, I was developing one report for my customer and the requirement was very simple, to find the people with minimum wage in each department. We do have the solution using RANK(ROW_NUMBER) function. However, we can achieve it using FIRST_VALUE function (Shipped with SQL Server 2012) as well.

Let me create a sample to demonstrate the script.

--This script is compatible with SQL Server 2005 and above.
--Create table
Create Table [Employee]
(
[Employee ID] int,
[Employee Name] varchar(50),
[Department] varchar(50),
[Salary] int
)

GO
--Insert records into table
Insert Into [Employee] Values (1,'Ali','IT',10000)
Insert Into [Employee] Values (2,'Derek','IT',6000)
Insert Into [Employee] Values (3,'Jack','IT',9000)
Insert Into [Employee] Values (4,'Simon','Marketing',5000)
Insert Into [Employee] Values (5,'Monica','Marketing',7500)
Insert Into [Employee] Values (6,'Sandra','Marketing',8000)
GO

Given below are the solutions :

SOLUTION 1 :
This is an old method and we have been using it since SQL Server 2005. We use a Ranking Function namely ROW_NUMBER in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
;With CTE As
(
Select
Row_Number() Over (Partition By Department Order By Salary) As [S.No]
,[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
From dbo.[Employee]
)
Select
[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
from CTE
Where [S.No]=1
--OUTPUT

minimum value in the group.1.1

SOLUTION 2 :
We will use a new analytic function namely FIRST_VALUE in this solution to find the minimum value in the group. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
;WITH CTE As
(
Select
[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
,FIRST_Value([Salary]) Over
(Partition By [Department] Order By [Salary]) As [minimum Value]
From dbo.[Employee]
)
Select
[Employee ID]
,[Employee Name]
,[Department]
,[Salary]
From CTE
Where [Salary]=[minimum Value]
GO
--OUTPUT

minimum value in the group.1.1

Read Full Post »

SQL SERVER – How to filter tables in sp_MSforeachtable

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

Today, I was working on customer’s test database and I wanted to delete all the tables having “_temp” in the names. Even though I could manually write the script by using a table at a time, I was looking for a shortcut. I have been using sp_MSforeachtable on test databases for multiple purposes. But as far as I know, it performs the specified operation in all tables in the particular database. So I started doing my research. First of all, I searched if there was any parameter available in the sp_MSforeachtable that we can use to filter tables objects and I found one parameter namely @whereand. Given below is the script to view the available parameters in sp_MSforeachtable.

EXEC sp_help sp_msforeachtable
--OUTPUT

sp_msforeachtable_conditional.1.1

SOLUTION :
You need to use the @whereand parameter to filter any table object in sp_MSforeachtable (Undocumented system stored procedure).
Given below is a script, where I will select table contains “_temp” in the name.

USE tempdb
GO
EXEC sp_msforeachtable
@command1 ='SELECT * FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%_temp%'')'

Note : It is not recommended to use sp_MSforeachtable on production databases.

Read Full Post »

SQL SERVER – Fix – Error – 1011 – The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Posted in Errors, SQL SERVER, tagged , , , , on December 6, 2013| 1 Comment »

In SQL Server, alias are very helpful, because you do not need to write the long table names again and again. I usually use ALIAS in my development to speed up the work. Yesterday, I was working on a complex query and I used a lot of alias to minimize the line of code and finally I came across an error.

Let’s discuss this in detail:
Message Number: 1011

Severity : 16

Error Message: The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO

SELECT
p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,p.[JobTitle]
FROM [HumanResources].[Employee] p
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]

GO

Msg 1011, Level 16, State 1, Line 2
The correlation name ‘p’ is specified multiple times in FROM clause.

Ooopps…… I am unable to execute the above qquery.

Resolution:
The resolution is very simple. Basically, you aliased more than one table with the same name, so SQL Server is unable to relate alias with table name. In the above table, I ALIASED both tables with p while I can assign one name (p) as an alias to one table only and I must change the alias of other table name (p) to something else. (That make sense also). Lets rewrite the script with the different alias and it works…….

USE [AdventureWorks2012]
GO
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
GO
--OUTPUT

error_message_1011.1.1

Read Full Post »

SQL SERVER 2012 – How to check last modified date of Sequence Object

Posted in Solutions, SQL SERVER, tagged , , , , , on December 1, 2013| Leave a Comment »

I deployed some Sequence objects on production database and after couple of days, I was facing some problem with Sequence objects. It was little difficult to debug all sequence objects at once, so I thought of checking the sequence objects modified dates to find if I modified any Sequence object after deployment.

Given below are the two methods to find the modified dates of Sequence Object.

METHOD 1 : Using sys.sequence
Given below is the script to find the modified date of all sequence object using sys.sequences

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.sequences
GO
--OUTPUT

Sequence modified dates

METHOD 2 : Using sys.objects
Given below is the script to find the modified date of all sequence object using sys.objects

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.objects
WHERE [type] ='SO'
GO
--OUTPUT

Sequence modified dates

Read Full Post »

SQL Server – How to trim all columns in a table

Posted in Solutions, SQL SERVER, tagged , , , on November 28, 2013| 6 Comments »

Today, I was searching for some scripts from my scripts library and I came across one of my handy scripts that help me to trim all columns in a table.  You do not need to trim column by column. It will trim all the string columns at once. Given below is the script. I usually use it to clean / massage the data.

SOLUTION :

USE AdventureWorks2012
GO
--DROP PROCEDURE usp_trim_all_string_columns
--GO
CREATE PROCEDURE usp_trim_all_string_columns
@schema_Table_name VARCHAR(MAX)
AS
DECLARE @SQL AS VARCHAR(MAX)

SET @SQL=STUFF((SELECT ', ' + QUOTENAME([name])
+ ' = LTRIM(RTRIM(' + QUOTENAME([name]) + '))' FROM
sys.columns WHERE object_id=object_id(@schema_Table_name)
AND system_type_id IN(35,99,167,175,231,29)
FOR XML PATH('')),1,1,'')
PRINT @SQL

SET @SQL = 'UPDATE ' + @schema_Table_name + ' SET' + @SQL
PRINT @SQL

EXEC(@SQL)
GO

EXAMPLE :

USE AdventureWorks2012
GO
EXEC usp_trim_all_string_columns '[HumanResources].[Employee]'
GO

Read Full Post »

SQL SERVER – Mathematical Functions – LOG

Posted in Functions, SQL SERVER, tagged , , , , , on November 25, 2013| Leave a Comment »

In my earlier article, I wrote about a new major enhancement in LOG function in SQL Server 2012 and the enhancement is that you can pass any custom base value along with the expression to calculate the natural logarithm in this function. Recently, I received a query from Mr. Willy Van inquiring how to custom the base value in the LOG function in the earlier versions of SQL Server. After some research I finally developed a solution that can help you to pass custom base value along with the expression in the earlier versions of SQL Server.

Given below is user defined function that calculate the LOG with custom base value.

SOLUTION :

--DROP FUNCTION [dbo].[LOG]
--GO
CREATE FUNCTION [dbo].[LOG](@Number float,@Base bigint)
RETURNS float
AS
Begin
	DECLARE @Log float
	SET @Log = (log10(@Number)*2.302585093)/(log10(@Base)*2.302585093)
	RETURN @Log
End
GO



EXAMPLE :


SELECT 1 as [Number] , 10 as Base, dbo.[LOG](1,10) As [Log]
UNION ALL
SELECT 2 as [Number] , 15 as Base, dbo.[LOG](2,15) As [Log]
UNION ALL
SELECT 3 as [Number] , 30 as Base, dbo.[LOG](3,30) As [Log]
UNION ALL
SELECT 4 as [Number] , 45 as Base, dbo.[LOG](4,45) As [Log]
--OUTPUT
GO



custom base value in LOG


Reference : MSDN

					
Read Full Post »

SQL SERVER 2012 – How to make Sequence as default value for a column in a table

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

Sequence object is one of the new objects shipped with SQL Server 2012. I wrote multiple articles on its features / solutions in earlier articles. One of the major facts that Sequence object is not a table dependent like IDENTITY columns enables you to inset its value manually in any table. I have been asked in a forum whether we can make Sequence as a default value for a column in a table ? The answer is YES & NO.  YES because we have a work around to make it happen and NO because it is not implemented by default, like identity in a table.

So what is the work around to make SEQUENCE as a default for a column ?

Lets do it step by step.

Step 1 :
In this step, we need to create a sample table and a sequence object to demonstrate it.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE TABLE
USE tempdb
GO
CREATE TABLE dbo.tbl_sample
( [ID] VARCHAR(8) ,
[Product Name] varchar(50)
)
GO

--CREATE SEQUENCE
USE tempdb
GO
CREATE SEQUENCE dbo.Sample_Seq AS
INT START WITH 1
INCREMENT BY 1 ;
GO

Step 2 :
In this step, we need to create a default constraint on the above created table on [ID] column to make SEQUENCE as DEFAULT value for [ID] column.
Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT (NEXT VALUE FOR dbo.Sample_Seq) FOR [ID];
GO

Step 3 :
Lets insert few records into the table.

-- Insert records into the table
USE tempdb
GO
INSERT INTO dbo.tbl_sample ([Product Name])
VALUES ('SQL SERVER 2005'),('SQL SERVER 2008')
,('SQL SERVER 2012'),('SQL SERVER 2014')
GO

Step 4 :
Once the data has been inserted, you can browse the table and view the [ID] column data to check whether SEQUENCE became the DEFAULT value of this column or not.

--Browse Table
USE tempdb
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

sequence as a default.1.1

Read Full Post »

« Newer Posts - Older Posts »