SQL Clone
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3845 Visits: 1660
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3845 Visits: 1660
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1744 Visits: 759
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3845 Visits: 1660
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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1744 Visits: 759
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3845 Visits: 1660
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4183 Visits: 1456
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
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32066 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