Ads

15 March 2011

SQL Server 2005 Features Comparison

Scalability and Performance

Feature

Express

Workgroup

Standard

Enterprise

Comments

Number of CPUs

1

2

4

Max OS supported

Includes support for multicore processors.

RAM

1 gigabyte (GB)

3 GB

Operating system maximum

Operating system maximum

Memory limited to maximum supported by operating system.

64-bit Support

Windows on Windows (WOW)

WOW

Available

Available


Database Size

4 GB

No Limit

No Limit

No Limit


Partitioning




Available

Support for large-scale databases

Parallel Index Operations




Available

Parallel processing of indexing operations

Indexed Views




Available

Indexed view creation is supported in all editions. Indexed view matching by the query processor is supported only in Enterprise Edition.

High Availability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Database Mirroring



Available 1

Available

Advanced high availability solution that includes fast failover and automatic client redirection

Failover Clustering



Available 2

Available


Backup Log-shipping


Available

Available

Available

Data backup and recovery solution

Online System Changes

Available

Available

Available

Available

Includes Hot Add Memory, dedicated administrative connection, and other online operations

Online Indexing




Available


Online Restore




Available


Fast Recovery




Available

Database available when undo operations begin

1 Single REDO thread and the safety setting is always on.

2 Supports two-node clustering support for the Database server only.

Manageability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Auto Tuning

Available

Available

Available

Available

Automatically tunes database for optimal performance.

Profiler

1

2

Available

Available


SQL Server Management Studio Express

See Comments

Available

Available

Available

Easy-to-use graphical management tool available as a separate download or included with the SQL Server Express w/Advanced Services download

Management Studio


Available

Available

Available

Full management platform for SQL Server; includes Business Intelligence (BI) Development Studio.

Database Tuning Advisor



Available

Available

Automatically suggests enhancements to your database architecture to improve performance.

Serviceability Enhancements

Available

Available

Available

Available

Dynamic management views and reporting enhancements.

Full-text Search

See comments

Available

Available

Available

Available for SQL Server Express in the SQL Server Express w/ Advanced Services download

SQL Agent Job Scheduling Service


Available

Available

Available


1 Local and Relational Servers only.

2 This edition can be profiled from a Standard or Enterprise Edition of SQL Server 2005.

Security

Feature

Express

Workgroup

Standard

Enterprise

Comments

Advanced Auditing, Authentication, and Authorization

Available

Available

Available

Available


Data Encryption and Key Management

Available

Available

Available

Available

Built-in data encryption for advanced data security.

Integration with Microsoft Baseline Security Analyzer

Available

Available

Available

Available

Scans your system to check for common security vulnerabilities.

Integration with Microsoft Update

Available

Available

Available

Available


Programmability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Stored Procedures, Triggers, and Views

Available

Available

Available

Available


T-SQL Enhancements

Available

Available

Available

Available

Includes exception handing, recursive queries, and support for new data types.

Common Language Runtime and .NET Integration

Available

Available

Available

Available


User-defined Types

Available

Available

Available

Available

Extend the server with your own custom data types.

Native XML

Available

Available

Available

Available

Includes XML indexing and full-text XML search.

XQuery

Available

Available

Available

Available


Notification Services



Available

Available

Allows the building of advanced subscription and publication applications.

Service Broker

See comments

Available

Available

Available

SQL Server Express can exchange messages with other editions. Messages between instances of SQL Server Express must be routed through another edition.

Integration and Interoperability

Feature

Express

Workgroup

Standard

Enterprise

Comments

Import/Export


Available

Available

Available


Integration Services with Basic Transforms



Available

Available

Provides graphical extract, transform, and load (ETL) capabilities.

Integration Services Advanced Transforms




Available

Includes data mining, text mining, and data cleansing.

Merge Replication

Available 1

Available 2

Available

Available


Transactional Replication

Available 3

Available 4

Available

Available


Oracle Replication




Available

Transactional replication with an Oracle database as a publisher

Web Services (HTTP Endpoints)



Available

Available

Support for native Web services, Web Services Description Language (WSDL), and Web authentication

1 Subscriber only.

2 Publish to up to 25 subscribers.

3 Subscriber only.

4 Publish to up to five subscribers.

Business Intelligence

Feature

Express

Workgroup

Standard

Enterprise

Comments

Report Server

See comments

Available

Available

Available

Report Server is available for SQL Server Express in the SQL Server Express with Advanced Services download.

Report Builder


Available

Available

Available

End-user reporting tool

Reporting Data Sources

1

Available

Available

Available


Scale Out Report Servers




Available


Data Driven Subscriptions




Available


Infinite Clickthrough




Available


Data Warehousing



Available

Available


Star Query Optimization

Available

Available

Available

Available


SQL Analytical Functions

Available

Available

Available

Available


BI Development Studio

See comments2

Available 3

Available

Available

Integrated development environment for building and debugging data integration, OLAP, data mining, and reporting solutions.

Enterprise Management Tools


Available

Available

Available

Integration with SQL Management Studio, SQL Server Profiler, SQL Server Agent, Backup/Restore.

Native Support for Web Services (Service Oriented Architectures)

Available 4

Available 5

Available

Available

Allows access to data from any device.

Analysis Services



Available

Available

Powerful analytics and data mining capabilities.

Unified Dimensional Model (UDM)



Available

Available

Enterprise business data model enables fast, interactive, ad hoc analysis of large data sets. Builds smarter reports that leverage centralized business logic and key performance indicators (KPIs), as well as the performance of UDM.

Business Analytics



Available

Available

Multidimensional Expression (MDX) scripts and MDX debugger, .NET stored procedures, Time Intelligence, KPI Framework.

Advanced Business Analytics




Available

Account intelligence, metadata translation, perspective and semi-additive measures.

Proactive Caching




Available

Provides automated caching for greater scalability and performance.

Advanced Data Management




Available

Partitioned cubes, parallel processing, server synchronization.

Full Writeback Support




Available

Dimension and cell writeback

Data Mining



Available

Available

Nine algorithms including decision and regression trees, clustering, logistic and linear regression, neural networks, naive bayes, association, sequence clustering, and time series. Build smarter reports that leverage centralized business logic and KPIs, as well as the performance of UDM.

Advanced Performance Tuning




Available

Additional options for tuning data mining models for the highest accuracy, performance, and scalability.

SQL Server Integration Services Data Flow Integration




Available

Perform data mining prediction and training operations directly in your operational data pipelines.

Text Mining




Available

Convert unstructured text data to structured data for analysis via reporting, online analytical processing (OLAP), or data mining.

1 Local and Relational Servers only.

2 Report Designer only.

3 Report Designer only.

4 Reporting Services only.

5 Reporting Services only.

COMPARE 2 DB's

Pass DBNames as parameters. Very usefull.



CREATE PROC sp_CompareDB
@db1 varchar(128),
@db2 varchar(128),
@OnlyStructure bit = 0,
@TabList varchar(8000) = '',
@NumbToShow int = 10,
@NoTimestamp bit = 0,
@VerboseLevel tinyint = 0
AS
if @OnlyStructure <> 0
set @OnlyStructure = 1
if @NoTimestamp <> 0
set @NoTimestamp = 1
if @VerboseLevel <> 0
set @VerboseLevel = 1

SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
declare @sqlStr varchar(8000)
set nocount on
-- Checking if there are specified databases
declare @SrvName sysname
declare @DBName sysname
set @db1 = RTRIM(LTRIM(@db1))
set @db2 = RTRIM(LTRIM(@db2))
set @SrvName = @@SERVERNAME
if CHARINDEX('.',@db1) > 0
begin
set @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)
if not exists (select * from master.dbo.sysservers where srvname = @SrvName)
begin
print 'There is no linked server named '+@SrvName+'. End of work.'
return
end
set @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))
end
else
set @DBName = @db1
exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')

if @@rowcount = 0
begin
print 'There is no database named '+@db1+'. End of work.'
return
end
set @SrvName = @@SERVERNAME
if CHARINDEX('.',@db2) > 0
begin
set @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)
if not exists (select * from master.dbo.sysservers where srvname = @SrvName)
begin
print 'There is no linked server named '+@SrvName+'. End of work.'
return
end
set @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))
end
else
set @DBName = @db2
exec ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
if @@rowcount = 0
begin
print 'There is no database named '+@db2+'. End of work.'
return
end

print Replicate('-',LEN(@db1)+LEN(@db2)+25)
print 'Comparing databases '+@db1+' and '+@db2
print Replicate('-',LEN(@db1)+LEN(@db2)+25)
print 'Options specified:'
print ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' END
print ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList END
print ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))
print ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' END
print ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END

