Proc Results To Excel Workbook

Hello,

I have tried to use a DaraReader Source to execute a SQL Server 2000 proc, and send the results to an Excel Destination.

The proc is named spv_CDGetFuelManDuplicates. Within the proc, temporary tables are created and dropped. When I attempt to set 'Exec dbo.spv_CDGetFuelManDuplicates' for the SqlCommand property of the DataReader, I get an error message indicating that my temporary tables are invalid objects.

The DaraReader is using an ADO.NET Connection Manager.

Should I be using an Execute SQL Task instead?

Thank you for your help, the proc code is below.

cdun2

********************************

alter PROCEDURE dbo.spv_CDGetFuelManDuplicates

AS

--Isolate Accounts and Contact Info per the View criteria

SELECT

RTRIM(Acctcode) AS AcctCode,

RTRIM(CompanyName)AS CompanyName,

MMS#, RTRIM(Phone) AS Phone

INTO #CorrectAcctCodes

FROM dbo.DailyAccountsDownload

WHERE

(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR

(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR

(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR

(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')

--Isolate duplicate phone numbers

--per the view criteria

SELECT

RTRIM(Phone)AS Phone, COUNT(*) AS RecordCount

INTO

#DupPhoneNumbers

FROM

dbo.DailyAccountsDownload

WHERE

(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR

(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR

(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR

(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')

GROUP BY

RTRIM(Phone)

HAVING

(COUNT(*)>1)

--Isolate duplicate Company Names

--per the View criteria

SELECT

RTRIM(CompanyName)AS CompanyName,

COUNT(*) AS RecordCount

INTO

#DupCompanyName

FROM

dbo.DailyAccountsDownload

WHERE

(AcctCode BETWEEN 'IQ001' AND 'IQ999') OR

(AcctCode BETWEEN 'QI001' AND 'QI999') AND (AcctCode NOT IN ('QI125', 'QI601', 'QI603', 'QI613', 'QI623', 'QI653', 'QI697')) OR

(AcctCode BETWEEN 'DQ001' AND 'DQ999') AND (AcctCode NOT IN ('DQ011')) OR

(AcctCode BETWEEN 'FQ002' AND 'FQ999') AND (AcctCode <> 'FQ011')

GROUP BY

RTRIM(CompanyName)

HAVING

(COUNT(*)>1)

--Result with duplicate company or phone numbers

SELECT

Acctcode, CompanyName, MMS#, Phone

FROM

#CorrectAcctCodes

WHERE

Phone IN (SELECT Phone FROM #DupPhoneNumbers)

OR

CompanyName IN (SELECT CompanyName FROM #DupCompanyName)

ORDER BY

Phone, companyname

--Drop the temp tables

DROP TABLE #CorrectAcctCodes

DROP TABLE #DupCompanyName

DROP TABLE #DupPhoneNumbers