Find client`s computer name - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Find client`s computer name

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-06 : 02:25:03

Hi i have SQL Server-2005(named as 'SS') & client connected to it(computer named as'CC'), using only connectivity tool SSMS.
I just want to know the name of the client, to which SQL Server is connected, by sitting at client side place, in SQL Server SSMS.

like as i tried(at CC in SSMS), works fine
EXEC master..xp_cmdshell 'echo %ComputerName%'

Returns:
CC
NULL

But when i try to store the output of xp_cmdshell as

declare @CompName varchar(100)
EXEC @CompName = master..xp_cmdshell 'echo %ComputerName%',no_output
Print @CompName

Returns:
0 (ZERO) (i know it returns '0' or '1' depending upon the return value)
I want the output as
CC
(the name of the client to which 'SS' is connected)

How could i do this?

Kind Regards,
Thanks.
Gurpreet S. Gill

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-06 : 02:28:32

why not use

select HOST_NAME()

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-06 : 02:33:37

OMG !
so easy way to know the client name.
But still i am interested in doing the same thing using XP_CMDSHELL

Thanks,
Regards,
Gurpreet S. Gill

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 02:34:35

How about this query


SELECT Hostname 'Connected User', db_name(dbid) 'Database', Program_Name, Count(Program_Name) 'Connections'
FROM master.dbo.sysprocesses Where HostName <> '' And db_name(dbid) <> 'master' 
Group By Hostname, db_name(dbid), Program_Name

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 02:38:56

then somthing like this may help you ..


Create Table ##Temp 
(
	MachName varchar(1000)
)
Insert ##Temp
EXEC master..xp_cmdshell 'echo %ComputerName%'

Select * From ##Temp

Drop Table ##Temp

Chirag

http://chirikworld.blogspot.com/

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-06 : 04:00:27

Hi chiragkhabaria

about your first suggetion,

SELECT Hostname 'Connected User',
db_name(dbid) 'Database',
Program_Name,
Count(Program_Name) 'Connections'
FROM master.dbo.sysprocesses
Where
HostName <> ''
And
db_name(dbid) <> 'master'
Group By Hostname, db_name(dbid), Program_Name

output:

VSNET1 msdb SQLAgent - Generic Refresher 1
VSNET1 msdb SQLAgent - Job Manager 1
VSNET5 newnew Microsoft SQL Server Management Studio - Query 1

Which i really dont understnd.(more over its not of my use)

regarding second suggetion, i modify it & working very fine

declare @CompName varchar(100)
Create Table ##Temp
( MachName varchar(1000) )
Insert ##Temp EXEC master..xp_cmdshell 'echo %ComputerName%'
Select top 1 @CompName = MachName From ##Temp
print @CompName
Drop Table ##Temp

Thanks,
Regards.
Gurpreet S. Gill

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 04:13:45

the first query gives all the users connected to the database, and the channel through which they are connected, it may be due to some application or via ssms or etc..

may be you dont require it.. i just gave for some more information

Chirag

http://chirikworld.blogspot.com/

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-06 : 05:57:54

THANKS chiragkhabaria

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 07:25:49

If there are two or more ways to do a certain thing, why choose the least efficient way to do it?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-06 : 07:35:37

quote:
Originally posted by Peso

If there are two or more ways to do a certain thing, why choose the least efficient way to do it?

Peter Larsson
Helsingborg, Sweden

Just an guess ..

May be its a learning process..

Chirag

http://chirikworld.blogspot.com/

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-06 : 07:51:41

chiragkhabaria is right, i had used the least efficient way, but i just want to know the thing for the other way.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-06 : 09:32:43

quote:
Originally posted by chiragkhabaria

then somthing like this may help you ..

Create Table ##Temp
(
MachName varchar(1000)
)
Insert ##Temp
EXEC master..xp_cmdshell 'echo %ComputerName%'

Select * From ##Temp

Drop Table ##Temp

Chirag

http://chirikworld.blogspot.com/

This will give you the Server's computer name not client. Remember xp_cmdshell runs on server side not client side.

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-07 : 00:01:35

Mr. Chirag
you are right this returns the server name not the client machine name. I forget to throw the light.
Although my problem is solved, but could you please tell me how can i do this using the XP_CMDSHELL command?
Kind Regards,
Thanks.
Gurpreet S. Gill

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 00:08:58

I don't believe you can, since the scope of the command windows is on the server, not the client.
I also think we have pointed out that HOST_NAME() is the proper way to accomplish this task.

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-07 : 01:11:58

quote:
Originally posted by gsgill76

Mr. Chirag
you are right this returns the server name not the client machine name. I forget to throw the light.
Although my problem is solved, but could you please tell me how can i do this using the XP_CMDSHELL command?
Kind Regards,
Thanks.
Gurpreet S. Gill

Well, this was not pointed out by me , but by Tan and Secondly you wont be able to acheieve this as Peter said.

Chirag

http://chirikworld.blogspot.com/

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-11-07 : 01:12:15

Hello Mr. Peter Larsson
Yes my problem had been solved, this is, just i want to know how can i do the other way(xp_cmdshell). OK i understnd the the things, regarding the xp_cmdshell, this is a server based command prompt utility.
Regards,
Thanks.
Gurpreet S. Gill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources