Estou tendo problemas para executar um bloco de códigos, mas não consigo entender realmente onde está o erro e muito menos corrigi-lo. Podem dar uma ajuda, por favor? porque na vdd ele até retorna informações e corretas, mas quando coloco ele no SSIS 2005 ele tá erro e não continua executando os demais blocos de código. Erro no SSIS: [Execute SQL Task] Error: Executing the query "IF(SELECT OBJECT_ID('TEMPDB..#transacoes_faturadas_atendimento')) IS NOT NULL DROP TABLE #transacoes_faturadas_atendimento SELECT * INTO #transacoes_faturadas_atendimento FROM datacare_ods..credit_transaction_billing (nolock) WHERE loaddate between '2013-07-14' and @loaddate_faturadas CREATE INDEX idx_bill ON #transacoes_faturadas_atendimento (BillRuleID) CREATE INDEX idx_cust ON #transacoes_faturadas_atendimento (CustomerID) CREATE INDEX idx_tra ON #transacoes_faturadas_atendimento (TransactionID) " failed with the following error: "Must declare the scalar variable "@loaddate_faturadas".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Segue script rodado no SQL 2005:
declare
@loaddate_campaign smalldatetime
declare
@loaddate_notyet smalldatetime
declare
@loaddate_faturadas smalldatetime
declare
@loaddate_percentual smalldatetime
declare
@reference_period char(7)
set
@loaddate_campaign = (select max (loaddate) from datacare_ods..credit_campaign_provisioning (NOLOCK))
set
@loaddate_notyet = (select max (loaddate) from datacare_ods..credit_transaction_notyet (NOLOCK))
set
@loaddate_faturadas = (select max (loaddate) from datacare_ods..credit_transaction_billing (NOLOCK))
set
@loaddate_percentual = (select max (loaddate) from datacare_ods..credit_campaign_percentage (NOLOCK))
set
@reference_period = '2013-08' -->alterar para mes de atualizao
IF
(SELECT OBJECT_ID('TEMPDB..#fct_customer_campaign')) IS NOT NULL
begin
DROP
TABLE #fct_customer_campaign end
SELECT
*
INTO
#fct_customer_campaign
FROM
datacare_ods
..credit_campaign_provisioning (NOLOCK)
WHERE
loaddate
= @loaddate_campaign
CREATE
INDEX idx_bill ON #fct_customer_campaign (BillRuleID)
CREATE
INDEX idx_cust ON #fct_customer_campaign (CustomerID)
CREATE
INDEX idx_camp ON #fct_customer_campaign (CampaignID)
IF
(SELECT OBJECT_ID('TEMPDB..#transacoes_faturadas_atendimento')) IS NOT NULL
begin
DROP
TABLE #transacoes_faturadas_atendimento end
SELECT
*
INTO
#transacoes_faturadas_atendimento
FROM
datacare_ods
..credit_transaction_billing (nolock)
WHERE
loaddate
between '2013-07-14' and @loaddate_faturadas
CREATE
INDEX idx_bill ON #transacoes_faturadas_atendimento (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_faturadas_atendimento (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_faturadas_atendimento (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..#transacoes_notyed_atendimento')) IS NOT NULL
begin
DROP
TABLE #transacoes_notyed_atendimento end
SELECT
*
INTO
#transacoes_notyed_atendimento
FROM
datacare_ods
..credit_transaction_notyet (NOLOCK)
WHERE
loaddate
= @loaddate_notyet
CREATE
INDEX idx_bill ON #transacoes_notyed_atendimento (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_notyed_atendimento (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_notyed_atendimento (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..#Telecinejunho2013Campaign')) IS NOT NULL
begin
DROP
TABLE #Telecinejunho2013Campaign end
SELECT
*
INTO
#Telecinejunho2013Campaign
FROM
#fct_customer_campaign a
WHERE
CampaignID
= '3606'
AND isAtendimento = 'Y'
and
exists (
SELECT
customerID
FROM
dbmii
.[directv\etsonoha].CampanhaProdutos20130621 b
WHERE
a
.CustomerID = b.CustomerID
and Oferta = 'Telecine 3'
and Falha = 'N'
)
SELECT
convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento]
,cast(sum(price) as money) as [Total Campanhas]
FROM
#fct_customer_campaign a
(NOLOCK)
WHERE
convert(varchar(7), EstimatedBillingDate, 120) >= @reference_period -- ms de referncia
AND loaddate = @loaddate_campaign
AND isAtendimento = 'Y'
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_notyed_atendimento b
(NOLOCK)
WHERE
a
.customerid = b.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120)
and a.billruleid = b.billruleid
and loaddate = @loaddate_notyet
)
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_faturadas_atendimento c
(NOLOCK)
WHERE
a
.customerid = c.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120)
and a.billruleid = c.billruleid
and loaddate = @loaddate_faturadas
)
GROUP
BY
convert(varchar(7), EstimatedBillingDate, 120)
ORDER
BY
1
IF
(SELECT OBJECT_ID('TEMPDB..SELECT #fct_customer_campaign_1 ')) IS NOT NULL
begin
DROP
TABLE #fct_customer_campaign_1
end
SELECT
*
INTO
#fct_customer_campaign_1
FROM
datacare_ods
..credit_campaign_provisioning (NOLOCK)
WHERE
loaddate
= @loaddate_campaign
CREATE
INDEX idx_bill ON #fct_customer_campaign_1 (BillRuleID)
CREATE
INDEX idx_cust ON #fct_customer_campaign_1 (CustomerID)
CREATE
INDEX idx_camp ON #fct_customer_campaign_1 (CampaignID)
IF
(SELECT OBJECT_ID('TEMPDB..SELECT #transacoes_faturadas_atendimento ')) IS NOT NULL
begin
DROP
TABLE #transacoes_faturadas_atendimento_1 end
SELECT
*
INTO
#transacoes_faturadas_atendimento_1
FROM
datacare_ods
..credit_transaction_billing (nolock)
WHERE
loaddate
between '2013-07-14' and @loaddate_faturadas
CREATE
INDEX idx_bill ON #transacoes_faturadas_atendimento_1 (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_faturadas_atendimento_1 (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_faturadas_atendimento_1 (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..SELECT #transacoes_notyed_atendimento ')) IS NOT NULL
begin
DROP
TABLE #transacoes_notyed_atendimento end
SELECT
*
INTO
#transacoes_notyed_atendimento_2
FROM
datacare_ods
..credit_transaction_notyet (NOLOCK)
WHERE
loaddate
= @loaddate_notyet
CREATE
INDEX idx_bill ON #transacoes_notyed_atendimento (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_notyed_atendimento (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_notyed_atendimento (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..SELECT ##FATURAMENTOCAMPANHAS ')) IS NOT NULL
begin
DROP
TABLE ##FATURAMENTOCAMPANHAS end
SELECT
customerid,
smsaccountid
,
campaignid
,
isatendimento
,
convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento]
,cast(sum(price) as money) as [Total Campanhas]
INTO
##FATURAMENTOCAMPANHAS
FROM
#fct_customer_campaign a
(NOLOCK)
WHERE
convert(varchar(7), EstimatedBillingDate, 120) >= @reference_period -- ms de referncia
AND loaddate = @loaddate_campaign
AND isAtendimento = 'Y'
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_notyed_atendimento b
(NOLOCK)
WHERE
a
.customerid = b.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120)
and a.billruleid = b.billruleid
and loaddate = @loaddate_notyet
)
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_faturadas_atendimento c
(NOLOCK)
WHERE
a
.customerid = c.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120)
and a.billruleid = c.billruleid
and loaddate = @loaddate_faturadas
)
GROUP
BY
customerid
,
smsaccountid
,
campaignid
,
isatendimento
,
convert(varchar(7), EstimatedBillingDate, 120)
ORDER
BY
1
SELECT
[ms faturamento], SUM([TOTAL CAMPANHAS]) AS TOTAL
FROM
##FATURAMENTOCAMPANHAS
WHERE
CAMPAIGNID IN (3606)
GROUP
BY [MS FATURAMENTO]
ORDER
BY [MS FATURAMENTO]
Retorno:
(14183086 row(s) affected)
(12611440 row(s) affected)
(1376470 row(s) affected)
(255754 row(s) affected)
(14 row(s) affected)
Msg 2714, Level 16, State 6, Line 136
There is already an object named '#fct_customer_campaign_1' in the database.
Pergunta
ricardo.bezerra
Boa tarde!
Estou tendo problemas para executar um bloco de códigos, mas não consigo entender realmente onde está o erro e muito menos corrigi-lo. Podem dar uma ajuda, por favor? porque na vdd ele até retorna informações e corretas, mas quando coloco ele no SSIS 2005 ele tá erro e não continua executando os demais blocos de código. Erro no SSIS: [Execute SQL Task] Error: Executing the query "IF(SELECT OBJECT_ID('TEMPDB..#transacoes_faturadas_atendimento')) IS NOT NULL DROP TABLE #transacoes_faturadas_atendimento SELECT * INTO #transacoes_faturadas_atendimento FROM datacare_ods..credit_transaction_billing (nolock) WHERE loaddate between '2013-07-14' and @loaddate_faturadas CREATE INDEX idx_bill ON #transacoes_faturadas_atendimento (BillRuleID) CREATE INDEX idx_cust ON #transacoes_faturadas_atendimento (CustomerID) CREATE INDEX idx_tra ON #transacoes_faturadas_atendimento (TransactionID) " failed with the following error: "Must declare the scalar variable "@loaddate_faturadas".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Segue script rodado no SQL 2005:
declare
@loaddate_campaign smalldatetime
declare
@loaddate_notyet smalldatetime
declare
@loaddate_faturadas smalldatetime
declare
@loaddate_percentual smalldatetime
declare
@reference_period char(7)
set
@loaddate_campaign = (select max (loaddate) from datacare_ods..credit_campaign_provisioning (NOLOCK))
set
@loaddate_notyet = (select max (loaddate) from datacare_ods..credit_transaction_notyet (NOLOCK))
set
@loaddate_faturadas = (select max (loaddate) from datacare_ods..credit_transaction_billing (NOLOCK))
set
@loaddate_percentual = (select max (loaddate) from datacare_ods..credit_campaign_percentage (NOLOCK))
set
@reference_period = '2013-08' -->alterar para mes de atualizao
IF
(SELECT OBJECT_ID('TEMPDB..#fct_customer_campaign')) IS NOT NULL
begin
DROP
TABLE #fct_customer_campaign end
SELECT
*
INTO
#fct_customer_campaign
FROM
datacare_ods
..credit_campaign_provisioning (NOLOCK)
WHERE
loaddate
= @loaddate_campaign
CREATE
INDEX idx_bill ON #fct_customer_campaign (BillRuleID)
CREATE
INDEX idx_cust ON #fct_customer_campaign (CustomerID)
CREATE
INDEX idx_camp ON #fct_customer_campaign (CampaignID)
IF
(SELECT OBJECT_ID('TEMPDB..#transacoes_faturadas_atendimento')) IS NOT NULL
begin
DROP
TABLE #transacoes_faturadas_atendimento end
SELECT
*
INTO
#transacoes_faturadas_atendimento
FROM
datacare_ods
..credit_transaction_billing (nolock)
WHERE
loaddate
between '2013-07-14' and @loaddate_faturadas
CREATE
INDEX idx_bill ON #transacoes_faturadas_atendimento (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_faturadas_atendimento (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_faturadas_atendimento (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..#transacoes_notyed_atendimento')) IS NOT NULL
begin
DROP
TABLE #transacoes_notyed_atendimento end
SELECT
*
INTO
#transacoes_notyed_atendimento
FROM
datacare_ods
..credit_transaction_notyet (NOLOCK)
WHERE
loaddate
= @loaddate_notyet
CREATE
INDEX idx_bill ON #transacoes_notyed_atendimento (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_notyed_atendimento (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_notyed_atendimento (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..#Telecinejunho2013Campaign')) IS NOT NULL
begin
DROP
TABLE #Telecinejunho2013Campaign end
SELECT
*
INTO
#Telecinejunho2013Campaign
FROM
#fct_customer_campaign a
WHERE
CampaignID
= '3606'
AND isAtendimento = 'Y'
and
exists (
SELECT
customerID
FROM
dbmii
.[directv\etsonoha].CampanhaProdutos20130621 b
WHERE
a
.CustomerID = b.CustomerID
and Oferta = 'Telecine 3'
and Falha = 'N'
)
SELECT
convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento]
,cast(sum(price) as money) as [Total Campanhas]
FROM
#fct_customer_campaign a
(NOLOCK)
WHERE
convert(varchar(7), EstimatedBillingDate, 120) >= @reference_period -- ms de referncia
AND loaddate = @loaddate_campaign
AND isAtendimento = 'Y'
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_notyed_atendimento b
(NOLOCK)
WHERE
a
.customerid = b.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120)
and a.billruleid = b.billruleid
and loaddate = @loaddate_notyet
)
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_faturadas_atendimento c
(NOLOCK)
WHERE
a
.customerid = c.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120)
and a.billruleid = c.billruleid
and loaddate = @loaddate_faturadas
)
GROUP
BY
convert(varchar(7), EstimatedBillingDate, 120)
ORDER
BY
1
IF
(SELECT OBJECT_ID('TEMPDB..SELECT #fct_customer_campaign_1 ')) IS NOT NULL
begin
DROP
TABLE #fct_customer_campaign_1
end
SELECT
*
INTO
#fct_customer_campaign_1
FROM
datacare_ods
..credit_campaign_provisioning (NOLOCK)
WHERE
loaddate
= @loaddate_campaign
CREATE
INDEX idx_bill ON #fct_customer_campaign_1 (BillRuleID)
CREATE
INDEX idx_cust ON #fct_customer_campaign_1 (CustomerID)
CREATE
INDEX idx_camp ON #fct_customer_campaign_1 (CampaignID)
IF
(SELECT OBJECT_ID('TEMPDB..SELECT #transacoes_faturadas_atendimento ')) IS NOT NULL
begin
DROP
TABLE #transacoes_faturadas_atendimento_1 end
SELECT
*
INTO
#transacoes_faturadas_atendimento_1
FROM
datacare_ods
..credit_transaction_billing (nolock)
WHERE
loaddate
between '2013-07-14' and @loaddate_faturadas
CREATE
INDEX idx_bill ON #transacoes_faturadas_atendimento_1 (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_faturadas_atendimento_1 (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_faturadas_atendimento_1 (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..SELECT #transacoes_notyed_atendimento ')) IS NOT NULL
begin
DROP
TABLE #transacoes_notyed_atendimento end
SELECT
*
INTO
#transacoes_notyed_atendimento_2
FROM
datacare_ods
..credit_transaction_notyet (NOLOCK)
WHERE
loaddate
= @loaddate_notyet
CREATE
INDEX idx_bill ON #transacoes_notyed_atendimento (BillRuleID)
CREATE
INDEX idx_cust ON #transacoes_notyed_atendimento (CustomerID)
CREATE
INDEX idx_tra ON #transacoes_notyed_atendimento (TransactionID)
IF
(SELECT OBJECT_ID('TEMPDB..SELECT ##FATURAMENTOCAMPANHAS ')) IS NOT NULL
begin
DROP
TABLE ##FATURAMENTOCAMPANHAS end
SELECT
customerid,
smsaccountid
,
campaignid
,
isatendimento
,
convert(varchar(7), EstimatedBillingDate, 120) as [Ms Faturamento]
,cast(sum(price) as money) as [Total Campanhas]
INTO
##FATURAMENTOCAMPANHAS
FROM
#fct_customer_campaign a
(NOLOCK)
WHERE
convert(varchar(7), EstimatedBillingDate, 120) >= @reference_period -- ms de referncia
AND loaddate = @loaddate_campaign
AND isAtendimento = 'Y'
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_notyed_atendimento b
(NOLOCK)
WHERE
a
.customerid = b.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), b.EstimatedBillingDate, 120)
and a.billruleid = b.billruleid
and loaddate = @loaddate_notyet
)
AND NOT EXISTS (
SELECT
1
FROM
#transacoes_faturadas_atendimento c
(NOLOCK)
WHERE
a
.customerid = c.customerid
and convert(varchar(7), a.EstimatedBillingDate, 120) = convert(varchar(7), c.BillingDate, 120)
and a.billruleid = c.billruleid
and loaddate = @loaddate_faturadas
)
GROUP
BY
customerid
,
smsaccountid
,
campaignid
,
isatendimento
,
convert(varchar(7), EstimatedBillingDate, 120)
ORDER
BY
1
SELECT
[ms faturamento], SUM([TOTAL CAMPANHAS]) AS TOTAL
FROM
##FATURAMENTOCAMPANHAS
WHERE
CAMPAIGNID IN (3606)
GROUP
BY [MS FATURAMENTO]
ORDER
BY [MS FATURAMENTO]
Retorno:
(14183086 row(s) affected)
(12611440 row(s) affected)
(1376470 row(s) affected)
(255754 row(s) affected)
(14 row(s) affected)
Msg 2714, Level 16, State 6, Line 136
There is already an object named '#fct_customer_campaign_1' in the database.
Grato desde já.
Att,
Ricardo.
Link para o comentário
Compartilhar em outros sites
1 resposta a esta questão
Posts Recomendados
Participe da discussão
Você pode postar agora e se registrar depois. Se você já tem uma conta, acesse agora para postar com sua conta.