July 3, 2009 at 4:15 pm
Comments posted to this topic are about the item View conenction and Locking Information
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 10, 2009 at 3:20 am
Great troubleshooting query.
As you point out, the use of Ping to retrieve the IP address is very slow. How about the following alternatives to speed it up:
1. Add parameters to the Ping command to reduce the number of requests to send from the default 4 down to 1; and reduce the Time to Live to 1 to stop the ping request traveling through routers and slow networks. The line to set the Ping command would then change to:
SET @cmd = 'ping -n 1 -i 1 ' + @HostName
2. As the query is only interested in the IP address and does not need to test if the client machine is available on the network, we could use nslookup to query DNS. This requires changing a few lines as the results need parsing slightly differently:
SET @cmd = 'nslookup ' + @HostName
INSERT INTO @Results
EXECUTE master..xp_cmdshell @cmd
SELECT @ClientIPAddress = LTrim(Replace(Results, 'Address:', ''))
FROM @Results
WHERE Results LIKE 'Address:%'
On a fairly slow server with only 7 connections, option 1 reduces the query time from 1 min 8 secs to 49 secs. And option 2 reduces the query time down to 2 seconds :-D.
July 10, 2009 at 9:22 am
great work done...i din't know that ping accepts parameters....thanks dude 🙂
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 10, 2009 at 9:25 am
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?
it sounds helpful, but I'm a little stuck.
Dan
July 10, 2009 at 9:42 am
There are some strange blank characters at the beginning of the lines in the original post. You can use Find and Replace in SSMS to fix them:
1. Paste the SQL into a new SSMS query window
2. Highlight the single character immediately before the first Declare word
3. Press Ctrl-H This will open the Find and Replace window and copy the selected character in the Find What field
4. Enter a single space into the Replace With field and click Replace All
This should replace about 528 occurances.
You should then be able to run the query to create the stored proc.
Andy
July 10, 2009 at 11:59 am
Dan Guzman (7/10/2009)
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?it sounds helpful, but I'm a little stuck.
Dan
it can be used with sql 2005
Regards,
[font="Verdana"]Sqlfrenzy[/font]
July 10, 2009 at 12:08 pm
Sqlfrenzy (7/10/2009)
Dan Guzman (7/10/2009)
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?it sounds helpful, but I'm a little stuck.
Dan
it can be used with sql 2005
That doesn't help with any of the other questions.
July 10, 2009 at 12:11 pm
Dan Guzman (7/10/2009)
Sqlfrenzy (7/10/2009)
Dan Guzman (7/10/2009)
The post doesn't say which version of SQL Server this is for, but with my 2005 I got close to 30 errors, mostly syntax errors near ' ', plus a must declare scalar variable @sqltext. What database am I supposed to run this in? Master? msdb? any user db?it sounds helpful, but I'm a little stuck.
Dan
it can be used with sql 2005
That doesn't help with any of the other questions.
those were already answered by Andy
Regards,
[font="Verdana"]Sqlfrenzy[/font]
February 16, 2012 at 2:10 pm
Awesome I added your script with SP and works fine.
Just one concern about to use xp_cmdshell in production servers is good?
May 19, 2016 at 7:01 am
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply