Feeds:
Posts
Comments

Posts Tagged ‘raresql’

SQL SERVER 2012 – How to find if a Sequence exist in database

Posted in Solutions, SQL SERVER, tagged , , , , , on October 26, 2013| Leave a Comment »

I came across this query many times whenever I was working on SEQUENCE object (A new object introduced in SQL Server 2012). Because whenever you need to use any sequence object in any piece of code, make sure it exists.

Given below are the two methods, you can use to check the existence of the sequence object.

METHOD 1 :
This is the preferred method because, it will not only check the existence of the sequence object but also it will give you the details about the sequence object.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--Do not forget to change you sequence object name
--in the below query.
SELECT * FROM sys.sequences
WHERE object_id=object_id('Seq_sample')
GO
--OUTPUT

existance of sequence object.1.1

METHOD 2 :
This is the same method we have been using since the earlier version of SQL Server. In this method, we use one of the renowned systems
object catalog view namely sys.objects. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--Do not forget to change you sequence object name
--in the below query.
SELECT * FROM sys.objects
WHERE object_id=object_id('Seq_sample')
AND [TYPE]='SO'
--OUTPUT

existance of sequence object.1.2

Read Full Post »

SQL SERVER 2012 – Fix – Error – 11730- Database name cannot be specified for the sequence object in default constraints.

Posted in Errors, SQL SERVER, tagged , , , , , on October 24, 2013| Leave a Comment »

I usually create the samples and test cases on tempdb database. Today, I came a across an issue while working on Sequence object, a new database feature shipped with SQL Server 2012. I tried to use Sequence object from some other database in tempdb database and it gave me given below error message.

Let’s discuss this in detail:

Message Number: 11730

Severity : 16

Error Message: Database name cannot be specified for the sequence object in default constraints.

Error Generation:

In the given below script, I tried to make sequence object (resides in another database (Adventureworks2012)) as a default value of a column in a table. This table resides in tempdb database. Let me create a sample to demonstrate the issue.

--This script is compatible with SQL Server 2012 and above
--Create a sequence object in AdventureWorks2012 database.
USE [AdventureWorks2012]
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [int]
START WITH 1
INCREMENT BY 1
GO

USE tempdb
GO
CREATE TABLE dbo.tbl_sample
(
[ID] int,
[Name] varchar(100)
)
GO

ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_tbl_sample DEFAULT
(NEXT VALUE FOR AdventureWorks2012.[dbo].[Seq_Sample])
FOR [ID];
GO
--OUTPUT

Msg 11730, Level 15, State 1, Line 2
Database name cannot be specified for the sequence object in default constraints.

Ooopps…… I am unable to make a sequence as a default value in a table if Sequence already resides in another database.

Resolution:
This is the limitiation of a sequence object, that you cannot make any sequence as a default value of a column if it resides in any other database. But there is a way around that you can use to achieve it. So, how can we do it ? You cannot make it as a default but you can still access the sequence even if it is outside your database boundary, using three part name (AdventureWorks2012.[dbo].[Seq_Sample]
). So, using this three part name, you can insert the sequence value in any database. Given below is the sample.

USE tempdb
GO
CREATE TABLE dbo.tbl_sample
(
[ID] int,
[Name] varchar(100)
)
GO
INSERT INTO tbl_sample
VALUES (NEXT VALUE FOR AdventureWorks2012.[dbo].[Seq_Sample],'raresql')
GO

Read Full Post »

How to restore SQL SERVER 2012 backup on earlier versions of SQL Server

Posted in Solutions, SQL SERVER, tagged , , , , , on October 23, 2013| 2 Comments »

‘Can you restore SQL Server 2012 backup in earlier versions of SQL Server?’ I came across this question many times from the community and the simple answer is NO. You cannot restore a new version of SQL Server backup in earlier versions. However, there is a way around that can be followed to achieve it. Basically, you can generate the script of all objects along with the data using SQL Server 2012 with any earlier versions compatibility and run those scripts on earlier versions of SQL Server. This is how you can get the complete data with schema from SQL Server 2012 to earlier versions.

Problem :
Before proceeding with the solution, I would like to show you the error, that happens when you restore a higher versions of
SQL Server database backup to an earlier version of SQL Server. Given below is the image.

erromessage33241.1

The media family on device ‘E:\DBBackup\AdventureWorks2012_Backup\AdventureWorks2012_Backup’ is incorrectly formed. SQL Server cannot process this media family.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)

SOLUTION :
Let me proceed with the solution, step by step.

Step 1:
Open SSMS and right click on the database that you want to restore in earlier versions of SQL Server and then select Task and then further click on Generate Script …
Given below is the image.
Restore SQL Server 2012 backup on earlier versions.1.1

Step 2 :
Given below is the informational screen, that will guide you how to generate the scripts along with the steps. Press NEXT button.
Restore SQL Server 2012 backup on earlier versions.1.2

Step 3 :
In this step, you will get two options. Given below are the details.

  1. Script entire database and all database objects
  2. Select specific database objects

Option 1 is preferred because you can generate all database objects altogether from it. However if you upgrade any object using any new features of SQL Server 2012, then you should select Option 2 and exclude that particular object and press NEXT button. Given below is the screen image.

Restore SQL Server 2012 backup on earlier versions.1.3-1

Step 4 :
In this option you need to select the script file location to save all the scripts in a particular file. Rest of the options you can leave as default. Once you are done with the file location, you can see an ADVANCE button. Click on this button. It will open the advance option for the scripting.
Restore SQL Server 2012 backup on earlier versions.1.4

Step 5 :
Once you are inside the Advance option, you need to select further two options. Given below are the details.

  1. Script for SQL Server version
  2. Type of data to script.

In the Option 1 make sure that you have selected the right earlier Version of SQL Server, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.5

In the Option 2, make sure that you have selected Schema and data, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.6-1

Once you configure both options, press OK and press Next in the previous screen, as shown below.

Restore SQL Server 2012 backup on earlier versions.1.7

Step 6
Once you press NEXT button, it will take you to the summary screen where you can see all your configuration altogether.
Again press NEXT button to proceed, as show in the image below.

Restore SQL Server 2012 backup on earlier versions.1.8

Step 7
In this step, SQL Server will automatically script all your selected object along with the data. And will give you the status report, as shown in the image below.

Restore SQL Server 2012 backup on earlier versions.1.9

Restore SQL Server 2012 backup on earlier versions.1.10

Step 8 :
Now you need to browse the file you saved in particular location in the Step 4 and Execute it in selected earlier version of SQL Server.

Let me know if you came across this problem and resolved it in a different manner.

Read Full Post »

SQL SERVER – How to implement conditional where clause

Posted in Solutions, SQL SERVER, tagged , , , on October 8, 2013| 2 Comments »

Few days ago, I received a query from one of my blog readers inquiring how to implement a conditional where clause in a SQL Server query / stored procedure. His requirement was quiet simple. He said that if he passes any filter value to the query / procedure, it should consider that filter otherwise query should ignore that filter and return all records.

Given below is the script.
SAMPLE :

USE AdventureWorks2012
GO
--DROP PROCEDURE Usp_sample
--GO
CREATE PROCEDURE Usp_sample
@City nvarchar(30),
@CountryRegionName nvarchar(50)
AS

SELECT *
FROM
HumanResources.vEmployee
WHERE (1=(CASE WHEN @City IS NULL THEN 1 ELSE 0 END) Or [City]=@City)
AND (1=(CASE WHEN @CountryRegionName IS NULL THEN 1 ELSE 0 END)
Or [CountryRegionName]=@CountryRegionName)
GO

EXAMPLE 1 : (WITHOUT any filter)
In this example, I did not pass any filter to the stored procedure, so the stored procedure ignored all filters and it returned all records as expected.

USE AdventureWorks2012
GO
EXEC Usp_sample @City=NULL,@CountryRegionName=NULL
GO

EXAMPLE 2 : (With ONE filter)
In this example, I passed only one filter to the stored procedure and it returned records on the basis of only one filter and ignored the second filter.

USE AdventureWorks2012
GO
EXEC Usp_sample @City='Renton',@CountryRegionName=NULL
GO

Read Full Post »

SQL SERVER 2012 – How to check Sequence has reached its minimum or maximum value

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 5, 2013| Leave a Comment »

Recently I came across an issue related to the sequence object (shipped in SQL Server 2012) because usually we generate the next ID from the sequence object without checking whether it has reached its limit and it cannot generate a new ID. Due to this it generates an error as well. Given below are the error details.

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘Seq_Sample1’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Sequence object maintain its minimum and maximum value by default.

So what is the solution ? But before proceeding with the solution let me create a sample.

SAMPLE :

--This script is compatible with SQL Server 2012 and above.
--Create a sequence object
USE [AdventureWorks2012]
GO
--DROP SEQUENCE [dbo].[Seq_Sample1]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample1]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
GO
--Generate sequence number up to 4 ID
--because it has a maximum limit of 4.
SELECT Next value for [dbo].[Seq_Sample1]
GO 4

SOLUTION 1 : (Not recommended)
In this solution, you need to check the minimum and maximum value of the sequence with the current value and if it matches either of them, it means that sequence has been reached to its minimum or value and you cannot generate a new ID. It is not recommended because it is a lengthy procedure. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @minimum_value As sql_variant
DECLARE @maximum_value As sql_variant
DECLARE @current_value As sql_variant

SELECT
@minimum_value=[minimum_value]
,@maximum_value=[maximum_value]
,@current_value=[current_value]
FROM
sys.sequences AS seq
WHERE name ='Seq_Sample1'

SELECT IIF(@minimum_value=@current_value
OR @maximum_value=@current_value
,'You cannot generate a new ID'
,'You can generate a new ID') AS [Result]
--OUTPUT

sequence limit.1.1

SOLUTION 2 : Using Is_exhausted property (Recommended)
In this solution, we will use a sequence property namely “is_exhausted” and can quickly check whether it reached its limit or not.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT IIF((SELECT is_exhausted FROM sys.sequences AS seq
WHERE name ='Seq_Sample1')=1
,'You cannot generate a new ID'
,'You can generate a new ID')
AS [Result]
--OUTPUT

sequence limit.1.1

CONCLUSION :
As you can see, both solutions generate the same output but solution 2 is recommended because we used a property namely “is_exhausted” and reduced the size of the script.

Read Full Post »

SQL SERVER – How do I get only the numbers before the decimal?

Posted in Solutions, SQL SERVER, tagged , , , , , on October 3, 2013| 1 Comment »

I came across this query today, when I was generating some reports and I do not want to show the decimals in the report. I did some research and came up with multiple solutions. Before proceeding with the solution, let me create a sample to demonstrate the solution.

Sample :

USE Tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[Col_ID] INT,
[Col_Decimal] decimal(18,4)
)
GO
INSERT INTO tbl_sample VALUES (1,12345.9876)
INSERT INTO tbl_sample VALUES (2,-12345.9876)
INSERT INTO tbl_sample VALUES (3,123.45)
INSERT INTO tbl_sample VALUES (4,12.00)
GO

Given below are the solutions.

Solution 1 : (Without any function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, [Col_Decimal] - ([Col_Decimal]%1) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.1

Solution 2 : (Using ROUND function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ROUND([Col_Decimal],0,1) As [Col_Number]
FROM tbl_sample
GO
--OUPUT

get the number before decimals.1.2

Solution 3 : (Using CAST function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, CAST([Col_Decimal] AS INT) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.3

Read Full Post »

SQL SERVER 2012 – How to check date is End of Month date or not?

Posted in Solutions, SQL SERVER, tagged , , , , , on September 30, 2013| 3 Comments »

Today is month end (30 September 2013) and I was checking my general checklist for month end closing procedure. I saw one of my scripts that I had written earlier regarding how to check whether a date is month end date or not. So, I thought of re-writing this script even much shorter & efficient than my earlier script using EOMONTH .

Let me create a sample table to demonstrate.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-03-31')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-12-31')
GO

SOLUTION 1 :
Given below is the script that we usually use in SQL Server 2005/2008 to check whether the date is End of month date or not.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT [ID],[Date],
(CASE WHEN
CONVERT(varchar(11)
,DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date])+1, -1),120)=[Date]
THEN 'Month End' ELSE 'Not a Month End' END) As [Result]
FROM tbl_Sample
GO

