Feeds:
Posts
Comments

Archive for November, 2013

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 »

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 »

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 & NOYES 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 »

Recently, I was working on semantic search and tried to create a database having filestream and got an error FILESTREAM feature is disabled. After some research, I found the solution.

Let’s discuss this in detail:

Message Number: 5591

Severity : 16

Error Message: FILESTREAM feature is disabled.

Error Generation:

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

USE master
GO
CREATE DATABASE [SampleDB]
ON PRIMARY
( NAME = N'SampleDB',
FILENAME = N'C:\DATA\SampleDB.mdf'
),
FILEGROUP SampleFileGroup CONTAINS FILESTREAM
(NAME = SampleFileTableFS,
FILENAME='C:\Data\SampleFileTable'
)
LOG ON
( NAME = N'SampleDB_log',
FILENAME = N'C:\DATA\SampleDB_log.ldf'
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'SampleFileTableDB'
)
GO
--OUTPUT

Msg 5591, Level 16, State 1, Line 1
FILESTREAM feature is disabled.

Ooopps…… I am unable to create a database having FILESTREAM. So what is the solution ?

Solution:
Let me explain the solution step by step.

Step 1 :
Click on the Start >>All Programs >>Microsoft SQL Server 2012 >>Configuration Tools >> SQL Server Configuration Manager, as shown in the image below.

filestream_disabled.1.1

Step 2 :
Once you click on SQL Server configuration manager, it will open SQL Server configuration manager options. It has two panes. Now you need to select SQL Server Services from the left hand pane and select the SQL Server Instance on which you need to enable the FILESTREAM. Just right click on it and select PROPERTIES as shown in the image below.

filestream_disabled.1.2

Step 3 :
Once you select the PROPERTIES, it will open SQL Server properties dialogue box. Now you need to find FILESTREAM Tab and select it.
Once you select it, you will find given below options:

    1. Enable FILESTREAM for Transact-SQL access (You need to enable this to enable FILESTREAM).
    2. Enable FILESTREAM for file I/O access. (If you need to read and write data (FILESTREAM) from windows then enable it and provide a windows share name)
    3. Allow remote clients to have streaming access to FILESTREAM data. (If you need to give access to remote clients on FILESTREAM data then enable it)

And Click APPLY button as shown in the picture below.

filestream_disabled.1.3

Step 4 : 

Once you are done with SQL Server Configuration Manager, open SQL Server Management Studio and open a new query window.

Step 5 :

Now, you are in the new query window, just execute the given below script. And after that DO NOT forget to restart the SQL Server SERVICE.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

Step 6 :
FILESTREAM is enabled now. You can execute any script having FILESTREAM. It will be executed successfully.

Read Full Post »

I developed a utility in SQL Server 2008 and recently upgraded to SQL Server 2012. The job of this utility is to receive the data from external sources, validate the data and insert the data into respective fields of a table for further processing. I used one of the famous SQL functions ISDATE() to validate the date fields in this utility and it was working perfect. Recently, I began to receive some errors. After debugging, I found that ISDATE() is not compatible with datetime2 datatype.

Let me demonstrate the error before heading towards the solution.

--This script is compatible with SQL Server 2008 and above.
DECLARE @Datetime2 AS DATETIME2
SET @Datetime2=GETDATE()

SELECT ISDATE(@Datetime2)  AS [Validate_Date]
GO
--OUTPUT

Msg 8116, Level 16, State 1, Line 4
Argument data type datetime2 is invalid for argument 1 of isdate function.

isdate_with_datetime2.1.1

Opsssssssssssss, I cannot validate the datetime2 datatype using ISDATE() function. So what is the solution ?

SOLUTION :
I developed the given below solution using TRY_CONVERT() (One of the new conversion functions shipped with SQL Server 2012)

Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @Datetime2 AS DATETIME2
DECLARE @Varchar AS VARCHAR(10)
SET @Datetime2=GETDATE()
SET @Varchar='raresql'

SELECT
IIF(TRY_CONVERT(DATETIME,@Datetime2) is null ,0,1) AS [Validate_Date]
,IIF(TRY_CONVERT(DATETIME,@Varchar) is null ,0,1) AS [Validate_Date]
GO
--OUTPUT

isdate_with_datetime2.1.2

Do share if you came across this issue and resolved it differently.

Read Full Post »

‘How to remove the last character in a string’ is a general problem that we usually face while developing a dynamic SQL string or sometimes due to legacy data etc.

Given below are multiple solutions to remove the last character from a string.

SOLUTION 1 : Using LEFT string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,LEFT(@String,DATALENGTH(@String)-1)
As [Last Character removed from string]
GO
--OUTPUT

last character removed from string.1.1

SOLUTION 2 : Using STUFF string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,STUFF(@String,DATALENGTH(@String), 1, '')
As [Last Character removed from string]
GO
--OUTPUT

last character removed from string.1.1

SOLUTION 3 : Using SUBSTRING string function.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @String as VARCHAR(50)
SET @String='1,2,3,4,5,'

SELECT
@String As [String]
,SUBSTRING(@String,1, DATALENGTH(@String)-1)
As [Last Character removed from string]
GO
--OUTPUT

last character removed from string.1.1

Read Full Post »

Sometimes, we need to copy the data from SQL Server result set to any external source (excel, word, notepad etc.) for different purposes. But the problem is when you usually copy (Ctrl+ C or right click >> Copy) and paste it into external source, the column name is not copied and you need to type it manually.

As you can see in the given below images, we copied data from SQL Server and pasted it in the excel but column name did not appear in the excel.

copy column name.1.1

copy column name.1.2

SOLUTION 1 :
The solution is a permanent one, meaning it will copy the column header from the query result set everytime. But, I usually do not recommend this solution because sometimes you do not need the column header and in this case you need to manually delete the column header. Opsssssssssssss.
So what is the solution ? Basically it is a built-in feature in SQL Server.

Let me explain it step by step.

Step 1 :
You need to browse Tools menu and select Options as shown in the image below.

copy column name.1.3

Step 2 :
Once you select Options menu, it will open options dialogue box.
Now you need to select Query results\ SQL Server\Results to Grid from the left hand pane and place the check mark on Include column headers when copying or saving the results in the right hand pane and press OK. Given below is the screen image.

copy column name.1.5

Step 3 :
Now, run query in any window, select the result set (data) then copy (Ctrl+C) and paste it into any external application. This time, it will copy the column header as well.

copy column name.1.4

SOLUTION 2 :
In this solution, you need not go to multiple screens and set any options. In a way, it’s a shortcut to the above solution. The most important benefit using this solution is, if you need the header you can copy it, else copy the data only.

Step 1 :
Run query in any window, select the result set (data) and right click on it as shown in the image below.

copy column name.1.6

Step 2 :
Once you right click on the selected result set, you can either copy without header (Ctrl+C) or copy with header (Ctrl+Shift+C) and paste it into any external application.

copy column name.1.4

Read Full Post »

Older Posts »