Retrieve current SQL Server connection string within SQL query

Question

(this is an abstract question)

How can I get the current connection string from a T-SQL query?

I mean need for something like this:

Select ConnectionString From Something

and I get the result like this:

data source=HAMCKER-PC;initial catalog=CMMS;trusted_connection=true

Why I need this?

Actually I want to pass some extra parameters via my connection string, suppose the above connection to be something like this:

data source=HAMCKER-PC;initial catalog=CMMS;trusted_connection=true;Area=W9

I need that W9 in my queries.

Solution

To get data source use

SELECT @@SERVERNAME

initial catalog:

SELECT DB_NAME()

I could go look up trusted_connection as well but I don't see the point because you've already connected so you already know.

If you want to pass additional information using the connection string, your options are limited. You should probably use application name which can be accessed by SELECT APP_NAME() or Workstation ID. But these have meanings and I would be reluctant to 'hijack' them

You can also use CONTEXT_INFO to pass information msdn.microsoft.com/en-us/library/ms187768.aspx, msdn.microsoft.com/en-us/library/ms180125.aspx

Comments

[Dan Guzman]

Possible duplicate of stackoverflow.com/questions/25581002/…

[Hamed Zakery Miab]

thanks for guide, it was dup actually

[Hamed Zakery Miab]

thanks for the time. I've tested to send custom data to sql-server, it doesn't crash, but the problem is to retrieve that data!

[Dan Guzman]

The client API does not send the connection string to SQL Server. For some connection string keywords like application name and host name, it passes the value to SQL Server via TDS protocol messages. Other values are not available on the server.

[Nick.McDermaid]

"the problem is to retrieve that data" ? You mean your "Area" string? if it isn't a connection string attribute, it isn't passed. There might be other ways to do what you want if you explain further.

[Nick.McDermaid]

You should prbably look into CONTEXT_INFO for your needs. msdn.microsoft.com/en-us/library/ms187768.aspx, msdn.microsoft.com/en-us/library/ms180125.aspx