Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generate MAc addresses with a stored procedure Expand / Collapse
Author
Message
Posted Monday, September 14, 2009 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 12:23 AM
Points: 7, Visits: 7
Hi all.

I would like to generate MAC addresses between a certain range in a stored procedure. The MAC addresses needed to be stored in a table.

Any ideas? Suggestions?

Thanks in advance

Tom Denys
Post #787528
Posted Monday, September 14, 2009 2:38 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
Write a scalar UDF that returns you a MAC address based on an int, then use a commonly documented tally table method to generate the MAC addresses based on your selected range... I don't know the format of MAC addresses particularly, so sorry to not be more helpful...

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #787755
Posted Tuesday, September 15, 2009 12:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 12:23 AM
Points: 7, Visits: 7
Thanks for your answer but could you give me some sample code.

Regards
Tom
Post #787914
Posted Tuesday, September 15, 2009 12:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 12:23 AM
Points: 7, Visits: 7
Thanks for your answer but is it possible to give me some sample code.

Regards
Tom
Post #787936
Posted Tuesday, September 15, 2009 3:39 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
Heh

http://lmgtfy.com/?q=sample+tally+table+t-sql

and

http://lmgtfy.com/?q=create+scalar+function+t-sql

Sorry - i'm more of a 'give a man a fishing rod' kind of guy


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #788032
Posted Tuesday, September 15, 2009 8:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,519, Visits: 4,071
Beyond which, I personally don't know the exact schema / rules for MAC addresses. Maybe I'm the only one. I'm sure I could google it and find it after a bit of research... but it'd be easier if I didn't have to.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #788176
Posted Tuesday, September 15, 2009 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 12,896, Visits: 32,095
ok here's one way:
i'm assuming you want it in the same format i see when i do ipconfig/all

pairs of [0-9,A-E] with dashes:

cross joining the widget below against a row-number derived table gives me 100 examples as results...easily adjustable to your needs.
00-1A-A0-CA-1E-51

example results:

92-1B-2B-9E-BC-42
BD-28-61-DE-59-A2
E5-65-A8-05-8D-82
46-7B-5A-96-A0-EB

from ipconfig/all:
--Physical Address. . . . . . . . . : 00-1A-A0-CA-1E-51
select
Random_String =
substring(x,(abs(checksum(newid()))%15)+1,1)+
substring(x,(abs(checksum(newid()))%15)+1,1)+
'-' +
substring(x,(abs(checksum(newid()))%15)+1,1)+
substring(x,(abs(checksum(newid()))%15)+1,1)+
'-' +
/* and so on for as many characters as needed */
substring(x,(abs(checksum(newid()))%15)+1,1)+
substring(x,(abs(checksum(newid()))%15)+1,1)+
'-' +
substring(x,(abs(checksum(newid()))%15)+1,1)+
substring(x,(abs(checksum(newid()))%15)+1,1)+
'-' +
substring(x,(abs(checksum(newid()))%15)+1,1)+
substring(x,(abs(checksum(newid()))%15)+1,1)+
'-' +
substring(x,(abs(checksum(newid()))%15)+1,1)+
substring(x,(abs(checksum(newid()))%15)+1,1)

from
(select x='0123456789ABCDE') a
cross apply (select top 100 row_number() over(order by id) As RW from syscolumns) b




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #788199
Posted Tuesday, September 15, 2009 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 12:23 AM
Points: 7, Visits: 7
Thank you but i need something like below


DECLARE @Max_MAC BINARY(8)
DECLARE @StartRange BINARY(8)
DECLARE @EndRange BINARY(8)
DECLARE @MAC_Address BINARY(8)

SET @StartRange = 0x00000004A500114B
SET @EndRange = 0x00000004A50F11FF

SELECT @Max_MAC = MAX(MAC_Address)
FROM MAC_Address

IF (@Max_Mac >= @StartRange and @Max_Mac < @EndRange)
begin

@Max_Mac = generate a higher macaddress
INSERT INTO MAC_Address(MAC_Address,Timestamp) VALUES (@MAX_MAC, getdate())
END


I do not know how I can count a number more to the MAcAdress I would like to become to a table with macadresses like

0x00000004A500114B
0x00000004A500114C
0x00000004A500114D
...

Post #788232
Posted Tuesday, September 15, 2009 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 12,896, Visits: 32,095
so you need to generate just under a million rows;
here's how i did it:
DECLARE @StartRange BINARY(8) 
DECLARE @EndRange BINARY(8)
SET @StartRange = 0x00000004A500114B
SET @EndRange = 0x00000004A50F11FF

--select convert(integer,+ @EndRange) - convert(integer,+ @StartRange) = 983220

select convert(BINARY(8),RW + convert(integer, @StartRange))
from
(select row_number() over(order by a.id) As RW from syscolumns,syscolumns a,syscolumns b ,syscolumns c) b
where RW between 1 and (convert(integer, @EndRange) - convert(integer, @StartRange))




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #788252
Posted Tuesday, September 15, 2009 10:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:57 PM
Points: 7,064, Visits: 15,270
Use the database as it should be. Generate ALL of the MAC addresses in one shot, and assign them as needed.

From what you have listed there - looks like you have 3 variable octets. This is simply an incrementing number represented in Hexadecimal format, so you should use the code provided to you for a Tally or Numbers table. Looks like your range is just shy of 1M numbers.

In addition - there are several Decimal to Hex utilities in the scripts area to implement. Having a decimal and hex notation column in your tally table, so that you can then simply append to the "fixed" octets.



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #788323
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse