So I found the problem. Macro Recorder added in an extra ARRAY() for
the CommandText property. Once I took the SQL statement out of the
array encapsulation and included the rest of the code that was
truncated, it functioned as expected.

-------------------------------------------------------------------------
Sub RetrieveHeadcountFromCMSLive()

'Import Data From SQL Server to populate headcount table based on the
office
'and department of the user opening the spreadsheet.

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _

"ODBC;DSN=seassql08;Description=seassql08;UID=cmsa dm;PWD=cmsadm;APP=Microsoft
Office 2003;WSID=SEAD502366;Network=DBMSSOCN;Address=se" _
), Array("assql08,1433")), Destination:=Range("A5"))
.CommandText = "SELECT HBM_PERSNL.EMPLOYEE_CODE as EmpID,
HBM_PERSNL.EMPLOYEE_NAME as EmpName," & " " & _
"HBM_PERSNL.OFFC as Offc, HBM_PERSNL.DEPT
as Dept, HBM_PERSNL.LOCATION as Loc," & " " & _
"HBM_PERSNL.LOGIN as Login,
HBM_PERSNL.PHONE_NO as Phone, HBM_PERSNL.POSITION as Position," & " "
& _
"HBL_PERSNL_TYPE.PERSNL_TYP_CODE as
TypeID, HBL_PERSNL_TYPE.PERSNL_TYP_DESC as TypeName," & " " & _
"TBM_PERSNL.RANK_CODE as Rank,
TBM_PERSNL.PARTIME_PCNT as FTE" & " " & _
"FROM (dbo.HBM_PERSNL INNER JOIN
HBL_PERSNL_TYPE ON" & " " & _
"dbo.HBM_PERSNL.PERSNL_TYP_CODE =
HBL_PERSNL_TYPE.PERSNL_TYP_CODE)" & " " & _
"INNER JOIN TBM_PERSNL ON
TBM_PERSNL.EMPL_UNO = dbo.HBM_PERSNL.EMPL_UNO" & " " & _
"WHERE HBM_PERSNL.INACTIVE='N' and
HBM_PERSNL.PERSNL_TYP_CODE NOT IN ('PERKI','RESR')" & " " & _
"and HBM_PERSNL.LOGIN NOT IN
('','15REC','ZZZZA','EVENTS','SPALA','PZZZX','DCGU 1','INTAPPADMIN','LAGU1','TECHS','DR0NE')"
& " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'%TEMP%' and
HBM_PERSNL.LOGIN NOT LIKE'TRANS%'" & " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'TRON%' and
HBM_PERSNL.LOGIN NOT LIKE'POGU%'" & " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'BIT%' and
HBM_PERSNL.LOGIN NOT LIKE'DPC%'" & " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'PERK%' and
HBM_PERSNL.LOGIN NOT LIKE'CMS%'" & " " & _
"and HBM_PERSNL.OFFC IN('10','09') --and
HBM_PERSNL.DEPT IN('890')" & " " & _
"ORDER BY HBM_PERSNL.OFFC,
HBM_PERSNL.DEPT, HBM_PERSNL.EMPLOYEE_NAME"
.Name = "Query from seassql08"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
-------------------------------------------------------------------------