Feeds:
Posts
Comments

Archive for the ‘Errors’ Category

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.

Advertisements

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 »

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 »

Change Data Capture (CDC) is one of the best features shipped with SQL Server 2008. We usually use CDC to record the change of the tables using DML operations for audit purpose. Today, I configured CDC for one of my clients. In order to configure the CDC, first of all, you need to enable it. However, once I tried enabling it but it gave me an error.

Let me explain the error and its solution in detail.

Message Number: 22830

Severity : 16

Error Message: Could not update the metadata that indicates database %s is enabled for Change Data Capture. The failure occurred when executing the command ‘%s’. The error returned was %d: ‘%s’. Use the action and error to determine the cause of the failure and resubmit the request.

Error Generation:
Let me enable the CDC to generate the error.

USE AdventureWorks2012
GO
EXEC sys.sp_cdc_enable_db
GO
--OUTPUT

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database AdventureWorks2012 is enabled for Change Data Capture. The failure occurred when executing the command ‘SetCDCTracked(Value = 1)’. The error returned was 15517: ‘Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.’. Use the action and error to determine the cause of the failure and resubmit the request.

Ooopps…… I am unable to enable the CDC. What is the solution ? Let me explain the solution.

Resolution:
If you look at the error message, you will find two error numbers 22830 & 15517. In fact the actual error is 15517 and the reason behind this is, you do not have a valid log in for the database owner of the selected database. The solution I found is from Microsoft support site. Given below is the script.

USE AdventureWorks2012 -- Change the database name
GO
sp_changedbowner 'sa' --Change the valid login name
GO

Once you execute the above statement, you can easily enable the CDC and enjoy the benefit of CDC.

Conclusion:
To conclude, whenever you come across this error, you should change the database owner of the selected database to a valid SQL Server log in.

Read Full Post »

Filetable is one of the new features shipped with SQL Server 2012. I have discussed its core functionality earlier in my articles. Today, I was trying to open one of the filetables using Explorer File Table Directory and it gave me given below error message.

file location cannot be opened.1.1

Let me explain the error and its solution in detail.

Error Message: The File location cannot be opened. Either access is not enabled or you do not have permissions for the same.

Solution:
If you look at the error description, it seems that either your access to this filetable is not enabled by the administrator or you do not have the permission to access this filetable. And I am a bit surprised because I logged in as an administrator, what next then ?

Let me explain the solution step by step.

Step 1 :
Click on the Start >>All Programs >>Microsoft SQL Server 2012 >>Configuration Tools >> SQL Server Configuration Manager, as shown in the image below.

filestream_disabled.1.1

Step 2 :
Once you click on SQL Server configuration manager, it will open SQL Server configuration manager options. It has two panes. Now you need to select SQL Server Services from the left hand pane and select the SQL Server Instance, right click on it and select Properties as shown in the image below.

filestream_disabled.1.2

Step 3 :
Once you select the Properties, it will open SQL Server Properties dialogue box. Now you need to find FILESTREAM Tab and select it.
Once you select it, you will find given below options as shown in the image.

    1. Enable FILESTREAM for Transact-SQL access.
    2. Enable FILESTREAM for file I/O access. 
    3. Allow remote clients to have streaming access to FILESTREAM data. 

file location cannot be opened.1.2

As you can see, the 2nd option (Enable FILESTREAM for file I/O access) has NOT been ENABLED and due to this reason, you cannot access filtable from SQL Server Management Studio (SSMS). Just enable this option and click Apply as shown in the picture below.

file location cannot be opened.1.3

Step 4 : 
Once you are done with SQL Server Configuration Manager, open SQL Server Management Studio and go to the same filetable and open it using Explorer FileTable Directory. This time it will not give you this error.

file location cannot be opened.1.4

Read Full Post »

Last weekend, my support engineer and I were at a client site to upgrade SQL Server 2005 to SQL Server 2012. Before proceeding with any activity, we tried to take a backup and got the given below error that I received it later by my support engineers as shown below.

error

Let me explain the error and its solution in details.

Message Number: 3023 

Severity : 16

Error Message: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

Resolution:
If you look at the error description, it seems that there is a transaction going on in the background, and due to this reason SQL Server is not allowing us to take a backup or may be the database is corrupt. So we started doing our basic testing and it seems everything is fine. Then why SQL Server is not allowing us to take a backup ?

So, I thought of executing the given below query to check whether any backup or restore operation is in progress and I found that a scheduled backup was already in progress. Ooopss……….. So we waited for a while and took the backup again.

erromessage3023.1.1

Conclusion:
Whenever you come across this error, you should check what the processes are in progress in SQL Server. It will give you a hint, why you are unable to take backup. In my case, a scheduled backup was in progress at the same time when I tried to take the backup.

Read Full Post »

Older Posts »