Comparing data

Suppose you need to compare data coming from two tables.

ProductionServer

USE SampleDatabase

CREATE TABLE dbo.SameTable

(

 ID1 INT NOT NULL,

 ID2 INT NOT NULL,

 COL VARCHAR(50) NOT NULL,

 PRIMARY KEY (ID1, ID2)

)

INSERT INTO dbo.SameTable VALUES(1, 1, 'A')

INSERT INTO dbo.SameTable VALUES(1, 2, 'B')

INSERT INTO dbo.SameTable VALUES(1, 3, 'C')

TestServer

USE SampleDatabase

CREATE TABLE dbo.SameTable

(

 ID1 INT NOT NULL,

 ID2 INT NOT NULL,

 COL VARCHAR(50) NOT NULL,

 PRIMARY KEY (ID1, ID2)

)

INSERT INTO dbo.SameTable VALUES(1, 1, 'A')

INSERT INTO dbo.SameTable VALUES(1, 2, 'D')

INSERT INTO dbo.SameTable VALUES(1, 4, 'E')

From TestServer using the LinkToProd linked server pointing to ProductionServer

we could use a full join query to spot the differences:

USE SampleDatabase

SELECT

 A.*,B.*

FROM

 dbo.SameTable A

  FULL JOIN

 LinkProd.SampleDatabase.dbo.SameTable B ON

  B.ID1 = A.ID1 AND

  B.ID2 = A.ID2

WHERE

 A.ID1 IS NULL OR

 B.ID1 IS NULL OR

 A.COL <> B.COL

You should obtain the follwing result:

ID1	ID2	COL	ID1	ID2	COL
1	2	D	1	2	B
NULL	NULL	NULL	1	3	C
1	4	E	NULL	NULL	NULL

Here you'll find the code to automatically create comparing scripts.

CompareData.zip

Reporting Services Rdl Align

RdlAlign is a small standalone utility that will allow you to change your rdl report grid spacing.

All sizes will be recomputed accordingly and, if needed, converted from cm to inches.

The program creates a new file OriginalReport.rdl.xml

Requirements

- .Net Framework

RdlAlign.zip

Extract MOSS 2007 list info via SQL

I want to share a short script developed to extract information and data regarding MOSS 2007 lists.

List settings are available quering dbo.Lists view:

SELECT tp_ID, tp_Fields FROM dbo.lists WHERE tp_Title = 'My List Name'

tp_ID list unique identifier

tp_Fields XML fragment containing list fields info

tp_Title list name

Using SQL Server xml capabilities, the script can extract fields info, relationships and other things.

You can also view the list content because the script queries dbo.UserData view.

You only have to set two variables.

SET @LIST_NAME = ''

SET @MODE = 'I' --[I]=Info; [C]=Choice; [F]=Fields info; [R]=FieldRef info; [D]=Data

--Ex. all lists: LIST_NAME = '' and MODE = 'I'

--Ex. info about lists where name contains "abc": LIST_NAME = 'abc' and MODE = 'I'

--Ex. "My List" Fields Info + FieldRef + Data: LIST_NAME = 'My List' and MODE = 'FRD'

Of course you could turn the script into a stored procedure.

MossListInfo.zip

Access SQL objects using the right order

Sometimes you may want to obtain the correct order to access tables.

You may want, for example, to create a script that empty a group af tables following the order imposed by their foreign key constraints.

In SQL Server, relations between tables are available using sys.foreign_keys system view (parent_object_id, referenced_object_id columns).

Relations between procedures, views and functions are available using sys.sql_dependencies (object_id, referenced_major_id) and mantained by SQL Server using object_id values (SQL Server 2008 provides a new approach).

If you create a table T1 and then a procedure P1 that refers to T1 you'll find in the system view something like:

P1 (object_id), T1 (referenced_major_id).

If you drop and re-create T1 you'll loose this relation until ALTER P1.

The following script provides object depth. It not deals with circular references.

DECLARE @DEPTH INT

DECLARE @COUNT INT

IF OBJECT_ID('tempdb..#OBJ') IS NOT NULL

    DROP TABLE #OBJ

SELECT

    O.object_id,

    0 depth

INTO #OBJ

FROM

    sys.objects O

WHERE

    NOT EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') AND

    O.is_ms_shipped = 0 AND

    O.type IN

    (

        'U', --tables

        'P', --procedures

        'V', --views

        'TR', --triggers

        'SN', --synonyms

        'FN','IF','TF' --functions

    )

SET @COUNT = @@ROWCOUNT

SET @DEPTH = 0

WHILE @COUNT > 0 AND @DEPTH <> 25--stop circular references

BEGIN

    SET @DEPTH = @DEPTH + 1

    UPDATE T1 SET

        DEPTH = @DEPTH

    FROM

        #OBJ T1

            INNER JOIN

        (

            SELECT parent_object_id, referenced_object_id FROM sys.foreign_keys

            UNION

            SELECT object_id, referenced_major_id FROM sys.sql_dependencies

        ) F ON

            F.parent_object_id = T1.object_id

            INNER JOIN

        #OBJ T2 ON

            T2.object_id = F.referenced_object_id

    WHERE

        T1.object_id <> T2.object_id AND --avoid self references

        T2.depth = @DEPTH-1

    SET @COUNT = @@ROWCOUNT

END

SELECT

    T.depth,

    O.type,

    T.object_id,

    SCHEMA_NAME(O.schema_id) + '.' + O.name obj_name

FROM

    #OBJ T

        INNER JOIN

    sys.objects O ON

        O.object_id = T.object_id

 ORDER BY

    T.DEPTH DESC

You can download the full script.

ObjectDepth.zip

SakScript

SakScript is an intuitive standalone application that will enable you to easily script and compare database items.

Features

• SQL Server 2005+ support
• Tables, views, procedures, triggers, functions and synonyms scripts
• Select, Insert and Update scripts
• Text search inside code definition with highlitghting
• One file per object or single file
• Table and view data export
• Table data insert script creation
• Compare databases via sql script generation
• Missing indexes search

Requirements

• .Net Framework 2.0+

Screenshots

• Screenshot1
• Screenshot2
• Screenshot3

SakScript is free for personal and commercial usage. It does not alter directly any SQL object, but use at your own risk and always check the results.

If you agree you can download the latest version (v1.4.0.0 - 2009-10-11)

SakScript.zip

For questions and suggestions write to Miken.

How to script SQL Server database objects

Sometimes you may need to script SQL objects using T-Sql instead of rely on SSMS or thirdy part tools.

SQL Server 2005 and 2008 offer you the chance to query their system views to obtain what you need.

Note that you could use a dll from SQL Server SDK, but here we are trying the sql approach.

For objects like procedures, triggers, functions and views you may use the OBJECT_DEFINITION function and something like

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.MyStoredProcedure'))

but combining the properties coming from sys.objects and sys.sql_modules views we will extract all the data we need.

SELECT

CASE

        WHEN O.type = 'V' THEN 'View'

        WHEN O.type = 'P' THEN 'Procedure'

        WHEN O.type = 'TR' THEN 'Trigger'

        WHEN O.type IN ('FN','IF','TF') THEN 'Function'

    END [type],

    SCHEMA_NAME(O.schema_id) obj_schema,

    O.name obj_name,

    CONVERT(VARCHAR, O.modify_date, 120) modify_date,

    SM.definition code

FROM

    sys.objects O

        INNER JOIN

    sys.sql_modules SM ON

        SM.object_id = O.object_id

WHERE

    O.is_ms_shipped = 0 AND

    O.type IN ('P','V','FN','IF','TF','TR')

ORDER BY

    type,

    obj_schema,

    obj_name

Table property definitions are spread across many system views. The most important are sys.tables and sys.columns.

SELECT

SCHEMA_NAME(TB.schema_id) tb_schema,

    TB.name tb_name,

    CL.name cl_name

FROM

    sys.tables TB

        INNER JOIN

    sys.columns CL ON

        CL.object_id = TB.object_id

ORDER BY

    tb_schema,

    tb_name,

    CL.column_id

I don't want to bother you with all the details, so you can download the full script.

ScriptSqlObjects.zip

It allows to generete the code for tables, views, procedures, triggers, functions and synonyms.

CRM 4.0 bulk delete performance degradation

After investigating the progressive performance degradation during a bulkdelete I think I found the problem.

It's the intence use of table variables (dbo.fn_CollectForCascadeDelete CRM function).

Table variables are cached in temptb so restarting the SQL Server service temporary solves the problem.

Of corse we cannot restart the server, but we can free the cache using the command:

DBCC FREESYSTEMCACHE ('ALL').

Beware this command causes a temporary SQL Server general perfomance degradation, so you should use it to solve this specific problem avoiding it during user activities.

In other words you'd better run it during the bulk delete batch activity.

In a real case deletion starts with 3600 records/second and ends with 12 records/second.

During the bulk delete, after empting the cache, the speed go back to 3600 then degradation starts again.

A solution could be periodically empty the cache only if tempdb contains the bulk delete table variable.

Here is the script to search for the specific CRM variable and run the cache cleancing only when we found it and if it was created at least two minutes ago.

select

 t.[object_id]

from

 tempdb.sys.tables t with(nolock)

  inner join

 (

  select

   [object_id]

  from

   tempdb.sys.columns with(nolock)

  where

   [name] in ('o','t'/*,'r'*/,'p')

  group by

   [object_id]

  having

   count([object_id]) = 3

 ) c on

  c.[object_id] = t.[object_id]

where

 datediff(mi, t.create_date, getdate()) >= 2

if @@rowcount > 0

 DBCC FREESYSTEMCACHE ('ALL')

We could for example include this command in a SQL Job scheduled during the bulk delete every 3 minutes.

Another slowing down cause could be missing foreign key indexes (ex. AsyncOperationId column, WorkFlowLogBase table).