Solved

NEWBIE: How to use WorkstationID in TSQL?

Posted on 2006-06-22
13
628 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

16 Experts available now in Live!

Get 1:1 Help Now