Transact SQL and the Null Character

Background

Transact SQL is a very high level language and 99.99% of the time one does not really have to pay attention.

This morning for me was not one of those times that I did not have to be attentive.

Lineage

A quick following up to our last post :-

SQL Server – Transact SQL – Get Network Info
Link

In that post we discussed a couple of options for discovering network metadata per SQL Server Instances.

Query

Original Query

Here is our original query

SQL


select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

Output

sysDOTdm_server_registry__20180607_0918PM

Revised Query – Filter Out Null and Empty Records

In our revised query we will filter out null and empty records

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

Output

sysDOTdm_server_registry__20180607_0918PM

Explanation

Our filter clauses “is Not null” and != ” did not help.

Revised Query – Filter Out Null Character

In our second attempt, we filter out records that simply have the NULL CHARACTER as there whole value

SQL

select *

from sys.dm_server_registry tblDSR

where tblDSR.value_name = 'TcpDynamicPorts'

and tblDSR.value_data is not null

and tblDSR.value_data != ''

and tblDSR.value_data != char(0)

Output

sysDOTdm_server_registry__20180607_0925AM

Explanation

Our filter clause is != char(0) and did discard the unwanted records.

SQL Server – Transact SQL – Get Network Info

Background

Wanted to review the IP Addresses for some of our SQL Server Instances that are hosted on Cloud Servers.

Outline

  1. CONNECTIONPROPERTY
  2. sys.dm_exec_connections
  3. sys.dm_tcp_listener_states

Code

CONNECTIONPROPERTY

SQL

Sample

SELECT 

          [netTransport]
            = CONNECTIONPROPERTY('net_transport')

        , [authScheme]
            = CONNECTIONPROPERTY('auth_scheme')

        , [ipAddress]
            = CONNECTIONPROPERTY('local_net_address')

        , [iPort]
            = CONNECTIONPROPERTY('local_tcp_port')

Output

connectionProperty_20181107_0732AM

sys.dm_exec_connections

SQL

Sample

SELECT
              [machineName]
                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

            , [sqlInstance]
                = SERVERPROPERTY('servername') 

            , [instance]
                = SERVERPROPERTY('InstanceName')

            , [ipAddress]
                = tblDMEC.[LOCAL_NET_ADDRESS]

            , [numberofConnections]
                = count(*)

 FROM sys.dm_exec_connections tblDMEC

 where
        (

            ( tblDMEC.LOCAL_NET_ADDRESS is not null )

        )

 group by

         tblDMEC.LOCAL_NET_ADDRESS

Output

sys.dm_exec_connections.20181107.0738AM.PNG

sys.dm_tcp_listener_states

SQL

Sample

SELECT * 

FROM   sys.dm_tcp_listener_states tblTLS

Output

sys.dm_tcp_listener_states.20181107.0811AM.PNG

SQL Server – Network – Get TCP Ports

Background

Get network ports that the current SQL Server Instance is using.

Outline

There are some pathways to getting the SQL Server Network Ports:

  1. sys.dm_server_registry
    • SQL Server Version
      • v2008

Code

sys.dm_server_registry

SQL


select 

          tblDSR.registry_key
        , tblDSR.value_name
        , tblDSR.value_data
        , [userOrAdmin]
            = case
                when (tblDSR.registry_key like '%AdminConnection%') then 'Admin'
                else 'User'
              end

from   sys.dm_server_registry tblDSR

/*
	Database Engine - MSSQLServer
*/
where
       (

            (
                ( tblDSR.registry_key like '%\MSSQLServer\%' )
			)
        )

/*
	Filter in TCP Ports
*/
and
       (

            (
                tblDSR.value_name in
                    (
                          'TcpDynamicPorts'
                        , 'TcpPort'
                    )
            )

        )

/*
    Filter out \IP1 thru \IP4
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP1
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP2
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP3
        HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\Tcp\IP4
*/
and
       (
            ( tblDSR.registry_key not like '%\IP_')
        )

/*
    Filter out empty data
*/
and
        (

                ( tblDSR.value_data is not null )
            and ( tblDSR.value_data != '' )
            and ( tblDSR.value_data != '0' )
        )

Output

Output – Default Instance

