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..."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?|||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.|||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.|||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|||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?|||DBO owns everything in the system.
orders.datecompleted is of type datetime.
Showing posts with label behavior. Show all posts
Showing posts with label behavior. Show all posts
Sunday, March 11, 2012
Subscribe to:
Posts (Atom)