invalid object name 'SDAC_NS_52_QUEUE'

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 104
Joined: Sat 18 Aug 2012 08:33

invalid object name 'SDAC_NS_52_QUEUE'

Post by LHSoft » Sat 04 May 2013 10:18

I have a Form with 2 TMSTables, the Connection and 2 Changenotifications are on a DataModule.
On the Form.Create I add following lines for the 2 Tables:
//1st Table:
TbStmKun.SQL.Add('select ' + SQLFields + ' from dbo.MoStmKun');
TbStmKun.LockMode := lmOptimistic;
TbStmKun.Options.ReflectChangeNotify := True;
TbStmKun.ChangeNotification := DBM.TbStmKunCN;
//2nd Table:
TbStmKunNr.SQL.Add('select nr, lastKdNr from dbo.MoStmKunNr');
TbStmKunNr.LockMode := lmOptimistic;
TbStmKunNr.Options.ReflectChangeNotify := True;
TbStmKunNr.ChangeNotification := DBM.TbStmKunNrCN;

When opening the Form, everything is OK. All Notifications come through network too.
I log in as "sa"

But when I close Form (closing the Tables in Form.Close) I get 2 errormessages as described in another post a view years ago:
1.: invalid object name 'SDAC_NS_52_QUEUE'
2.: Cannot drop the service 'SDAC_NS_52', because it does not exist or you do not have permission.

So, according to the last post, I created an account with all database specific rights possible, but error remains.

If I attach only 1 table to the notification, everything is OK on Close too. The Error only occurs if having 2 tables (and 2 notifications).

I use SQLServer 2008 Express and your latest version.

Can you help me?

best regards


Re: invalid object name 'SDAC_NS_52_QUEUE'

Post by AndreyZ » Mon 13 May 2013 12:48


The point is that if the TMSChangeNotification.Service property is not assigned, MSChangeNotification automatically creates a service and associates it with a queue in order to receive change notifications from this queue. When you close a connection, these service and queue are dropped. If you use two TMSChangeNotification for the same connection and do not provide the Service property for both of them, SDAC creates two services with the same name. When these services are dropped, the same service is dropped twice.
To avoid the problem, you should specify service names in the Service property of your TMSChangeNotification. For more information, please read the description of the TMSChangeNotification.Service property in the SDAC documentation.

Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: invalid object name 'SDAC_NS_52_QUEUE'

Post by LHSoft » Tue 21 May 2013 22:02

Hi AndreyZ,

thanks for your answer, I've read it yesterday and tried it today. It works.
I have created the Services and the Queues with Servicebroker in Connection.AfterConnect, while generating Service-Name at runtime in the Format serviceID_NS_SPID, while the section _NS_ seams to be important. Without this it did not work.

Getting the SPID:

Code: Select all

MainQuery.SQL.Add('select @@SPID');
SPID := MainQuery.Fields[0].AsString;
ServiceBroker1.Service := 'xyz_NS_' + SPID;
ChangeNotification1.Service := ServiceBroker1.Service;
same for the second Notification.

But now I have a big other problem arround Notifications, and I start a seperate thread for it.

Thanks a lots.


Re: invalid object name 'SDAC_NS_52_QUEUE'

Post by AndreyZ » Wed 22 May 2013 11:17

I am glad that this problem was solved. I have answered on your another question at

Post Reply