Feeds:
Posts
Comments

Archive for the ‘Errors’ Category

Recently, I was busy with one of the major data recoveries and unfortunately there was no backup. First of all, I asked for the SQL Server log data to recover their deleted data. After a while, I got it but the major problem was that the varbinary data was available in ntext data type column in the dummy table. As you guys know, SQL Server maintains its log data in varbinary format. I tried to convert it into varbinary and received the given below error message. After a while, I solved it. Given below is the solution.

Message Number : 529

Severity : 16

Error Message : Explicit conversion from data type %ls to %ls is not allowed.

Error Generation:
Let me create a sample table to demonstrate this error.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
--Create Table
CREATE TABLE tbl_sample
(
 [Col_ID] INT,
 [Col_Ntext] NTEXT
)
GO

--Insert few records
INSERT INTO tbl_sample VALUES (1,'0x7261726573716C2E636F6D')
INSERT INTO tbl_sample VALUES (2,'0x496D72616E')
INSERT INTO tbl_sample VALUES (3,'0x53514C20536572766572')
GO

--Convert the ntext data type column to varbinary
SELECT [Col_ID],CONVERT(VARBINARY(MAX),[Col_Ntext]) AS [Col_Ntext]
FROM tbl_sample
GO

--OUTPUT

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type ntext to varbinary(max) is not allowed.

Ooopps…… I am unable to convert ntext to varbinary data type. What is the solution ? Let me explain the solution step by step.

Step 1 :
First of all, add one varbinary data type column in the table as shown below.

USE tempdb
GO

ALTER TABLE tbl_sample ADD [Col_Varbinary] VARBINARY(MAX)
GO

Step 2 :
Create an update statement using select statement as shown below.

USE tempdb
GO

SELECT 'UPDATE tbl_sample SET [Col_Varbinary]='
+ CONVERT(VARCHAR(MAX),[Col_Ntext]) +' WHERE [Col_ID] ='
+ CONVERT(VARCHAR(MAX),[Col_ID]) AS [Query]
FROM tbl_sample
GO
--OUTPUT

ntext to varbinary.1.1

Step 3 :
Once you execute the Step 2 query, you will get the updated statement as a result set as shown above. Just execute that updated statement as shown below.

USE tempdb
GO

UPDATE tbl_sample SET [Col_Varbinary]=0x7261726573716C2E636F6D
WHERE [Col_ID] =1

UPDATE tbl_sample SET [Col_Varbinary]=0x496D72616E
WHERE [Col_ID] =2

UPDATE tbl_sample SET [Col_Varbinary]=0x53514C20536572766572
WHERE [Col_ID] =3

GO

Step 4 :
Delete the ntext column of the table (if not required) as shown below. This step is not mandatory.

USE tempdb
GO

ALTER TABLE tbl_sample DROP COLUMN [Col_Ntext]
GO

Step 5 :
Now, browse the table and you can view that you successfully converted ntext data type to varbinary data type.

USE tempdb
GO

SELECT
 [Col_ID]
,[Col_Varbinary]
FROM tbl_sample

GO

ntext to varbinary.1.2

Advertisements

Read Full Post »

Today, I installed a SQL Server instance in one of my labs, and tried to run a query using OPENROWSET and got an error. Given below are the details of the error.

Message Number: 15281

Severity : 16

Error Message: SQL Server blocked access to %S_MSG ‘%ls’ of component ‘%.*ls’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘%.*ls’ by using sp_configure. For more information about enabling ‘%.*ls’, see “Surface Area Configuration” in SQL Server Books Online.

Error Generation:

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

USE AdventureWorks2012
GO
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=test;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

Ooopps…… I am unable to execute the OPENROWSET query.

Resolution:
Basically, ‘Ad hoc Distributed Queries’ are disabled by default in SQL Server due to security configuration and you cannot use either OPENROWSET or OPENDATASOURCE and if you cannot execute these two rowset functions, then you cannot access any remote data sources. So how to fix this issue?
Given below is the script to enable ‘Ad hoc Distributed Queries’.

