SqlDependency Notification database Options troubleshooting

For SQL notifications we followed the steps described in http://dimarzionist.wordpress.com/2009/04/01/how-to-make-sql-server-notifications-work.

However it didn’t work for our database. The instructions missed a step to ensure that database options are set for SqlDependency Notification

I’ve created a function to Follow recommendation to always check the notification source, info and type, it returned
 
SqlNotificationEventArgs, Type : Subscribe, Source : Statement, Info : Options 

    //TODO: pass  SQL string
      /// <summary>
               ///
               /// </summary>
               /// <param name=”logger”></param>
               /// <param name=”e”></param>
               public static void LogSqlNotificationEventArgs( Logger logger, SqlNotificationEventArgs e,DbConnection dbConnection=null)
              {
                      LogLevel level = LogLevel .Warn;
                      //You’re supposed to check the values of the SqlNotificationEventArgs argument. Only if Type is Change and Source is Data where you notified for a data change.
                      if ((e.Type == SqlNotificationType .Change) && e.Source == SqlNotificationSource.Data)
                     {
                           level = LogLevel.Info;
                     }
                      if (e.Info == SqlNotificationInfo .Options)
                     {
                            if (dbConnection != null )
                           {
                                   //TODO http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/. Make SP
                                   //check DB set options,
                                   // Execute SqlDependencies_CheckDBSetOptions
                                   // Run Alter DB

                           }
                     }
               //http://msdn.microsoft.com/en-us/library/ms189308%28SQL.105%29.aspx Insert/update/delete/truncate are valid
                     logger.Log(level, “SqlNotificationEventArgs, Type : {0}, Source : {1}, Info : {2} “, e.Type, e.Source, e.Info);
                     
              }

Based on TROUBLESHOOTING QUERY NOTIFICATIONS in http://rusanu.com/2006/06/17/the-mysterious-notification/ 

and Troubleshooting Query Notifications I’ve created Stored Procedure


/*
— RUN manually for every DB that required change

ALTER DATABASE MyDB
set ANSI_NULLS ON
       , ANSI_PADDING ON
, ANSI_WARNINGS ON
, CONCAT_NULL_YIELDS_NULL ON
, QUOTED_IDENTIFIER ON
, NUMERIC_ROUNDABORT OFF
,ARITHABORT ON
*/
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— Drop stored procedure if it already exists
IF EXISTS (  SELECT *     FROM INFORMATION_SCHEMA.ROUTINES     WHERE SPECIFIC_SCHEMA = N’dbo’     AND SPECIFIC_NAME = N’SqlDependencies_CheckDBSetOptions’ )
   DROP PROCEDURE dbo.SqlDependencies_CheckDBSetOptions
GO

— =============================================
— Author:           MNF
— Create date:
— Description:      
— =============================================
CREATE PROCEDURE SqlDependencies_CheckDBSetOptions
       @dbName sysname 
AS
BEGIN
        — SET NOCOUNT ON added to prevent extra result sets from
        — interfering with SELECT statements.
        SET NOCOUNT ON;
select –*
       is_ANSI_NULLS_ON
        ,is_ANSI_PADDING_ON
        ,is_ANSI_WARNINGS_ON
        , is_CONCAT_NULL_YIELDS_NULL_ON
, is_QUOTED_IDENTIFIER_ON
, is_NUMERIC_ROUNDABORT_ON–OFF
,is_ARITHABORT_ON
–select *
From sys .databases where name =@dbName 
and (
       is_ANSI_NULLS_ON =0
        or is_ANSI_PADDING_ON =0
        or is_ANSI_WARNINGS_ON =0
        or is_CONCAT_NULL_YIELDS_NULL_ON =0
or is_QUOTED_IDENTIFIER_ON= 0
or is_NUMERIC_ROUNDABORT_ON = 1–OFF
or is_ARITHABORT_ON= 0)

END
— SqlDependencies_CheckDBSetOptions ‘MyDB’
GO
Advertisements

Eager Loading more than 1 table in LinqtoSql

When I’ve tried in Linq2Sql to load table with 2 child tables, I’ve noticed, that multiple SQLs are generated. I’ve found that  it is
a known issue, if you try to specify more than one to pre-load it just  picks which one to pre-load and which others to leave deferred (simply ignoring those LoadWith hints)
There are more explanations in 
The reason the relationship in your blog post above is generating multiple queries is that you have two (1:n) relationship (Customers->Orders) and (Orders->OrderDetails). If you just had one (1:n) relationship (Customer->Orders) or (Orders->OrderDetails) LINQ to SQL would optimize and grab it in one query (using a JOIN). 

The alternative -to use SQL and POCO classes-see http://stackoverflow.com/questions/238504/linq-to-sql-loading-child-entities-without-using-dataloadoptions?rq=1

Fortunately the problem is not applicable to Entity Framework, that we want to use in future development instead of Linq2Sql
Product firstProduct =  db.Product.Include("OrderDetail").Include("Supplier").First();

м

Monitoring Database disk space

An article Data files: To Autogrow Or Not To Autogrow? recommends NOT to rely on auto-grow, because it causing delays in unplanned times.

