February 6, 2002 at 4:56 am
Hi,
Is there a way in T-SQL (sp, functions etc.) to find out the macaddress of network card on which runs SQLServer?
Thank you in advance
February 6, 2002 at 6:37 am
Interesting question. Not directly that I know of. You could use xp_cmdshell to run ipconfig /all and then parse the output.
Andy
February 6, 2002 at 7:16 am
If you have Sql Agent running, several of its processes have connections to Sqlserver running under the account that you specified at startup. You could look in sysprocesses table for the sql agent process and find the mac address.
select net_address from sysprocesses
where hostname = '[Your computer name]'
and program_name like 'SQLAgent%'
Tom Goltl
February 6, 2002 at 11:10 am
February 6, 2002 at 11:28 am
it's a great solution, but on my server sqlagent doesn't run! I need macaddress for software protection purpose.
quote:
Great solution!Andy
February 6, 2002 at 11:33 am
Neither does mine, but that's not a big deal. Just drop from the AND on... Try this:
SELECT DISTINCT net_address
FROM sysprocesses
WHERE hostname = '[Your computer name]'
Tom, wonderful solution!
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 6, 2002 at 11:42 am
Sorry for my English!
I want know from each client the macaddress of network card on which sqlserver runs!
Thanks
quote:
Neither does mine, but that's not a big deal. Just drop from the AND on... Try this:
SELECT DISTINCT net_address
FROM sysprocesses
WHERE hostname = '[Your computer name]'Tom, wonderful solution!
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
February 6, 2002 at 11:47 am
If you need to know the MAC address of the SQL Server box, connect to it using Query Analyzer. Then run the query, putting in the name of the SQL Server to find the MAC address. For instance:
SELECT DISTINCT net_address
FROM sysprocesses
WHERE hostname = 'MySQLServer'
If you are looking for the MAC addresses of all the clients connected to SQL Server at a given time, do the following:
SELECT DISTINCT hostname, net_address
FROM sysprocesses
WHERE RTRIM(LTRIM(hostname)) <> ''
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/06/2002 11:55:51 AM
K. Brian Kelley
@kbriankelley
February 7, 2002 at 1:18 am
Ok. it's what I want.
If you run the query on the server from which you want the macaddress it doesn't return anything, in you run the query from a client it works.
thanks at everybody!
Sorry for my english!
quote:
If you need to know the MAC address of the SQL Server box, connect to it using Query Analyzer. Then run the query, putting in the name of the SQL Server to find the MAC address. For instance:
SELECT DISTINCT net_address
FROM sysprocesses
WHERE hostname = 'MySQLServer'If you are looking for the MAC addresses of all the clients connected to SQL Server at a given time, do the following:
SELECT DISTINCT hostname, net_address
FROM sysprocesses
WHERE RTRIM(LTRIM(hostname)) <> ''K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/06/2002 11:55:51 AM
February 7, 2002 at 1:32 am
Sorry but it doesn't work!
quote:
Ok. it's what I want.If you run the query on the server from which you want the macaddress it doesn't return anything, in you run the query from a client it works.
thanks at everybody!
Sorry for my english!
quote:
If you need to know the MAC address of the SQL Server box, connect to it using Query Analyzer. Then run the query, putting in the name of the SQL Server to find the MAC address. For instance:
SELECT DISTINCT net_address
FROM sysprocesses
WHERE hostname = 'MySQLServer'If you are looking for the MAC addresses of all the clients connected to SQL Server at a given time, do the following:
SELECT DISTINCT hostname, net_address
FROM sysprocesses
WHERE RTRIM(LTRIM(hostname)) <> ''K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/06/2002 11:55:51 AM
February 7, 2002 at 10:19 am
This doesn't make sense? What does not work?
Assume server MAC is "AA" and you have clients with "BB" and "CC" as their MACs.
In sysprocesses, you will see:
SPID NET_ADDRESS
1
10 BB
11 CC
assuming that SPID 1 is a server process and
the clients are 10 and 11.
what are you looking to get back? The same data is being queried from both the server and client.
Steve Jones
February 8, 2002 at 1:58 am
Ok steve,
i want know from client BB and from client CC the macaddress of Server (AA)
Thanks
quote:
This doesn't make sense? What does not work?Assume server MAC is "AA" and you have clients with "BB" and "CC" as their MACs.
In sysprocesses, you will see:
SPID NET_ADDRESS
1
10 BB
11 CC
assuming that SPID 1 is a server process and
the clients are 10 and 11.
what are you looking to get back? The same data is being queried from both the server and client.
Steve Jones
February 8, 2002 at 8:19 am
Do the clients have Query Analyzer?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 9:13 am
No they have not.
I need this information programmatically!
quote:
Do the clients have Query Analyzer?K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
February 8, 2002 at 9:32 am
Ah, I see an issue now. Since you don't have SQL Agent running and you're trying to get it from the server, there aren't any processes running under the hostname.
I'll see if I can come up with a solution, but I don't think there is any documented way within SQL Server without resorting to either starting up the Agent or using the command shell.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply