Feeds:
Posts
Comments

Archive for the ‘Errors’ Category

SQL Server – Fix – Error – This backend version is not supported to design database diagrams or tables

Posted in Errors, SQL SERVER, Uncategorized, tagged , , , , on April 6, 2023| 1 Comment »

Recently, I was working on one of the databases and I tried to insert a table through SQL Server Management Studio (SSMS) and I came across the following error.

Error:

The backend version is not supported to design database diagrams or tables.

Reproduce Error:

Let me reproduce this error step by step.

Step 1:

Let’s create a new database namely ‘TestDB’ as shown below.

 
CREATE DATABASE TestDB;
GO

Step 2:

Now, lets expand the database TestDB, then right click on the Tables option and then select New and then select Table… as shown below.

Step 3:

This will display the following error.

Solution:

This error occurs when you have the older version (18) of SSMS installed on your device. To check the version installed on your device, navigate to the Help menu and click on About menu in the menu bar as shown below.

A new window will pop up with all the version details of the SSMS.

In order to solve this error, you need to install a new version (19 or higher) of SQL Server Management Studio (SSMS). You can download it from here

Conclusion:

Make sure you have updated version of SSMS all the time to avoid such errors.

Read Full Post »

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the RTRIM() function

Posted in Errors, SQL SERVER, tagged , , , on March 30, 2023| Leave a Comment »

In SQL Server 2022, an enhancement came in RTRIM()  function, which was a long awaited functionality. This enhancement helps us to remove last character from the string. I have written an article about it in detail.

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

Error:

Invalid length parameter passed to the rtrim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
 EmployeeID   INT IDENTITY(1,1),
 EmployeeName VARCHAR(250),
 EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using RTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , RTRIM(EmailAddress, @separator) AS EmailAfterRTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , RTRIM(EmailAddress, @separator) AS EmailAfterRTrim
FROM #Employees;
GO

Conclusion:

Whenever you use RTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.

Read Full Post »

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the LTRIM() function

Posted in Errors, SQL SERVER, tagged , , , on March 29, 2023| Leave a Comment »

In SQL Server 2022, an enhancement came in LTRIM() function, which was a long awaited enhancement. This enhancement helps us to remove first character from the string. I have written an article about it in detail.

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

Error:

Invalid length parameter passed to the ltrim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
EmployeeID   INT IDENTITY(1,1),
EmployeeName VARCHAR(250),
EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using LTRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

USE tempdb
GO

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , LTRIM(EmailAddress, @separator) AS EmailAfterLTrim
FROM #Employees;
GO

Conclusion:

Whenever you use LTRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length to avoid such error.

Read Full Post »

SQL SERVER 2022 – Error – Fix – Invalid length parameter passed to the TRIM() Function

Posted in Errors, SQL SERVER, tagged , , on March 28, 2023| Leave a Comment »

In SQL Server 2022, an enhancement came in TRIM() function which was a long awaited functionality. This enhancement helps us in removing first or last character from the string. I have written an article about it in detail.

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

Error:

Invalid length parameter passed to the trim function.

Sample:

 
USE tempdb
GO

CREATE TABLE #Employees
(
 EmployeeID   INT IDENTITY(1,1),
 EmployeeName VARCHAR(250),
 EmailAddress VARCHAR(MAX)
);
GO

INSERT INTO #Employees
VALUES ('Alex','alex114@gmail.com;')
     , ('Sarina','sarina152@gmail.com;')
     , ('Sophia','sophiaa123@gmail.com;');
GO

SELECT * FROM #Employees;
GO

Example:

In the given below example, I have written a simple script using TRIM() function and I declared separator as variable and kept its data type & length as VARCHAR(MAX). This script generates an error as shown below.

DECLARE @separator VARCHAR(MAX);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO

Solution:

The solution to this problem is that while declaring datatype and length of a separator variable, we should never use MAX as its length since it does not support. We should always give length in numbers as shown below.

DECLARE @separator VARCHAR(1);
SET @separator = ';';

SELECT EmployeeID
	 , EmployeeName
	 , EmailAddress
	 , TRIM(@separator FROM EmailAddress) AS EmailAfterTrim
FROM #Employees;
GO

Conclusion:

Whenever you use TRIM() function by using a separator variable, you should always define the length of the variable. Please don’t use MAX as a length in order to avoid such error.

Read Full Post »

SQL Server 2022 – Error – Fix – Invalid column name ‘ordinal’

Posted in Errors, SQL SERVER, tagged , , , on December 12, 2022| Leave a Comment »

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 »

SQL Server 2022 – Fix – Error – Procedure expects parameter ‘separator’ of type ‘nchar(1)/nvarchar(1)’

Posted in Errors, SQL SERVER, tagged , , , , on December 2, 2022| Leave a Comment »

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 »

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

Posted in Errors, SQL SERVER, tagged , , , , , on November 14, 2022| Leave a Comment »

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 »

SQL SERVER – Fix – Error – 11553 – EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d

Posted in Errors, SQL SERVER, tagged , , , , , on September 4, 2015| Leave a Comment »

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 »

SQL SERVER – Fix – Error : 262- SHOWPLAN permission denied in database.

Posted in Errors, SQL SERVER, tagged , , , on July 30, 2015| 1 Comment »

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 »

SQL SERVER – Fix – Error :195 – ‘TRY_CONVERT’ is not a recognized built-in function name.

Posted in Errors, SQL SERVER, tagged , , , , on April 15, 2015| 2 Comments »

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 »

Older Posts »