We should mtonitor database files(both data and log), and if they close to max capacity, manually increase the size.

 However it doesn’t give references, how to monitor the free space inside databases.
 
I’ve tried to look how to do it. 

It can be done manually using   

execute sp_spaceused for the database in question or 

sp_SOS (can be downloaded from
http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure)

Alternatively you can run SQL commands as suggested in Http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359 by Michael Valentine Jones

select 
[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) 
from dbo.sysfiles a
More useful article Monitor database file sizes with SQL Server Jobs describes how to setup monitoring

 Finally I found the excellent article

Managing Database Data Usage With Custom Space Alerts, that can be followed even support personnel without much DBA experience.

Links about SQL PIVOT

One of my colleagues asked how to create SQL query to show results as columns of a row, that actually stored in multiple rows in the table. Other co-worker suggested to use cursor to manually generate required rows.
I’ve found a few links about SQL PIVOT.  
It is easy to start with
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx
or http://programmingsolution.net/post/SQL-Server-Pivot-Converting-Rows-to-Columns-with-Dynamic-Query.aspx

More detailed are articles written by Itzik Ben-Ganhttp://www.sqlmag.com/article/tsql3/pivot-or-unpivot-your-data
and
http://www.sqlmag.com/content1/topic/pivot-or-unpivot-your-data/catpath/tsql3/page/2compaslightly  

How to use Pivoting Without Aggregation is described inhttp://www.sqlmag.com/article/tsql3/pivoting-without-aggregation

Disable messages from SQL Server “Login failed for user” in Event log

I’ve noticed multiple messages from SQL Server in EventLog on my machine
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.222.25.129]
I’ve found that there are machines of my co-workers, but they were not sure, which processes tried to access my SQL server.

I’ve tried a few things and finally in SQL Server Configuration Manager disabled tcp, as it was suggested in
http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx

DDL ‘IF not Exists” conditions to make SQL scripts re-runnable

 

As a part of continuous integration we are using deployment of database scripts, which makes very important to make the scripts re-runnable. Some checks for DDL elements are not obvious, and I decided to put hem in one place.Most answers were found on StackOverflow

 

Column does not exists
if NOT Exists(select * from sys.columns where Name = N’CreatedDate’ and Object_ID = Object_ID(N’MyTableName‘))

Check if primary key does not exists
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND TABLE_NAME = ‘MyTableName’ )
or
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME= ‘PK_MyTableName‘)

Check if foreign key does not exists
if NOT Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =’FK_MyRefTable_MyInstanceTable’)
or (from here)
IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = ‘FK_Name’)
–http://stackoverflow.com/questions/869081/a-way-to-check-if-foreign-key-exists-in-sql-2005


Check if DEFAULT does not exist
if NOT Exists(select * from sysobjects where xtype=’D’ and NAME=’DF_MyTableName_MyColumnName’)
 

Check if an INDEX doesn’t exist
–from http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table
IF NOT EXISTS(SELECT * FROM sys .indexes WHERE name = ‘_index_MyTableName_MyColumnName_MyColumn2 AND object_id = OBJECT_ID(MyTableName ))
CREATE NONCLUSTERED INDEX [_index_MyTableName_MyColumnName_MyColumn2] ON [dbo].[MyTableName]
(
        MyColumnName ASC ,
       MyColumn2 DESC
)
go

Check if STATISTICS name doesn’t exist
IF NOT EXISTS(SELECT name  FROM sys .stats WHERE name = ‘_dta_stat_MyTableName_MyColumn1_MyColumn2 AND object_id = OBJECT_ID(MyTableName ))
CREATE STATISTICS _dta_stat_MyTableName_PDFAttachmentName_MailID_DateCreated ON [dbo].[MyTableName] (MyColumn1,MyColumn2)
go

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc') exec('CREATE PROCEDURE [dbo].[MyProc] AS select 1')
GO ALTER PROCEDURE [dbo].[MyProc] AS
--body of your SP

GO

 Check if VIEW doesn’t exist
From Books Online: “CREATE VIEW must be the first statement in a query batch.”
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]’ ))
exec(‘ create view MyView AS select 1′)
GO
 
Alter VIEW [dbo]. [MyView]
AS
….
 
 

 
 

Some compatibility differences between versions of SQL Server

In our environment most of developers use SQL Server 2008,but in production we have SQL Server 2005, and some databases still have compatibility level 80(SQL server 2000)
It sometimes causes problems
E.g. the SQL wtitten on SQL Server 2008

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])

    VALUES  (11,’ChildOfXX’,(select [SiteID] from [SiteIds] where SiteCode=’XX’))

GO

on SQL Server 2005 caused
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Fortunately it’s easy to rewrite in SQL 2005 acceptable form
 

DECLARE @SiteId int

    set @siteID=(select [SiteID] from [SiteIds] where SiteCode=’XX’)

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])  VALUES  (11,’ChildOfXX’,@siteID)

Another statement that generated in SQL Server 2008 should be removed in SQL Server 2005
ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE)

Also we had a stored procedure, that worked in  compatibility level 80(SQL server 2000), but caused an error after changing to compatibility level 90

Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
The explanation of the error can be found in SELECT DISTINCT and ORDER BY post.  In my case DISTINCT was completely redundant and I’ve removed it without any side effects.