Sou novo no uso do BCP, mas estou tentando extrair dados gerados por meio de um procedimento armazenado. Esta é uma continuação da minha pergunta original, que era um procedimento armazenado com tabelas temporárias. Este procedimento cresceu um pouco, mas o núcleo é o mesmo, gerar tabelas temporárias, executar SQL dinâmico para preencher as tabelas e retornar dados.
Percebi rapidamente que o bcp não gosta de tabelas temporárias por meio do erro:
SQLState = S0002, NativeError = 208
Erro = [Microsoft][SQL Server Native Client 10.0][SQL Server]Nome de objeto inválido '#SurveyData
Então mudei meu procedimento para remover as tabelas #temp e usar tabelas reais. Mas agora recebo um erro:
SQLState = 37000, NativeError = 156
Erro = [Microsoft][SQL Server Native Client 10.0][SQL Server]Sintaxe incorreta perto da palavra-chave 'as'
Acredito que o problema seja com o SQL dinâmico. Há um as
na declaração.
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)
O código que estou usando no BCP para executar é:
bcp "exec dbo.getresults" queryout "c:\temp\mytext.txt" -S <myserver> -T -c -t^|
Estou aberto a qualquer sugestão sobre como obter esses dados extraídos, mesmo que eu tenha que reescrever o procedimento armazenado.
Não posso usar o BCP para executar um procedimento armazenado com SQL dinâmico? Se não, de que outra forma devo considerar a extração desses dados? O SSIS também não parece gostar das tabelas temporárias ou do SQL dinâmico.
Editar: usandoprint(@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'
Editar: Aqui está o script completo que estou executando sem nenhuma tabela temporária:
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
Acredito que o BCP faz um processo de duas fases. A primeira é feita com um formato apenas onde a consulta não é realmente executada, mas é analisada para que o BCP possa descobrir as colunas que serão retornadas e na segunda fase ele realmente executa a consulta. Você está falhando na primeira fase porque a tabela temporária não existe, então ela não pode ser usada para a opção somente de formato.
Basicamente, o uso da tabela temporária não vai funcionar.