Solved

NEWBIE: How to use WorkstationID in TSQL?

Posted on 2006-06-22
13
654 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

733 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