Feeds:
Posts
Comments

Archive for the ‘Errors’ Category

The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid is one of the new error messages shipped with SQL Server 2012. This error message is related to sp_describe_undeclared_parameters, a new system stored procedure shipped with SQL Server 2012 as well.

Lets discuss this in detail:
Message Number: 11507

Severity : 16

Error Message: The parameter type for ‘%.*ls’ cannot be deduced because no type would make the query valid.

Error Generation:

Given below is the script that I tried to execute but it gave me this error.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID
And Instructions =@Instruction'
GO
--OUTPUT

Msg 11507, Level 16, State 1, Line 1
The parameter type for ‘@Instruction’ cannot be deduced because no type would make the query valid.

Ooopps…… I am unable to execute it.

erromessage11507.1.1

Resolution:

To resolve this issue you need to go to details of sp_describe_undeclared_parameters because sp_describe_undeclared_parameters does not support few data types and if those data types comes as undeclared parameters, it generates the error message.
Given below are the details of unsupported data types:

  • image
  • text
  • ntext
  • xml

Lets rewrite the script and remove the xml parameter from the script.
Given below is the script
:

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql = N'
Select * from [Production].[ProductModel]
Where ProductModelID=@ProductModelID '
--OUTPUT

Conclusion :
Remember, whenever you use sp_describe_undeclared_parameters, make sure you are using only supportive data type columns.

Let me know if you faced this issue and solved it in a different way.

Read Full Post »

EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid data type usage in the new feature EXEC WITH RESULT SETS.

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

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘%ls’ for column #%d in result set #%d, and the corresponding type sent at run time was ‘%ls’; there is no conversion between the two types.

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

USE tempdb
GO
Create Procedure Usp_Resultset
As
Select 1 as [Number]
GO
EXEC Usp_Resultset
GO
--OUTPUT

errormessage11538.1.1

You can see that the above stored procedure is executed successfully and it has ONE result sets with int data type column.
Lets try to execute it using WITH RESULT SETS clause.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] xml
));
--OUTPUT
Msg 11538, Level 16, State 1, Procedure Usp_Resultset, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified type ‘xml’ for column #1 in result set #1, and the corresponding type sent at run time was ‘int’; there is no conversion between the two types.

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

errormessage11538.1.2

Resolution:
Why this error ? Because, the above stored procedure returns one result sets with int data type column but we defined  XML Data type column in WITH RESULT SETS clause. This is true that you can change the data type & column name in result sets, but the issue is, we must look at the data type compatibility as well. And as we know, int & xml data types are not compatible with each other. Lets rewrite the script and define int data type column (or any data type that is compatible with int) in the result sets to avoid this error.

USE tempdb
GO
EXEC Usp_Resultset
WITH RESULT SETS
(([Number] int
));
--OUTPUT

errormessage11538.1.3

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, make sure you use data type in the result sets that is compatible with the source column data type to avoid such errors discussed above.

Read Full Post »

Invalid data type %.*ls in function %.*ls. is one of the new error messages in SQL Server 2012. This error message is related to PARSE function, a new conversion function shipped with SQL Server 2012.

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

Severity : 15

Error Message: Invalid data type %.*ls in function %.*ls.

Error Generation:

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

SELECT PARSE(GETDATE() AS varchar(11)  USING 'en-US') AS Result;
--OUTPUT

Msg 10761, Level 15, State 2, Line 1
Invalid data type varchar in function PARSE.

Ooopps…… I am unable to execute it.

Resolution:
In the above example, I tried to parse a datetime into a varchar. First of all, I checked the syntax of PARSE function but the syntax is perfectly fine but the problem is, I used the wrong data type and that datatype is not compatible with the PARSE function.

Here is the list of compatible data types (Numeric & datetime data types) that you can use with this function.

Lets rewrite the above statement with the compatible data types using PARSE function. Given below is the script.

SELECT PARSE('2013-07-12' AS datetime USING 'en-US') AS Result;
--OUTPUT

Result
———————–
2013-07-12 00:00:00.000

(1 row(s) affected)

Conclusion :
Whenever you use PARSE function, make sure that you are using the compatible data type (Numeric & datetime data types) in it.

Read Full Post »

An invalid value was specified for argument ‘%.*ls’ for the given data type  is one of the new error messages in SQL Server 2012. This error message is related to Sequence object, a new database feature shipped with SQL Server 2012.

Lets discuss this in detail:
Message Number: 11708

Severity : 16

Error Message: An invalid value was specified for argument ‘%.*ls’ for the given data type.

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].[Seq_sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 256
NO CACHE
GO

Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument ‘MAXVALUE’ for the given data type.

Ooopps…… I am unable to execute it.

Resolution:
The issue is quite simple. I created the sequence object using tinyint data type and range of tinyint data type is from 0 to 255 as per MSDN. But if you observe carefully, in my above example, the maximum value of sequence object is 256 and that is more than the tinyint data type maximum range (255).

Lets rewrite the script and correct the maximum value range. It must be less than or equal to the defined data type range.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 255
NO CACHE
GO

Conclusion :
Remember, whenever you create the sequence object always keep the minimum and maximum value within the range of the data type.

Let me know if you have a better solution.

Read Full Post »

EXECUTE statement failed because its WITH RESULT SETS clause specified %d column(s) for result set number %d, but the statement sent %d column(s) at run time is one of the new error messages seen in SQL Server 2012. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS.

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

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified %d column(s) for result set number %d, but the statement sent %d column(s) at run time.

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

CREATE PROCEDURE usp_Resultset
AS
SELECT 'A' AS [First Letter],
       'B' as [Second Letter]
GO
EXEC usp_Resultset

errormessage11537.1.2

You can see that the above stored procedure is executed successfully and it has ONE result sets with TWO columns.
Lets try to execute it using WITH RESULT SETS clause.

USE tempdb
GO
EXEC usp_Resultset
WITH RESULT SETS
(([1st Letter] VARCHAR(1)
));
--OUTPUT
Msg 11537, Level 16, State 1, Procedure usp_Resultset, Line 3
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 column(s) for result set number 1, but the statement sent 2 column(s) at run time.

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

errormessage11537.1.3

Resolution:
Why this error ? Because, the above stored procedure returns ONE result sets with TWO columns but we defined WITH RESULT SETS clause for ONE column ONLY. Lets rewrite the script and define TWO columns in the result sets (the same number of columns in the result sets returned by stored procedure) to avoid this error.

USE tempdb
GO
EXEC usp_Resultset
WITH RESULT SETS
(([1st Letter] VARCHAR(1)
, [2nd Letter] VARCHAR(1)
));
--OUTPUT

errormessage11537.1.4

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, make sure you use exactly the same number of columns in result set returned by stored procedure to avoid such errors.

Read Full Post »

Sequence is one of the nice features shipped with SQL Server 2012 and it enhances a lot of identity column features and provides hands on a lot of things that identity does not support. But on the other hand it has its own limitations as well. Today, I was working on one of the user defined functions in my application using SEQUENCE and I came across an error.

Let me explain this error in detail :

Message Number: 11724

Severity : 15

Error Message: An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.

Error Generation:

Let me create a User defined function and a Sequence to demonstrate this error.

USE tempdb
GO

--Create a sequence
CREATE SEQUENCE [dbo].[Seq_Sequence]
AS [int]
START WITH 1
GO

--Create a user defined function
CREATE FUNCTION [dbo].[fn_Sample]
(
    @SequenceID INT
)
RETURNS TABLE AS RETURN
(
    SELECT 'RCP' + TRY_Convert(varchar(10),@SequenceID) AS [ID]
   , GETDATE() AS [CREATION DATETIME]
)
GO

The purpose to create the Sequence object and the user defined function is basically I want to get the new ID from sequence and pass it to the user defined function to FORMAT and return it with creation date time.
It seems very simple, lets do it.

USE tempdb
GO
SELECT * FROM [dbo].[fn_Sample]
(
   NEXT VALUE FOR [Seq_Sequence]
)
--OUTPUT

Msg 11724, Level 15, State 1, Line 1
An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function

Ooopps…… I am unable to execute it.

Resolution:

The resolution is very simple, NEVER pass the NEXT VALUE of sequence directly to table value user defined function. Just introduce a variable, store the NEXT VALUE in it and pass the variable into the user defined function.
Lets do it.

USE tempdb
GO
DECLARE @ID AS BIGINT = NEXT VALUE FOR [Seq_Sequence]
SELECT * FROM [dbo].[fn_Sample](@ID)
--OUTPUT

errormsg11724.1

Conclusion :

Remember, whenever you use SEQUENCE object with any other object always be careful about the limitations of sequence object.

Read Full Post »

In my earlier article, I wrote about one of the new system stored procedure sp_describe_undeclared_parameters shipped with SQL Server 2012. This is very handy system stored procedure whenever you need to find undeclared parameters in a SQL query.
I was trying to find undeclared parameters in a query using this system stored procedure and came across with a new error message. I did some research and fixed it.

Let me explain this error in detail :

Message Number: 11508

Severity : 16

Error Message: The undeclared parameter ‘%.*ls’ is used more than once in the batch being analyzed.

Error Generation:

In the given below example, I need to search country region name and a portion of a name in First name, Middle Name & Last Name columns of the employee view. So I wrote a query and it works perfectly. Given below is the script.

USE [AdventureWorks2012]
GO
DECLARE @Search_Name AS VARCHAR(MAX)
DECLARE @Country_Region_Name AS VARCHAR(MAX)

SET @Search_Name = 'William'
SET @Country_Region_Name='United States'

SELECT
  FirstName
, MiddleName
, LastName
, CountryRegionName
FROM HumanResources.vEmployee
WHERE
( FirstName  like '%' + @Search_Name + '%'
  OR
  MiddleName like '%' + @Search_Name + '%'
  OR
  LastName   like '%' + @Search_Name + '%'
)
AND CountryRegionName = @Country_Region_Name
--OUTPUT

errormsg11508.1

Once the query is executed successfully, lets pass the above query to sp_describe_undeclared_parameters to find undeclared parameters.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql =
N'SELECT * FROM HumanResources.vEmployee
WHERE
( FirstName  like ''%'' + @Search_Name + ''%''
  OR
  MiddleName like ''%'' + @Search_Name + ''%''
  OR
  LastName   like ''%'' + @Search_Name + ''%''
) AND CountryRegionName = @Country_Region_Name'
--OUTPUT

Msg 11508, Level 16, State 1, Line 5
The undeclared parameter ‘@Search_Name’ is used more than once in the batch being analyzed.

Ooopps…… I am unable to execute it.

Resolution:

If you look at the ERROR, you will easily realize that @Search_Name is used as a parameter in the query multiple times. But if you remove @Search_Name from the query, the purpose of the query would be dead. So what is the remedy? It is very simple to fix, just pass @Search_Name parameter in the sp_describe_undeclared_parameters as an input parameter, and the error message will be gone. Lets do it.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql =
N'SELECT * FROM HumanResources.vEmployee
WHERE
( FirstName  like ''%'' + @Search_Name + ''%''
  OR
  MiddleName like ''%'' + @Search_Name + ''%''
  OR
  LastName   like ''%'' + @Search_Name + ''%''
) AND CountryRegionName = @Country_Region_Name'
,@params =N'@Search_Name nvarchar(50)'
--OUTPUT

errormsg11508.2

Conclusion :

Remember, whenever you come across this error message just search for that particular parameter name causing error in the query and pass it into the sp_describe_undeclared_parameters as an input parameter. There is a possibility that multiple parameters can be placed in the query in multiple times. So to fix this error just pass all the parameters mentioned in the error message and run sp_describe_undeclared_parameters again.

Read Full Post »

In my earlier articles, I wrote about new enhancement of FORCESEEK table hint. It is a very handy feature when you have more than one index on the table and you want to select not only the index but also the column name of the index in FORCESEEK table hint. But you should be very careful about the syntax and parameters of this table hint. Recently, I came across a new error message related to FORCESEEK table hint.

Let me explain this error in detail :

Message Number: 365

Severity : 16

Error Message: The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘%.*ls’ specified more seek columns than the number of key columns in index ‘%.*ls’.

Error Generation:

Let me create a NONCLUSTERED INDEX to demonstrate this error.

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_Sample]
ON Purchasing.[PurchaseOrderDetail]
([PurchaseOrderID]
,[ProductID] ASC)
WITH (DROP_EXISTING = OFF) ON [PRIMARY]

