Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012’

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 »

SQL SERVER 2012 – Fix – Error – 11728 – The sequence object has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Posted in Errors, SQL SERVER, tagged , , , , , on April 5, 2013| 2 Comments »

The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated is one of the new error messages came in SQL Server 2012. This error message is related to Sequence object, a new database feature shipped with SQL Server 2012.

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

Severity : 16

Error Message: The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Error Generation:

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

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Ticket]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
NO CACHE
GO
Select NEXT VALUE FOR [Ticket]
GO 3

Msg 11728, Level 16, State 1, Line 2
The sequence object ‘Ticket’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Ooopps…… I am unable to execute it.

Resolution:
There are three different methods to resolve this error.

Method 1:
In method 1, you need to RESTART the sequence and it will be resolved but temporarily. Once it reaches to its maximum, it will give you the same error again.

ALTER SEQUENCE [dbo].[Ticket]
RESTART WITH 1
GO

Method 2:
In method 2, you need to INCREASE the sequence object  maximum number (If required by business logic) but again, it will be resolved but temporarily because once it reaches to its maximum, it will give you the same error again.

ALTER SEQUENCE [dbo].[Ticket]
MAXVALUE 10000
GO

Method 3:
In method 3, you need to CYCLE the sequence object (If required by business logic) and it will never give you this error message. But again it depends on your business logic because if you follow method 3 it will duplicate your sequence numbers.

ALTER SEQUENCE [dbo].[Ticket]
CYCLE
GO

Conclusion :
You can fix this error message by using above three different methods but before selecting any method be certain of your business logic.

Read Full Post »

SQL SERVER 2012– Convert Text to Numbers

Posted in Solutions, SQL SERVER, tagged , , , , , , , on October 14, 2012| Leave a Comment »

In this article, we will convert text to number in multiple versions of SQL Server and will see the difference. I will use four different Data conversion functions (Convert, Cast, Try_Convert &  Try_Cast) to convert Text to Number.

Let me explain this with simple examples.

In SQL Server 2005/2008 :

Example 1 : (Convert Text to integer if text having numeric data only)

Declare @string as varchar(5)
Set @string ='12345'

Select Convert(int,@string) as [Convert Text to Integer]
Select Cast(@string as int) as [Cast Text to Integer]

--RESULT
Convert Text to Integer
-----------------------
12345

(1 row(s) affected)

Cast Text to Integer
--------------------
12345

(1 row(s) affected)

Example 2 : (Convert Text to integer if text having string data)


Declare @string as varchar(7)
Set @string ='raresql'

Select Convert(int,@string) as [Convert Text to Integer]
Select Cast(@string as int) as [Cast Text to Integer]
--RESULT
Convert Text to Integer
-----------------------

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value ‘raresql’ to data type int.

In SQL Server 2012 :

Example 3 : (Convert Text to integer if text having numeric data only)

Declare @string as varchar(5)
Set @string ='12345'

Select Try_Convert(int,@string) as [Convert Text to Integer]
Select Try_Cast(@string as int) as [Cast Text to Integer]

--RESULT
Convert Text to Integer
-----------------------
12345

(1 row(s) affected)

Cast Text to Integer
--------------------
12345

(1 row(s) affected)

Example 4 : (Convert Text to integer if text having string data)


Declare @string as varchar(7)
Set @string ='raresql'

Select Try_Convert(int,@string) as [Convert Text to Integer]
Select Try_Cast(@string as int) as [Cast Text to Integer]
--RESULT
Convert Text to Integer
-----------------------
NULL

(1 row(s) affected)

Cast Text to Integer
--------------------
NULL

(1 row(s) affected)

Conclusion :
If we compare Examples 1 & 3, we can easily find out that there is no difference, but we can see that Examples 2 and 4 having much difference because Cast & Convert generates an error if there is a compatibility issue with data type, but Try_Convert & Try_Cast (SQL Server 2012) will return NULL instead of error.

Read Full Post »

« Newer Posts