Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-12-14 : 12:21:16

Hi,

I have the following select query which works fine

SELECT     dbo.TblUser.Signature, dbo.TblloggedUser.username, dbo.TblUser.Contact_no, 
                      dbo.TblUser.first_name + ' ' + dbo.TblUser.surname AS Contactname, dbo.TblloggedUser.machine_name
FROM         dbo.TblUser INNER JOIN
                      dbo.TblloggedUser ON dbo.TblUser.user_name = dbo.TblloggedUser.username

Results

signature username telephone Contact name Machinename
<Binary data> AliG 0203 080 1111 Ali Gerry LAPT20661
<Binary data> AliK 0203 080 2222 Ali Kassim LAPT20662

What I waant to do is ONLY display details of yourself when logged on based on the machine name, in the query.

I am Ali Kassim, so I require the query to display;

signature username telephone Contact name Machinename
<Binary data> AliK 0203 080 2222 Ali Kassim LAPT20662

How can I have the query to do something like...

SELECT     dbo.TblUser.Signature, dbo.TblloggedUser.username, dbo.TblUser.Contact_no, 
                      dbo.TblUser.first_name + ' ' + dbo.TblUser.surname AS Contactname, dbo.TblloggedUser.machine_name
FROM         dbo.TblUser INNER JOIN
                      dbo.TblloggedUser ON dbo.TblUser.user_name = dbo.TblloggedUser.username
WHERE dbo.TblloggedUser.machine_name = "Local PC"

Many thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-14 : 12:56:06

[code]WHERE dbo.TblloggedUser.machine_name = host_name()[/code]

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-12-16 : 05:18:10

ok tried this;

SELECT     dbo.TblUser.Signature, dbo.TblloggedUser.username, dbo.TblUser.Contact_no, dbo.TblUser.first_name + ' ' + dbo.TblUser.surname AS Contactname, 
                      dbo.TblloggedUser.machine_name
FROM         dbo.TblUser INNER JOIN
                      dbo.TblloggedUser ON dbo.TblUser.user_name = dbo.TblloggedUser.username
WHERE     (dbo.TblloggedUser.machine_name = HOST_NAME())

all fields are empty.. Meaning machine_name <> HOST_NAME(), right!

SQL server 2005 has been installed on a different server, which I think have a different host name. However, my local PC which then connects to the server has HOST Name = LAPT20662. This is displayed clearly on the screen too.

So I tried to run;

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

Result:

Hostname LoggedInUser
GPRDDBPH001 gprdsql

Now, its clear the query retrieves GPRDDBPH001 as the HOST_NAME(), hence the empty fields.

However, this does not resolve my problem, I want to link it to my LOCAL PC hostname rather than the sql server hostname, is this anyhow possible..

Many Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-16 : 06:02:36

Host_name function returns the workstation name which must be your local pc.

http://msdn.microsoft.com/en-us/library/ms178598(v=SQL.90).aspx

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-12-16 : 06:11:24

then it is not working for me.

My Local PC Hostname = LAPT20662

and when I check the hostname in Sql server = GPRDDBPH001

I would like to read LAPT20662 and NOT GPRDDBPH001...

Any help please!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources