Generate MAc addresses with a stored procedure
-
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
-
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[/url]
Why I wrote a sql query analyzer clone -
Thanks for your answer but could you give me some sample code.
Regards
Tom
-
Thanks for your answer but is it possible to give me some sample code.
Regards
Tom
-
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[/url]
Why I wrote a sql query analyzer clone -
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.
-
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-51selectRandom_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') across apply (select top 100 row_number() over(order by id) As RW from syscolumns) bLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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
...
-
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 = 0x00000004A500114BSET @EndRange = 0x00000004A50F11FF--select convert(integer,+ @EndRange) - convert(integer,+ @StartRange) = 983220select 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) bwhere RW between 1 and (convert(integer, @EndRange) - convert(integer, @StartRange))Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply