Feeds:
Posts
Comments

Archive for the ‘Errors’ Category

TRY_CONVERT is one of the new conversion functions shipped with SQL Server. This function converts expression from one data type to another data type. The beauty of this function is that if it fails to convert, it returns NULL value as a results and due to this functionality, it has an edge over CONVERT function. I started using this function wherever conversion required since SQL Server 2012, however, I received an error message this morning when I was trying to use TRY_CONVERT function in SQL Server 2014 as shown below.

Let me explain this error in detail :

Message Number: 195

Severity : 15

Error Message: ‘TRY_Convert’ is not a recognized built-in function name.

Error Generation:

Let me create a sample using TRY_Convert  function to demonstrate this error.

USE SampleDB
GO
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
SELECT @@VERSION
GO

Msg 195, Level 15, State 10, Line 5
‘TRY_CONVERT’ is not a recognized built-in function name.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)

Try_Convert .1.2

Ooopps…… I am unable to use TRY_CONVERT even though I am using SQL Server 2014 as shown above.

Resolution:

The resolution is very simple, actually, what you are trying to do is to use TRY_CONVERT function in one of the databases having compatibility less than 110 even though you are using SQL Server 2014. Lets fix this issue step by step.

Step 1: Check compatibility

USE SampleDB;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'SampleDB';
GO
--OUTPUT

compatibility_level
——————-
100

(1 row(s) affected)

Step 2: Change compatibility
As you can see above the compatibility of the database is below 110, lets change it to enjoy the core functionality of SQL Server 2014.
Note : Test the compatibility change of your database on your test/development database first, before proceeding to production database.

USE master
GO
ALTER DATABASE SampleDB
SET COMPATIBILITY_LEVEL = 120;
GO

Step 3: Use TRY_CONVERT

USE SampleDB
GO
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
--OUTPUT

Try_Convert .1.1

Conclusion :

Remember, whenever you use NEW SQL Server functions that are compatible with specific versions / compatibility level, you MUST check the version/ compatibility before implementing those functions.

Advertisements

Read Full Post »

Change Data Capture (CDC) is one of the frequently used features in SQL Server 2008 and above, it records any (CDC enabled) table’s changes and stores in audit tables. Recently, I upgraded one of my client’s database from SQL Server 2005 database to SQL Server 2012 and one of the key reasons to upgrade is to utilize the new features in the upgraded version. Once I started enabling CDC feature in few tables of the database it gave me given below error.

Message Number: 22939

Severity : 16

Error Message: The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Error Generation:
I presume that CDC has been enabled on this particular database. Let me create a sample table to demonstrate this error.

USE AdventureWorks2012
GO
--Create Sample Table
CREATE TABLE tbl_Sample
(
 [ID] INT NOT NULL,
 [NAME] VARCHAR(50)
)
GO

-- Enable CDC feature on this table with net changes support parameter.
 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
GO
--OUTPUT

Error Message 22939.1

Msg 22939, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 194
The parameter @supports_net_changes is set to 1, but the source table does not have a primary key defined and no alternate unique index has been specified.

Ooopps…… I am NOT able to enable CDC on sample table.

Resolution:
The reason behind this error is that you do not have either primary key or unique index on the sample table and you want to enable net changes support in CDC. Before proceeding with the resolution, you should check whether you can create a Primary Key or Unique Index on the table to avoid such error.
Given below is the simple script to add Primary Key OR Unique Index in the sample table.

--Create Primary Key
USE AdventureWorks2012
GO
ALTER TABLE dbo.tbl_Sample ADD PRIMARY KEY (ID)
GO

-- Create Unique Index
USE AdventureWorks2012
GO
ALTER TABLE tbl_Sample
ADD CONSTRAINT UX_Constraint UNIQUE (ID)
GO
--OUTPUT

You can execute any one of the above scripts and can create Primary Key or Unique Index in the sample table.

Once you executed the above script, you can easily enabled the CDC with supports_net_changes as shown below.

 USE AdventureWorks2012
 GO
 EXEC sys.sp_cdc_enable_table
 @source_schema = N'dbo',
 @source_name = N'tbl_sample',
 @role_name = NULL,
 @supports_net_changes = 1
 GO
--OUTPUT

Command(s) completed successfully.

Error Message 22939.2

Conclusion :
Remember, whenever you need to enable CDC with supports_net_changes, make sure that the particular table HAS either Primary Key or Unique Index in order to avoid this error.

