Retrieve MAC address with SQL

Posted on 2003-11-14
Last Modified: 2012-05-04
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.
Question by:risoy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

ID: 9748935
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.



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


-- 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
      PRINT 'Database is up to date. Do nothing.'
      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.'
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9749823
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

LVL 34

Expert Comment

ID: 9750104
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?

Author Comment

ID: 9832301
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...?

Accepted Solution

Lunchy earned 0 total points
ID: 10197102
PAQed, with points refunded (350)

Friendly Neighbourhood Community Support Admin

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question