Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Retrieve MAC address with SQL

Posted on 2003-11-14
6
Medium Priority
?
433 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
[X]
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
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

670 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