USE master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
--OUTPUT

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.

As you can see above, the ‘Ad hoc Distributed Queries’ setting changed from 0 to 1. Now you can easily execute any ‘Ad hoc Query’

Note : By default, this option is set to 0 and you need to change it to 1 to activate this feature.

Read Full Post »

A Few days ago, I was working on resource governor implementation on my test server. Once I implemented & tested, I had to delete the resource governor settings from the test server. But Unfortunately, I got stuck because of the given below error. It did not allow me to delete the resource governor properly. Let me explain the error and its solution in details.

Message Number: 10920

Severity : 16

Error Message: Cannot %S_MSG user-defined function ‘%.*ls’. It is being used as a resource governor classifier.

Error Generation:

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

 DROP FUNCTION dbo.rgclassifier_MAX_CPU

Msg 10920, Level 16, State 1, Line 1
Cannot drop user-defined function ‘rgclassifier_MAX_CPU’. It is being used as a resource governor classifier.

Ooopps…… I am unable to drop the above classifier function.

Resolution:
If you look at the error description, it seems that classifier function is in use of resource governor, so you cannot drop this function. Then I thought of disabling it and tried to drop after that but I could not :(.

So what is the solution? Basically, you need to assign all new sessions to the default workload by eliminating the classifier function from the resource governor configuration of your server. When there is no user defined classifier function assigned as a classifier function, you can easily drop the classifier function. Given below is the script.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Now, you can easily drop the classifier function.


DROP FUNCTION dbo.rgclassifier_MAX_CPU

Read Full Post »

In SQL Server, alias are very helpful, because you do not need to write the long table names again and again. I usually use ALIAS in my development to speed up the work. Yesterday, I was working on a complex query and I used a lot of alias to minimize the line of code and finally I came across an error.

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

Severity : 16

Error Message: The correlation name ‘%.*ls’ is specified multiple times in a FROM clause.

Error Generation:

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

Use AdventureWorks2012
GO

SELECT
p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,p.[JobTitle]
FROM [HumanResources].[Employee] p
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]

GO

Msg 1011, Level 16, State 1, Line 2
The correlation name ‘p’ is specified multiple times in FROM clause.

Ooopps…… I am unable to execute the above qquery.

Resolution:
The resolution is very simple. Basically, you aliased more than one table with the same name, so SQL Server is unable to relate alias with table name. In the above table, I ALIASED both tables with p while I can assign one name (p) as an alias to one table only and I must change the alias of other table name (p) to something else. (That make sense also). Lets rewrite the script with the different alias and it works…….

USE [AdventureWorks2012]
GO
SELECT
e.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,e.[JobTitle]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
GO
--OUTPUT

error_message_1011.1.1

Read Full Post »

Recently, I was working on semantic search and tried to create a database having filestream and got an error FILESTREAM feature is disabled. After some research, I found the solution.

Let’s discuss this in detail:

Message Number: 5591

Severity : 16

Error Message: FILESTREAM feature is disabled.

Error Generation:

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

USE master
GO
CREATE DATABASE [SampleDB]
ON PRIMARY
( NAME = N'SampleDB',
FILENAME = N'C:\DATA\SampleDB.mdf'
),
FILEGROUP SampleFileGroup CONTAINS FILESTREAM
(NAME = SampleFileTableFS,
FILENAME='C:\Data\SampleFileTable'
)
LOG ON
( NAME = N'SampleDB_log',
FILENAME = N'C:\DATA\SampleDB_log.ldf'
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'SampleFileTableDB'
)
GO
--OUTPUT

Msg 5591, Level 16, State 1, Line 1
FILESTREAM feature is disabled.

Ooopps…… I am unable to create a database having FILESTREAM. So what is the solution ?

