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 »

Mostly companies develop several environments from development stage till production like Development environment, Integration environment, Validation environment, Quality Assurance environment, User Acceptance environment and Production environment to enhance quality of the product for the customer. But once we have different environments, there is a very common issue called inconsistency in database between these environments. Sometimes, script manager may miss out some scripts to deploy, and due to this one or all of the environments can create serious problems and may crashes also. And if this is user acceptance / production environment, it may create a cumbersome image about your product. Sometimes, we use user defined functions and stored procedures to compare two databases like this. However, I felt that these solutions are good, it can help you how to fix, but it is not a smart solution (can automatically fix the issues).
Recently, I came across an issue where we have many environments and in all different environments it was generating different database inconsistency errors. So I started doing my research, and came across a tool namely ApexSQL Diff, a schema comparison tool. I tested this tool and I synced all environments in a very few minutes with just a few clicks. My customer was damn happy and was inquiring how I managed to do it in few minutes as it seems, it takes few weeks to sort out. I recommended this solution to the customer & exclaimed,”it is money’s worth”. I approached APEX team to purchase this product for my customers & get some discount, so thought of getting same discount for my blog reader. And finally I got it :). You can use coupon Code ApexSQLDiffPro25 to get 25% discount on APEXSQL Diff.

Note : There is a FREE community version of ApexSQL Diff for SQL Express and Windows Azure SQL Databases.

Let me explain step by step how it works.

STEP 1:
Once you open ApexSQL Diff, click on New button, it asks about source and destination of your SQL server database (Backup, Script folder, Snapshot, Source control) to compare as shown in the image below.

apexsqldiff.1.1

STEP 2:
Once you select source and destination databases to compare, just press Compare button at the bottom of the screen. It takes a while and it will give you all schema differences between both databases. It supports new object shipped with SQL Server 2012 as well, as shown in the image below.
apexsqldiff.1.2

It also provides additional sorting, formatting (alignment & grouping) and filter options like object filter, difference filter & script difference view to give you full control over differences, as shown in the image below.

apexsqldiff.1.2.1

STEP 3:
Once you have the differences, just press Sync button and it can sync both databases and can give you the script also (in case you want to deploy by yourself), as shown in the picture below.

apexsqldiff.1.3

Last but not least, it does not stop working here, it syncs again to check if you missed to upgrade/update any scripts and gives you the difference (if any).
I like this product and I also recommend that you should try and view its results by yourself. Its trial version is available here.
You can get further details about the product from http://blog.apexsql.com/category/apexsql-diff/.  Let me know your opinions whether positive or not.

Read Full Post »

How to view the definition of a module, is one of the activities that we need very frequently whether it is customization or debugging in SQL Server. Today, I was debugging one of the stored procedures, so thought of writing this article.

There are multiple ways to view the definition of a module. I will mention two of them here and you can choose either of them.

METHOD 1 :
Given below is the solution using OBJECT_DEFINITION (One of the built-In metadata functions in SQL Server).

USE AdventureWorks2012 -- Donot forget to change database name here
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('[HumanResources].[vEmployee]'))
-- Donot forget to change schema and table name
-- as highlighed above in blue color.
AS ObjectDefinition;
GO
--OUTPUT

definition of modules.1.1

METHOD 2 :
Given below is the solution using sys.sql_modules (One of the object catalog views in SQL Server).

USE AdventureWorks2012 -- Donot forget to change database name here
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.dEmployee');
-- Donot forget to change schema and table name
-- as highlighed above in blue color.
GO
--OUTPUT

definition of modules.1.1

CONCLUSION :
As you can see, both methods give you the same result set. However, I personally use Method 1 due its less number of codes. Let me know which method you prefer ?

Read Full Post »

We usually use temporary table to keep temporary data in the stored procedure, it can be local, global or a table variable. Recently, I was working on the stored procedures’ metadata in a client database using sp_describe_first_result_set (a new system stored procedure shipped with SQL Server 2012). Ooopps….. there’s an error, let me explain the error in detail. This error message is available in SQL Server 2012 and above.

Message Number: 11526

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’ uses a temp table.

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

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
CREATE TABLE #temp_Sample
(
 [ID] INT,
 [NAME] VARCHAR(32)
)
INSERT INTO #temp_Sample VALUES (1,'Bob')

SELECT * FROM #temp_Sample
END
GO

EXEC [dbo].[usp_sample]
GO
--OUTPUT

error_message_11526.1.1

You can see that I have created a stored procedure and executed it as well and got the result set. Now I would like to view the metadata of the above stored procedure. Given below is the script to view the metadata using sp_describe_first_result_set

USE tempdb
GO
Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘INSERT INTO #temp_Sample VALUES (1,’Bob’)’ in procedure ‘usp_sample’ uses a temp table.

Ooopps…… I am unable to execute it.

Resolution:
The reason behind this error is, you used a local temporary table in the stored procedure and you cannot view the metadata using sp_describe_first_result_set. So what to do ? Very simple guys, just convert local temporary table to table variable and view its metadata.
Given below is the correct script.

USE tempdb
GO
--DROP PROCEDURE [dbo].[usp_sample]
--GO
CREATE PROCEDURE [dbo].[usp_sample]
AS
BEGIN
DECLARE @temp_Sample TABLE
(
 [ID] INT,
 [NAME] VARCHAR(32)
)
INSERT INTO @temp_Sample VALUES (1,'Bob')

SELECT * FROM @temp_Sample
END
GO

Sp_describe_first_result_set @tsql =N'usp_sample'
GO
--OUTPUT

error_message_11526.1.2

Conclusion :
Remember, whenever you use temporary table in the stored procedure and would like to view the metadata of that stored procedure using sp_describe_first_result_setalways convert local temporary table to table variable.

Read Full Post »

Older Posts »