How do I uniquely identify a database?

[Breck_Carter]

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?

[VolkerBarth]

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:)

[Breck_Carter]

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

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

[MarkCulp]

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.

[Breck_Carter]

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).

[VolkerBarth]

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') );

[MarkCulp]

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

[johnsmirnios]

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.

[VolkerBarth]

...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:)

[Former Member]

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

[VolkerBarth]

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...

[Breck_Carter]

> 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.