Feeds:
Posts
Comments

Archive for the ‘Solutions’ Category

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 – 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 – 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 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 »

« Newer Posts - Older Posts »