SQL Server Performance Forum – Threads Archive

IP address and time zone?

does sql server provide a tool to find out IP address (not host_name()) and the time zone of the server where sql server is running?

---

From BOL GETUTCDATE Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. Syntax
GETUTCDATE() Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

---

To know the Ipaddress can be found from Enterprise Manager —> Management –> Current activity or SP_WHO & SP_WHO2 from query analyzer. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

in my query analyser window, sp_who and sp_who2 do not
return ipaddress, only hostnames. sp_who:
spid ecid status loginame hostname blk dbname cmd
sp_who2 returns some more attrbutes but no ipaddress.

---

But this still doesn’t tell if it is an East Standard Time,
or Central Daylight Saving Time.

---

to add to this, I think you’re out of luck to find out IP Adress with SQL Server.
However, you can use command line tools like those onhttp://www.sysinternals.com to get what you want. Frank
http://www.insidesql.de
http://www.familienzirkus.de

---

Some scripts I’ve gathered on the net at some time ——————————– –To get the IP of the SQL Server –Get sql server IP. Doesn’t work on cluster
declare @ip varchar(255)
create table #temptb (grabfield varchar(255))
insert into #temptb exec master.dbo.xp_cmdshell "ipconfig"
select @ip = ltrim(right(grabfield,len(grabfield)-charindex(‘:’,grabfield))) from #temptb where charindex(‘IP Address’, grabfield) > 0
print @ip
drop table #temptb ——————————– –To get Time Zone Friendly name: DECLARE @zoneNameVARCHAR(500) /* Get time zone friendly name on this SQL server */
EXEC master.dbo.xp_regread
‘HKEY_LOCAL_MACHINE’,
‘SYSTEMCurrentControlSetControlTimeZoneInformation’,
‘StandardName’,
@zoneName OUT PRINT @zoneName ——————————- –To find out UTC Time in SQL 7 DECLARE @deltaUTCINT
DECLARE @UTCdateDATETIME /* Get UTC time difference on this SQL server */
EXEC master.dbo.xp_regread
‘HKEY_LOCAL_MACHINE’,
‘SYSTEMCurrentControlSetControlTimeZoneInformation’,
‘ActiveTimeBias’,
@DeltaUTC OUT /* Calculate UTCdate */
SELECT @UTCdate = DATEADD(MINUTE, @deltaUTC, GETDATE())
PRINT @UTCDate ——————————– –To find out UTC Time in SQL 2000 PRINT GETUTCDATE() ——————————– /Argyle

---

Thanks a lot!

---

Still a problem for the time zone. This script
doesn’t know if the daylight setting is on.
For example, Central Daylight Time, or,
Central Standard Time?

---

Look in the registry under:
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlTimeZoneInformation There are more entries there that might help you. There should be a daylightflag entry but it’s not available on all systems last I checked. /Argyle

---

Create a batch file that calls either e.g. ping anotherservername or if it is the current server use ipconfig. e.g.
REM Start of Batch File
@echo off
ipconfig > ipaddresses.log
REM Find text and output somewhere
REM End of Batch File Use the find utility in MSDOS to locate the string and to overwrite the file with the required information. Then call this batch file from SQL using xp_cmdShell and read the desired information back into SQL using this function. Regards,
Rik

---

I belive that’s what one of the functions above do <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />/Argyle

---

]]>