[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

NEWBIE: How to use WorkstationID in TSQL?

Posted on 2006-06-22
13
Medium Priority
?
681 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 1000 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

590 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