-----------------------------------------------------------------------------------------
-- Comparing structures
-----------------------------------------------------------------------------------------
print CHAR(10)+Replicate('-',36)
print 'Comparing structure of the databases'
print Replicate('-',36)
if exists (select * from tempdb.dbo.sysobjects where name like '#TabToCheck%')
drop table #TabToCheck
create table #TabToCheck (name sysname)
declare @NextCommaPos int
if len(@TabList) > 0
begin
while 1=1
begin
set @NextCommaPos = CHARINDEX(',',@TabList)
if @NextCommaPos = 0
begin
set @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'
exec (@sqlstr)
break
end
set @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'
exec (@sqlstr)
set @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)
end
end
else -- then will check all tables
begin
exec ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')
exec ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')
end
-- First check if at least one table specified in @TabList exists in db1
exec ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')
if @@rowcount = 0
begin
print 'No tables in '+@db1+' to check. End of work.'
return
end
-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)
if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB2%')
drop table #TabNotInDB2
create table #TabNotInDB2 (name sysname)
insert into #TabNotInDB2
exec ('select name from '+@db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @@rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'
select * from #TabNotInDB2
end
delete from #TabToCheck where name in (select * from #TabNotInDB2)
drop table #TabNotInDB2

if exists (select * from tempdb.dbo.sysobjects where name like '#TabNotInDB1%')
drop table #TabNotInDB1
create table #TabNotInDB1 (name sysname)
insert into #TabNotInDB1
exec ('select name from '+@db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @@rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'
select * from #TabNotInDB1
end
delete from #TabToCheck where name in (select * from #TabNotInDB1)
drop table #TabNotInDB1
-- Comparing structures of tables existing in both dbs
print CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)
if exists (select * from tempdb.dbo.sysobjects where name like '#DiffStructure%')
drop table #DiffStructure
create table #DiffStructure (name sysname)
set @sqlStr='
declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,
@TypeName1 sysname, @TypeName2 sysname,
@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysname
declare @DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@db1+'.dbo.sysobjects d1o
JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@db2+'.dbo.sysobjects d2o
JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@db2+'.dbo.sysobjects d2o2
JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@db1+'.dbo.sysobjects d1o2
JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
set @PrevTName = ''''
set @DiffStructure = 0
while @@fetch_status = 0
begin
if Coalesce(@TName1,@TName2) <> @PrevTName
begin
if @PrevTName <> '''' and @DiffStructure = 1
begin
insert into #DiffStructure values (@PrevTName)
set @DiffStructure = 0
end
set @PrevTName = Coalesce(@TName1,@TName2)
print @PrevTName
end
if @CName2 is null
print '' Colimn ''+RTRIM(@CName1)+'' not in '+@db2+'''
else
if @CName1 is null
print '' Colimn ''+RTRIM(@CName2)+'' not in '+@db1+'''
else
if @TypeName1 <> @TypeName2
print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then length are diff
print '' Colimn ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+
''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''
if @Type1 = @Type2
set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicate
else
set @DiffStructure = 1
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
end
deallocate Diff
if @DiffStructure = 1
insert into #DiffStructure values (@PrevTName)
'
exec (@sqlStr)
if (select count(*) from #DiffStructure) > 0
begin
print CHAR(10)+'The table(s) have the same name and different structure in the databases:'
select distinct * from #DiffStructure
delete from #TabToCheck where name in (select * from #DiffStructure)
end
else
print CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)
if @OnlyStructure = 1
begin
print 'The option ''Only compare structures'' was specified. End of work.'
return
end
exec ('declare @Name sysname select @Name=d1o.name
from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
if @@rowcount = 0
begin
print 'There are no tables with the same name and structure in the databases to compare. End of work.'
return
end


-----------------------------------------------------------------------------------------
-- Comparing data
-----------------------------------------------------------------------------------------
-- ##CompareStr - will be used to pass comparing strings into dynamic script
-- to execute the string
if exists (select * from tempdb.dbo.sysobjects where name like '##CompareStr%')
drop table ##CompareStr
create table ##CompareStr (Ind int, CompareStr varchar(8000))

if exists (select * from tempdb.dbo.sysobjects where name like '#DiffTables%')
drop table #DiffTables
create table #DiffTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#IdenticalTables%')
drop table #IdenticalTables
create table #IdenticalTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#EmptyTables%')
drop table #EmptyTables
create table #EmptyTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#NoPKTables%')
drop table #NoPKTables
create table #NoPKTables (Name sysname)

if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')
truncate table #IndList1
else
create table #IndList1 (IndId int, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')
truncate table #IndList2
else
create table #IndList2 (IndId smallint, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))

print Replicate('-',51)
print 'Comparing data in tables with indentical structure:'
print Replicate('-',51)
--------------------------------------------------------------------------------------------
-- Cursor for all tables in dbs (or for all specified tables if parameter @TabList is passed)
--------------------------------------------------------------------------------------------
declare @SqlStrGetListOfKeys1 varchar(8000)
declare @SqlStrGetListOfKeys2 varchar(8000)
declare @SqlStrGetListOfColumns varchar(8000)
declare @SqlStrCompareUKeyTables varchar(8000)
declare @SqlStrCompareNonUKeyTables varchar(8000)
set @SqlStrGetListOfKeys1 = '
declare @sqlStr varchar(8000)
declare @ExecSqlStr varchar(8000)
declare @PrintSqlStr varchar(8000)
declare @Tab varchar(128)
declare @d1User varchar(128)
declare @d2User varchar(128)
declare @KeyAndStr varchar(8000)
declare @KeyCommaStr varchar(8000)
declare @AndStr varchar(8000)
declare @Eq varchar(8000)
declare @IndId int
declare @IndStatus int
declare @CurrIndId smallint
declare @CurrStatus int
declare @UKey sysname
declare @Col varchar(128)
declare @LastUsedCol varchar(128)
declare @xType int
declare @Len int
declare @SelectStr varchar(8000)
declare @ExecSql nvarchar(1000)
declare @NotInDB1 bit
declare @NotInDB2 bit
declare @NotEq bit
declare @Numb int
declare @Cnt1 int
declare @Cnt2 int
set @Numb = 0

declare @StrInd int
declare @i int
declare @PrintStr varchar(8000)
declare @ExecStr varchar(8000)
declare TabCur cursor for

select d1o.name, d1u.name, d2u.name from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o,
'+@db1+'.dbo.sysusers d1u, '+@db2+'.dbo.sysusers d2u
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.uid = d1u.uid and d2o.uid = d2u.uid
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)
order by 1

open TabCur
fetch next from TabCur into @Tab, @d1User, @d2User
while @@fetch_status = 0
begin
set @Numb = @Numb + 1
print Char(13)+Char(10)+LTRIM(STR(@Numb))+''. TABLE: [''+@Tab+''] ''

set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db1+'.[''+@d1User+''].[''+@Tab+'']''
exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt1 output
print CHAR(10)+STR(@Cnt1)+'' rows in '+@db1+'''
set @ExecSql = ''SELECT @Cnt = count(*) FROM '+@db2+'.[''+@d2User+''].[''+@Tab+'']''
exec sp_executesql @ExecSql, N''@Cnt int output'', @Cnt = @Cnt2 output
print STR(@Cnt2)+'' rows in '+@db2+'''
if @Cnt1 = 0 and @Cnt2 = 0
begin
exec ('' insert into #EmptyTables values(''''[''+@Tab+'']'''')'')
goto NextTab
end
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
set @NotInDB1 = 0
set @NotInDB2 = 0
set @NotEq = 0
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
truncate table #IndList1
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.sysindexes i, '+@db1+'.dbo.sysindexkeys k, '+@db1+'.dbo.syscolumns c
where i.id = o.id and o.name = @Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
while @@fetch_status = 0
begin
if @KeyAndStr <> ''''
begin
set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10)
set @KeyCommaStr = @KeyCommaStr + '', ''
end
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@UKey+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''
end
else if @xType = 189 -- timestamp (189)
begin
set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''
end
else if @xType = 98 -- SQL_variant
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
if @IndId <> @CurrIndId
begin
insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList1 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)'
set @SqlStrGetListOfKeys2 = '
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
truncate table #IndList2
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@db2+'.dbo.sysobjects o, '+@db2+'.dbo.sysindexes i, '+@db2+'.dbo.sysindexkeys k, '+@db2+'.dbo.syscolumns c
where i.id = o.id and o.name = @Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
while @@fetch_status = 0
begin
if @KeyAndStr <> ''''
begin
set @KeyAndStr = @KeyAndStr + '' and '' + CHAR(10)
set @KeyCommaStr = @KeyCommaStr + '', ''
end
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 971428763405345098745 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @KeyAndStr = @KeyAndStr + '' CASE WHEN d1.[''+@UKey+''] is null THEN 99999 ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN 99999 ELSE d2.[''+@UKey+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@UKey+''] END=''+
''CASE WHEN d2.[''+@UKey+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@UKey+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @KeyAndStr = @KeyAndStr +
'' CASE WHEN d1.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@UKey+''],109) END=''+
''CASE WHEN d2.[''+@UKey+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@UKey+''],109) END ''
end
else if @xType = 189 -- timestamp (189)
begin
set @KeyAndStr = @KeyAndStr + '' d1.[''+@UKey+'']=d2.[''+@UKey+''] ''
end
else if @xType = 98 -- SQL_variant
begin
set @KeyAndStr = @KeyAndStr + '' ISNULL(d1.[''+@UKey+''],''''!#null$'''')=ISNULL(d2.[''+@UKey+''],''''!#null$'''') ''
end
set @KeyCommaStr = @KeyCommaStr + '' d1.''+@UKey
fetch next from UKeys into @IndId, @IndStatus, @UKey, @xType
if @IndId <> @CurrIndId
begin
insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @CurrIndId = @IndId
set @CurrStatus = @IndStatus
set @KeyAndStr = ''''
set @KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList2 values (@CurrIndId, @CurrStatus, @KeyAndStr, @KeyCommaStr)
set @KeyCommaStr = null

select @KeyCommaStr=i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr
where (i1.IndStatus & 2048)<> 0 and (i2.IndStatus & 2048)<>0

if @KeyCommaStr is null
set @KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)
set @KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr = @KeyCommaStr)
if @KeyCommaStr is null
set @KeyCommaStr = ''''
if @KeyAndStr is null
set @KeyAndStr = '''''
set @SqlStrGetListOfColumns = '
set @AndStr = ''''
set @StrInd = 1
declare Cols cursor local fast_forward for
select c.name, c.xtype, c.length from '+@db1+'.dbo.sysobjects o, '+@db1+'.dbo.syscolumns c
where o.id = c.id and o.name = @Tab
and CHARINDEX(c.name, @KeyCommaStr) = 0
open Cols
fetch next from Cols into @Col, @xType, @len
while @@fetch_status = 0
begin
if @xType = 175 or @xType = 167 or @xType = 239 or @xType = 231 -- char, varchar, nchar, nvarchar
begin
set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''
end
if @xType = 173 or @xType = 165 -- binary, varbinary
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 56 or @xType = 127 or @xType = 60 or @xType = 122 -- int, 127 - bigint,60 - money, 122 - smallmoney
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 971428763405345098745 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 971428763405345098745 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 106 or @xType = 108 -- int, decimal, numeric
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 71428763405345098745098.8723 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 62 or @xType = 59 -- 62 - float, 59 - real
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 8764589764.22708E237 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 52 or @xType = 48 or @xType = 104 -- smallint, tinyint, bit
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null THEN 99999 ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN 99999 ELSE d2.[''+@Col+''] END ''
end
else if @xType = 36 -- 36 - id
begin
set @Eq = ''CASE WHEN d1.[''+@Col+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@Col+''] END=''+
''CASE WHEN d2.[''+@Col+''] is null''+
'' THEN CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@Col+''] END''
end
else if @xType = 61 or @xType = 58 -- datetime, smalldatetime
begin
set @Eq =
''CASE WHEN d1.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d1.[''+@Col+''],109) END=''+
''CASE WHEN d2.[''+@Col+''] is null THEN ''''!#null$'''' ELSE CONVERT(varchar(40),d2.[''+@Col+''],109) END ''
end
else if @xType = 34
begin
set @Eq = ''ISNULL(DATALENGTH(d1.[''+@Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@Col+'']),0) ''
end
else if @xType = 35 or @xType = 99 -- text (35),ntext (99)
begin
set @Eq = ''ISNULL(SUBSTRING(d1.[''+@Col+''],1,DATALENGTH(d1.[''+@Col+
''])),''''!#null$'''')=ISNULL(SUBSTRING(d2.[''+@Col+''],1,DATALENGTH(d2.[''+@Col+''])),''''!#null$'''') ''
end
else if @xType = 189
begin
if '+STR(@NoTimestamp)+' = 0
set @Eq = ''d1.[''+@Col+'']=d2.[''+@Col+''] ''
else
set @Eq = ''1=1''
end
else if @xType = 98 -- SQL_variant
begin
set @Eq = ''ISNULL(d1.[''+@Col+''],''''!#null$'''')=ISNULL(d2.[''+@Col+''],''''!#null$'''') ''
end
if @AndStr = ''''
set @AndStr = @AndStr + CHAR(10) + '' '' + @Eq
else
if len(@AndStr) + len('' and '' + @Eq)<8000
set @AndStr = @AndStr + '' and '' + CHAR(10) + '' '' + @Eq
else
begin
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr)
set @AndStr = '' and '' + @Eq
end
fetch next from Cols into @Col, @xType, @len
end
deallocate Cols '
set @SqlStrCompareUKeyTables = '
if @KeyAndStr <> ''''
begin
set @SelectStr = ''SELECT ''+ @KeyCommaStr+'' INTO ##NotInDb2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@KeyAndStr+'')''
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@db1+', but are not in db2:''+CHAR(10)+
REPLACE (@SelectStr, ''into ##NotInDB2'','''')
exec (@SelectStr)
if @@rowcount > 0
set @NotInDB2 = 1
set @SelectStr = ''SELECT ''+@KeyCommaStr+'' INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@KeyAndStr+'')''
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@db2+', but are not in '+@db1+':''+CHAR(10)+
REPLACE (@SelectStr, ''into ##NotInDB1'','''')
exec (@SelectStr)
if @@rowcount > 0
set @NotInDB1 = 1
-- if there are non-key columns
if @AndStr <> ''''
begin
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+ @KeyCommaStr+'' INTO ##NotEq FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 ''+
'' INNER JOIN '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 ON ''+CHAR(10)+@KeyAndStr+CHAR(10)+''WHERE not('')
-- Adding last string in temp table containing a comparing string to execute
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr+'')'')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotEq'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are different in non-key columns:'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)

if @@rowcount > 0
set @NotEq = 1
end
else
if '+STR(@VerboseLevel)+' = 1
print CHAR(10)+''There are no non-key columns in the table''
truncate table ##CompareStr
if @NotInDB1 = 1 or @NotInDB2 = 1 or @NotEq = 1
begin
print CHAR(10)+''Data are different''
if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''These key values exist in '+@db1+', but do not exist in '+@db2+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''
exec (@SelectStr)
end
if @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''These key values exist in '+@db2+', but do not exist in '+@db1+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''
exec (@SelectStr)
end
if @NotEq = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''Row(s) with these key values contain differences in non-key columns: ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotEq''
exec (@SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'')
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')
drop table ##NotEq
end
else '
set @SqlStrCompareNonUKeyTables = '
begin
exec (''insert into #NoPKTables values(''''[''+@Tab+'']'''')'')
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB2 FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+
'' (SELECT * FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d2 WHERE '')
set @StrInd = @StrInd + 1
Insert into ##CompareStr values(@StrInd,@AndStr+'')'')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB2'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@db1+', but are not in '+@db2+':'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)

if @@rowcount > 0
set @NotInDB2 = 1
delete from ##CompareStr where ind = 1
set @PrintStr = '' Print ''
set @ExecStr = '' exec (''
set @SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB1 FROM '+@db2+'.[''+@d2User+''].[''+@Tab+''] d1 WHERE not exists ''+CHAR(10)+
'' (SELECT * FROM '+@db1+'.[''+@d1User+''].[''+@Tab+''] d2 WHERE '')
set @i = 1
while @i <= @StrInd
begin
set @SqlStr = @SqlStr + '' declare @Str''+LTRIM(STR(@i))+'' varchar(8000) ''+
''select @Str''+LTRIM(STR(@i))+''=CompareStr FROM ##CompareStr WHERE ind = ''+STR(@i)
if @ExecStr <> '' exec (''
set @ExecStr = @ExecStr + ''+''
if @PrintStr <> '' Print ''
set @PrintStr = @PrintStr + ''+''
set @ExecStr = @ExecStr + ''@Str''+LTRIM(STR(@i))
set @PrintStr = @PrintStr + '' REPLACE(@Str''+LTRIM(STR(@i))+'','''' into ##NotInDB1'''','''''''') ''
set @i = @i + 1
end
set @ExecStr = @ExecStr + '') ''
set @ExecSqlStr = @SqlStr + @ExecStr
set @PrintSqlStr = @SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@db2+', but are not in '+@db1+':'''' ''+
@PrintStr
if '+STR(@VerboseLevel)+' = 1
exec (@PrintSqlStr)
exec (@ExecSqlStr)

if @@rowcount > 0
set @NotInDB1 = 1
truncate table ##CompareStr
if @NotInDB1 = 1 or @NotInDB2 = 1
begin
print CHAR(10)+''Data are different''
if @NotInDB2 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@db1+', but do not exist in '+@db2+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB2''
exec (@SelectStr)
end
if @NotInDB1 = 1 and '+STR(@NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@db2+', but do not exist in '+@db1+': ''
set @SelectStr = ''select top ''+STR('+STR(@NumbToShow)+')+'' * from ##NotInDB1''
exec (@SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@Tab+'']'''')'')
end
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB1%'')
drop table ##NotInDB1
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotInDB2%'')
drop table ##NotInDB2
NextTab:
fetch next from TabCur into @Tab, @d1User, @d2User
end
deallocate TabCur
'
exec (@SqlStrGetListOfKeys1+@SqlStrGetListOfKeys2+@SqlStrGetListOfColumns+
@SqlStrCompareUKeyTables+@SqlStrCompareNonUKeyTables)
print ' '
SET NOCOUNT OFF
if (select count(*) from #NoPKTables) > 0
begin
select name as 'Table(s) without Unique key:' from #NoPKTables
end
if (select count(*) from #DiffTables) > 0
begin
select name as 'Table(s) with the same name & structure, but different data:' from #DiffTables
end
else
print CHAR(10)+'No tables with the same name & structure, but different data'+CHAR(10)
if (select count(*) from #IdenticalTables) > 0
begin
select name as 'Table(s) with the same name & structure and identical data:' from #IdenticalTables
end
if (select count(*) from #EmptyTables) > 0
begin
select name as 'Table(s) with the same name & structure and empty in the both databases:' from #EmptyTables
end
drop table #TabToCheck
drop table ##CompareStr
drop table #DiffTables
drop table #IdenticalTables
drop table #EmptyTables
drop table #NoPKTables
drop table #IndList1
drop table #IndList2
return

GO

14 March 2011

Assigning Log On As A Service rights - SQL SERVER SERVICE ACCOUNT PROBLEMS


To assign Log on as a service rights to a user account on Microsoft Windows XP (SP2) or Microsoft Windows Vista machine manually:
1. Navigate to Start ► Control Panel ► Administrative Tools ► Local Security Policy.
2. Expand Security Settings ► Local Policies ► User Rights Assignment.
3. Right-click Log on as a service from the right panel and click Properties.
4. Select the Local Security Setting tab.
5. Click Add User or Group button.
7. Click Apply and OK.
8. Close Local Security Settings dialog.
To assign Log on as service rights to a user account on Microsoft Windows Server 2003 or Microsoft Windows Server 2008 machines manually:
1. Navigate to StartProgramsAdministrative ToolsLocal Security Policy.
C:\Documents and Settings\ozammit\My Documents\WebMon Proxy\MyVersion_2\Pictures\Routing Traffic Mode.png
2. Expand Security Settings ► Local Policies ► User Rights Assignment.
3. Right-click Log on as a service from the right panel and click Properties.
4. Select the Local Security Setting tab.
5. Click Add User or Group button.
7. Click Apply and OK.
To assign Log on as service rights on clients’ machines through Microsoft Windows Server 2003 GPO:
1. Navigate to Start ► Programs ► Administrative Tools ► Active Directory Users and Computers on the DNS server.
3. Select Group Policy tab in the Domain Properties dialog.
4. Select Default Domain Policy from the list and click Edit
C:\Documents and Settings\ozammit\My Documents\WebMon Proxy\MyVersion_2\Pictures\Routing Traffic Mode.png
5. Expand Computer Configuration Windows Settings Security SettingsLocal Policies and click User Rights Assignment.
6. Right-click Log on as a service from the right panel and click Properties.
7. Select the Security Policy Setting tab.
8. Check Define these policy settings checkbox
9. Click Add User or Group button.
11. Click Apply and OK.
To assign Log on as service rights on clients’ machines through Microsoft Windows Server 2008 GPO:
1. In the command prompt key in mmc.exe and press Enter.
2. In the Console Root window, navigate to File Add/Remove Snap-in… to open the Add or Remove Snap-ins window.
C:\Documents and Settings\ozammit\My Documents\WebMon Proxy\MyVersion_2\Pictures\Routing Traffic Mode.png
3. Select Group Policy Management from the Available snap-ins list, and click Add.
C:\Documents and Settings\ozammit\My Documents\WebMon Proxy\MyVersion_2\Pictures\Routing Traffic Mode.png
5. Expand Group Policy ManagementForestDomains and .
6. Right-click Default Domain Policy and click Edit to open the Group Policy Management Editor.
C:\Documents and Settings\ozammit\My Documents\WebMon Proxy\MyVersion_2\Pictures\Routing Traffic Mode.png
7. Expand Computer Configuration Policies Windows SettingsSecurity SettingsLocal Policies and click User Rights Assignment.
8. Right-click Log on as a service from the right panel and click Properties.
9. Select the Security Policy Setting tab.
10. Check Define these policy settings checkbox
11. Click Add User or Group button.
13. Click Apply and OK.

10 March 2011

Login Script

* This is a modified procedure of Sp_help Logins
* Run the Proc in master



USE [master]
GO

/****** Object: StoredProcedure [dbo].[PRADI] Script Date: 03/10/2011 12:10:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRADI]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PRADI]
GO

USE [master]
GO

/****** Object: StoredProcedure [dbo].[PRADI] Script Date: 03/10/2011 12:10:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[PRADI]

@LoginNamePattern sysname = NULL
AS

set nocount on

declare
@exec_stmt nvarchar(3550)

declare
@RetCode int
,@CountSkipPossUsers int
,@Int1 int

declare
@c10DBName sysname
,@c10DBStatus int
,@c10DBSID varbinary(85)

declare
@charMaxLenLoginName varchar(11)
,@charMaxLenDBName varchar(11)
,@charMaxLenUserName varchar(11)
,@charMaxLenLangName varchar(11)

declare
@DBOptLoading int --0x0020 32 "DoNotRecover"
,@DBOptPreRecovery int --0x0040 64
,@DBOptRecovering int --0x0080 128

,@DBOptSuspect int --0x0100 256 ("not recovered")
,@DBOptOffline int --0x0200 512
,@DBOptDBOUseOnly int --0x0800 2048

,@DBOptSingleUser int --0x1000 4096


CREATE TABLE #tb1_UA
(
LoginName sysname collate database_default NOT Null
,DBName sysname collate database_default NOT Null
,UserName sysname collate database_default NOT Null
,UserOrAlias char(8) collate database_default NOT Null
)

---------------- Initial data values -------------------

select
@RetCode = 0 -- 0=good ,1=bad
,@CountSkipPossUsers = 0


---------------- Only SA can run this -------------------


if (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
select @RetCode = 1
goto label_86return
end

---------------------- spt_values ----------------
-------- 'D'

select @DBOptLoading = number
from master.dbo.spt_values
where type = 'D'
and name = 'loading'

select @DBOptPreRecovery = number
from master.dbo.spt_values
where type = 'D'
and name = 'pre recovery'

select @DBOptRecovering = number
from master.dbo.spt_values
where type = 'D'
and name = 'recovering'

select @DBOptSuspect = number
from master.dbo.spt_values
where type = 'D'
and name = 'not recovered'

select @DBOptOffline = number
from master.dbo.spt_values
where type = 'D'
and name = 'offline'

select @DBOptDBOUseOnly = number
from master.dbo.spt_values
where type = 'D'
and name = 'dbo use only'

select @DBOptSingleUser = number
from master.dbo.spt_values
where type = 'D'
and name = 'single user'



--------------- Cursor, for DBNames -------------------


declare ms_crs_10_DB
Cursor local static For
select
name ,status ,sid
from
master.dbo.sysdatabases



OPEN ms_crs_10_DB


----------------- LOOP 10: thru Databases ------------------


--------------
WHILE (10 = 10)
begin --LOOP 10: thru Databases


FETCH
next
from
ms_crs_10_DB
into
@c10DBName
,@c10DBStatus
,@c10DBSID


IF (@@fetch_status <> 0)
begin
deallocate ms_crs_10_DB
BREAK
end


-------------------- Okay if we peek inside this DB now?


IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
AND @c10DBSID <> suser_sid()
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end


IF (@c10DBStatus & @DBOptSingleUser > 0)
begin

select @Int1 = count(*)
from sys.dm_exec_requests
where session_id <> @@spid
and database_id = db_id(@c10DBName)

IF (@Int1 > 0)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
end


IF (@c10DBStatus &
(
@DBOptLoading
| @DBOptRecovering
| @DBOptSuspect
| @DBOptPreRecovery
)
> 0
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end


IF (@c10DBStatus &
(
@DBOptOffline
)
> 0
)
begin
--select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end

IF (has_dbaccess(@c10DBName) <> 1)
begin
raiserror(15622,-1,-1, @c10DBName)
CONTINUE
end



--------------------- Add the User info to holding table.
select @exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
select
N' + quotename(@c10DBName, '''') + '
,l.name
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.sys.sysusers u
,sys.server_principals l
where
u.sid = l.sid' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
select

N' + quotename(@c10DBName, '''') + '
,l.name
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.sys.database_role_members m
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2
,sys.server_principals l
where
u1.sid = l.sid
and m.member_principal_id = u1.principal_id
and m.role_principal_id = u2.principal_id' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end

EXECUTE(@exec_stmt)

end --loop 10

------------ Optimize UA report column display widths -----------


select
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
,isnull ( max(datalength(DBName)) ,6)
)
,@charMaxLenUserName =
convert ( varchar
,isnull ( max(datalength(UserName)) ,8)
)
from
#tb1_UA



------------ Print out the UserOrAlias report ------------

EXEC(
'
set nocount off


select
''LoginName'' = substring (LoginName ,1 ,'
+ @charMaxLenLoginName + ')

,''DBName'' = substring (DBName ,1 ,'
+ @charMaxLenDBName + ')

,''UserName'' = substring (UserName ,1 ,'
+ @charMaxLenUserName + ')

,UserOrAlias
from
#tb1_UA
order by
1 ,2 ,3


Set nocount on
'
)


----------------------- Finalization --------------------
label_86return:


IF (object_id('#tb1_UA') IS NOT Null)
DROP Table #tb1_UA

Return @RetCode
GO