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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.



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.'
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

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?
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...?
PAQed, with points refunded (350)

Friendly Neighbourhood Community Support Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.