May | 2011 | SQL with Manoj

CTE Recursion | Sequence, Dates, Factorial, Fibonacci series

May 23, 2011 14 comments

CTE, Common Table Expressions

According to MS BOL CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

More info on CTE can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Some examples of CTE Recursion or Recursive CTE can be found here:

-- Number Sequence: 1 to 10
;with num_seq as (
	select 1 as num
	union all
	select num+1
	from num_seq
	where num<100)
select num
from num_seq
where num < 10

-- Date Sequence: May 2011
;with dt_seq as (
	select cast('5/1/2011' as datetime) as dt, 1 as num
	union all
	select dt+1, num+1
	from dt_seq
	where num<31)
select dt
from dt_seq

-- Factorial
;with fact as (
	select 1 as fac, 1 as num
	union all
	select fac*(num+1), num+1
	from fact
	where num<12)
select fac
from fact
where num=5

-- Fibonacci Series
;with fibo as (
	select 0 as fibA, 0 as fibB, 1 as seed, 1 as  num
	union all
	select seed+fibA, fibA+fibB, fibA, num+1
	from fibo
	where num<12)
select fibA
from fibo

More on Recursive CTE: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Categories: SQL Tips Tags: CTE, CTE recursion, Recursive CTE

SQL Error – Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…

May 20, 2011 2 comments

Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I came through this strange and new error message while our team was deploying build on one of the servers. After debugging and reviewing the code we find the problem which was in one of the hundreds of stored procedures.

The scenario was:
– A stored procedure that uses Linked Server to pull record-set from another SQL Server database.
– Pull records and load this result set on a temp-table.
– For implementing the business logic and to do some calculations the query uses some aggregate functions, like SUM(), MIN(), MAX() on a few columns in that temp table.

Here if any of these aggregated column contain NULL values, it results in an obvious Warning message (not Error), i.e.: Warning: Null value is eliminated by an aggregate or other SET operation.
We could ignore this warning message, but when used in SSIS packages it causes the package to fail. So to avoid this Warning message, “SET ANSI_WARNINGS OFF” option was set at the beginning of the stored procedure.

The stored procedure compiled fine, but when we executed it, it resulted into this strange error: “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…”.

We observed that this error is due to setting ANSI_WARNINGS to OFF just before the linked server call. So the workaround was to shift the “SET ANSI_WARNINGS OFF” statement just below the linked server call. And to be on the safer side applied the “SET ANSI_WARNINGS ON” statement at the end of the stored procedure.

MSDN Forum links:
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1d501b57-fc58-4fbe-9bec-6c38ad158a62
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/03cab0c7-9e2b-459a-8ffd-21616ac5c465/
http://social.msdn.microsoft.com/Forums/en-IE/vstsdb/thread/67d2b3d2-e0fd-4669-a2e9-ca79c894b8d2

MS KB article regarding this issue: http://support.microsoft.com/kb/296769

Categories: SQL Errors Tags: ANSI_NULLS, ANSI_WARNINGS, Linked Server

Database Schema diff

May 16, 2011 1 comment

Are you also looking for a Database diff tool that can provide you difference in 2 similar but different databases?

Few days back I was also searching for the same and stumbled over hell lot of tools. Found some good tools but they were trial-ware, limited to 14 or 30 days, but not worth to buy. Among them I liked SQL Delta, packed with lot of features.

SQL Delta provides you difference between 2 different database’s schemas and you can also compare data within those schemas. But the kind of html report it provides is not detailed and user friendly. I was looking for output on excel reports, thus thought to write my own logic to query the metadata of both the databases and get the difference, and here’s that:

 with cte as (
 select isnull(at.TABLE_CATALOG,'Database_A') as Database_A, at.TABLE_NAME as TBL_NME_A, ac.COLUMN_NAME as COL_NME_A,
 isnull(bt.TABLE_CATALOG,'Database_B') as Database_B, bt.TABLE_NAME as TBL_NME_B, bc.COLUMN_NAME as COL_NME_B
 from Database_A.INFORMATION_SCHEMA.TABLES at
 join Database_A.INFORMATION_SCHEMA.COLUMNS ac on ac.TABLE_NAME = at.TABLE_NAME
 left join Database_B.INFORMATION_SCHEMA.TABLES bt on bt.TABLE_NAME = at.TABLE_NAME
 left join Database_B.INFORMATION_SCHEMA.COLUMNS bc on bc.TABLE_NAME = bt.TABLE_NAME and bc.COLUMN_NAME = ac.COLUMN_NAME
 UNION
 select isnull(at.TABLE_CATALOG,'Database_A') as Database_A, at.TABLE_NAME as TBL_NME_A, ac.COLUMN_NAME as COL_NME_A,
 isnull(bt.TABLE_CATALOG,'Database_B') as Database_B, bt.TABLE_NAME as TBL_NME_B, bc.COLUMN_NAME as COL_NME_B
 from Database_B.INFORMATION_SCHEMA.TABLES bt
 join Database_B.INFORMATION_SCHEMA.COLUMNS bc on bc.TABLE_NAME = bt.TABLE_NAME
 left join Database_A.INFORMATION_SCHEMA.TABLES at on at.TABLE_NAME = bt.TABLE_NAME
 left join Database_A.INFORMATION_SCHEMA.COLUMNS ac on ac.TABLE_NAME = at.TABLE_NAME and ac.COLUMN_NAME = bc.COLUMN_NAME)
 select * from cte
 order by isnull(TBL_NME_A, TBL_NME_B), isnull(COL_NME_A, COL_NME_B)

The output of the above query gives you total 6 columns, 3 columns from first database & same 3 columns from second database. The columns listed are “Database Name”, “Table Name” & “Column Name”.

– If only “Column Name” values is absent then the tables exist but the column does not.

– If both “Table Name” & “Column Name” values are absent then the table does not exist.

The INFORMATION_SCHEMA.TABLES & INFORMATION_SCHEMA.COLUMNS views provides you information on underlying tables & columns of those tables. They contain metadata of the database, generally they internally use the sys views to get information, like sys.tables & sys.columns.

For more information on metadata & system catalog tables/views check my following post: https://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/

Categories: SQL Tips Tags: Database Diff, Database Schema Diff, Schema Diff, SQL Delta