Property DefaultSchema is not available for Database [DatabaseName] in SSMS

I've been researching the notification message [Property DefaultSchema is not available for Database [DatabaseName]]. This message is generated when I try to create a new table object using SSMS. I am logging into the SQL Server using an NT Authenticated Login which is mapped to an Active Directory Resource Group. I have found the following information specific to Default Schema's in SQL Server 2005.

First, by design, you cannot assign a Default Schema to an NT Authenticate Login that is mapped to a Windows Group. This is noted in the CREATE USER (Transact SQL) BOL topic - http://msdn2.microsoft.com/en-us/library/ms173463.aspx

"DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key."

QUESTION: Are there any plans to remove, or modify the query used to derive the Default Schema which is generating the message notification from SSMS?

SELECT (select default_schema_name from sys.database_principals where name = user_name()) AS [DefaultSchema]

This is quite frustrating for me, as I have to reply to my developers - by design you will receive this message when you try to create a table through the table editor using SSMS. I have also been informed that this same error is raised through VSTS for Database Developers.

It is a hassle, but you don't just have to tell people that they will get that error and cannot do anything about it. You just need to tell them that they must now specify the schema whenever they try to create a table.|||You'll encounter this error when you try to use the GUI to create a table, are not sysadmin, and your access to the SQL Server 2005 database is from a Windows group.

Propagation of event notification when tables are updated.

I would like to propagate an event signal to an external application
when a table in my MSSql2000 server is updated.

Prog A; I have an external application adding records to a table.
Prog B; I have another external application using this table as well.

When Prog A creates a new record in the Table, how can I have Prog B be
notified of the event without polling the table or creating a link
between Prog A and Prog B.

Thanks.> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event

Depends what you mean by "notified". If you want to invoke some code within
the current process (B) then the obvious way is to have B read a table or
watch for some other event and then act accordingly. What exactly do you
want to achieve? Is this simply about optimistic locking?

--
David Portas
SQL Server MVP
--|||dubian (collatz@.bigtexansoftware.com) writes:
> I would like to propagate an event signal to an external application
> when a table in my MSSql2000 server is updated.
> Prog A; I have an external application adding records to a table.
> Prog B; I have another external application using this table as well.
> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event without polling the table or creating a link
> between Prog A and Prog B.

By far, the easiest way is to poll.

The other way would be to have a trigger on the table, that fires of an
extended stored procedure or OLE object to somehow send a singal to
Process B. Since extended stored procedures and local OLE objects in
the same memory space as the rest of SQL Server, they are somewhat
dangerous: if they crash on an access violation, the entire server
goes belly-up.

I believe that you also can use the sp_OAxxx routiens to start an
OLE object on a remote server. In this there is less risk for crashes.
But this is like to take time, and when you are in a trigger you are
in a transaction hold locks. If the update frequency is high, this
trigger can kill your throughput.

A variation is to have the trigger to write to a table, and then
run a job from SQL Server Agent that reads the table and alerts the
other process. Such a job would run once a minute or so. Since this job
could be an ActiveX task you could signal with XP:s and that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp