Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Have you tried to bcp into a global temporary table? i.e. ##GlobalTable instead? –  Mr.Brownstone Aug 16 '12 at 16:28
    
@Mr.Brownstone just tried and same error message invalid object name ##surveydata –  bluefeet Aug 16 '12 at 16:33
    
Also, can you replace the exec(@sql) with print(@sql) and post the output please? –  Mr.Brownstone Aug 16 '12 at 16:34
    
@Mr.Brownstone posted print that is one of the 4 statements that are generated –  bluefeet Aug 16 '12 at 16:38
2  
Just noticed that your dynamic SQL query will show exactly that error if @ColumnName and @TableName is empty when you build @sql. If it is a two phase thing as @mrdenny claims it could be that the queries you are using is not returning any data in the first phase so the default values for the variables are still used. You could try to trick it by assigning something else but the empty string as default values for @ColumnName and @TableName like 'ColName' and 'TableName'. It will give you a valid query but BCP may of course still fail for other reasons. –  Mikael Eriksson Aug 16 '12 at 17:17

1 Answer 1

up vote 1 down vote accepted

I believe that BCP does a two phase process. The first is done with a format only where the query isn't actually executed but it is parsed so that BCP can figure out the columns that will be returned and in the second phase it actually runs the query. You are failing on the first phase as the temp table doesn't exist so it can't be used for the format only option.

Basically the use of the temp table isn't going to work.

share|improve this answer
    
when I remove the temp table and create an actual table on the database, it still fails with the dynamic SQL piece to insert into the table –  bluefeet Aug 16 '12 at 16:52
    
It'll probably have the same problem with the dynamic SQL as it does with the temp table. –  mrdenny Aug 16 '12 at 16:54
    
any suggestions about other routes that I can take? –  bluefeet Aug 16 '12 at 17:00
    
You'll need to hard code the main query into the procedure. Does the schema really change all that often that this needs to be dynamic? The only other option I can think of would be to setup a SQL Agent job which runs the dynamic SQL portion and have your procedure start the agent job then wait for the job to finish. Once the job is finished you can then query for the data from the table that is written to and continue on. –  mrdenny Aug 16 '12 at 17:02
    
yes, unfortunately it changes. A new survey table is created when the users create a new survey - poor vendor design. So we have no way of knowing how many tables there will be when we execute it. We don't want to have to change our process daily based on survey changes –  bluefeet Aug 16 '12 at 17:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.