Couple issues/questions - xml support and invalid object

Mar 29 at 10:11 PM
Using 5.1.0.0

Initially tried to create an onchange watcher on a table with an xml column (from what I read in the notes and discussions, this was supported), but received an exception message that said that xml types are not supported.

I tried to remove the xml column from the local model. And get an exception back upon start which says invalid object name - e.g. Invalid object name 'dbo.dbo_ActionQueue_697d0bcd-a814-44f5-a031-cf7137335b34'.

I tried adding the column back into the model and changed the type in the database to varchar(max) and I get the same message.

Class looks like this:
public class ActionQueue
    {
        public int ActionQueue_PK { get; set; }
        public int DeviceID { get; set; }
        public System.DateTime CreatedOn { get; set; }
        public Nullable<System.DateTime> ExecutedOn { get; set; }
        public string Action { get; set; }
        public string MutingServerID { get; set; }
        public Nullable<int> TerminalID { get; set; }
    }
Table looks like this:
CREATE TABLE [dbo].[ActionQueue](
    [ActionQueue_PK] [int] IDENTITY(1,1) NOT NULL,
    [DeviceID] [int] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [ExecutedOn] [datetime2](7) NULL,
    [Action] [xml] NULL, -- have tried this as varchar(max) as well
    [MutingServerID] [varchar](50) NULL,
    [TerminalID] [int] NULL
Code copied from sample looks like this:
            try
            {
             
                using (var dep = new SqlTableDependency<ActionQueue>(connection))

                   // want to use filter eventually, but just trying to get it running right now
                    // connection, "ActionQueue", mapper: mapper, filter: whereCondition))
                {
                    dep.OnChanged += TableDependency_Changed;
                    dep.Start();

                    sqlTableDependencyDbObjectsName = dep.DataBaseObjectsNamingConvention;

                    Console.WriteLine(@"Waiting for receiving notifications...");
                    Console.WriteLine(@"Press a key to stop");
                    Console.ReadKey();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return;
            }
Thanks for any help you can provide.

Regards,
Brian
Coordinator
Mar 30 at 6:06 AM
Hi Brian

it sound strange....I will check immediatelly.

Regards
Coordinator
Mar 30 at 7:31 AM
Hi

The problem is not in the model, but in database table.

To works, [Action] should be a NVARCHAR(MAX) instead of XML type.
Mar 30 at 10:08 AM
bcinnv,
Invalid object name 'dbo.dbo_ActionQueue_697d0bcd-a814-44f5-a031-cf7137335b34'
means you are trying to use the dropped DB objects. The problem may be caused by using teardown=true (the default value if not specified in SqlTableDependency constructor) and reusing the old reference to already dropped db objects by the lines
sqlTableDependencyDbObjectsName = dep.DataBaseObjectsNamingConvention;

// somewhere else
tableDependency = new SqlTableDependency<ActionQueue>(
        connection, 
        namingForObjectsAlreadyExisting: sqlTableDependencyDbObjectsName );
If it so, either set teardown=false initially or don't use namingForObjectsAlreadyExisting based on your needs.
Mar 30 at 2:30 PM
@delbianco and @redshift312 thank you. Both helped with what I am trying to do. teardown=false in the constructor.

I did find another issue - we use domain credentials (integrated security) and after fixing the above two issues, upon start, I received a message cannon find user "domain\username". I changed to a sql server local account and it started up and worked fine.

Thanks again,
Brian
Mar 30 at 2:58 PM
Can you able to login with those "domain\username" credentials on SSMS?
Mar 30 at 4:27 PM
It's part of the connection string. We use "Integrated Security=true", so no username or password is provided. Just uses windows credentials of the running process.

Also, I don't know if it was related, but I was able to remove the teardown=true flag. It may have something to do with the credential change.
Mar 30 at 5:34 PM
Brian,
imho, the message "cannot find user "domain\username" is not related to SqlTableDependency at all. Try to seach the web for the similar scenario and how to connect with win auth to remote sql server etc.

What did you mean by "I was able to remove the teardown=true flag"? Teardown option has nothing related to credentials.
Apr 3 at 3:31 PM
Redshift,

My domain login issue may have been permissions as well, our domain accounts don't have dbo, but our SQL Server auth accounts do for the particular databases.

The teardown comment was related to credentials, in that setting teardown=false with the domain credentials, let the startup get further along without getting the "Invalid object name" message like you suggested. But changing the to the SQL Server auth user with dbo on the database, did allow us to startup without requiring teardown=false. This will make adoption easier of SqlTableDependency with our DBAs, who weren't all that excited about running the cleanup procedures.

Thanks again,
Brian