Dataset not bringing back all data from serveer

[RichardMGreen]

Hi all

I'v got a query that's supposed to bring back data into a recordset but it's only bringing back the first two columns.
The query looks like this:-

Option ExplicitDim conn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
Dim strConn As String, strQry As String
Dim wsData As Worksheet
Dim rngData As Range
Dim param1



Sub refresher3()'----- set up all the initial bits
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
'----- Refresh the running queries table
    ActiveWorkbook.Connections("Query from DW-DEV").Refresh
'----- Now set up the connection -----
    Set conn = New ADODB.Connection
    strConn = "DRIVER=SQL Server;SERVER=DW-DEV;DATABASE=Utilities"
    conn.ConnectionString = strConn
    conn.Open
    Set cmd = New ADODB.Command
    cmd.CommandTimeout = 0
'----- Get a slightly different parameter for the running queries -----
        param1 = Sheets("Stats").Range("M3").Value
'----- get running query data -------
    strQry = "SELECT q.[Date], q.[ObjectName]" & _
        ", [Text] = left(replace(replace(q.Text,char(10),''),char(13),''),500), q.[dbname], q.[session_id]" & _
        ", q.[open_tran], q.[nt_username], q.[nt_domain]" & _
        " FROM Utilities.dbo.tbl_Running_Queries q where q.Date >= '" & _
        param1 & "' and q.Text not like '%tbl_Running_Queries%' order by q.Date desc"
'----- Drop it into a worksheet -----
    cmd.CommandType = adCmdText
    cmd.CommandText = strQry
    cmd.ActiveConnection = conn
    Set rs = cmd.Execute
    Set wsData = Worksheets("Queries")
    Set rngData = wsData.Range("A2")
    rngData.CopyFromRecordset rs
'----- clear down all the connections -----
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
'----- switch everything back on -----
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

The code runs fine but only brings back the first 2 columns.

Anyone any ideas why?

 

[mole999]

could it be
", [Text] = left(replace(replace(q.Text,char(10),''),
", q.[Text] = left(replace(replace(q.Text,char(10),''),

SELECT
q.[Date]
, q.[ObjectName]
, [Text] = left(replace(replace(q.Text,char(10),''),char(13),''),500)
, q.[dbname]
, q.[session_id]
, q.[open_tran]
, q.[nt_username]
, q.[nt_domain]
FROM Utilities.dbo.tbl_Running_Queries q
where q.Date >= '" param1 & "'
and
q.Text not like '%tbl_Running_Queries%'
order by q.Date desc

 

[RichardMGreen]

It looks like there's a type in my original code when I posted on here (but it's fine in my VBA).
My code looks like they code you posted.