3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hamed Zakery Miab
  • 738
  • 2
  • 12
  • 34

1 Answers1

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

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • 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! – Hamed Zakery Miab Nov 29 '14 at 06:27
  • 3
    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. – Dan Guzman Nov 29 '14 at 06:56
  • "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 Nov 29 '14 at 11:30
  • 1
    You should prbably look into CONTEXT_INFO for your needs. http://msdn.microsoft.com/en-us/library/ms187768.aspx, http://msdn.microsoft.com/en-us/library/ms180125.aspx – Nick.Mc Nov 29 '14 at 11:35