Feeds:
Posts
Comments

Posts Tagged ‘SQL’

SQL SERVER – How to retrieve the metadata of a stored procedure

Posted in Solutions, SQL SERVER, tagged , , , , , on January 18, 2014| Leave a Comment »

In my earlier articles, I wrote about sys.dm_exec_describe_first_result_set
(A new dynamic management view shipped with SQL Server 2012), it is very handy when you need to know the metadata of the first possible result set of any Transact SQL. Today, I came across an issue when I tried to get the metadata of a stored procedure in earlier version of SQL Server and wanted to use the list of columns for further manipulations. However, I could not find a proper solution like sys.dm_exec_describe_first_result_set in the earlier version of SQL Server. So I started developing the solution.

Given below are the solution.

SOLUTION 1 : For SQL Server 2012 and above using sys.dm_exec_describe_first_result_set

USE AdventureWorks2012;
GO
SELECT *  FROM sys.dm_exec_describe_first_result_set
(N'[dbo].[uspGetManagerEmployees] 16', null, 0) ;
--OUTPUT

metadata of stored procedure.1.1

SOLUTION 2 : For SQL Server 2005 and above using OPENROWSET

USE AdventureWorks2012
GO
--DROP TABLE #temp1
GO
SELECT * INTO  #temp1
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=sa;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');
GO

SELECT * FROM tempdb.sys.columns
WHERE object_id=object_id('tempdb..#temp1')
GO
--View the column name / metadata of the
--above created temporary table.
--OUTPUT

metadata of stored procedure.1.2

Read Full Post »

SQL SERVER – How to return ‘0’ instead of NULL in a query resultset

Posted in Solutions, SQL SERVER, tagged , , , , , on January 17, 2014| Leave a Comment »

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 »

SQL SERVER – APEXSQL Diff – Schema comparison tool (25% Discount!)

Posted in Solutions, SQL SERVER, tagged , , , , , on January 15, 2014| Leave a Comment »

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 »

SQL SERVER – Multiple ways to view the definition of a module

Posted in Solutions, SQL SERVER, tagged , , , , , on January 13, 2014| Leave a Comment »

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 »

SQL SERVER 2012 – Fix – Error :11526 – The metadata could not be determined because statement ‘%.*ls’ in procedure ‘%.*ls’ uses a temp table.

Posted in Errors, SQL SERVER, tagged , , , , on January 10, 2014| 4 Comments »

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_set,  always convert local temporary table to table variable.

Read Full Post »

SQL SERVER – How to get first Sunday of next month using EOMONTH

Posted in Solutions, SQL SERVER, tagged , , , , , on January 7, 2014| Leave a Comment »

I came across this query when I was automating one of my services for a customer and it had to run on first Sunday of every month. I already had this solution earlier and utilized it in many places, but as my customer is using SQL Server 2012, I would like to write this script in a different way with less no of codes. So I started scripting and finally developed it using EOMONTH function. (A new function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-05')
INSERT INTO tbl_Sample VALUES (2,'2013-02-01')
INSERT INTO tbl_Sample VALUES (3,'2013-05-05')
INSERT INTO tbl_Sample VALUES (4,'2013-07-20')
INSERT INTO tbl_Sample VALUES (5,'2013-08-28')

GO

SOLUTION 1 : Using EOMONTH
Given below is the script.

USE tempdb
GO
SELECT DATEADD(DAY,8-
DATEPART(WEEKDAY,DATEADD(DAY,1,EOMONTH([Date])))
,EOMONTH([Date])) AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

firstsundayofthemonth.1.1

SOLUTION 2 : Using Traditional Method
Given below is the script.

USE tempdb
GO
SELECT CONVERT(DATE,DATEADD(DAY,8-
DATEPART(WEEKDAY,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1,0))
,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1, -1)))
AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

firstsundayofthemonth.1.1

Read Full Post »

SQL SERVER – How to get the list of all triggers using sp_MSforeachtable

Posted in Solutions, SQL SERVER, tagged , , , , on January 5, 2014| Leave a Comment »

In one of the my earlier articles related to triggers, I had written about how to get the list of triggers along with its different properties using sys.triggers & OBJECTPROPERTY. It was quite a big script to get these details. Recently, when I was working on some system level stored procedures, I got a shortcut to achieve list of triggers along with its schema using sp_MSforeachtable & sp_helptrigger

Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
EXEC sp_MSforeachtable
@command1 = 'sp_helptrigger ''?'''
,@whereand = 'And Object_id In (Select parent_id From sys.triggers)';
--OUTPUT

listoftrigger.1.1

Read Full Post »

SQL SERVER – dm_db_log_space_usage – dynamic management view

Posted in Dynamic Management Views, SQL SERVER, tagged , , , , on January 1, 2014| 1 Comment »

