Can i detecet ip addresses curently connected on sql server 2000 (1 Viewer)
- Thread starter MBMSOFT
- Start date
Rx_
Nothing In Moderation
- Local time
- Today, 04:48
- Joined
- Oct 22, 2009
- Messages
- 2,803
The server can be connected with the IP or the Server name.
I don't think the IP is exposed via the network.
The 1433 is the port number. Sometimes, SQL Server has a different port than the default to add to the security.
I did a quick google, came up empty on a solution for you.
If you find a script or Windows DLL somewhere, let us know. There is such a thing as adding a vba Wrapper around a Windows DLL for example.
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;"
I don't think the IP is exposed via the network.
The 1433 is the port number. Sometimes, SQL Server has a different port than the default to add to the security.
I did a quick google, came up empty on a solution for you.
If you find a script or Windows DLL somewhere, let us know. There is such a thing as adding a vba Wrapper around a Windows DLL for example.
"Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;"
Just to explain exactly what i need...
So I have WIN server 2003 as back end with sql server 2000 on it
So I have several clients with access app connected to the server through VPN connection, and they need to know if some client is on line(connected to the server).
I tried to ping them client to client but no success even they have the same class of ip address...
So i was considering that server can help them to see what client is connected at the moment or some other way to find out what computers are connected...
So I have WIN server 2003 as back end with sql server 2000 on it
So I have several clients with access app connected to the server through VPN connection, and they need to know if some client is on line(connected to the server).
I tried to ping them client to client but no success even they have the same class of ip address...
So i was considering that server can help them to see what client is connected at the moment or some other way to find out what computers are connected...
Last edited:
Rx_
Nothing In Moderation
- Local time
- Today, 04:48
- Joined
- Oct 22, 2009
- Messages
- 2,803
Ok, that information helps.
I did something like that where the hard disk available space for SQL Server shows up on the main menu form. I wrote an SP on SQL Server that logs the value in a SQL Server Table every day. Then just call on the latest value.
It prevents the Server from running out of hard disk space.
No comment on why the administrators can't manage that kind of thing. A developer has to do what a developer has to do.
I have an account over at SQL Server Central. They have a Newbie Question section. Now, be aware, they are very helpful, but very advanced.
Here is a script they published a little while ago, so it should work on your version of SQL.
Granted, this doesn't do exactly what you want. It does evaluate all the Connected users and the method of Authentication.
So, it groups it into a count. You indicated wanting individual names.
So, this would have to be modified not to include the count.
Create a SQL table for it to publish to. Put it in a job that re-occurs every 5 minutes. It is a pretty lightweight query.
Then, link to the table from Access.
It could also be turned into a Stored Procedure that could be called from Access.
Please share your thoughts and code. If there is enough time available, I will try it on my end too (SQL 2008R2) and see if it can be improved on.
Tested the above, it works great. You might want to filter out the System Admin users.
I chose not to use Integrated Security (where the network people give access to the SQL Server account).
I created a User Name (with PW) and a Admin User (with PW).
My script creates the linked tables every time the database is opened.
So basically, 30 users show up in my script as the same name.
If you are using Integrated security, this should list each connected user and the method of connection.
So, you could drop a Table X
The Make a Table X with something like this select statement.
This could be done on a timer or you could create an SP to command the refresh from VBA.
I did something like that where the hard disk available space for SQL Server shows up on the main menu form. I wrote an SP on SQL Server that logs the value in a SQL Server Table every day. Then just call on the latest value.
It prevents the Server from running out of hard disk space.
No comment on why the administrators can't manage that kind of thing. A developer has to do what a developer has to do.
I have an account over at SQL Server Central. They have a Newbie Question section. Now, be aware, they are very helpful, but very advanced.
Here is a script they published a little while ago, so it should work on your version of SQL.
Code:
SELECT 'Authentication Method'=(
CASE
WHEN nt_user_name IS not null THEN 'Windows Authentication'
ELSE 'SQL Authentication'
END),
login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count'
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_nameSo, it groups it into a count. You indicated wanting individual names.
So, this would have to be modified not to include the count.
Create a SQL table for it to publish to. Put it in a job that re-occurs every 5 minutes. It is a pretty lightweight query.
Then, link to the table from Access.
It could also be turned into a Stored Procedure that could be called from Access.
Please share your thoughts and code. If there is enough time available, I will try it on my end too (SQL 2008R2) and see if it can be improved on.
Code:
SELECT 'Authentication Method'=(
CASE
WHEN nt_user_name IS not null THEN 'Windows Authentication'
ELSE 'SQL Authentication'
END),
login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name'
FROM sys.dm_exec_sessionsI chose not to use Integrated Security (where the network people give access to the SQL Server account).
I created a User Name (with PW) and a Admin User (with PW).
My script creates the linked tables every time the database is opened.
So basically, 30 users show up in my script as the same name.
If you are using Integrated security, this should list each connected user and the method of connection.
So, you could drop a Table X
The Make a Table X with something like this select statement.
This could be done on a timer or you could create an SP to command the refresh from VBA.
Last edited:
I found this article
http://social.msdn.microsoft.com/Fo...ient-pcs-name-or-ip-address?forum=transactsql
as I could understand
sys.dm_exec is not available in sql 2000
http://social.msdn.microsoft.com/Fo...ient-pcs-name-or-ip-address?forum=transactsql
as I could understand
sys.dm_exec is not available in sql 2000
accedeholdings
Registered User.
- Local time
- Today, 03:48
- Joined
- Sep 29, 2014
- Messages
- 15
I don't think it is possible even with the latest versions of SQL. You will need a separate program for that.
Similar threads
