Feeds:
Posts
Comments

Archive for April, 2013

SQL SERVER – How to find SQL Server port number

Posted in Solutions, SQL SERVER, tagged , , , , , on April 20, 2013| Leave a Comment »

“How to find the SQL Server port number ?” has been an old topic being discussed in multiple forums. The common solution provided is using xp_instance_regread. It is one of the useful undocumented stored procedures when you need to read registry settings. In this article, I will give you two solutions. First one using xp_instance_regread and second, using sys.dm_server_registry, a new dynamic management view shipped in SQL Server 2008 R2

Method 1 : Using xp_instance_regread.
In this method we will use xp_instance_regread to find the port number.

--This script will work on SQL Server 2005 and above
DECLARE @HkeyLocal nvarchar(18)
Declare @Instance varchar(100)
DECLARE @MSSqlServerRegPath nvarchar(200)
DECLARE @PortNumber nvarchar(100)

SET @Instance ='MSSQL11.MSSQLSERVER'
SET @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SET @MSSqlServerRegPath=N'SOFTWARE\Microsoft\\Microsoft SQL Server\'
+ @Instance + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'

Print @MSSqlServerRegPath
EXEC xp_instance_regread @HkeyLocal
, @MSSqlServerRegPath
, N'TcpPort'
, @PortNumber OUTPUT
SELECT @PortNumber as [Port Number]
--OUTPUT

Port Number
————
1433

(1 row(s) affected)

Method 2 : Using using sys.dm_server_registry.
In this method we will use sys.dm_server_registry to find the port number.

--This script will work on SQL Server 2012 and above
Use master
GO
Select value_data as [Port Number]
From sys.dm_server_registry
Where registry_key like '%IPALL%' and
value_name ='TcpPort'

Port Number
————
1433

(1 row(s) affected)

Conclusion :
If you compare both methods, you will feel the difference, because the code is much optimized in the second method. Also, you can utilize the result set of second method further without using any temporary table but on the other hand you need temporary table to utilize the result set further.

Read Full Post »

SQL SERVER – Get day of week in SQL 2005/2008/2012

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

How to get the day of the week ? I have been asked this question several times and we do have its solution via DATENAME function. However today we will achieve the same solution using FORMAT function, introduced in SQL Server 2012 as well. Given below are the two methods that help in identifying the day of a week in SQL Server 2005 & SQL Server 2012 respectively.
Method 1 :
Script supported in SQL Server 2005 and above :

--This script will work in SQL Server 2005 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select DATENAME(dw , @Datetime) as [Weekday Name]
--OR
Select DATENAME(weekday, @Datetime) as [Weekday Name]
--OUTPUT

Get week day name1.1

Method 2 :
Script supported in SQL Server 2012 and above :

--This script will work in SQL Server 2012 and above
Declare @Datetime as datetime
Set @Datetime =getdate()
Select FORMAT(@Datetime,'dddd') as [Weekday Name]
--OUTPUT

Get week day name1.2

Have you come across any other method ? Do share.

Read Full Post »

SQL SERVER 2012 – Fix – Error :11514 – The metadata could not be determined because statement in procedure contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Posted in Errors, SQL SERVER, tagged , , , , on April 17, 2013| 1 Comment »

Sometimes, its essential to use dynamic SQL when there are different queries for different scenarios, to build the query dynamically. The error we will discuss in this article is related to dynamic SQL and sp_describe_first_result_set , a new system stored procedure shipped with SQL Server 2012. This error message is available in SQL Server 2012 and above.

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

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Error Generation:
Let me create a sample to demonstrate this error.

Use tempdb
Go
Create Procedure UDP_Resultset
As
Declare @FirstLetter varchar(3)
Declare @SecondLetter varchar(3)

Set @FirstLetter ='''A'''
Set @SecondLetter ='''B'''

Declare @String varchar(Max)

Set @String ='Select ' + @FirstLetter +' As [First Letter]'
EXEC (@String)

Set @String ='Select ' + @SecondLetter +' As [Second Letter]'
EXEC (@String)
GO
EXEC UDP_Resultset
--OUTPUT

errormsg11514.1.1

You can see that the above script executed successfully. Now I would like to view the metadata of the above stored procedure. Given below is the script to view the metadata using sp_describe_first_result_set

EXEC Sp_describe_first_result_set @tsql =N'EXEC UDP_Resultset'
--OUTPUT

Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘EXEC (@String)’ in procedure ‘UDP_Resultset’ contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, we have dynamic SQL inside the SQL stored procedure and whenever we need to view the metadata of stored procedure having dynamic SQL, we must use the result set clause along with stored procedure.
Given below is the correct script.

Sp_describe_first_result_set @tsql =
N'EXEC UDP_Resultset WITH RESULT SETS (([1st Letter] varchar(50))
,([2nd Letter] varchar(50)));'
GO
--OUTPUT

errormsg11514.1.2

Conclusion :
Remember, whenever you use dynamic SQL stored procedure and would like to view the metadata of that stored procedure using sp_describe_first_result_set,  always use result set clause.

Read Full Post »

SQL SERVER 2012 – FORCESEEK Table Hint – Enhancement

Posted in SQL SERVER, Tips, tagged , , , , on April 16, 2013| 2 Comments »

SQL Server new versions always come up with the new performance optimization techniques, enhancements and additions. FORCESEEK table hint enhancement is also one of the new enhancements that came up in SQL Server 2012.

Let me create an index on Purchasing. [PurchaseOrderDetail] table to demonstrate this enhancement.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_PurchaseOrderID]
ON Purchasing.[PurchaseOrderDetail]
(
[PurchaseOrderID] ASC
) WITH (DROP_EXISTING = OFF) ON [PRIMARY]

FORCESEEK in earier version of SQL :
In the ealier version of SQL Server, FORCESEEK table hint needed only an index on the table and it could optimize your query. But sometimes you may have more than one index and you cannot define those indexes anywhere in the syntax of forceseek (Limitation). Let me script it to elaborate the same.

---This query will work on SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with (forceseek)
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

FORCESEEK in SQL Server 2012 :
In SQL Server 2012, you can define index name along with the column in forceseek table hint to further optimize your query. But you can also use the above syntax  and it will not break your code.

Let me explain it with examples :

Example 1 : (Define the index name in forceseek) 

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d with
(forceseek,INDEX(IX_PurchaseOrderDetail_PurchaseOrderID))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Example 2 : (Define the index name along with the column name in forceseek) 

---This query will work on SQL Server 2012 and above.
USE [AdventureWorks2012]
GO
Select * from Purchasing.PurchaseOrderHeader as h
Inner join Purchasing.PurchaseOrderDetail as d
with (forceseek(IX_PurchaseOrderDetail_PurchaseOrderID(PurchaseOrderID)))
On h.PurchaseOrderID =d.PurchaseOrderID
Where h.TotalDue >50

Conclusion:

In SQL Server 2012, FORCESEEK  came up with the very nice enhancement that you can define index name along with the column in FORCESEEK, to have further control on query optimization. However, remember this is the last option we should choose at the time of optimization. Finally, this is only recommended for experienced programmers.

Reference : MSDN

Read Full Post »

SQL SERVER – Undocumented function – fn_dblog

Posted in Functions, SQL SERVER, Tips, tagged , , , on April 15, 2013| 10 Comments »

fn_dblog is one of my favorite undocumented functions; I use it to design multiple recovery solutions. Today, I will discuss its purpose, syntax, parameters and one of the problems that I faced with respect to its parameters.

Purpose :
It gives you the detail view of the transaction log.

Syntax:

fn_dblog (@start,@end)

Parameters:
It accepts two parameters, given below are the details.
@start : Start LSN but it must be in the integer format.
@end : End LSN but it must be in the integer format.

Let me explain it with simple examples :
Example 1: (With default parameters) 

Select * from fn_dblog(NULL,NULL)
--OUTPUT

fn_dblog1.1

Example 2: (Passed LSN)
Lets take a current LSN from above example and pass it as a parameter.The purpose to pass a single LSN is to pick up only one transaction log record.

use test
Go
Select * from fn_dblog('000005c9:0000133d:0022','000005c9:0000133d:0022')
--OUTPUT

Msg 9005, Level 16, State 3, Line 1
Either start LSN or end LSN specified in OpenRowset(DBLog, …) is invalid.

Ooops…… I am unable to execute it.

The reason behind the above error message is that I just picked up LSN from above result set and passed it into fn_dblog.
Whereas the correct way to do it is, first convert the LSN to integer and then pass it to fn_dblog.

Given below is the script that will convert your hexa decimal LSN into integer LSN.

Declare @Xml Xml
Declare @String Varchar(Max)
Declare @delimiter Varchar(5)
Set @delimiter=':'
Set @String ='000005c9:0000133d:0022'
SET @Xml = cast(('<a>0x'+replace(@String,@delimiter,'</a><a>0x')+'</a>')
AS XML)

;With CTE as (SELECT A.value('.', 'varchar(max)') as [Column]
from @Xml.nodes('a') AS FN(A) )
Select Stuff((Select ':' +
Convert (varchar(max),Convert(INT,cast('' AS XML).value
('xs:hexBinary(substring(sql:column("[Column]"),3) )', 'varbinary(max)')))
from CTE for xml path('') ),1,1,'') as [Current LSN]
--OUTPUT