Read Full Post »

Change Data Capture (CDC) has been discussed in detail in my earlier articles. In this article, I will discuss an error message that I came across while disabling the CDC for a table due to insufficient parameters.

error

Let me explain this error in detail :

Message Number: 22960

Severity : 16

Error Message: Change data capture instance ‘%s’ has not been enabled for the source table ‘%s.%s’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Error Generation:

Let me DISABLE CDC feature on a particular table.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = NULL
GO

Msg 22960, Level 16, State 1, Procedure sp_cdc_disable_table_internal, Line 75
Change data capture instance ‘(null)’ has not been enabled for the source table ‘HumanResources.Department’. Use sys.sp_cdc_help_change_data_capture to verify the capture instance name and retry the operation.

Ooopps…… I am unable to disable CDC on this table. How to fix it ?

Resolution:
The resolution is very simple because partially it is explained in the error message itself. Let me fix this error step by step.

Step 1 :
First of all, you need to execute sys.sp_cdc_help_change_data_capture (A system stored procedure) in order to find the capture instance name of that particular table name as shown below.

USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

error message 22960.1

Step 2 :
The next step is to get the capture instance name of the particular table from the above result set and pass it in the sys.sp_cdc_disable_table (A system stored procedure) to disable the CDC feature from that table as shown below.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO

Conclusion :

Remember, whenever you need to disable CDC feature from any table, find out the capture instance name of the table and then disable it using sys.sp_cdc_disable_table in order to avoid this error.

Read Full Post »

We usually use temporary table to keep temporary data in the stored procedure, it can be local, global or a table variable. Recently, I was working on the stored procedures’ metadata in a client database using sp_describe_first_result_set (a new system stored procedure shipped with SQL Server 2012). Ooopps….. there’s an error, let me explain the error in detail. This error message is available in SQL Server 2012 and above.

Message Number: 11526

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’ uses a temp table.

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

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
CREATE TABLE #temp_Sample
(
 [ID] INT,
 [NAME] VARCHAR(32)
)
INSERT INTO #temp_Sample VALUES (1,'Bob')

SELECT * FROM #temp_Sample
END
GO

EXEC [dbo].[usp_sample]
GO
--OUTPUT

error_message_11526.1.1

You can see that I have created a stored procedure and executed it as well and got the result set. 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

USE tempdb
GO
Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘INSERT INTO #temp_Sample VALUES (1,’Bob’)’ in procedure ‘usp_sample’ uses a temp table.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, you used a local temporary table in the stored procedure and you cannot view the metadata using sp_describe_first_result_set. So what to do ? Very simple guys, just convert local temporary table to table variable and view its metadata.
Given below is the correct script.

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
DECLARE @temp_Sample TABLE
(
 [ID] INT,
 [NAME] VARCHAR(32)
)
INSERT INTO @temp_Sample VALUES (1,'Bob')

SELECT * FROM @temp_Sample
END
GO

Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

error_message_11526.1.2

Conclusion :
Remember, whenever you use temporary table in the stored procedure and would like to view the metadata of that stored procedure using sp_describe_first_result_setalways convert local temporary table to table variable.

Read Full Post »

I came across this error message when I created full-text index on one of the file tables (a new table concept came in SQL Server 2012) and tried to create a full text index. Most likely this error is related to semantic search (a new type of search concept came in SQL Server 2012).
Let me explain this error in detail :

Message Number: 41209

Severity : 10
Error Message: A semantic language statistics database is not registered. Full-text indexes using ‘STATISTICAL_SEMANTICS’ cannot be created or populated.

Error Generation:
I tried to create full-text index on file table and received given below error message.

errormessage41209.1.1

RESOLUTION :
The resolution is very simple for this error. Basically, you have attached semantic language statistics database in your SQL Server but most probably you’ve forgotten to register it.
Lets debug this error step by step.

Step 1:
First of all, lets check whether you registered the semantic database or not. Given below is the script.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.3

As you can see that the above query result set is blank, which means semantic database is not registered.

Step 2:
Now, you just need to register semantic database, given below is the script.

USE master
GO
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
GO

Step 3 :
Once you registered the semantic database, execute the Step 1 again and check whether it is registered or not.
Given below is the same script but result set would be different because you registered the semantic database.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.6

You can view the above result set and see that semantic database has been registered. You can create full text indexes on file tables without any errors.

HAPPY NEW YEAR 🙂

Read Full Post »

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 »

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 »

Older Posts »