TSQL : Milliseconds

Alternative to RAND(), use milliseconds instead -

select (DATEPART(millisecond, GETDATE())) 

Purposely generating a record lock

Why? Is most probably what you're thinking.
I just use this technique for testing LOCKING check sql/reports.
Run this sql in 2 or more sessions -

-- open transaction
BEGIN TRANSACTION

-- run any select statement that brings back some data.
SELECT * FROM [DBO].[TABLENAME]

-- leave transaction open! Not commiting will cause a lock.
--COMMIT TRANSACTION

TSQL - Database Compatibility Mode

TSQL to set database compatibility mode.
The statement cannot be run when other users are connected, hence the 1st & 3rd lines -

ALTER DATABASE [databasename] SET SINGLE_USER;
EXEC sp_dbcmptlevel [databasename], 90;
ALTER DATABASE [databasename] SET MULTI_USER;

This statement cannot be run from dynamic sql though -

Msg 15432, Level 16, State 1, Procedure sp_dbcmptlevel, Line 28
Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

Batch File Deliberate Delay

Wait 10 seconds in a batch file >

PING localhost -n 10 -w 1000 >NUL

Bizarre way to do it, but this delay needs no user interaction eg; PAUSE and has the advantage of running on all OS versions without downloading any third party utilities.

Configure Autoplay

1) Start > Run > type 'GPEDIT.MSC' [enter]

2) Computer Configuration > Administrative Templates > System.

3) Double click 'Turn off Autoplay'.

4) Adjust settings to diable for CDRom drives or all drives.

Networking Basics - Ipconfig

Use Ipconfig to display IP information or perform limited DNS/DHCP functionality associated with the network adaptor.

IPCONFIG - display basic IP configuration
IPCONFIG /? - display options

Most useful

IPCONFIG /ALL - display detailed IP configuration
IPCONFIG /RENEW - get fresh ip from DHCP.
IPCONFIG /FLUSHDNS - if you suspect DNS caching.

Examples -

C:\>ipconfig

Windows IP Configuration

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . : it.domain.co.uk
IP Address. . . . . . . . . . . . : 10.4.1.7
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . : 10.4.0.1


C:\>ipconfig /all

Windows IP Configuration

Host Name . . . . . . . . . . . . : desktop007
Primary Dns Suffix . . . . . . . : it.domain.co.uk
Node Type . . . . . . . . . . . . : Hybrid
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
DNS Suffix Search List. . . . . . : it.domain.co.uk
it.domain.co.uk
domain.co.uk
co.uk

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . : it.domain.co.uk
Description . . . . . . . . . . . : Intel(R) PRO/100 VM Network Connection
Physical Address. . . . . . . . . : 00-0B-CD-A2-E1-0A
Dhcp Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
IP Address. . . . . . . . . . . . : 10.4.1.7
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . : 10.4.0.1
DHCP Server . . . . . . . . . . . : 10.4.255.2
DNS Servers . . . . . . . . . . . : 172.16.254.9
172.16.254.8
Primary WINS Server . . . . . . . : 172.16.254.9
Secondary WINS Server . . . . . . : 172.16.254.8
Lease Obtained. . . . . . . . . . : 16 March 2006 17:38:36
Lease Expires . . . . . . . . . . : 24 March 2006 17:38:36

Networking Basics - Telnet

Nowadays, Telnet is an utility for manually debugging connectivity issues.
Historically it was a network protocol developed in 1969.
You can use it to manually connect and talk to network services to establish they are functioning e.g SMTP, FTP, SQL Server

Use : TELNET [hostname/ip] [port]

Examples :

TELNET ftpserver 21

TELNET smtpserver 25

TELNET sqlserver 1433

Networking Basics - PathPing

PathPing is combination of PING and TRACERT which provides Packet Loss and Latency Reporting.
Like those utilities, it still wont work in networks where such requests are blocked.

PATHPING [hostname/ip]

PATHPING /? - to see options

Running against a website >

C:\>pathping yahoo.com

Tracing route to yahoo.com [68.180.206.184]
over a maximum of 30 hops:
0 desktop007.it.domain.co.uk [10.4.1.7]
1 10.4.0.3
2 10.0.1.49
3 10.192.0.1
4 172.16.253.252
5 193.19.88.10
6 dex-233-21.static.dxi.net [212.95.233.21]
7 gi5-2.cr1.th.hotchilli.net [217.72.167.165]
8 213.152.234.99.above.net [213.152.234.99]
9 so-0-1-0.mpr1.dca2.us.above.net [64.125.27.57]
10 so-0-1-0.mpr1.lga5.us.above.net [64.125.26.98]
11 so-2-1-0.mpr1.sjc2.above.net [64.125.26.229]
12 so-4-2-0.mpr3.pao1.us.above.net [64.125.28.142]
13 yahoo-above-1.pao1.above.net [64.125.12.70]
14 ae1-p150.msr2.sp1.yahoo.com [216.115.107.77]
15 te-9-1.bas-a1.sp1.yahoo.com [209.131.32.23]
16 w2.rc.vip.sp1.yahoo.com [68.180.206.184]

