Sql-server – BCP error near the keyword ‘as’

I am new to using BCP but I am trying to extract data generated via a stored proc. This is a continuation of my original question which was a stored procedure with temp tables. This procedure has grown a bit but the core is the same, generate temp tables, run dynamic SQL to populate the tables and then return data.

I realized quickly that bcp does not like temp tables via the error:

SQLState = S0002, NativeError = 208

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid Object name '#SurveyData

So I changed my procedure to remove the #temp tables and use actual tables. But now I get an error:

SQLState = 37000, NativeError = 156

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'as'

I believe the issue is with the dynamic SQL. There is an as in the statement.

SET @sql = 'INSERT INTO cl.Results ' +
                            ' SELECT s.SurveyId
                                , s.InstanceId
                                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END 
                                , Cast(s.' + @ColumnName + ' as varchar(1000))
                            FROM cl.SurveyData t 
                            INNER JOIN dbo.' + @TableName + ' s' +
                            ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                            ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

The code that I am using in BCP to execute is:

bcp "exec dbo.getresults" queryout "c:\temp\mytext.txt" -S <myserver> -T -c -t^|

I am open to any suggestions about how to get this data extracted, even if I have to rewrite the stored procedure.

Can I not use BCP to execute a stored proc with dynamic SQL? If not, how else should I consider extracting this data? SSIS does not seem to like either the temp tables or dynamic SQL either.

Edit: using print(@sql)

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_1' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_1 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_1 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  WHERE t.columnName = 'Q_1'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_2' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_2 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  WHERE t.columnName = 'Q_2'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_3' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_3 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  
WHERE t.columnName = 'Q_3'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_4' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_4 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  
WHERE t.columnName = 'Q_4'

Edit: Here is the full script that I am running without any temp tables:

truncate table cl.Results
truncate table cl.SurveyData

-- insert the survey table structures for use
insert into cl.SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM cl.SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM cl.SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO cl.Results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END 
                        , Cast(s.' + @ColumnName + ' as varchar(1000))
                    FROM cl.SurveyData t 
                    INNER JOIN dbo.' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT r.SurveyId, 
        r.InstanceId, 
        CASE 
            when chat.DataName = 'BRKR_ACCT_ID' and chat.DataValue is not null then chat.DataValue
            when email.PropName = 'ACCT_ID' and email.PropValue is not null then email.PropValue
        END Account_ID,
        CASE 
            when chat.DataName = 'BRKR_CUST_ID' and chat.DataValue is not null then chat.DataValue
            when email.PropName = 'CUST_ID' and email.PropValue is not null then email.PropValue
        END Cust_ID,
        case 
            when chat.LoginName is null 
            then email.LoginName 
            else chat.LoginName 
        end SchwabId,
        case 
            when chat.CustEmail is null 
            then email.ReplyTo 
            else chat.CustEmail 
        end CustomerEmail,
        sp.DateSent,
        CONVERT(varchar, DATEADD(ms, DATEDIFF(SECOND, sp.datesent, sp.datecompleted) * 1000, 0), 108) ResponseTime,
        r.QuestionNumber,
        r.Response
    FROM cl.Results r
    INNER JOIN dbo.SurveyParam sp
        ON r.InstanceId = sp.InstanceID
        AND r.SurveyId = sp.SurveyID
    -- chat surveys uses Param4
    LEFT JOIN 
    (
        SELECT si.SessionID, 
            si.CustEmail, 
            u.LoginName, 
            ltrim(rtrim(sie.DataValue)) DataValue, 
            ltrim(rtrim(sie.DataName)) DataName
        FROM dbo.SessionInfo si
        LEFT JOIN dbo.Users u
            ON si.LastAgent = u.id
        LEFT JOIN dbo.SessionInfoExternals sie
            ON si.SessionID = sie.SessionID
        WHERE sie.DataName IN ('BRKR_ACCT_ID', 'BRKR_CUST_ID')
    ) chat
        ON sp.Param4 = chat.SessionID
    -- email surveys uses Param3
    LEFT JOIN
    (
        SELECT mm.MsgID, 
            mr.ReplyTo, 
            u.LoginName, 
            ltrim(rtrim(mie.PropValue)) PropValue,
            ltrim(rtrim(mie.PropName)) PropName
        FROM dbo.MailReply mr
        INNER JOIN dbo.MailMessage mm
            ON mr.ReplyToID = mm.MsgID
        LEFT JOIN dbo.Users u
            on mr.AgentID = u.ID
        LEFT JOIN dbo.MsgInfoExternals mie
            ON mm.IncidentID = mie.Instance
        WHERE mie.PropName IN ('CUST_ID', 'ACCT_ID')
    ) email
        ON sp.Param3 = email.MsgID
    WHERE sp.DateCompleted is not null