Solved

NEWBIE: How to use WorkstationID in TSQL?

Posted on 2006-06-22
13
616 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
Comment Utility
select host_name()
0
 
LVL 13

Expert Comment

by:MikeWalsh
Comment Utility
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
Comment Utility
for workstation id use host_id
0
 
LVL 13

Expert Comment

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

Author Comment

by:BrianMc1958
Comment Utility
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
Comment Utility
To Mike Walsh:  Sorry.  I just posted before seeing your response.  I'll try it now...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:MikeWalsh
Comment Utility
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
Comment Utility
nevermind my last I posted before I saw your response about seeing my response :)
0
 

Author Comment

by:BrianMc1958
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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 …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

12 Experts available now in Live!

Get 1:1 Help Now