sysDOTdm_server_registry__Default__20180606_0258PM.png

Output – Named Instance

sysDOTdm_server_registry__20180606_0239PM.png

Explanation

  1. Registry Key
    • Please close out your eyes to the AdminConnection; unless you are trying to connect to the Admin Port
    • Most people will be using the regular user default port

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_server_registy

Technical: Microsoft – Networking / Active Directory – Net Commands

Technical: Microsoft – Networking / Active Directory – Net Commands

Background

One of the things I like about OS/X and Unix is that one gets in the habit of doing many things through the command line.

Whereas in Windows, one will use a GUI to get quick information.

But, truthfully Microsoft has many facilities for accomplishing things through the command line, or shall we say terminal mode, as well.

Introduction

In the Valley, the saying goes “Got tired of patch Tuesday, and so went Goggling“.
But before you go, you can at least give Street Creds to Redmond, also.

List Active Directory User Profile

Syntax:

net user "<AD Account>"  /Domain 

Sample


net user "danieladeniji"  /Domain

Sample Output:

netuser

List Active Directory Domain groups that a user belongs to

Syntax:

net user "<AD Account>"  /Domain | find "Group"

Sample


net user "danieladeniji"  /Domain | find "Group"

Sample Output:


Local Group Memberships
Global Group memberships *Marketing *GAMERS

Explanation:

  • The user does not belong to any local group; which is not true, but since we are targeting via “/Domain” we will overlook for now
  • And, he belongs to a couple of Domain Groups (Marketing and Gamers)

List all Active Directory Domain groups

Syntax:


net group /Domain

Sample:

net group  /Domain

List Members of Active Directory Domain group

Syntax:


net group "<AD Group Name>" /Domain

Sample:

net group "Domain Admins"  /Domain

 

List Shared folders on a remote machine

The net view commands lists the shared resources on a remote machine.

Syntax:

net view \\<machine-name>

Sample:


net view \\DWP

netview

List Local Services

List Local Services.

Syntax:

net view

Sample:


net view

 

References

References – Net Commands – NetUser

References – Net Commands – NetGroup

Atheros AR5006 EXS Wireless Network Adapter & MS Windows Vista ( trying to AT&T Universe supplied Wireless Access Point to get online) – Error “Unidentified Network”

A good friend texted me last week saying that upon the family upgrading to AT & T \ SBC U-verse one of their laptops is no longer able to connect to the Internet.  That is two of three computers are able to connect, but one last lone laptop is not.

He is a good friend and he is computer savvy.  And, I know he would not contact me unless he is tried and he really needs the help.

And, so he volunteered to bring the computer over, but I know it would not really help; as I needed to observe the interaction between his laptop and and the wireless access point.

But, first I had him connect the laptop to the Wireless Access Point via a Network Cable and that worked.

Drove over and spent the next 3 to 4 hours there tried everything.  But, nothing worked. Here are the things we tried:

  • Via Control Panel, Device Manager, removed the Wireless Adapter and had windows rediscovered it
  • Restarted Wireless Access Point
  • Unchecked IPV-6
  • Spent hours re-installing the TCP/IP Stack; since too many times when viruses take out the TCP/IP stack

The errors we were getting included:

  • Unidentified Network
  • On the “Network and Sharing” center, we were gaining connection to the WAP.  But, post WAP, no connection to the Internet
  • On the Status Window of the Wireless Card, we registered “Sent” traffic, but no “Received” traffic

Had a chance to think it over this last weekend and so was happy when he called that he will come by on Sunday evening.  Outside of watching “60 minutes” on CBS, my Sunday evenings are free.

Once he brought the computer by, I went to Control Panel \ Devices and checked the date-stamp on his Wireless Device Driver.

It was a bit dated:

Version#: 7.1.0.90
Date: 11/20/2006

Googled for “Atheros AR5006 EXS Wireless AR 5006 EXS Wireless” Drivers and found a later one:

Version#: 7.7.0.288
Date: 4/22/2009

So downloaded the new one:

Toshiba – Wireless Connection Portal
http://aps2.toshiba-tro.de/wlan/?page=downloads

BTW, here is a link to a slightly newer version:

