Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

Retrieve MAC address with SQL

A very simple question, but supposedly a difficult answer.

 - How do you retrieve a MAC address using SQL?

We need it for a redundancy system involving several SQL Servers and need to know which computer last updated the main DB. The Computer Name isn't good enough since a couple of machines actually have the same name (they are not operated simultaneously, one is cold standby.)

One complicating factor is the presence of two NICs. They are named LAN and PLC, respectively.

A quick solution would be greatly appreciated.
0
risoy
Asked:
risoy
1 Solution
 
risoyAuthor Commented:
Okay. I've written a litte procedure that hopefully will answer my question. There is a slight bug in it, marked <<<< SOMETHING HERE >>>, that I need help with. I want the one field returned by the above dynamic query to be set to @mac_last.

It is currently missing transaction and error handling, but I will add that later. Does anyone see any problems with the way I have solved my problem? Do you have any arguments against using net_address? I have read somewhere that it isn't to be trusted... But my (limited) experience is positive.

*** PROCEDURE ***

USE OvnDB
GO

DECLARE @hostname varchar(20)
DECLARE @external_datasource varchar(20)
DECLARE @mac_this varchar(20)
DECLARE @mac_last varchar(20)

-- Get the name of the main SQL Server
SELECT @external_datasource = Val FROM tblSysvars WHERE VarID = 'SQL_MAIN'

-- Get the host name of this SQL Server (the client)
SELECT @hostname = Val FROM tblSysvars WHERE VarID = 'SQL_LOCAL'

USE master

-- Get the MAC address of this computer
SELECT TOP 1 @mac_this = net_address FROM sysprocesses
WHERE hostname = @hostname
ORDER BY spid DESC

USE OvnDB

-- Get the MAC address of the computer that last updated the database
EXEC('SELECT Val FROM [' + @external_datasource + '].OvnDB.dbo.tblSysvars WHERE VarID = ''LAST_MAC''')
SET @mac_last = '' --<<<< SOMETHING HERE >>>>

IF @mac_last = @mac_this
BEGIN
      PRINT 'Database is up to date. Do nothing.'
END
ELSE
BEGIN
      PRINT 'Database was last updated by ' + @mac_last + '. Database update starting...'
      -- Store MAC address
      EXEC update_all
      EXEC('UPDATE [' + @external_datasource + '].OvnDB.dbo.tblSysvars SET Val = ''' + @mac_this + ''' WHERE VarID = ''LAST_MAC''')
      PRINT 'Database update completed.'
END
0
 
Anthony PerkinsCommented:
Please maintain your old open questions:

1 09/03/2003 500 Migrating from (Digital) Rdb to Microsof...  Open Databases
2 08/29/2003 75 NIC problems after kernel upgrade  Open Linux
3 09/17/2003 250 Want email -> database functionality  Open Microsoft Outlook
4 10/01/2003 250 SCSI recovery + storage strategy + sys u...  Open Storage

Thanks,
Anthony
0
 
arbertCommented:
The only gotcha I see is that you're issuing a query directly against the sysprocesses table--that will be the first thing that bites you in the butt.    Microsoft likes to change the internal structures of tables without telling anyone--don't use anything to access this information beside built-in and documented stored procs.

Secondly, even with this solution, sometimes the net_address is blank.  

I think maybe I would re-think what you're trying to do.  Why do you need to track the MAC?
0
 
risoyAuthor Commented:
I have a redundant pair of computers. Only one can function at a time. The _only_ difference between them is their MAC-address. There are actually two NICs in them, two of the NICs have the same MAC, the others have different ones.

Both computers are writing to the same table on a third machine, and I need to know which machine wrote last...?
0
 
LunchyCommented:
PAQed, with points refunded (350)

Lunchy
Friendly Neighbourhood Community Support Admin
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now