Feeds:
Posts
Comments

Archive for November, 2013

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 »

SQL SERVER 2012 – Fix – Error – 5591 – FILESTREAM feature is disabled.

Posted in Errors, SQL SERVER, tagged , , , , on November 19, 2013| 1 Comment »

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 »

SQL SERVER – How to use ISDATE with datetime2 datatype

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

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 »

SQL SERVER – Multiple ways to remove the last character in a string

Posted in Solutions, SQL SERVER, tagged , , , on November 16, 2013| 1 Comment »

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

SQL SERVER – Include column header when copying or saving the results

Posted in Solutions, SQL SERVER, tagged , , , on November 12, 2013| 3 Comments »

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 »

SQL SERVER 2012 – Security Enhancements – List of new permissions

Posted in Security Enhancement, SQL SERVER, tagged , , , , on November 9, 2013| Leave a Comment »

In my earlier articles I wrote about many new enhancements in SQL Server 2012. Today, I will discuss the new enhancement in the permission area that includes Availability group, Schema, Search property list & Server categories.

The simplest way to query permissions is using sys.fn_builtin_permissions.
Given below is the script.

SELECT * FROM sys.fn_builtin_permissions('');

Above script will give you the complete list of permissions in SQL Server, but I need only the new permissions shipped in SQL Server 2012, so I compared the SQL Server 2008 R2 permissions with SQL Server 2012 and got the given below new permissions shipped with SQL Server 2012.

S. No

Class Description

Permission Name

Covering Permission Name

Parent Class Description

Parent Covering Permission Name

1

AVAILABILITY GROUP

ALTER

CONTROL

SERVER

ALTER ANY AVAILABILITY GROUP

2

AVAILABILITY GROUP

CONTROL

 

SERVER

CONTROL SERVER

3

AVAILABILITY GROUP

TAKE OWNERSHIP

CONTROL

SERVER

CONTROL SERVER

4

AVAILABILITY GROUP

VIEW DEFINITION

CONTROL

SERVER

VIEW ANY DEFINITION

5

SCHEMA

CREATE SEQUENCE

ALTER

DATABASE

CONTROL

6

SEARCH PROPERTY LIST

ALTER

CONTROL

DATABASE

ALTER ANY FULLTEXT CATALOG

7

SEARCH PROPERTY LIST

CONTROL

 

DATABASE

CONTROL

8

SEARCH PROPERTY LIST

REFERENCES

CONTROL

DATABASE

REFERENCES

9

SEARCH PROPERTY LIST

TAKE OWNERSHIP

CONTROL

DATABASE

CONTROL

10

SEARCH PROPERTY LIST

VIEW DEFINITION

CONTROL

DATABASE

VIEW DEFINITION

11

SERVER

ALTER ANY AVAILABILITY GROUP

CONTROL SERVER

 

 

12

SERVER

ALTER ANY EVENT SESSION

CONTROL SERVER

 

 

13

SERVER

ALTER ANY SERVER ROLE

CONTROL SERVER

 

 

14

SERVER

CREATE AVAILABILITY GROUP

ALTER ANY AVAILABILITY GROUP

 

 

15

SERVER

CREATE SERVER ROLE

ALTER ANY SERVER ROLE

 

 

16

SERVER ROLE

ALTER

CONTROL

SERVER

ALTER ANY SERVER ROLE

17

SERVER ROLE

CONTROL

 

SERVER

CONTROL SERVER

18

SERVER ROLE

TAKE OWNERSHIP

CONTROL

SERVER

CONTROL SERVER

19

SERVER ROLE

VIEW DEFINITION

CONTROL

SERVER

VIEW ANY DEFINITION

I will discuss the above permissions in my future articles in detail.

Read Full Post »

SQL SERVER 2012 – Fix – Error – 12824 – The sp_configure value ‘contained database authentication’ must be set to 1.

Posted in Errors, SQL SERVER, tagged , , , , , on November 7, 2013| Leave a Comment »

The sp_configure value ‘contained database authentication’ must be set to 1 in order to %S_MSG a contained database. You may need to use RECONFIGURE to set the value_in_use is one of the new error messages come in SQL Server 2012. This error message is related to Contained database, a new database feature shipped with SQL Server 2012.

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

Severity : 16

Error Message: The sp_configure value ‘contained database authentication’ must be set to 1 in order to %S_MSG a contained database. You may need to use RECONFIGURE to set the value_in_use.

Error Generation:

Basically, I re-installed my test database server, and I had plenty of database to attach it back to the server. So I was attaching one by one and this process was performing well. Suddenly, in one database it gave me this error. Given below are the error details.

errormessage12824.1.1

Ooopps…… I am unable to attach this database.

Resolution:
Basically, the database I was trying to attach is a contained database and please note that whenever you need to create or attach any contained database, you must enable the contained database authentication in the database server. But how ?
Given below is the script to enable it.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO
--OUTPUT

Configuration option ‘contained database authentication’ changed from 0 to 1. Run the RECONFIGURE statement to install.

Once you receive the above output saying  ‘contained database authentication’ has been changed from 0 to 1, you can attach or create any contained database in your database server.

Reference : MSDN

Read Full Post »

SQL SERVER 2012- How to get the current value from a sequence object

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

Today, I received a query from one of my blog readers asking how to get the current value from a sequence ? He also mentioned to me that he usually uses given below (Database console command) script to get the current value from identity column.

DBCC CHECKIDENT ('Table Name', NORESEED)

So, do we have any DBCC command like this to get the current value from sequence object? Basically we do not have any DBCC command to get the current value from sequence same like identity but we do have work around to find it. Given below are the two methods to get the current value from any sequence object.

METHOD 1 :
In this method, you need to open SQL Server Management Studio and select the particular database and further select the sequence object in which current value is required. Then right click on it and browse its property to view the current value as shown in the image below.

sequence current value.1.1

sequence current value.1.2

METHOD 2 :
In this method, you need to open a NEW query window in SSMS and write the given below script to get the current value of sequence using sys.sequences  (A new system view shipped in SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT current_value FROM sys.sequences
WHERE [name]='Seq_sample'
--OUTPUT

sequence current value.1.3

Read Full Post »

Older Posts »