Computing statistics for 400 seconds...
Source to Here This Node/Link
Hop RTT Lost/Sent = Pct Lost/Sent = Pct Address
0 desktop007.it.domain.co.uk [10.4.1.7]
0/ 100 = 0% |
1 0ms 0/ 100 = 0% 0/ 100 = 0% 10.4.0.3
0/ 100 = 0% |
2 0ms 0/ 100 = 0% 0/ 100 = 0% 10.0.1.49
0/ 100 = 0% |
3 1ms 0/ 100 = 0% 0/ 100 = 0% 10.192.0.1
0/ 100 = 0% |
4 3ms 0/ 100 = 0% 0/ 100 = 0% 172.16.253.252
0/ 100 = 0% |
5 10ms 0/ 100 = 0% 0/ 100 = 0% 193.19.88.10
0/ 100 = 0% |
6 13ms 0/ 100 = 0% 0/ 100 = 0% dex-233-21.static.dxi.net [212.95.233.21]
0/ 100 = 0% |
7 11ms 0/ 100 = 0% 0/ 100 = 0% gi5-2.cr1.th.hotchilli.net [217.72.167.165]
0/ 100 = 0% |
8 8ms 0/ 100 = 0% 0/ 100 = 0% 213.152.234.99.above.net [213.152.234.99]
0/ 100 = 0% |
9 80ms 0/ 100 = 0% 0/ 100 = 0% so-0-1-0.mpr1.dca2.us.above.net [64.125.27.57]
0/ 100 = 0% |
10 84ms 0/ 100 = 0% 0/ 100 = 0% so-0-1-0.mpr1.lga5.us.above.net [64.125.26.98]
0/ 100 = 0% |
11 164ms 0/ 100 = 0% 0/ 100 = 0% so-2-1-0.mpr1.sjc2.above.net [64.125.26.229]
0/ 100 = 0% |
12 163ms 0/ 100 = 0% 0/ 100 = 0% so-4-2-0.mpr3.pao1.us.above.net [64.125.28.142]
0/ 100 = 0% |
13 164ms 0/ 100 = 0% 0/ 100 = 0% yahoo-above-1.pao1.above.net [64.125.12.70]
0/ 100 = 0% |
14 164ms 0/ 100 = 0% 0/ 100 = 0% ae1-p150.msr2.sp1.yahoo.com [216.115.107.77]
0/ 100 = 0% |
15 163ms 0/ 100 = 0% 0/ 100 = 0% te-9-1.bas-a1.sp1.yahoo.com [209.131.32.23]
0/ 100 = 0% |
16 162ms 0/ 100 = 0% 0/ 100 = 0% w2.rc.vip.sp1.yahoo.com [68.180.206.184]

Trace complete.

Networking Basics - Tracert

Tracert is an ICMP (Internet Control Message Protocol) echo request over TCP/IP used to report on packet routing.
It's use isnt always reliable as firewalls tend to be configured to block them on external networks.

TRACERT [hostname/ip]

Shows a list of hops taken (routers traversed) i.e the route taken by data on the network.

TRACERT /? to see options

Tracert showing route taken to local server >

C:\>tracert webserver01

Tracing route to webserver01.domain.co.uk [172.29.0.86]
over a maximum of 30 hops:

1 <1 ms <1 ms <1 ms 10.4.0.3 2 1 ms <1 ms <1 ms 10.0.1.53 3 1 ms 1 ms 1 ms 10.192.0.1 4 4 ms 6 ms 7 ms 172.16.253.252 5 7 ms 8 ms 8 ms webserver01.domain.co.uk [172.29.0.86] Trace complete.

Tracert showing route taken to external server >

C:\>tracert yahoo.com

Tracing route to yahoo.com [68.180.206.184]
over a maximum of 30 hops:

1 <1>
2 <1>
3 1 ms 1 ms 1 ms 10.192.0.1
4 3 ms 7 ms 7 ms 172.16.253.252
5 9 ms 4 ms 5 ms 193.19.88.10
6 4 ms 7 ms 4 ms dex-233-21.static.dxi.net [212.95.233.21]
7 5 ms 8 ms 4 ms gi5-2.cr1.th.hotchilli.net [217.72.167.165]
8 5 ms 9 ms 8 ms 213.152.234.99.above.net [213.152.234.99]
9 80 ms 81 ms 78 ms so-0-1-0.mpr1.dca2.us.above.net [64.125.27.57]
10 85 ms 82 ms 85 ms so-0-1-0.mpr1.lga5.us.above.net [64.125.26.98]
11 165 ms 162 ms 163 ms so-2-1-0.mpr1.sjc2.above.net [64.125.26.229]
12 161 ms 161 ms 163 ms so-4-2-0.mpr3.pao1.us.above.net [64.125.28.142]
13 166 ms 161 ms 163 ms yahoo-above-1.pao1.above.net [64.125.12.70]
14 162 ms 162 ms 162 ms ae0-p140.msr1.sp1.yahoo.com [216.115.107.49]
15 165 ms 162 ms 162 ms te-9-1.bas-a1.sp1.yahoo.com [209.131.32.23]
16 165 ms 167 ms 167 ms w2.rc.vip.sp1.yahoo.com [68.180.206.184]

Trace complete.

Networking Basics - Ping

Ping is an ICMP (Internet Control Message Protocol) ECHO request over TCP/IP used to test connectivity.
It's use isnt always reliable as firewalls tend to be configured to block them on external networks.

PING [hostname/ip] By default sends 4 (64k) packets of data.

PING /? To see options

Most useful >

PING {hostname/ip] -t Repeatedly pings until stopped (useful for monitoring reboots)

PING {hostname/ip] -r n Displays information on route taken. (n = 1-9)

Ping showing route taken to local server >

C:\>ping -r 5 webserver01

Pinging webserver01.domain.co.uk [172.29.0.86] with 32 bytes of data:

Reply from 172.29.0.86: bytes=32 time=9ms TTL=124
Route: 10.0.1.50 ->
10.192.0.6 ->
172.16.253.251 ->
172.16.254.252 ->
172.29.0.86
Reply from 172.29.0.86: bytes=32 time=7ms TTL=124
Route: 10.0.1.54 ->
10.192.0.2 ->
172.16.253.251 ->
172.16.254.252 ->
172.29.0.86
Reply from 172.29.0.86: bytes=32 time=10ms TTL=124
Route: 10.0.1.50 ->
10.192.0.6 ->
172.16.253.251 ->
172.16.254.252 ->
172.29.0.86
Reply from 172.29.0.86: bytes=32 time=9ms TTL=124
Route: 10.0.1.54 ->
10.192.0.2 ->
172.16.253.251 ->
172.16.254.252 ->
172.29.0.86

Ping statistics for 172.29.0.86:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 7ms, Maximum = 10ms, Average = 8ms


Ping -r doesnt show route to an external site, just returns standard ping results >

C:\>ping -r 5 google.co.uk

Pinging google.co.uk [72.14.221.104] with 32 bytes of data:

Reply from 72.14.221.104: bytes=32 time=54ms TTL=243
Reply from 72.14.221.104: bytes=32 time=26ms TTL=243
Reply from 72.14.221.104: bytes=32 time=26ms TTL=243
Reply from 72.14.221.104: bytes=32 time=79ms TTL=243

Ping statistics for 72.14.221.104:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 26ms, Maximum = 79ms, Average = 46ms


Equivalent and improved functionality is provided by TRACERT and PATHPING

Networking Basics - NSLookup

NSLookup means Name Server Lookup, and provides you with the DNS name associated with an IP you dont know in addition to the name of the DNS server it fetched that information from.

NSLookup is widely complained about, because >

1. NSLookup requires reverse DNS to be configured.
   If there is a DNS problem it will most likely effect Reverse DNS too, or reverse DNS may not be configured at all.
   
   
2. NSLookup and PING results can differ >
   Ping consults WINS and any HOSTS files present on a client. NSLookup does not.
   
   
3. NSLookup uses different timeouts and queries available name servers in a different order to other tools when attempting name resolution.

Some examples >

C:\>nslookup 10.4.1.7
Server: ukns01.domain.co.uk
Address: 172.16.254.9

Name: dev008.it.domain.co.uk
Address: 10.4.1.7

Omitting parameters put it into interactive mode (note the '>' rather than the DOS command prompt ) >

C:\>nslookup
Default Server: ukns01.domain.co.uk
Address: 172.16.254.9

>

Then, entering some IPs >

> 10.4.1.7
Server: ukns01.domain.co.uk
Address: 172.16.254.9

Name: dev008.it.domain.co.uk
Address: 10.4.1.7

> 127.0.0.1
Server: ukns01.domain.co.uk
Address: 172.16.254.9

Name: localhost
Address: 127.0.0.1

> ls
Server: ukns01.domain.co.uk
Address: 172.16.254.9

The LS command dumps all IPs and servernames the server knows for A & NS record types >

> ls domain.co.uk
[ukns01.domain.co.uk]
domain.co.uk. A 10.10.255.26
domain.co.uk. A 10.10.255.101
domain.co.uk. A 172.16.254.9
domain.co.uk. A 172.16.254.8
domain.co.uk. A 10.10.0.248
domain.co.uk. A 10.10.255.5
domain.co.uk. A 10.59.0.4
domain.co.uk. A 10.10.255.1
domain.co.uk. NS server = uk-dc-1.domain.co.uk
domain.co.uk. NS server = uk2ns02.domain.co.uk
domain.co.uk. NS server = ukns01.domain.co.uk
gc._msdcs A 10.10.255.1
gc._msdcs A 10.47.255.4
gc._msdcs A 10.47.255.2
gc._msdcs A 10.110.255.1
gc._msdcs A 10.128.17.4
gc._msdcs A 10.47.255.10
bacs A 10.10.255.34
bcd NS server = bcd-mail.bcd.domain.co.uk
uk1app01 A 172.29.0.11
uk1app03 A 172.29.0.27
uk1app05 A 172.28.0.6
uk1bacs01 A 172.16.254.16
uk1cpu01 A 172.16.254.35
uk1db01 A 172.29.0.17
uk1ddb01 A 172.29.0.25
uk1dist01 A 172.29.0.8
uk1file01 A 172.29.0.9
uk1rdac01 A 172.29.0.97
uk1rdac03 A 172.29.0.99
uk1rdac05 A 172.29.0.113
uk1sds01 A 172.29.0.13
uk1tdb01 A 172.29.0.21
uk1TSQL A 172.29.0.139
uk1tweb01 A 172.29.0.31
uk1tweb05 A 172.29.0.115
uk1udb01 A 172.29.0.29
uk2dist02 A 172.29.0.2
uk2file02 A 172.29.0.48
uk2file04 A 172.29.0.60
uk2tapp02 A 172.28.0.66
uk2tdb02 A 172.29.0.18
uk2tweb02 A 172.29.0.16
uk2udb02 A 172.29.0.20
uk2uweb02 A 172.29.0.24
uk1ext01 A 193.19.89.11
uk1ext03 A 193.19.89.2
uk1ext05 A 193.19.89.4
uk2ldap02 A 172.16.254.6
uk2mom02 A 172.29.0.124
uk2ns02 A 172.16.254.8
uk2tsp04 A 193.19.88.164
uk2tstbkp02 A 172.29.0.134
uk2vbe02 A 172.16.254.18
uk2wfl02 A 193.19.88.138
uk3CA01 A 10.10.255.21
uk3xvc01 A 10.10.255.35
ukhq NS server = ukhq-pdc.ukhq.domain.co.uk
ukhq-pdc.ukhq A 10.47.255.2
ukhqcsips01 A 10.10.255.41
ukHQCSIPS02 A 10.10.255.40
ukintranet A 212.95.233.40
ukit NS server = uk2ns02.domain.co.uk
ukit NS server = ukit-server.ukit.domain.co.uk
ukit-server.ukit A 10.4.255.2
ukit-server A 10.4.255.2
ukitcsdr01 A 10.4.1.54
uksharepoint A 10.10.255.20
uktestsp01 A 10.10.255.12
ukukdc1amd01 A 172.16.254.73
ukukdc1apn01 A 172.16.254.67
ukukdc1vas01 A 172.16.254.71
ukukdc2amd02 A 172.16.254.72
bsl NS server = bsl-mail.bsl.domain.co.uk
bsl-mail.bsl A 10.110.255.1
bsrdb A 172.29.0.249
dr.build A 10.10.0.7
live.build A 172.29.0.41
test.build A 10.10.0.7
buildsystem A 172.28.0.4
vm.buildsystem A 172.27.3.2
ca A 172.27.0.3
Canon0A59E7 A 10.10.1.55
Canon0A78B8 A 10.47.1.48
Sharepoint A 193.19.88.154
dev.Sharepoint A 10.10.255.12
hq.Sharepoint A 193.19.88.154
ithq.Sharepoint A 193.19.88.154
test.Sharepoint A 193.19.88.164
dc1sdb.sj A 172.28.0.34
dc2sdb.sj A 172.28.0.35
sdb.sj A 172.28.0.36
vm.sdb.sj A 172.27.3.7
staging A 193.19.88.135
stats A 193.19.88.147
webmail A 193.19.88.36
webqa A 193.19.88.135
webtrends A 193.19.89.2
webusagereport A 172.29.0.49
www A 193.19.89.12
XEROX3545 A 10.128.18.46
>

Changing the type of query to see what is handling email (MX records) in the domain >

> ls -t mx domain.co.uk
[ukns01.domain.co.uk]
domain.co.uk. MX 30 uk2ex03.domain.co.uk
domain.co.uk. MX 10 uk1ex01.domain.co.uk
domain.co.uk. MX 20 uk1ex02.domain.co.uk

To exit the interface >

> exit