Feeds:
Posts
Comments

Archive for January, 2014

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

Read Full Post »

I received this query recently, when I was recovered one of my blog reader data. Once the recovery is done, I advised him to take a backup on regular basis etc etc. Next morning, I receive an email from him with a screen shot showing that, there is no option to take Transaction Backup in his SQL Server. I recreated the same screen shot, as shown below.

transaction backup.1.1

So What is the solution ? Basically your database is in SIMPLE recovery model due this you do not have Transaction Backup option. So In order to take transaction backup, you need to change your database recovery options from SIMPLE to FULL. Lets do it step by step.

Step 1 :
Open your SQL Server management studio than right click on the database and select database properties as shown in the screen shot below.

transaction backup.1.4

Step 2 :
Select the options and further select the recovery model as FULL on your right hand side and press OK as shown in the screen shot below.

transaction backup.1.2

Step 3 :
Now, you need to go back and take the backup and this time you will find the transaction backup option as shown in the screen shot below.

transaction backup.1.3

Note : It is recommended to change your recovery model from simple to FULL

Read Full Post »

Statistics usually helps us to analyse any object and this is the same for SQL Server as well. We should monitor SQL Server statistics on and off to make sure that it is up and running. To achieve this, each DBA has its own tool to analyse different components of SQL Server. Most of them, write their own Transact SQL and use them against the database and return values. But there is a shortcut that SQL Server provides to give you a number of statistics about the database at your finger tips. So what is that shortcut ? Basically, these are standard reports shipped with SQL Server and provide detailed statistics about the database with graphical interface as well.

Let me show you how to reach standard reports.

Step 1 :
Open the SQL Server Management Studio (SSMS) and select the particular database that statistics you need to view and then right click on it and select REPORTS and STANDARD REPORTS as well, as shown in the picture below.

standard reports.1.1

Step 2 :
Once you select the standard reports, you can view a series of statistical reports shipped with SQL Server, as shown in the picture below.

standard reports.1.2

Step 3 :
Let me open a “Disk Usage” report to demonstrate, how it works as shown in the picture below.

standard reports.1.3

Read Full Post »

Recently, I came across a query in a blog, ‘How to remove leading zeros after a decimal point’. I then started developing the shortest possible solution and thought of sharing with my blog readers. Let me create a sample to demonstrate the solution.

DECLARE @tbl_sample AS TABLE
(
 [ID] INT,
 [Col_Varchar] VARCHAR(50)
)

INSERT INTO @tbl_sample VALUES (1,'2013.0000000001')
INSERT INTO @tbl_sample VALUES (2,'2014.0000000002')
INSERT INTO @tbl_sample VALUES (3,'2015.0000000003')
INSERT INTO @tbl_sample VALUES (4,'2016.0000000044')
INSERT INTO @tbl_sample VALUES (5,'2017.0000000555')

SELECT * FROM @tbl_sample
 --OUTPUT

remove leading zero.1.1

SOLUTION 1 :
Given below is the solution using PARSENAME & CONCAT function.

--This script is compatible with SQL Server 2012 and above.
SELECT [ID]
,CONCAT(PARSENAME([Col_Varchar],2)
,'.',CONVERT(INT,PARSENAME([Col_Varchar],1)))
AS [Col_Varchar]
FROM @tbl_sample
 --OUTPUT

remove leading zero.1.2

SOLUTION 2 :
Given below is the solution using PARSENAME & CONVERT function.

--This script is compatible with SQL Server 2005 and above.
SELECT
[ID]
,CONVERT(VARCHAR(5),PARSENAME([Col_Varchar],2))
+ '.'
+ CONVERT(VARCHAR(5),CONVERT(INT,PARSENAME([Col_Varchar],1)))
AS [Col_Varchar]
FROM @tbl_sample
 --OUTPUT

remove leading zero.1.3

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 »

In legacy data, it is very common that you find a lot of unnecessary NULL values and you need to do massage to present this data, whether it is a report or an email. Generally, we use few techniques to avoid NULL and replace it with any characters or numbers.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.

SAMPLE :

DECLARE @tbl_sample TABLE
(
 [ID] INT,
 [Name] VARCHAR(50),
 [Code] INT
)

INSERT INTO @tbl_sample VALUES(1,'Bob',1234)
INSERT INTO @tbl_sample VALUES(2,'Sandra',NULL)
INSERT INTO @tbl_sample VALUES(3,'Mike',NULL)

SELECT
*
FROM
@tbl_sample
--OUTPUT

return_result_0.1.1

METHOD 1 :
Given below is the script to replace NULL using ISNULL (a SQL Server built-in function).

SELECT
 [ID]
,[Name]
,ISNULL([Code],0) AS [Code]
FROM @tbl_sample
--OUTPUT

return_result_0.1.2

METHOD 2 :
Given below is the script to replace NULL using COALESCE (a SQL Server built-in function).

SELECT
 [ID]
,[Name]
,COALESCE([Code],0) AS [Code]
FROM @tbl_sample
--OUTPUT

return_result_0.1.2

METHOD 3 :
Given below is the script to replace NULL using CASE STATEMENT (a SQL Server expression).

SELECT
 [ID]
,[Name]
,CASE WHEN [Code] is NULL THEN 0 ELSE [Code] END 
As [Code]
FROM @tbl_sample
--OUTPUT

return_result_0.1.2

Read Full Post »

Older Posts »