1481:4925:34

Lets pass the above output (1481:4925:34) to the fn_dblog and view the result set.

use test
Go
Select * from fn_dblog('1481:4925:34','1481:4925:34')
--OUTPUT

fn_dblog1.2

Now, you can see that fn_dblog filter worked and it picked up only one LSN.

Conclusion :
No doubt fn_dblog is one of the helpful undocumented functions but do not use this function in the production server unless otherwise required. Also, remember to convert the hexa decimal to integer before passing it to fn_dblog as a parameter.

Read Full Post »

SQL SERVER – How to find uncontained objects in the database

Posted in Solutions, SQL SERVER, tagged , , , , on April 14, 2013| Leave a Comment »

Today, I was working on contained database and came across with an issue (will post this issue in my upcoming post), where I had to find a list of uncontained objects (As per MSDN, uncontained objects are basically those objects that cross the database boundaries in a contained database.)

Also, as per MSDN, given below are the objects that usually cross the boundaries of the database:

  • Unknown containment behavior (dynamic SQL or deferred name resolution)
  • DBCC command
  • System stored procedure
  • System scalar function
  • System table valued function
  • System built-in function

But it is quiet difficult, if you search for any of the above objects in the database one by one. SQL Server resolves this issue by releasing a dynamic management view (sys.dm_db_uncontained_entities) that will give you all uncontained objects of any database, in one shot.

Let me create an uncontained objects in a contained database.

--Create a stored procdure by using sys.allobjects
USE AdventureWorks2012
GO
CREATE PROC test_Procedure
AS
SELECT * FROM sys.all_objects
GO

Once you created the above stored procedure, just execute the given below script it will give you the name of above procedure along with other uncontained objects.

Use AdventureWorks2012
GO
SELECT O.name
,O.type_desc
,UCE.class_desc
,UCE.statement_type
,UCE.feature_name
,UCE.feature_type_name
FROM sys.dm_db_uncontained_entities AS UCE
LEFT JOIN sys.objects AS O
ON UCE.major_id = O.object_id
--OUTPUT

dm_db_uncontained_entities1.1-1

Note : If the above query return Nil(or emplty) it means that you donot have any uncontained objects.

Read Full Post »

SQL SERVER – Get the first letter of each word in a String (Column)

Posted in Solutions, SQL SERVER, tagged , , , on April 12, 2013| 6 Comments »

String manipulation in a different format is one of my favorite activities. Today, I came across a question in a forum, how to get the first letter of each word in a column. Obviously, my primary search was the internet, where I found few solutions, but all of them were loop based. Hence  developed a solution without loop.

Give below is the script :

--Create a Sample
Create Table Student
(
[Student ID] int Identity(1,1),
[Student Name] varchar(50)
)
Go
Insert into Student Values ('Steve Masters Bob')
Insert into Student Values ('David Ortiz')
Insert into Student Values ('Michael Sean Ray')
Insert into Student Values ('Steven SElikoff')
Insert into Student Values ('Carole POLAND')
Insert into Student Values ('Bjorn Rettig')
Insert into Student Values ('Michiko OSAda')
Insert into Student Values ('CarOL Philips')
Insert into Student Values ('Merav Netz')
GO

--Create the procedure to get the first letter of each word.
Create Function dbo.[UDF_FIRST_LETTER_FROM_WORD]
(
@String Varchar(Max) -- Variable for string
)
RETURNS Varchar(Max)
BEGIN
Declare @Xml Xml
Declare @firstletter 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 @firstletter =Stuff((SELECT '' + LEFT([Column],1)
FROM CTE
FOR XML PATH('') ),1,0,'')

RETURN (@firstletter)
END
GO

Example 1 :
Given below script will get the first letter of each word from a column of a table.

SELECT [Student Name],
dbo.[UDF_FIRST_LETTER_FROM_WORD] ([Student Name]) as [First Letters]
FROM Student
GO

firstletterfromword1.1

Example 2 :
Given below script will get the first letter of each word from a string.

Declare @String as varchar(100)
Set @String ='My Best Friend'
SELECT @String as [String]
, dbo.[UDF_FIRST_LETTER_FROM_WORD] (@String) as [First Letters]

firstletterfromword1.2

Read Full Post »

SQL SERVER 2012 – Fix – Error : 35343 – CREATE INDEX statement failed. Column has a data type that cannot participate in a columnstore index. Omit column.

Posted in Errors, SQL SERVER, tagged , , , , , on April 11, 2013| Leave a Comment »