Once you create the index, lets write a query using the above index and forceseek table hint.

USE [AdventureWorks2012]
GO
SELECT * FROM Purchasing.PurchaseOrderHeader AS H
INNER JOIN Purchasing.PurchaseOrderDetail AS D
WITH (FORCESEEK
(IX_PurchaseOrderDetail_Sample
(PurchaseOrderID
,ProductID
,OrderQty
)))
ON H.PurchaseOrderID =D.PurchaseOrderID
WHERE
D.ProductID>100
AND D.PurchaseOrderID>10
AND D.OrderQty>0

Msg 365, Level 16, State 1, Line 1
The query processor could not produce a query plan because the FORCESEEK hint on table or view ‘PurchaseOrderDetail’ specified more seek columns than the number of key columns in index ‘IX_PurchaseOrderDetail_Sample’.

Ooopps…… I am unable to execute it.

Resolution:

If you look at the above query, you will realize that there are two key columns ([PurchaseOrderID], [ProductID]) available in IX_PurchaseOrderDetail_Sample index. But what you are trying to do in the above query is that you included an additional column [OrderQty] that is not part of the index.

Lets remove the Order Qty (line # 7) column from the FORCESEEK table hint and execute the query again.

USE [AdventureWorks2012]
GO
SELECT * FROM Purchasing.PurchaseOrderHeader AS H
INNER JOIN Purchasing.PurchaseOrderDetail AS D
WITH (FORCESEEK
(IX_PurchaseOrderDetail_Sample
(PurchaseOrderID
,ProductID

)))
ON H.PurchaseOrderID =D.PurchaseOrderID
WHERE
D.ProductID>100
AND D.PurchaseOrderID>10
AND D.OrderQty>0
--OUTPUT

erromessage365.1.1

Conclusion :

Remember, whenever you use FORCESEEK hint on a table and define the index name and column name as well, the column name must be part of the Index key columns.

Read Full Post »

« Newer Posts - Older Posts »