Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Question Unanswered: 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

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    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

    Quote Originally Posted by blindman
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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 section
    Code:
    DECLARE	@@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:
    Code:
    SET	@@totall = sum(Lines_Changed) from #CustomTable1
    Next, all of these lines and more...
    Code:
    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 statement
    Code:
    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
    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:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •