Results 1 to 7 of 7
Thread: Curious DTSWizard behavior

041207, 09:31 #1Registered User
 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 autoit to simulate my actually stepping through the process. Very kludgy, but "when all you've got is a hammer...."

041207, 09:39 #2World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,592
Provided Answers: 1Very 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

041207, 09:56 #3Registered User
 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.

041207, 11:51 #4World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,592
Provided Answers: 1I 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

041207, 12:08 #5Registered User
 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

041207, 13:58 #6World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,592
Provided Answers: 1Are 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

041207, 14:03 #7Registered User
 Join Date
 Mar 2007
 Posts
 6
DBO owns everything in the system.
orders.datecompleted is of type datetime.