Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    6

    Unanswered: Curious DTSWizard behavior

    Can someone try and explain to me how a sql query can run fine and return data, yet when I try to run it in dtswizard, while the "preview" view shows the data, actually running it returns zero rows?

    This is on SQL2005 express and since I can't get dtsrun or dtexec to work, I'm using auto-it to simulate my actually stepping through the process. Very kludgy, but "when all you've got is a hammer...."

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Very difficult to debug a DTS issue without looking over your shoulder. More details on the query might help. Are you getting any error messages? Does the target object exist under different ownerships?
    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 2007
    Posts
    6
    I can post the query here if that would help, I just find it odd that the preview shows me data while the actual process does not. If I didn't make myself clear, I'm just running the dtswizard to create an Excel file and not actually saving the dts package since I have no way of running the package.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't promise that posting the query will help, but I can tell you that you will not get a lot of responses on this forum unless you do post the query.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Mar 2007
    Posts
    6
    Fair enough. Here it is...

    use Hayesonline


    SELECT
    o.name
    , Coalesce(Sum(CASE WHEN 1 = d THEN 1 END), 0) AS [01] -- Show explicit zero in first column
    , Sum(CASE WHEN 2 = d THEN 1 END) AS [02]
    , Sum(CASE WHEN 3 = d THEN 1 END) AS [03]
    , Sum(CASE WHEN 4 = d THEN 1 END) AS [04]
    , Sum(CASE WHEN 5 = d THEN 1 END) AS [05]
    , Sum(CASE WHEN 6 = d THEN 1 END) AS [06]
    , Sum(CASE WHEN 7 = d THEN 1 END) AS [07]
    , Sum(CASE WHEN 8 = d THEN 1 END) AS [08]
    , Sum(CASE WHEN 9 = d THEN 1 END) AS [09]
    , Sum(CASE WHEN 10 = d THEN 1 END) AS [10]
    , Sum(CASE WHEN 11 = d THEN 1 END) AS [11]
    , Sum(CASE WHEN 12 = d THEN 1 END) AS [12]
    , Sum(CASE WHEN 13 = d THEN 1 END) AS [13]
    , Sum(CASE WHEN 14 = d THEN 1 END) AS [14]
    , Sum(CASE WHEN 15 = d THEN 1 END) AS [15]
    , Sum(CASE WHEN 16 = d THEN 1 END) AS [16]
    , Sum(CASE WHEN 17 = d THEN 1 END) AS [17]
    , Sum(CASE WHEN 18 = d THEN 1 END) AS [18]
    , Sum(CASE WHEN 19 = d THEN 1 END) AS [19]
    , Sum(CASE WHEN 20 = d THEN 1 END) AS [20]
    , Sum(CASE WHEN 21 = d THEN 1 END) AS [21]
    , Sum(CASE WHEN 22 = d THEN 1 END) AS [22]
    , Sum(CASE WHEN 23 = d THEN 1 END) AS [23]
    , Sum(CASE WHEN 24 = d THEN 1 END) AS [24]
    , Sum(CASE WHEN 25 = d THEN 1 END) AS [25]
    , Sum(CASE WHEN 26 = d THEN 1 END) AS [26]
    , Sum(CASE WHEN 27 = d THEN 1 END) AS [27]
    , Sum(CASE WHEN 28 = d THEN 1 END) AS [28]
    , Sum(CASE WHEN 29 = d THEN 1 END) AS [29]
    , Sum(CASE WHEN 30 = d THEN 1 END) AS [30]
    , Sum(CASE WHEN 31 = d THEN 1 END) AS [31]
    FROM dbo.Offices AS o
    LEFT JOIN (SELECT a.officeID
    , DatePart(d, o.DateCompleted) AS d
    FROM dbo.Orders AS o
    JOIN dbo.Appraisers AS a
    ON (o.AppraiserID = a.AppraiserID)
    WHERE Convert(CHAR(8), GetDate(), 121) + '01' <= o.dateCompleted
    AND o.DateCompleted < DateAdd(month, 1, Convert(CHAR(8), GetDate(), 121) + '01')
    AND 1 = o.StatusID) AS z
    ON (z.officeID = o.officeID)
    GROUP BY o.name
    ORDER BY o.name ASC

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are there any other tables named Offices, Orders, or Appraisers, under ownerhips other than DBO?

    What is the datatype of the Orders.dateCompleted column? Is it datetime or is it varchar?
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Mar 2007
    Posts
    6
    DBO owns everything in the system.

    orders.datecompleted is of type datetime.

Posting Permissions

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