Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


View conenction and Locking Information


View conenction and Locking Information

Author
Message
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1652
Comments posted to this topic are about the item View conenction and Locking Information

Regards,
Sqlfrenzy
AndyJost
AndyJost
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 126
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.



Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1652
great work done...i din't know that ping accepts parameters....thanks dude :-)

Regards,
Sqlfrenzy
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 736
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
AndyJost
AndyJost
SSC Eights!
SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)SSC Eights! (974 reputation)

Group: General Forum Members
Points: 974 Visits: 126
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



Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1652
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,
Sqlfrenzy
Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 736
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.
Ahmad Osama
Ahmad Osama
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1509 Visits: 1652
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,
Sqlfrenzy
logicinside22
logicinside22
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1391
Awesome I added your script with SP and works fine.
Just one concern about to use xp_cmdshell in production servers is good?

Aim to inspire rather than to teach.
SQL Server DBA
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8706 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search