isdate is a mont end date.1.1

SOLUTION 2 :
Given below is the script, that I developed using new functions (EOMONTH & IIF) shipped in SQL Server 2012 to check whether the date is End of month date or not. Due to these functions, we can reduce the script size and improve the performance.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT [ID],[Date],
IIF(EOMONTH([Date])=[Date],'Month End','Not a Month End')
As [Result]
FROM tbl_Sample
GO
--OUTPUT

isdate is a mont end date.1.2

CONCLUSION :
As you can see, both solutions have the same result set but Solution 2 is recommended because it is using only one function to check whether the date is month end date or not, instead of a lot of date functions.

Read Full Post »

SQL SERVER 2012 – Fix – Error :11521 – The metadata could not be determined because statement ‘%.*ls’ uses an undeclared parameter in a context that affects its metadata.

Posted in Errors, SQL SERVER, tagged , , , , , on September 29, 2013| 2 Comments »

In the earlier versions of SQL Server, we usually used SET FMTONLY to grab the metadata of the query. In SQL Server 2012, a new system stored procedure shipped namely sp_describe_first_result_set to browse the metadata of the first possible result set of the queries. Today, I will discuss a new error message shipped in SQL Server 2012 which I came across when I was trying to get the metadata of a query having variables.

Let’s discuss this in detail:

Message Number: 11521

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ uses an undeclared parameter in a context that affects its metadata.

Error Generation:
Let me create a sample to demonstrate this error. In this sample, I tried to pass a variable with its value along with the query and it executed successfully.
Given below is the script and its output.

USE AdventureWorks2012
GO
DECLARE @Country as varchar(100)
SET @Country='United Arab Emirates'
SELECT @Country as [Country], CountryRegionCode,CurrencyCode
FROM [Sales].[CountryRegionCurrency]
WHERE CountryRegionCode='AE'
GO
--OUTPUT

errormessage11521.1

You can see that the above script executed successfully. Now I would like to view the metadata of the first possible result set of the above SQL query. Given below is the script to view the metadata using sp_describe_first_result_set

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
SELECT @Country as [Country], CountryRegionCode,CurrencyCode FROM [Sales].[CountryRegionCurrency]
WHERE CountryRegionCode=''AE'''
GO
--OUTPUT

Msg 11521, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘SELECT @Country as [Country], CountryRegionCode,CurrencyCode FROM [Sales].[CountryRegionCurrency] W’ uses an undeclared parameter in a context that affects its metadata.

Ooopps…… I am unable to get the metadata of the above query.

Resolution:
The reason behind this error is, I declared a variable and passed it in the above query at the time of execution but when I tried to get its metadata of the first possible result set, I did not declare the variable within sp_describe_first_result_set. Because I thought that only the query is enough to get the metadata information. Make sure that if you pass any variable into the query, you also declare it at the time of metadata retrieving using sp_describe_first_result_set. Let’s correct the script and declare the variable.

Given below is the correct script.

USE AdventureWorks2012
GO
sp_describe_first_result_set @tsql =N'
DECLARE @Country as varchar(100)
SELECT @Country as [Country], CountryRegionCode,CurrencyCode FROM [Sales].[CountryRegionCurrency]
WHERE CountryRegionCode=''AE'''
GO
GO
--OUTPUT

erromessage11521.1.2

Conclusion :
Remember, whenever you use any SQL query having variables and would like to view the metadata of the first possible result set of that query using sp_describe_first_result_set,  always declare the variables used in the query.

Read Full Post »

SQL SERVER 2014 – List of all Table & its Index sizes in MBs along with row count

Posted in Solutions, SQL SERVER, tagged , , , , on September 28, 2013| 2 Comments »

