Table Dependency in SQLServer 2005

Feb 6, 2016 at 3:06 PM
i Christian,

Sorry for brother you, I was looking for a Package for monitor SqlServer,

i almost cry of emotion when i discover TableDependency,


But I have this problem, and i wondwe if you can give a hand with it.


when i run my console app (adding almost the same code as in the video example), it throws this error:


Exception in 'System.Data.SqlClient.SqlException' in System.Data.dll

Adittional information: incorrect sintaxis near : 'POISON_MESSAGE_HANDLING'.

incorrect sintaxis near ')'.



Thanks again Christian.
Feb 6, 2016 at 3:06 PM
Hi Christian,


I find a way to fix this problem,



SqlServer 2005 doesnt conaint the param: POISON_MESSAGE_HANDLING, so i removed this param.


sqlserver 2005 have another problem, you cant declare and assign a variable in the same line, so i replaced this:


DECLARE @currentRow INT = 0;


With this:


DECLARE @currentRow INT
SET @currentRow=0;



Recompiie the dll, and it works....


I understand you cannot read and answer all the mails you receive, thanks for working in such amazing package!!
Mar 2, 2016 at 6:29 PM
I had this same issue, but you might want to modify SqlTableDependency.cs in the following manner in order to account for SQL Server versions 2008R2 and above which do have the POISON_MESSAGE_HANDLING capability:
string serverVersion = sqlConnection.ServerVersion;
int[] serverVersionComponents = serverVersion.Split('.').Select(Int32.Parse).ToArray<int>();
string sqlPoisonFlag = String.Empty;
if (serverVersionComponents[0] >= 10)
{
    if (serverVersionComponents[0] == 10 && serverVersionComponents[1] == 50
        || serverVersionComponents[0] > 10)
        {
            sqlPoisonFlag = @"POISON_MESSAGE_HANDLING (STATUS = OFF), ";
        }
}
string sqlCreateQueue = $"CREATE QUEUE[{databaseObjectsNaming}] WITH STATUS = ON, RETENTION = OFF, {sqlPoisonFlag}ACTIVATION(STATUS = ON, PROCEDURE_NAME = [{databaseObjectsNaming}_QueueActivation], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER)";
Otherwise, by removing it, in SQL Server 2008R2 and above, the default would be POISON_MESSAGE_HANDLING (STATUS = ON) per this article which is not what the original code wants.