SQL Server | SQL Server Portal

Posts Tagged ‘SQL Server’

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

Posted in Errors, SQL SERVER, tagged FILESTREAM feature is disabled., Msg 5591 Level 16 State 1 Line 1, raresql, SQL, SQL Server 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.

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.

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:

Enable FILESTREAM for Transact-SQL access (You need to enable this to enable FILESTREAM).
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)
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.

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 datetime2, ISDATE, raresql, SQL, SQL Server, SQL Server 2012 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.

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

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 raresql, SQL, SQL Server, tsql 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

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

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

Read Full Post »

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

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL Server 2012 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.

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.

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.

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.

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.

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.

Read Full Post »

SQL SERVER 2012 – Security Enhancements – List of new permissions

Posted in Security Enhancement, SQL SERVER, tagged raresql, SQL, SQL Server, SQL Server 2012, sys.fn_builtin_permissions 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 Msg 12824 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012, 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 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.

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 Current value of Sequence Object, raresql, SQL, SQL Server, SQL Server 2012 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.

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

Read Full Post »

SQL SERVER – How to remove extra spaces from string value

Posted in Solutions, SQL SERVER, tagged How to remove extra spaces from string value, raresql, SQL, SQL Server on November 4, 2013| 1 Comment »

How to remove extra spaces from string value is a common issue and we usually come across this issue while massaging the data. I used this solution as a part of a solution in one of my earlier solutions.

Let me create a sample to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create table
CREATE TABLE tbl_sample
(
[ID] INT,
[NAME] VARCHAR(100)
)
GO
--Insert few records in the table
--Note : Names have been taken from adventureworks2012 database.
INSERT INTO tbl_sample VALUES (1,'Terri    Lee          Duffy')
INSERT INTO tbl_sample VALUES (2,'Roberto     Tamburello')
INSERT INTO tbl_sample VALUES (3,'Rob   Walters')
INSERT INTO tbl_sample VALUES (4,'Gail   A        Erickson')
INSERT INTO tbl_sample VALUES (5,'Gigi      N      Matthew')
GO
--Browse table
SELECT
[ID]
,[NAME] AS [String with extra spaces]
FROM tbl_sample
--OUTPUT

SOLUTION 1 : Using REPLACE(string) Function
In this solution, we need to use the built-in function REPLACE to remove extra spaces from string value.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[NAME] AS [String with extra spaces]
,REPLACE(REPLACE(REPLACE([NAME]
,CHAR(32),'()'),')(',''),'()',CHAR(32))
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

SOLUTION 2 : Using User Defined Function
In this solution, we need to create a User Defined Function to remove extra spaces from string using XML.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Extra_Space_From_String]
(
@String VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Xml XML
DECLARE @Removed_Extra_Space VARCHAR(MAX)
DECLARE @delimiter VARCHAR(5)

SET @delimiter=' '
SET @Xml = CAST(('<a>'+REPLACE(@String,@delimiter,'</a><a>')+'</a>')
AS XML)

;WITH CTE AS (SELECT
A.value('.', 'VARCHAR(MAX)') AS [Column]
FROM @Xml.nodes('A') AS FN(A))

SELECT @Removed_Extra_Space=REPLACE(
Stuff((
SELECT ';' + A.[Column]
FROM CTE A
WHERE ISNULL(A.[Column],'') <>''
FOR XML PATH('')),1,1,''),';',' ')

RETURN (@Removed_Extra_Space)
END
GO

SELECT
[ID]
,[NAME] AS [String with extra spaces]
,dbo.[UDF_Remove_Extra_Space_From_String] ([Name])
AS [String without extra spaces]
FROM tbl_sample
GO
--OUTPUT

Read Full Post »

SQL SERVER 2012 – How to get a value from next result row effectively

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - How to get a value from next result row effectively, SQL Server 2012 – Analytic Functions – LEAD on November 3, 2013| Leave a Comment »

Today, I was developing a customer analysis report, basically trying to find out customer’s trend of purchasing. To get the trend I need to get his next row (purchasing) result set in the current row for comparison purposes. Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution, using LEAD (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] int,
[Levels] varchar(50)
)
GO

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,B.Levels AS [Next Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID+1=B.ID
ORDER BY A.ID
GO
--OUTPUT

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LEAD function and it will calculate the next result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,LEAD(A.levels,1,0) OVER (ORDER BY A.ID) AS [Next Level]
FROM tbl_sample A
GO
--OUTPUT

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.

Read Full Post »

SQL SERVER – How to find a row that contains a lower case letter

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL SERVER - How to find a row that contains a lower case letter on November 1, 2013| 1 Comment »

A few days ago, I was working on list of customers and all the customers’ names were in upper case. Coincidentally, I found one customer having upper and lower case in his name. So, I thought of checking the entire customer list if anyone was having name in upper and lower case.
Note : The database is not case sensitive.

Let me create a sample to demonstrate the solution.

USE tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Name] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'RARESQL.COM')
INSERT INTO tbl_sample VALUES (2,'RaReSql.com')
INSERT INTO tbl_sample VALUES (3,'Raresql')
INSERT INTO tbl_sample VALUES (4,'raresql.com')
GO
SELECT * FROM tbl_sample
--OUTPUT

SOLUTION 1 : Using UPPER (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample WHERE
UPPER([NAME]) COLLATE Latin1_General_CS_AS !=[NAME]
--OUTPUT

SOLUTION 2 : Using PATINDEX (String) Function

USE tempdb
GO
SELECT * FROM tbl_sample
WHERE PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
,[NAME] COLLATE Latin1_General_CS_AS)>0
GO
--OUTPUT

SOLUTION 3 : Using ASCII(String) Function
In this solution, it will not only give you the rows having lower case letter but it will also give you what lower characters are there in those rows. Given below is the script.

USE tempdb
GO
--DROP FUNCTION dbo.[UDF_Extract_small_letters_From_String]
--GO
CREATE FUNCTION dbo.[UDF_Extract_small_letters_From_String]
(
@String VARCHAR(MAX)  -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_STRING VARCHAR(MAX)

;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_STRING=ISNULL(@RETURN_STRING,'')
+ SUBSTRING(@String,Nums.n,1)
FROM N4 Nums
WHERE Nums.n<=LEN(@String)
AND ASCII(SUBSTRING(@String,Nums.n,1)) BETWEEN 97 AND 122

RETURN @RETURN_STRING
END
GO

SELECT *,dbo.[UDF_Extract_small_letters_From_String]([NAME])
As [Lower cases letters]
FROM tbl_sample
--OUTPUT