How to get list of all tables’ and its index sizes along with row count looks very simple ? We do have the solution for it. Either you can use sp_spaceused or you can use one of my own solutions.

But things became strange when I tried both solutions on SQL Server 2014 having memory optimized table and both solutions return zero for memory optimized tables. Given below is the script and its output in SQL Server 2014.

USE hkNorthwind
GO
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
GO

table and index size of memory optimized table.1.1

As you can see in the above image, all memory optimized tables showing zero result. Oopsssssss. So what is the solution ?
I started my research and developed a solution . Given below is the script.

SOLUTION :

USE hkNorthwind
GO
DECLARE @PageSize float
SELECT @PageSize=v.low/1024.0 FROM MASTER.dbo.spt_values v WHERE v.number=1 and v.type='E'

SELECT
OBJECT_SCHEMA_NAME(tbl.object_id) as [Schema Name]
, OBJECT_NAME(tbl.object_id) as [Table Name]
, CASE
WHEN (tbl.is_memory_optimized=0) THEN
ROUND(TRY_CONVERT(float,ISNULL((SELECT @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE i.object_id = tbl.object_id),0.0))/1024,3)
ELSE
Round(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_table_kb])
FROM [sys].[dm_db_xtp_table_memory_stats] tms
WHERE tms.object_id = tbl.object_id), 0.0))/1024,3)
END
as [DataSpaceUsed (In Mbs)]
, (CASE
WHEN (tbl.is_memory_optimized=0) THEN
ROUND(TRY_CONVERT(float,ISNULL((
(SELECT SUM (used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = tbl.object_id)
+ ( CASE (SELECT count(*) FROM sys.internal_tables WHERE parent_id = tbl.object_id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222))
WHEN 0 THEN 0
ELSE (
SELECT sum(p.used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = tbl.object_id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id)
END )
- (SELECT SUM (CASE WHEN(index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END)
FROM sys.dm_db_partition_stats WHERE object_id = tbl.object_id)
) * @PageSize, 0.0) )/1024,3)
ELSE
ROUND(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_indexes_kb])
FROM [sys].[dm_db_xtp_table_memory_stats] tms
WHERE tms.object_id = tbl.object_id), 0.0)) /1024,3)
END)
as [IndexSpaceUsed (In Mbs)]

,(CASE
WHEN (tbl.is_memory_optimized=0) THEN
OBJECTPROPERTYEX(OBJECT_ID, N'Cardinality')
ELSE
(
SELECT ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes as Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2 AND Ind.object_id=tbl.object_id
)
END) [Total No of Rows]

FROM
sys.tables as tbl
--OUPUT

table and index size of memory optimized table.1.2

As you can see in the above script (solution), SQL Server 2014 handles disk based table and memory optimized table space, index space and row count separately. So you need to calculate it separately.

Let me know if you came across the same situation and its solution.

Read Full Post »

SQL SERVER 2012 – How to convert varchar to currency

Posted in Solutions, SQL SERVER, tagged , , , , on September 27, 2013| Leave a Comment »

I had come across this problem (how to convert varchar to currency) a couple of years ago and fixed this problem. Recently I had a chance to revisit my solutions and I discovered that we can re-script this solution much more efficiently than I did earlier, using FORMAT & TRY_CONVERT (new functions shipped in SQL Server 2012).

Note : It is recommended to do this formatting from the front end of the application.

Let me create a sample to demonstrate the solution :

SAMPLE:

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
CREATE TABLE [dbo].[tbl_sample]
(
[Col_ID] [int] NULL,
[Col_varchar] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (1, N'1000.00')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (2, N'-10.21')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (3, N'1234.5678')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (4, N'123.45')
GO

SOLUTION:
In this solution, first of all, we need to convert the data into money data type then format it into any currency using culture.
Given below is the solution.

SELECT
[Col_ID]
,[Col_varchar]
,FORMAT(TRY_CONVERT(money,[Col_varchar]),'C','en-US')
AS [Varchar_to_currency]
FROM [tbl_sample]
GO

varchar_to_currency.1.1

Read Full Post »

« Newer Posts - Older Posts »