Solution:
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 on which you need to enable the FILESTREAM. Just 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:

    1. Enable FILESTREAM for Transact-SQL access (You need to enable this to enable FILESTREAM).
    2. Enable FILESTREAM for file I/O access. (If you need to read and write data (FILESTREAM) from windows then enable it and provide a windows share name)
    3. Allow remote clients to have streaming access to FILESTREAM data. (If you need to give access to remote clients on FILESTREAM data then enable it)

And Click APPLY button as shown in the picture below.

filestream_disabled.1.3

Step 4 : 

Once you are done with SQL Server Configuration Manager, open SQL Server Management Studio and open a new query window.

Step 5 :

Now, you are in the new query window, just execute the given below script. And after that DO NOT forget to restart the SQL Server SERVICE.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE
GO

Step 6 :
FILESTREAM is enabled now. You can execute any script having FILESTREAM. It will be executed successfully.

Read Full Post »

Today I was working on Maintenance Plans of SQL Server and I had to schedule a database backup on daily basis for a client. But once I right clicked on Management >> Maintenance Plan and selected New Maintenance Plan using SSMS, it gave me given below error.

Let’s discuss this in detail:

Error Message: ‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online. (Microsoft.SqlServer.Management.MaintenancePlanWizard)

Agent Xps component is turned off.1.1

Reason:

Basically, Agent XPs option enables the SQL Server Agent extended stored procedures on the SQL Server and the reason why this error occurs is because Agent XPs is not enabled in SQL Server. Also, if this option is not enabled, SQL Server Agent node will not display in SQL Server Management Studio Object Explorer.

Resolution:
There are two methods to resolve it.

Method 1:
In this method, we will execute the script to enable Agent XPs. You need to execute the given below script in Query window.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Agent Xps component is turned off.1.2

Method 2:
In this method, you do not need to write any script, you just need to open the SQL Server configuration Manager and start the SQL Server Agent as shown in the image below. It will automatically enable Agent XPs.

Agent Xps component is turned off.1.3

Note : There is no need to stop and restart the server, these settings take immediate effect.

Read Full Post »

I came across this error message in SQL Server 2012 using Format function, after that I did some research about this error message and came across that this error was part of SQL Server since 2005 or may be earlier than that. But the method to fix this error is same across the versions. Given below are the details & fixes of this error.

Message Number: 8116

Severity : 16

Error Message: Argument data type %ls is invalid for argument %d of %ls function.

Error Generation:
In the following example, I have date in varchar format and I am trying to format it into ARABIC culture using Format function.
Given below is the script.

USE tempdb
GO
DECLARE @Varchar_Date AS varchar(11)
SET @Varchar_Date='2013-12-12'
SELECT FORMAT (@Varchar_Date,'D','ar-SA') AS [Arabic Culture]
GO
--OUTPUT

Msg 8116, Level 16, State 1, Line 3
Argument data type varchar is invalid for argument 1 of format function.

Ooopps…… I cannot convert the date into Arabic format.

Resolution:
Basically, the reason for this error is not Format function exactly.  You can come across this error in any built-in function which accepts any argument(s). The resolution to this error is that you need to check the syntax of that particular function and see what type of data type it accepts, in which which argument(s). In our case it is Format function, it accepts three arguments and the first argument must be either Number or date time but we passed as varchar. So we need to change this varchar data type to datetime  data type and pass it to Format function.
Given below is the correct script.

USE tempdb
GO
DECLARE @Varchar_Date AS varchar(11)
DECLARE @Date AS datetime
SET @Varchar_Date='2013-12-12'
Set @Date=CONVERT(datetime,@Varchar_Date)
SELECT FORMAT (@Date,'D','ar-SA') AS [Arabic Culture]
GO
--OUTPUT

Arabic Culture
————–
09/صفر/1435

(1 row(s) affected)

Conclusion :
Remember, whenever you come across this error, you must consider the argument(s)’s data types of the function and whatever data types it accepts, you must pass exactly the same data type to that particular function.

Let me know if you came across this error and fixed it in a different way.

Read Full Post »

« Newer Posts - Older Posts »