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
