Retriving host IP Address via T-SQL

Question

Retriving host IP Address via T-SQL

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

If you need the IP of the server, here is another way (if you don't mind using undocumented stored procedures):

CREATE TABLE #ErrLog (errorlog varchar(8000), ContinuationRow int)

INSERT INTO #ErrLog
EXEC sp_readerrorlog

SELECT SUBSTRING(errorlog, 
 CHARINDEX('SQL server listening on ',errorlog)+24,
 CHARINDEX(': ',errorlog)-CHARINDEX('SQL server listening on ',errorlog)-24)
FROM #ErrLog WHERE errorlog LIKE '%SQL server listening on %:%'

DROP TABLE #ErrLog

Razvan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Kenneth and Razvan, do you mind me putting your solutions and their equivalents for use with the German versions along with credit to you , of course, on my site?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Kenneth Wilhelmsson

SSC-Dedicated

Points: 30043

December 14, 2004 at 3:07 am

#533716

No problems Frank. Be my guest

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Here we go http://www.insidesql.de/content/view/179/

Thanks, Kenneth!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 5

OK, Frank, go ahead.

It would be nice, however, if you'd have an english version of your site.

Razvan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Thanks, Razvan!

And honestly, it wouldn't really be nice. I think, there is a lot of work to do in the German SQL Server community. More than in other countries. And while there might be a need for a German speaking site, I question the need for yet another english site. There are already enough good ones around.

Not to speak of the fact, that and even such a small site like mine is causing much more work than I ever expected.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 7

found something that may be of interest

@@servername returns data from sysservers.srvname, populated at the time of the instance installation.

host_name() executes sp_who to return sysprocesses.hostname. sysprocesses stores process information, hostname is used for NT security and is presumably initialised by sqlole.dll when requesting authentifcation.

In order to bring stability to the force, it is necessary to drop and add your server (see books online: sp_dropserver, heed "Remarks" and "Permissions").

Max

Jorge Cordero Ten Centuries Points: 1144 More actions · Answer 8

Prefer and IPConfig solution, since you won't depend of a proper sql config. @@servername may not have your server name, for instance.

Wayne West SSC-Insane Points: 22586 More actions · Answer 9

I am VERY disappointed. There is obviously a dearth of hard-core Dos or *nix people here. 🙂

From a command prompt you can do:

c:\>ipconfig | find "IP Address"

You can get the subnet mask the same way. I would suggest using the /I switch on find, it makes the text being searched for case-insensitive. If you're not getting an address back, this could be the culprit. And, of course, if you're not running TCP/IP....

One problem you'll run in to is if you have multiple network cards in your box. Right now, I'm on my laptop VPNing to work, and from my laptop side I have two connections: my normal 10.x address to my router on my internal lan and my 162.x address for my VPN connection. You would probably want to parse the entire ipconfig return if you need to know all addresses when you have a multi-homed machine (if I'm remembering and using that term correctly).

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 10

Does anyone know what / how you would get the actual username used to logon to windows for the workstation firing the sql?

HostName and IP not sufficient for my purposes.

Maybe a cmd command I don't know of. Need to call it from T-SQL.

Thanx

Kumaran Govender Ten Centuries Points: 1089 More actions · Answer 11

xp_cmdshell 'SET USERNAME'

But the environment variable USERNAME needs to be set, try running the "SET" command from the command prompt and you will see other environment variables you can query. I would recommend piping this into a variable then padding the data to remove the "USERNAME=" prefix.

Let me know if u need more.

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 12

thx for the reply

When running in command prompt i get what I want : username=theoj

When running in SQL2000 Query analyzer i get "Environment variable USERNAME not defined" as you said

This is so close yet so far....

If I could get this to work it would REALLY mean a lot to me.

PS. Rookie mistake Should have said that I am connection to a server from my local machine, thus when run in cmd I get back what I want, but because the sql runs on the server, I get NULL as result.

thanx again

Kumaran Govender Ten Centuries Points: 1089 More actions · Answer 13

Hiya,

On the SQL server under the System Properties|Advanced tab you need to configure the follwoing system variable (under environment variables)

Variable = USERNAME

VALUE = SYSTEM

Then run the query.

Sergiy SSC Guru Points: 110206 More actions · Answer 14

Theo Jacobs (10/9/2007)
Does anyone know what / how you would get the actual username used to logon to windows for the workstation firing the sql?
HostName and IP not sufficient for my purposes.
Maybe a cmd command I don't know of. Need to call it from T-SQL.
Thanx

select suser_sname()

_____________
Code for TallyGenerator

Theo Jacobs Say Hey Kid Points: 694 More actions · Answer 15

Thx, this will only work when using NT Integrated security, if you are using sa blank password it will return sa