Feeds:
Posts
Comments

Archive for December, 2013

SQL SERVER 2012 – Fix – Error : 41209 – A semantic language statistics database is not registered. Full-text indexes using ‘STATISTICAL_SEMANTICS’ cannot be created or populated.

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

I came across this error message when I created full-text index on one of the file tables (a new table concept came in SQL Server 2012) and tried to create a full text index. Most likely this error is related to semantic search (a new type of search concept came in SQL Server 2012).
Let me explain this error in detail :

Message Number: 41209

Severity : 10
Error Message: A semantic language statistics database is not registered. Full-text indexes using ‘STATISTICAL_SEMANTICS’ cannot be created or populated.

Error Generation:
I tried to create full-text index on file table and received given below error message.

errormessage41209.1.1

RESOLUTION :
The resolution is very simple for this error. Basically, you have attached semantic language statistics database in your SQL Server but most probably you’ve forgotten to register it.
Lets debug this error step by step.

Step 1:
First of all, lets check whether you registered the semantic database or not. Given below is the script.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.3

As you can see that the above query result set is blank, which means semantic database is not registered.

Step 2:
Now, you just need to register semantic database, given below is the script.

USE master
GO
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
GO

Step 3 :
Once you registered the semantic database, execute the Step 1 again and check whether it is registered or not.
Given below is the same script but result set would be different because you registered the semantic database.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.6

You can view the above result set and see that semantic database has been registered. You can create full text indexes on file tables without any errors.

HAPPY NEW YEAR 🙂

Read Full Post »

SQL SERVER – How to check if data has carriage return and line feed?

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

I received this query from one of my consultants, when he was busy migrating legacy data for one of our customers, enquiring how to check if some of the employee job titles have carriage return and line feed? Whether there is any solution I can provide to check in any string that it has carriage return and line feed or not. This is the first time, I came across this query so I started searching the solution, but could not get a proper solution. So I started developing the solution. Given below is the script.

SOLUTION :

--This script is compatible with SQL Server 2005 and above.
--DROP FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
--GO
CREATE FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
(
@String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_BOOLEAN INT

;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_BOOLEAN =COUNT(*)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND ASCII(SUBSTRING(@String,Nums.n,1))
IN (13,10)

RETURN (CASE WHEN @RETURN_BOOLEAN >0 THEN 'TRUE' ELSE 'FALSE' END)
END
GO

EXAMPLE :
In this example, I manually inserted a carriage return and line feed in employee table to test the above solution using given below script.

USE AdventureWorks2012
GO
UPDATE [HumanResources].[Employee] SET [JobTitle] ='Research
and
Development Manager' WHERE BusinessEntityID =6

Lets browse the employee table using above solution and check. Given below is the script.

USE AdventureWorks2012
GO
SELECT BusinessEntityID,OrganizationLevel,JobTitle
,dbo.[UDF_Check_existance_of_carriage_return_line_feed] ([JobTitle])
AS [Boolean]
FROM HumanResources.Employee
--OUTPUT

carriage return and linefeed.1.1

Read Full Post »

SQL SERVER 2012 – Generating serial numbers for a result set by using sequence object

Posted in Solutions, SQL SERVER, tagged , , , , , on December 29, 2013| 3 Comments »

Generating Serial number is something that is very usual for a SQL Server developer, either it is for report or any result set. I would not be wrong if I said that it is something that we need to use every now and then. I usually use ROW_NUMBER to generate the serial number in most of the places unless otherwise any compatibility issues. However, I wanted to generate it in new ways and recently, I found a new method to generate serial number while playing with Sequence Object.
Given below are scripts to generate a serial number using ROW_NUMBER & SEQUENCE Object.

  • Using ROW_NUMBER
--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
ROW_NUMBER() OVER(ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--OUTPUT

serial number using sequence.1.1

  • Using SEQUENCE Object 
--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
--Create Sequence Object
CREATE SEQUENCE dbo.Seq_Sample
AS int     START WITH 1
INCREMENT BY 1 ;
GO
--Generate serial number using Sequence object
SELECT
NEXT VALUE FOR dbo.Seq_Sample OVER (ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--Reset Sequence object to 1, So it will start from 1
--in the next result set.
ALTER SEQUENCE [dbo].Seq_Sample RESTART WITH 1
GO
--OUTPUT

serial number using sequence.1.1

Read Full Post »

SQL SERVER – Virtual Labs

Posted in SQL SERVER, Trainings, tagged , , , on December 28, 2013| 2 Comments »

In the earlier days, learning a technology not only required passion but it pinched the pocket as well, due to the new hardware and software requirements. And in most of the cases, one has to setup a complete lab to learn it.
However, SQL server has resolved this problem since SQL Server 2005 and keep improving these labs namely Virtual Labs (http://www.microsoft.com/en-us/sqlserver/learning-center/virtual-labs.aspx). Basically these virtual labs are virtual machines and you do not need to do any installation or complex setup for it. You just need to connect it through internet for free & immediate use.  (Internet Explorer recommended).
Once you start the lab, it will give you the hands-on-lab material as well as lab for the particular area of SQL Server. In other words, it will guide you about what is new in the technology and how to learn it. SQL Server provides 16 virtual labs in different areas of SQL Server 2012, which are listed below:

  1. Introduction to SQL Server 2012 AlwaysOn Availability Groups
  2. Data Platform Showcase on SQL Server 2012
  3. Exploring new T-SQL features in SQL Server 2012 (SQL 202)
  4. Exploring SQL Server Data Tools (SSDT) in Visual Studio 2012
  5. Installing SQL Server 2012 on Windows 2012 Server Core
  6. Installing SQL Server 2012 on Windows Server 2012
  7. Upgrading to SQL Server 2012 from SQL 2005/2008 (SQL 208)
  8. Implementing Transparent Database Encryption
  9. Implementing and Managing AlwaysOn Availability Groups
  10. Creating a PerformancePoint Services dashboard based on a SQL Server 2012 BI Semantic Model
  11. Optimizing a SQL Server 2012 Tabular BI Semantic Model
  12. Exploring Power View in SQL Server 2012
  13. Creating a SQL Server Reporting Services Report based on a Tabular BI Semantic Model
  14. Developing a SQL Server 2012 Tabular BI Semantic Model using SQL Server Data Tools
  15. Creating a PerformancePoint Services dashboard based on a SQL Server 2012 BI Semantic Model
  16. Creating a PowerPivot workbook in Excel 2013

I found it very beneficial, specially for new bie (SQL Developers) that they can learn scripting on SQL Server without having knowledge of how to install and configure SQL Server. Let me know your feedback.

Read Full Post »

SQL SERVER – How to insert & view multiline text in any varchar field

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

I came across this query ‘how to add & view multiline texts in any varchar field’ when our team was migrating a customer data from legacy system, and one of our team members tried to update the comments field in a column of a table.

Before processing with the solution, I would like to create a sample table to demonstrate the solution.

SAMPLE :

USE Tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[Col_ID] INT,
[Col_varchar] varchar(50)
)
GO

SOLUTION :

In this solution, we can copy and paste the same text covered with single quote and insert into varchar column. Given below is the script.

USE Tempdb
GO
INSERT INTO tbl_sample VALUES (1,'This is a sample
comment on raresql.com to demonstrate
multiline text')

Lets browse the table and view the record that is it updated as a multiline or not. Given below is the script.

USE Tempdb
GO
SELECT * FROM tbl_sample
GO
-OUTPUT

multiline text.1.1

Opssssss, it is not updated as multiline in the column ;). Unfortunately if you view it in a grid it will not show as a multiline text. So to resolve it, you need to change from results to grid to results to text (Crtl+T).

multiline text.1.2

Read Full Post »

SQL SERVER – How to recover the accidentally renamed object name

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

Yesterday, I was browsing few objects in SSMS in my test SQL server database and by mistake, I did a single click on an object and typed something and it got renamed and disappered from the search result. Now, what and who I renamed? I wasn’t sure 😦  Fortunately, I had a backup and I could restore and recover my objects. But what if I did not have a backup and it happens on production database ? a nightmare I’d say?
Last year, I developed a tool that usually recovers the accidentally renamed table name but it does not give you the object type. After this incident, I started with my research and modified the script and now it can recover any renamed object with its object type as well.

Note : It will only recover the object name if you renamed it through SSMS.

Given below is the stored procedure that can recover any object name.

--DROP PROCEDURE Recover_Renamed_Object_Name_Proc
--GO
CREATE PROCEDURE Recover_Renamed_Object_Name_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
Select type_desc As [Object Type]
,REPLACE(Substring(A.[RowLog Contents 0]
,14,LEN(A.[RowLog Contents 0])),0x00,0x) as [Object Name Before Rename]
,REPLACE(Substring(B.[RowLog Contents 0]
,14,LEN(B.[RowLog Contents 0])),0x00,0x) as [Object Name After Rename]
FROM sys.fn_dblog(NULL, NULL) A
Inner Join sys.fn_dblog(NULL, NULL) B
On A.[Transaction ID]=B.[Transaction ID]
And A.AllocUnitId = B.AllocUnitId
Left Join sys.objects S On CONVERT(BIGINT,CONVERT(VARBINARY(MAX)
,REVERSE(Substring(A.[RowLog Contents 0],7,4))))=S.object_id
WHERE
A.AllocUnitId IN (562949955649536) AND A.Context IN ('LCX_MARK_AS_GHOST')
AND A.Operation IN ('LOP_DELETE_ROWS') AND B.Context IN ('LCX_INDEX_LEAF')
AND B.Operation IN ('LOP_INSERT_ROWS')
/*Use this subquery to filter the date*/
AND A.[TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM
sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name]='user_transaction'
AND CONVERT(NVARCHAR(11),[Begin Time])
BETWEEN @Date_From AND @Date_To)
GO
--Example EXEC Recover_Renamed_Object_Name_Proc 'yyyy/MM/dd','yyyy/MM/dd'
EXEC Recover_Renamed_Object_Name_Proc '2013/10/31','2014/12/31'
--OUTPUT

recover renamed object name

In case you do not know the modified date & time of any object, you can write given below query and use [modify_date] column.

Select name, type_desc, modify_date from sys.objects
Order by Modify_date DESC

Read Full Post »

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 »

Older Posts »