DTS Export to Excel

I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.

In the Transform Data Task Properties:

EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'

It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)

When I perform the same task using DTS Export utility, i get the following error:

Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS

Please Help

ThanksPost the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||Here goes the Stored Procedure

CREATE PROCEDURE sp_ProductivityReport_ByMonth

@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

DECLARE @.@.total1 decimal(20,2)
DECLARE @.@.total2 decimal(20,2)
set @.@.total1 = 0
set @.@.total2 = 0

select B.T_FName as "First_Name", B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"

into #CustomTable1
from dbo.BillingInfo A (nolock), dbo.TInfo B (nolock)
where
A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY
A.T_ID, B.T_FName, B.T_LName

DECLARE Total_Cursor CURSOR Local
FOR SELECT Lines_Changed FROM #CustomTable1
OPEN Total_Cursor
FETCH next From Total_Cursor
INTO @.@.total2
while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor
INTO @.@.total2
END
CLOSE Total_Cursor
DEALLOCATE Total_Cursor

/**** Calculating Total*******/
DECLARE @.Reports_Total int
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1

DECLARE @.T_M_Total decimal(15,2)
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1

select "xxx - Total" as First_Name, ' ' as Last_Name, @.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed, @.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2

set nocount on

SELECT First_Name, Last_Name,Reports_Changed,
Minutes_Changed, Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.@.total1) AS decimal(5,2))) + '%'
as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT * FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

Post the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||You really need to learn more about TSQL, and principles of database application design before attempting something like this. Why? Because you are taking the wrong approach to solving a problem that, really, you should not be trying to solve with SQL in the first place.

Lets begin...

Dump the cursor and learn how to write set-based logic. This entire sectionDECLARE @.@.total2 decimal(20,2)
set @.@.total2 = 0
.
.
.
DECLARE Total_Cursor CURSOR Local FOR
SELECT Lines_Changed
FROM #CustomTable1

OPEN Total_Cursor
FETCH next From Total_Cursor INTO @.@.total2

while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor INTO @.@.total2
END

CLOSE Total_Cursor
DEALLOCATE Total_Cursor...can be replace by one line:SET @.@.totall = sum(Lines_Changed) from #CustomTable1

Next, all of these lines and more...SET @.@.totall = sum(Line_Changed) from #CustomTable1
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1
.
.
.
select "xxx - Total" as First_Name,
' ' as Last_Name,
@.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed,
@.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
...can be run as a single statementselect "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

Then, I have to ask, what is with the use of the double ampersands?

Lastly, it poor programming practice to be calculating subtotals to a report within SQL, as you are doing with CustomTable2. This is best handled by whatever reporting application you are using. Not because of a limitation within SQL, but because you are essentially mixing record types (raw and total) within a single dataset. Bad form.

So, re-read the Books Online sections on SELECT statements and aggregate queries. And if you find yourself using cursors again, be confident you are doing something wrong because you probably are.

But for the heck of it, go ahead and try this shortened code:CREATE PROCEDURE sp_ProductivityReport_ByMonth
@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

select B.T_FName as "First_Name",
B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"
into #CustomTable1
from dbo.BillingInfo A (nolock),
dbo.TInfo B (nolock)
where A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY A.T_ID,
B.T_FName,
B.T_LName

/**** Calculating Total*******/
select "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

SELECT First_Name,
Last_Name,
Reports_Changed,
Minutes_Changed,
Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.total1) AS decimal(5,2))) + '%' as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT *
FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO