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.

Possible duplicate of stackoverflow.com/questions/25581002/… — Dan Guzman
– Dan Guzman, Commented Nov 29, 2014 at 6:24

Nick.Mc · Accepted Answer · 2014-11-30 22:46:34Z

7

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

edited Nov 30, 2014 at 22:46

answered Nov 29, 2014 at 5:54

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hamed Zakery Miab Over a year ago

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 Over a year ago

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.Mc Over a year ago

"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.Mc Over a year ago

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

Collectives™ on Stack Overflow

Retrieve current SQL Server connection string within SQL query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related