Object naming protocol when using triggers to write to another DB on same SQL server.

• mick L

  SSCommitted

  Points: 1526

  More actions

  October 5, 2010 at 3:46 pm

  #241226

  Folks,

  I am working on a production DB where I want to use simple auditing triggers to write to another (audit) database on the same instance. The triggers I have implemented are failing with 'no object of that name' type errors.

  if the table where the source database exists is called:

  db name: sourcedb

  table name: customers.orders

  and the destination.

  db name:auditdb

  table name customers.ordersaudit

  what should I use as the destination table name in the trigger?

  I have tried

  auditdb.customers.ordersaudit (where customers is the schema) and

  auditdb.ordersaudit

  Neither are working.

  I can query the audit table from the source database using the same security and using the fully qualified name i.e.

  select * from auditdb.customers.ordersaudit, but the trigger cannot find the table when I use

  Cheers

  Mick

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  October 5, 2010 at 3:50 pm

  #1232120

  It goes [database].[schema].

  So.. [auditdb].[orders].[customers.ordersaudit] ?

  You need to put brackets around names that have spaces, periods, etc in them.

• mick L

  SSCommitted

  Points: 1526

  More actions

  October 6, 2010 at 2:17 am

  #1232298

  Sorry Derrick,

  I had a mistake in my example (Late night).

  The schema is customers, not orders, I have no periods in the table name

  So, tablename is auditdb.customers.ordersaudit (where customers is the schema).

  Sor some reason, when I try to use the database.schema.tablename to get at the table from the trigger, it fails.

  auditdb.customers.ordersaudit (where customers is the schema)