Solved

Retrieve MAC address with SQL

Posted on 2003-11-14
6
427 Views
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.
0
Comment
Question by:risoy
6 Comments
 

Author Comment

by:risoy
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.

*** 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
 
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

Thanks,
Anthony
0
 
LVL 34

Expert Comment

by:arbert
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?
0
 

Author Comment

by:risoy
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...?
0
 
LVL 2

Accepted Solution

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

Lunchy
Friendly Neighbourhood Community Support Admin
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now