September 14, 2009 at 8:59 am
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
September 14, 2009 at 2:38 pm
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
September 15, 2009 at 12:06 am
Thanks for your answer but could you give me some sample code.
Regards
Tom
September 15, 2009 at 12:53 am
Thanks for your answer but is it possible to give me some sample code.
Regards
Tom
September 15, 2009 at 3:39 am
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
September 15, 2009 at 8:09 am
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.
September 15, 2009 at 8:31 am
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
September 15, 2009 at 8:53 am
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
...
September 15, 2009 at 9:11 am
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
September 15, 2009 at 10:06 am
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