SQL Server database Maintenance plays an important role to improve database performance. One of the important aspects of maintenance is SQL Server log, even if you configured it properly, you should keep an eye on it and time to time, you should do its maintenance. To do the maintenance, you need to know the size of SQL Server log, used size and its percentage as well. There are multiple ways to achieve it. One of the famous methods of DBCC command is DBCC SQLPERF to find these details. I also have been using this DBCC Command since SQL Server 2005. It works fine but the only problem with this DBCC command is that if you need to manipulate its result set further, you need to place its result set in the temporary table and manipulate.

A few days ago, I was working on database log size and looking for some alternate solution. After some research, I found one undocumented dynamic management view that is shipped with SQL Server 2012 namely dm_db_log_space_usage. It gives the log size of the database and other details in bytes, you can convert it in MBs and manipulate its result set further (no need for temporary table) as well. Given below is the script to demonstrate how it works.

USE AdventureWorks2012
GO
SELECT
DB_NAME(database_id) AS [Database Name],
ROUND(CONVERT(FLOAT,total_log_size_in_bytes/1024)/1024,2) AS [Log Size (MB)],
ROUND(CONVERT(FLOAT,used_log_space_in_bytes/1024)/1024,2) AS [Log Used size (MB)],
ROUND(used_log_space_in_percent,2) AS [Log Used % (MB)]
FROM
sys.dm_db_log_space_usage

--OUTPUT

SQL Server log size.1.1

Read Full Post »

SQL SERVER 2012 – Fix – Error : 41209 – A semantic language statistics database is not registered. Full-text indexes using ‘STATISTICAL_SEMANTICS’ cannot be created or populated.

Posted in Errors, SQL SERVER, tagged , , , , , on December 31, 2013| Leave a Comment »

I came across this error message when I created full-text index on one of the file tables (a new table concept came in SQL Server 2012) and tried to create a full text index. Most likely this error is related to semantic search (a new type of search concept came in SQL Server 2012).
Let me explain this error in detail :

Message Number: 41209

Severity : 10
Error Message: A semantic language statistics database is not registered. Full-text indexes using ‘STATISTICAL_SEMANTICS’ cannot be created or populated.

Error Generation:
I tried to create full-text index on file table and received given below error message.

errormessage41209.1.1

RESOLUTION :
The resolution is very simple for this error. Basically, you have attached semantic language statistics database in your SQL Server but most probably you’ve forgotten to register it.
Lets debug this error step by step.

Step 1:
First of all, lets check whether you registered the semantic database or not. Given below is the script.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.3

As you can see that the above query result set is blank, which means semantic database is not registered.

Step 2:
Now, you just need to register semantic database, given below is the script.

USE master
GO
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
GO

Step 3 :
Once you registered the semantic database, execute the Step 1 again and check whether it is registered or not.
Given below is the same script but result set would be different because you registered the semantic database.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Install Semantic 1.6

You can view the above result set and see that semantic database has been registered. You can create full text indexes on file tables without any errors.

HAPPY NEW YEAR 🙂

Read Full Post »

SQL SERVER – How to check if data has carriage return and line feed?

Posted in Solutions, SQL SERVER, tagged , , , , on December 30, 2013| 1 Comment »

I received this query from one of my consultants, when he was busy migrating legacy data for one of our customers, enquiring how to check if some of the employee job titles have carriage return and line feed? Whether there is any solution I can provide to check in any string that it has carriage return and line feed or not. This is the first time, I came across this query so I started searching the solution, but could not get a proper solution. So I started developing the solution. Given below is the script.

SOLUTION :

--This script is compatible with SQL Server 2005 and above.
--DROP FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
--GO
CREATE FUNCTION dbo.[UDF_Check_existance_of_carriage_return_line_feed]
(
@String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_BOOLEAN INT

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_BOOLEAN =COUNT(*)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND ASCII(SUBSTRING(@String,Nums.n,1))
IN (13,10)

RETURN (CASE WHEN @RETURN_BOOLEAN >0 THEN 'TRUE' ELSE 'FALSE' END)
END
GO

EXAMPLE :
In this example, I manually inserted a carriage return and line feed in employee table to test the above solution using given below script.

USE AdventureWorks2012
GO
UPDATE [HumanResources].[Employee] SET [JobTitle] ='Research
and
Development Manager' WHERE BusinessEntityID =6

Lets browse the employee table using above solution and check. Given below is the script.

USE AdventureWorks2012
GO
SELECT BusinessEntityID,OrganizationLevel,JobTitle
,dbo.[UDF_Check_existance_of_carriage_return_line_feed] ([JobTitle])
AS [Boolean]
FROM HumanResources.Employee
--OUTPUT

carriage return and linefeed.1.1

Read Full Post »

« Newer Posts - Older Posts »