What's the easiest way to determine if two different connections are connected to the same or different databases, when there is no opportunity to create any kind of user-defined identifier inside those databases?

asked 16 Oct '12, 08:37

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

Are the databases running on the same database server? Are they running concurrently? If connected to the same database, do they know of each other? If so, can you add APPINFO values to the connections?

Or are you asking whether there is any combination of server-level and database-level properties that should identify a database (at least for the same moment of time)?


Yes, that's just a comment, no answer at all:)

(16 Oct '12, 08:45) Volker Barth
Replies hidden

This: "Or are you asking...".

The two connections are coming from the same application, and going to arbitrary target servers.

(16 Oct '12, 17:02) Breck Carter

The combination of property( 'name' ) and db_property( 'name' ) should be unique though I am sure there are ways you could, with a little effort, create an environment where you have two servers with the same name running databases with the same name. You could further qualify with property( 'tcpipaddresses' ) if you are using tcpip.

permanent link

answered 16 Oct '12, 09:33

John%20Smirnios's gravatar image

John Smirnios
8.8k377108
accept rate: 40%

...all under the silent assumption these two connections are made from the same client or the same (sub-)net, I guess.


In contrast, say, with embedded applications, two connections on two different (and not connected) machines could surely return the same properties (and even the same TCP/IP address). But I'm sure that's not what Breck has asked for:)

(16 Oct '12, 10:05) Volker Barth

To build on John's suggestion, comparing

 string( property('machineName'), '/', property('name'), '/', db_property('name') )

on the two connections should be able to tell you that the connections are connected to the same database (on the same server running on the same computer). A user would have to really go out of their way to set up two servers with the same name on the same computer.

permanent link

answered 16 Oct '12, 09:40

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266
accept rate: 40%

edited 18 Oct '12, 13:46

That sounds like a plan... I might throw in some of the other ip-related properties like John suggests. The connections are all coming from the same application, and an 80% solution is good enough (and this is probably a 99.9% solution).

(16 Oct '12, 17:00) Breck Carter
2

I guess the first property is called "MachineName".

For almost 99.99 %, I'd add the server StartTime and TcpipAddresses, such as:

select string( property('MachineName'), '/', property('Name'),
    '/', property('TcpipAddresses'), '/', property('StartTime'),
     '/', db_property('Name') );
(17 Oct '12, 03:26) Volker Barth
Replies hidden

Arrg, yes, you are correct... I mistyped the name of the property. Thanks for catching the mistake... I have corrected my answer

(18 Oct '12, 13:47) Mark Culp

Historically you will see the query: "select @@servername, @@version, db_name( )" in use for similar purposes. This is often sufficient alone.

Given that variants of dbisql (and some applications) might be connected to any one of ASE, SQL Anywhere, IQ, ... and possibly other ... Sybase products, the above is a light-weight approach that could be helpful as a first pass to guide 'backend product awareness' in an application. [Albeit using the T/SQL dialect that Breck just loves ;-)] After which you can drill into SA specific sa_*_properties( ), properties( ), db_properties( ), conn_properties( ) many many differently (and possibly ) useful ways to suit and refine.

Not to take anything away from John's or Mark's or Volker's comments, of course.

Cheers

permanent link

answered 18 Oct '12, 11:23

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.2k33105
accept rate: 32%

edited 18 Oct '12, 15:45

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654

With v17, you may use the brandnew database properties "IdentitySignature" or "IdentitySignatureUUID", as Mark has explained here - and possibly that's why you have asked that question yourself?

AFAIK, that won't guarantee uniqueness when a copy of the same database is run on the same or different servers, so I guess it's just useful in conjunction with server-level properties and the database name...

permanent link

answered 25 Nov '15, 09:29

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654
accept rate: 32%

edited 25 Nov '15, 09:33

> possibly that's why you have asked that question yourself

No, just some "due diligence" to determine if any of the new V17 properties are of interest to Foxhound users.

(25 Nov '15, 13:28) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×32

question asked: 16 Oct '12, 08:37

question was seen: 1,014 times

last updated: 25 Nov '15, 13:28