Solved

NEWBIE: How to use WorkstationID in TSQL?

Posted on 2006-06-22
13
637 Views
Last Modified: 2008-01-09
Dear Experts,

Lowfatspread gave me a solution for identifying particular db users via their workstation name.  We are ready to implement it, but I'm afraid I don't know the actual name of the workstation field, as a "system" variable.  I have a working example of the USER system value:

select       @UserName = system_user ,...

If I wanted to pick up the workstation, what would I use? I assume it's something like "system_workstation"...

Thanks!
--BrianMc1958
0
Comment
Question by:BrianMc1958
  • 7
  • 6
13 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 250 total points
ID: 16960952
select host_name()
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16960965
that gives you the host that is executing the query. If this is run on the server through the console or RDP it will give you the server. if you open up a connection to a server and run SELECT host_name() you will get the name of the machine from where you are running it.

This should be what you want. You can look host_name up in books online to see more about it.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16960978
for workstation id use host_id
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16960982
to see more system functions, go to Books Online (BOL) and in the index search for "system functions"
0
 

Author Comment

by:BrianMc1958
ID: 16960986
I should have also said, the workstation system value will be provided by a connection string in this format:

connectionstring = "DSN=<dsn_name>;extended PROPERTIES=""workstationid=this_workstation""; "
0
 

Author Comment

by:BrianMc1958
ID: 16960994
To Mike Walsh:  Sorry.  I just posted before seeing your response.  I'll try it now...
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16960998
I am a little confused here.. are you trying to ask sql for the workstationid/workstation name or are you trying to pass one of those to sql?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16961002
nevermind my last I posted before I saw your response about seeing my response :)
0
 

Author Comment

by:BrianMc1958
ID: 16961114
OK.  Enough non-backward-compatible posting!

I'm afraid host_id didn't work.  I'm getting:

  Invalid column name 'host_id'.

I'm trying to use a klduge here.  In the connection string, I'm saying:

PROPERTIES=""workstationid=this_workstation".

Then, using that connection, I make some change to the database, which causes a TRIGGER to fire.

Then, within a TRIGGER, I'm trying to pull the workstation.

0
 

Author Comment

by:BrianMc1958
ID: 16961126
So I guess I'm both passing the workstation to SQL (at connection), and then trying to receive it within the trigger.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16961182
SELECT host_id()

SELECT host_name()

those should work. just try them in SQL and look at the results.. The name is probably more useful to your trigger?
0
 

Author Comment

by:BrianMc1958
ID: 16961274
Sorry.  I think I didn't give enough explanation from the start.  (This all started from an EE exchange last week).

We have a browser-based app, which the user signs on to as "Bob".  This is not the UserID with which they signed on to their PC, nor is it the UserID used to connect to the db itself.  "Bob" is just how they are known within our app.

When the trigger is triggered, we want to use this kludge to get the string "Bob" inserted into an audit table.  We are trying to fake out SQL a little bit, by moving "Bob" where it might expect "WorkStation_3".  So we don't want the ACTUAL workstation.  We want the "workstation" that was passed in in the connection string:

connectionstring = "DSN=<dsn_name>;extended PROPERTIES=""workstationid=Bob""; "

I think what you're giving me is the how to get the ACTUAL workstation.  Based on the above connection string, how do I get "Bob"?

Sorry for causing so much confusion...  Always better to ask the question right the first time...

0
 

Author Comment

by:BrianMc1958
ID: 16962686
Yikes.  Very sorry.  Your very first answer was all we needed.  For anyone reading this, we had received only a general description of how to write the connection string.

Instead of:

 connectionstring = "DSN=<dsn_name>;extended PROPERTIES=""workstationid=this_workstation""; "

it should have been:

connectionstring = "DSN=<dsn_name>;extended PROPERTIES=""WorkStation ID=this_workstation""; "

With the property SPELLED CORRECTLY, then Mike Walsh's original answer works:

select host_name()


Yours in Continuing Newbiehood,
BrianMc1958
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now