Feeds:
Posts
Comments

Archive for the ‘Errors’ Category

In SQL Server 2022, an enhancement came in STRING_SPLIT() function, which was a long awaited functionality. This enhancement provides a new column name ordinal. I have written an article about it in detail.

Recently, I was using STRING_SPLIT() function and came across an error as mentioned below.

Error:

Invalid column name ‘ordinal’.

Example:

In the given below example, I wrote a simple script by using STRING_SPLIT() function and placed an ordinal column in the select statement. This ordinal column provides the row number for each split string which is a very handy functionality but I got an error as shown below.

USE Northwind
GO

SELECT CategoryID
     , CategoryName
	 , Value AS Description
     , Ordinal
FROM   Categories
CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(100),Description), ',');
GO
--OUTPUT

Solution:

Remember, whenever you place ordinal column in select statement, you MUST pass “1” as third argument in STRING_SPLIT() function. By default, it takes third argument as “0” which was the case in the above example and in such cases, this function does NOT enable ordinal column in the select statement and resulted in error.

Let’s place ordinal column in the select statement and the MOST important thing, we MUST pass “1” in the third argument of STRING_SPLIT() function. This time, it will run sucessfully as shown below.

USE Northwind
GO

SELECT CategoryID 
     , CategoryName
     , Value AS Description
     , Ordinal
FROM   Categories
CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(100),Description), ',', 1);
GO
--OUTPUT

Conclusion:

Whenever you place ordinal column in select statement, you MUST pass “1” as third argument in STRING_SPLIT() in order to avoid this error.

Read Full Post »

STRING_SPLIT() is one of the most used string functions, which splits the string based on one character separator. Earlier I have written an article about it in detail.

Recently, I was using STRING_SPLIT() function and I came across given below error and the reason is I used two characters separator which is NOT valid for STRING_SPLIT() function:

Error :

Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’.

DECLARE @String AS VARCHAR(50);
SET     @String  = '1:30PM2:30PM3:30PM4:30PM5:30PM6:30PM7:30PM';

SELECT * FROM STRING_SPLIT(@String, 'PM');
GO
--OUTPUT

Solution:

As per SQL documentation, STRING_SPLIT() function can take only one character in the separator argument.

Now, in order to solve this issue, we are going to use REPLACE() function to replace the two characters separator “PM” to one character separator “|“. Also, make sure that you MUST replace with a separator which is NOT available in your data, I used “|” which is not available in my entire data. After that, we can simply split the string using STRING_SPLIT() function as shown in the below example.

--Declare a variable
DECLARE @String AS VARCHAR(50);
 
--Set variable value 
SET @String  = '1:30PM2:30PM3:30PM4:30PM5:30PM6:30PM7:30PM';
 
--Replace PM with another single character separator '|'
SET @String  = LEFT(REPLACE(@String,'PM','|'),LEN(REPLACE(@String,'PM','|'))-1);
 
--Apply String_Split function
SELECT * FROM STRING_SPLIT(@String, '|');
GO
--OUTPUT

Conclusion:

Try to use one character separator in STRING_SPLIT() function, then you will not get above error.

Read Full Post »

TRANSLATE() is one of the handy functions of SQL Server when it comes to replace one or more characters to another set of characters in one go. I have earlier written an article about it in detail.

Recently, I was using TRANSLATE() function and came across an error as mentioned below.

Error :

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.


DECLARE @Number AS VARCHAR(15)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , TRANSLATE(@Number,'+','00') AS TranslatedPhoneNumber;

GO
--OUTPUT

Solution:

As per SQL Server documentation translations must be the same data type and length as characters. And if you look at our script, the length of both characters (+) and translation (00) is not same.

In such cases, we need to simply apply REPLACE() function since TRANSLATE() function cannot handle such cases as shown below.


DECLARE @Number AS VARCHAR(15)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , REPLACE(@Number,'+','00') AS REPLACEDPhoneNumber;
GO
--OUTPUT

Conclusion:

In case of this error, we should use REPLACE() function instead of TRANSLATE() function.

Read Full Post »

EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null is one of the new error messages noticed in SQL Server 2012 and above. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS shipped with SQL Server 2012.

Lets discuss this in detail:
Message Number: 11553

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null.

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

Use AdventureWorks2014
GO
--DROP PROCEDURE usp_Sample
--GO
CREATE PROCEDURE usp_Sample
AS
SELECT  
  [BusinessEntityID]
, [FirstName]
, [MiddleName]
, [LastName]
FROM [HumanResources].vEmployee ORDER BY BusinessEntityID 
GO
EXEC usp_Sample
GO

Error number 11553.1.1

You can see that the above stored procedure is executed successfully and it has ONE result set.

Lets try to execute it using WITH RESULT SETS clause.

USE AdventureWorks2014 
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
  [First Name] Name NOT NULL,
  [MiddleName] Name NOT NULL,
  [LastName] Name NOT NULL
));
GO
--OUTPUT
Msg 11553, Level 16, State 1, Procedure usp_Sample, Line 22
EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #3 in result set #1, and the corresponding value sent at run time was null.

Ooops…… I am unable to execute it properly. It returned the error message.

Error number 11553.1.2

Resolution:
Why this error ? Because, I specifically mentioned in the RESULT SETS that none of the columns should return NULL and I did not realize that stored procedures return some NULL values in the middle name column and due to this I got this error. So, when you mention any column as a NOT NULL in RESULT SETS, please make sure that the stored procedure must NOT return NULL VALUE for that particular column. Lets re-write the RESULT SET and this time we need to mention, middle name column is NULL as shown below.

USE AdventureWorks2014 
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
  [First Name] Name NOT NULL,
  [MiddleName] Name NULL,
  [LastName] Name NOT NULL
));
GO
--OUTPUT

Error number 11553.1.3

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, along with the nullable type, make sure that the data that comes from stored procedure should match with the nullable type. In other words, if you define any column in RESULT SETS clause as NOT NULL column then make sure that stored procedure MUST NOT return NULL value in that particular column to avoid such errors.

Read Full Post »

Few days ago, I created some users in my development environment and gave them access to use my development database. After sometime, I received a complaint that when they run a normal query it is working fine but when they try to run a query along with execution plan (Ctrl + M), SQL server generates given below error.

Let me explain this error in detail :

Message Number: 262

Severity : 14

Error Message: SHOWPLAN permission denied in database ‘AdventureWorks2012’.

Error Generation:

Let me create a sample to demonstrate this error.

USE AdventureWorks2012
GO

--Create a user
CREATE USER Imran WITHOUT LOGIN;
GO
--Grant select permission to Imran user
GRANT SELECT ON [HumanResources].[Department] TO Imran;
GO

-- Press Ctl+M to Include Actual Execution Plan
EXECUTE AS USER = 'Imran';
SELECT * FROM [HumanResources].[Department] ;
REVERT;

Msg 262, Level 14, State 4, Line 12
SHOWPLAN permission denied in database ‘AdventureWorks2012’.

ShowPlan error.1.1

Ooopps…… I am unable to show the actual execution plan (Ctrl + M) of the select query as shown above.

Resolution:

The resolution is very simple, actually, once you created the user you did not grant SHOWPLAN access to the USER (Imran). Lets give this user the SHOWPLAN access. Given below is the script.

Grant SHOWPLAN access

USE AdventureWorks2012
GO
GRANT SHOWPLAN TO Imran
GO
-OUTPUT

Command(s) completed successfully.

Now, the SHOWPLAN access has been granted, lets re-run the above query with execution plan and it works as shown below.

ShowPlan error.1.2

Conclusion :

Remember, whenever you want any user to include the execution plan in the query, you MUST give him SHOWPLAN access.

Read Full Post »

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.

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 »

File table is one of the best features shipped with SQL Server 2012, it is a special type of table which allows us to store files and folders in windows and we can easily access it through windows application & SQL Server without any customization. I have written multiple articles on file table. However, still doing research on this special table. Today, I came across an issue with filetable at the client side once they were doing some activity on it. The issue is when they tried to explore FileTable Directory in a FileTable, the option was disabled as shown below. However I can perform any activity on file table except to explore it.

Explore FileTable Directory.1.1

Now, I cannot explore this file table using explore the filetable directory option. It means that I cannot view the filetable’s files and folder using windows directory. So what is the problem and possible solution ?

Resolution:
I asked the client what activity they did with filetable but they had no clue. So, I started doing my research and finally resolved it by MSDN help. In fact the Filetable’s NON_TRANSACTED_ACCESS has been switched OFF by mistake as shown below.

Explore FileTable Directory.1.2

Wow, I found the problem that has disabled the explore FileTable directory as mentioned above. Now what is the solution ?
Given below is the script that can enable the NON_TRANSACTED_ACCESS that can result in enabling the explore FileTable directory.

--This script is compatible with SQL Server 212 and above.
 ALTER DATABASE SameplDB
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL );
GO

Once you execute the above script, try again to check if the explore FileTable Directory in the FileTable has been enabled or not. This time you will succeed as shown below.

Explore FileTable Directory.1.3

Conclusion:
Whenever, you come across such issue, you must check the NON_TRANSACTED_ACCESS of the particular database in order to fix this issue.

Read Full Post »

SQL Server upgrade advisor is one of the important tools and it plays a pivotal role when you need to assess the upgrade in your SQL Server. However, I got an error message when installing SQL Server 2012 upgrade advisor. Given below is the screen image of the error message.

SQL Upgrade Advisor Setup.1.1

Let me explain the error and its solution in detail.

Error Message: 
Setup is missing prerequisites:
-Microsoft SQL Server 2012 Transact-SQL ScriptDom, which is not installed by
Upgrade Advisor Setup. To continue, install SQL Server 2012 Transact-SQL
ScriptDom from below hyperlink and then run the Upgrdae Advisor Setup operation
again :

Go to http://go.microsoft.com/fwlink/?linkID=216742

Resolution:
It seems that it is not only the error message but also the solution as well. Let me resolve it step by step.

Step 1 :
First of all, you need to go to the URL as mentioned above in order to fix this bug.

Step 2 :
Once you browse the above link, it will take you to a page where you need to download the instruction to proceed further. However, you can select the Install instruction link shown below to get the list of all SQL Server tools.

SQL Upgrade Advisor Setup.1.2

Step 3 :
Once you browse install instruction, you need to scroll down till you will find Microsoft® SQL Server® 2012 Transact-SQL ScriptDom. It is available for both (x86 & x64) system types as shown below. However make sure the system type of your server before downloading it. Its installation is self explanatory. Once you install it, you can easily install SQL Server 2012 upgrade advisor.

SQL Upgrade Advisor Setup.1.3

Read Full Post »

Older Posts »