Today when I was working on Columnstore index and I came across this error,  CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’. This is one of the new error messages come in SQL Server 2012. This error message is related to Columnstore index a new database feature shipped with SQL Server 2012.

Let me explain this with a simple example.
Message Number: 35343

Severity : 15

Error Message: CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’.

Error Generation:

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

Use AdventureWorks2012
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
(
[ProductID],
[OrderQty],
[UnitPrice],
[Rowguid]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Msg 35343, Level 16, State 1, Line 1
CREATE INDEX statement failed. Column ‘rowguid’has a data type that cannot participate in a columnstore index. Omit column ‘rowguid’.

Ooopps…… I am unable to create columnstore index.

Resolution:
To resolve this error, I need to find the  datatypes which are not supported by  Columnstore index . As per MSDN, given below are the datatypes .

  • binary and varbinary
  • ntext , text, and image
  • varchar(max) and nvarchar(max)
  • uniqueidentifier
  • rowversion (and timestamp)
  • sql_variant
  • decimal (and numeric) with precision greater than 18 digits
  • datetimeoffset with scale greater than 2
  • CLR types (hierarchyid and spatial types)
  • xml

Now, just change your script (to create the columnstore index) and remove all non supported datatype. In this example, I will remove [Rowguid] column because its datatype is uniqueidentifier.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
(
[ProductID],
[OrderQty],
[UnitPrice]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO
--OUTPUT

Command(s) completed successfully.

Conclusion :

Remember, whenever you create the columnstore index, you must not include any column which has datatype that is not supported by columnstore index.

Read Full Post »

SQL SERVER – Shorter way to convert Seconds to Days, Hours, Minutes, Seconds

Posted in Solutions, SQL SERVER, tagged , , , on April 10, 2013| 6 Comments »

Today, I came across this question in a forum – How to convert seconds to Day(s), Hour(s), Minute(s), Second(s)? For this we need to do a lot of divisions and remainders to convert Seconds to Day, Hour, Minute, Seconds. Given below is the simple script to convert it without doing any divisions and remainders, using only built-in SQL functions DateAdd, DatePart, DateDiff

--This script will work on SQL Server 2005 and above.
Declare @Seconds as int
Declare @SubtractDate as datetime
--Enter Number of Seconds here
Set @Seconds=9974501
Set @SubtractDate=DateAdd(s,@Seconds,getdate()) - Getdate()

Select Convert(varchar(10),DateDiff(day,'1900-01-01',@SubtractDate))
+ ' Day(s) ' +
Convert(varchar(10),DatePart(hh,@SubtractDate))
+ ' Hour(s) ' +
Convert(varchar(10),DatePart(mi,@SubtractDate))
+ ' Minute(s) ' +
Convert(varchar(10),DatePart(ss,@SubtractDate))
+ ' Second(s) ' AS [Result]
--OUTPUT

Result
——————————————————————————-
115 Day(s) 10 Hour(s) 41 Minute(s) 41 Second(s)

(1 row(s) affected)

Read Full Post »

SQL SERVER 2012- Shortcut – How to find all system objects related to any file table

Posted in Solutions, SQL SERVER, tagged , , , , , on April 9, 2013| 2 Comments »

File table is one of my favorite features introduced in SQL Server 2012. When I was working with file table, I came across a question how to find all system objects (primary key, default vaule, indexes etc) related to any file table.

We can achieve this by two different methods.

 Method 1:

In this method, we did self-join in the sys.objects to find all system objects related to any file table. This is general script that we normally use to find any child object related to parent object.

--This script will work in SQL Server 2012 and above.
Declare @FileTableName as varchar(50)='dbo.Databank'
-- Enter File table Name Here
Select B.[name] as [File Table Name]
,A.[name] as [Related objects]
from sys.objects A
Inner Join sys.objects B
On A.[parent_object_id] =B.[object_id]
Where B.[type] ='U'
And B.object_id=object_id(@FileTableName)
GO
--OUTPUT

filetablesystemobjects1.1

 Method 2 (Shortcut):

In this method, we used a new system view namely sys.filetable_system_defined_objects (This system view returns all the system objects related to any file table) introduced in SQL Server 2012.
Given below is the script that will give you all system objects related to file table without any self-join.

--This script will work in SQL Server 2012 and above.
Declare @FileTableName as varchar(50)='dbo.Databank'
Select object_name(parent_object_id) as [File Table Name]
,object_name(object_id) as [Related objects]
from sys.filetable_system_defined_objects
Where parent_object_id =object_id(@FileTableName)
GO
--OUTPUT

filetablesystemobjects1.2

Aware of any other shortcut ?

Read Full Post »

« Newer Posts - Older Posts »