How do I programatically determine which port a SQL Server is running on?
Posted
by Ralph Willgoss
on Geeks with Blogs
See other posts from Geeks with Blogs
or by Ralph Willgoss
Published on Sun, 25 Nov 2012 16:20:55 GMT
Indexed on
2012/11/25
17:05 UTC
Read the original article
Hit count: 278
Filed under:
How do I programatically determine which port a SQL Server is running on?
/*
===== Param ref for xp_readerrorlog ===
1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending
How many error logs do I have?
SMSStudio -> Management -> SQL Server Logs -> (right click) -> configure = see values
*/
USE Master
GO
-- get log count
DECLARE @logcount int
DROP TABLE #Result
CREATE TABLE #Result (ArchiveNo int, Date datetime, Size int)
INSERT INTO #Result
EXEC xp_enumerrorlogs
SET @logcount = (SELECT COUNT(*) FROM #Result)
-- search logs
DECLARE @counter int
SET @counter = 0
WHILE @counter <= @logcount
BEGIN
EXEC xp_readerrorlog @counter, 1, N'Server is listening on', 'any', NULL, NULL, N'asc'
SET @counter = @counter + 1
END
GO
/*
===== Param ref for xp_readerrorlog ===
1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending
How many error logs do I have?
SMSStudio -> Management -> SQL Server Logs -> (right click) -> configure = see values
*/
USE Master
GO
-- get log count
DECLARE @logcount int
DROP TABLE #Result
CREATE TABLE #Result (ArchiveNo int, Date datetime, Size int)
INSERT INTO #Result
EXEC xp_enumerrorlogs
SET @logcount = (SELECT COUNT(*) FROM #Result)
-- search logs
DECLARE @counter int
SET @counter = 0
WHILE @counter <= @logcount
BEGIN
EXEC xp_readerrorlog @counter, 1, N'Server is listening on', 'any', NULL, NULL, N'asc'
SET @counter = @counter + 1
END
GO
© Geeks with Blogs or respective owner