Atheros Wireless LAN Driver for Windows Vista (32bit/64bit)
http://www.csd.toshiba.com/cgi-bin/tais/support/jsp/download.jsp?ct=DL&soid=2382303&ref=EV


Version#: 7.7.0.288
Date: 6/30/2009

Had problems getting it to work until I extracted it.  Here is the folder hierarchy once extracted:

Atheros - Extracted Folder

There are two folders:

  • VistaDrv  –> Actual Device Driver
  • Vistautl –> Utility

Sojourn to the VistaDrv folder and run the setup.exe.

Once installed, revisit Control Panel\Device Manager and validate that your Atheros Wireless Driver is now 7.7.0.288 or later.

There are so many renditions of this problem on the Net:

The most comprehensive that I found post fixing the problem and doing my write-up is Aseem Kishore’s entry:

One can quickly see that he has seen his share of desktop networking problems – “Little Game Recognizing Big Game”

Fix Unidentified Network and No Network Access in Windows 7
Link

HP Pavilion — a847c Desktop – MS Windows – “Other Devices” “Ethernet Controller” –> Yellow Mark

HP Pavilion — a847c Desktop – MS Windows – “Other Devices” “Ethernet Controller” –> Yellow Mark

It all started one Saturday morning, actually this morning (Sat 2010-10-02) to be exact. A friend’s HP Pavilion hard-drive crashed and a new drive had been placed in for a couple of weeks now. But, only now completed MS Windows XP install. Only God knows how long it takes to apply all the security patches one has to download and apply these days.

Anyways, most everything was done. But, still there was a yellow exclamation mark that wouldn’t budge in the Control Panel \ Device Manager applet.

Visited HP’s Support site numerous times to see if I could find a matching driver, but no luck.

Reviewed, the Product Specifications – HP Pavilion a847c Desktop PC Product Specifications (http://h10025.www1.hp.com/ewfrf/wc/document?docname=c00280716&cc=us&dlc=en&lc=en&jumpid=reg_R1002_USEN), but very little detail regarding the Networking “elements”.

The specification per-functionary makes mention of “Network (LAN) – Integrated 10/100 Base-T Networking Interface”. And, that is it.

On HP’s Support \ Drivers web page , the closest we came to the drivers is:

HP Customer Care > HP Pavilion a847c Desktop PC > Microsoft Windows XP > Spring 2005 Original Network Adapter Driver Collection
but, even after downloading the driver, extracting the zip file, the extracted files lacked an install program (setup.exe) and the accompanying documentation yet failed to draw us any closer to knowing if we had the needed files.
So reached back and brought out an old trusted friend and companion (Halfdone Development – Unknown Devices
The “Unknown Devices” application immediately recognized the device as an “Atheros AR5212 Wireless”. So off I googled for that manufacturer and driver, but all
to dead ends. It appears that the driver is not publicly published, nor supported by the Vendor.
Googled some more and found a couple of Applications that would detect the “unknown devices” and match them with corresponding drivers.
Some of the Applications that were downloaded and tried are: DriverMedic and Unible – DriverScanner. But, as they were both commercial applications, could least afford them; at least on a broke Saturday morning ( on the 2nd day of October).
After much searching found the drivers on DriverGuide (http://members.driverguide.com/driver/detail.php?driverid=704874). Yes, had to endure and click through about 15 advertisement pages, but as long as I hard searched it was worth it.
Once the compressed\zip file is downloaded please extract it and return to MS Device Manager where will be able to affix the newly downloaded drivers to the “Ethernet Controller”.
So that is the long story. A shorter story and possibly more official driver can be gleaned from this next paragraph.
___________________________________________________________________________________________________________________
Luvr, a techspot member, gave an espidodial journal, of a similar journey in “Getting Atheros AR5212 Wireless Cards to Work with Windows XP “. The URL
is http://www.techspot.com/vb/topic39651.html. He rightfully directed readers to download the drivers from AirLink’s web site @
http://www.airlink101.com/support/index.php?cmd=files&id=54.
____________________________________________________________________________________________________
MS write-up about “Unknown Devices”

How to troubleshoot unknown devices that are listed in Device Manager in Windows 2000

____________________________________________________________________________________________________