How to access loopback linked server?

• Tobias Kiemes

  SSCommitted

  Points: 1604

  More actions

  August 21, 2015 at 1:55 am

  #303647

  Hi,

  for building an autonomous transaction, I created a loopback server as described here:

  So my aim is to call a logging procedure not directly but via the self referencing linked server.

  In the above sample, they do it like this:

  EXEC loopback.tempdb.dbo.usp_ErrorLogging @ERROR

  They are calling a procedure residing in tempdb. In my case the procedure resides in my own database, let's say customdb_dev, so the call would be

  EXEC loopback.customdb_dev.dbo.usp_ErrorLogging @ERROR

  This means, while deploying to uat(customdb_uat) I would have to change the call to:

  EXEC loopback.customdb_uat.dbo.usp_ErrorLogging @ERROR

  So my question is: is there a way to leave the database name out of this call? It should just use the database name from the current session...in a way that works for every envirionment regardless the database name.

  This call did not work:

  EXEC loopback..dbo.usp_ErrorLogging @ERROR

  Regards,

  Tobias

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 21, 2015 at 6:15 am

  #1821649

  Not with a loopback.

  using a linked server/loopback is going to require four part naming conventions. leaving out the database name would default to the default database of the user connecting, probably the master database. there's no way for the linked server to infer database context.

  without the linked server, then yes, you can put a stored procedure in master, make sure it starts with "sp_" , ie [sp_ErrorLogging], and mark it as a system object via EXECUTE sp_ms_marksystemobject 'sp_ErrorLogging'

  then , if you were in whatever database, you can call it, and it would infer the database reference of the current context.

  ie if i was in [production],, and referenced sys.tables, the procedure would get data from [production].sys.tables; jump to tempdb, and the same procedure now looks at [tempdb].sys.tables.

  without the sp_ms_marksystemobject , it would reference master.sys.tables, but could be called form any database context.

  if the procedure assumes a specific table, ie dbo.LoggingTable exists, as long as that table is in the current db schema, it will reference the current db context.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!