|
|
Posted by aaron.kempf@gmail.com on 10/24/06 00:18
Bob;
I love ADP.. I use them every single day
all views are 'just select statements'
My gut says that you have simple cartesianing
I would change from LEFT OUTER JOIN to INNER JOIN and make sure that
the results you get are similar to what you want.
and then I would evaluate whether you are missing another join
statement.
BTW; MDB sucks balls spit on anyone that uses it for anything.
> SELECT codes.proj_id, answers.report_date,
> answers.release_date, answers.notes,
> answers.answer_date, answers.answer_person,
> answers.answer, answers.answer_status,
> answers.answer_person2, codes.cust_id
> FROM dbo.codes LEFT OUTER JOIN
> dbo.demos ON
> dbo.codes.code = dbo.demos.code LEFT OUTER JOIN
> dbo.answers ON dbo.codes.code = dbo.answers.code
Bob wrote:
> I'm running sql server ver 7.0 SP4.
>
> I have an access project (.adp) that runs a view which is nothing more
> than a select statement. Access locks up solid when I try to run this
> query - with NO error messages what-so-ever.
>
> If I sign onto the server, and run the view from enterprise manager - I
> get:
> "the data provider or other service returned an E_FAIL status" !?!?!
> There is NO ERROR number, or further explanation of the message!!
>
> Now - this query DID work last week - all of a sudden I get this error
> - I've:
> 1) repaired / compacted this database
> 2) rebooted the server
> 3) spent several hours searching websites & groups for an explanation
> a) I don't care how useless some people think enterprise manager is -
> at least it gave me SOME kind of error; where access just locks up!
> b) There are no issues with null values, or problem date fields
> anywhere in these tables.
> 4) tried MANY variations on the query, and am now more confused than
> ever.
> 5) I've RUN THIS QUERY IN AN ACCESS MDB WITH LINKS TO THE SQL SERVER -
> AND IT WORKS!?!?!?!?
>
> The query I'm using follows; if I remove ONE field from the select
> statement - this query RUNS!!!
> It doesn't matter what field I remove! It's as if, I can only have so
> many fields in the select statement!?!?!?
> This is clearly ridiculous since the query ran fine last week with the
> same number of fields, not that
> that should have anything to do with this, as sql shouldn't care how
> many fields I request.
> I'm not actually requesting any fields in the last table - but if I
> remove this table it works!? If I leave this table, and select fields
> from it, but remove another table - again it works!
>
> So my questions:
> 1) What [T-] is going on here?
> 2) Why would an mdb be able to run a query that an adp can not!?!?
> 3) When will microsoft ever release a product that provides useful
> error messages?
>
> I really don't expect an answer to #3, because I know the answer is
> NEVER - microsoft doesn't want to make solving problems easy.
>
> Here's the query I'm using:
>
> SELECT codes.proj_id, answers.report_date,
> answers.release_date, answers.notes,
> answers.answer_date, answers.answer_person,
> answers.answer, answers.answer_status,
> answers.answer_person2, codes.cust_id
> FROM dbo.codes LEFT OUTER JOIN
> dbo.demos ON
> dbo.codes.code = dbo.demos.code LEFT OUTER JOIN
> dbo.answers ON dbo.codes.code = dbo.answers.code
>
>
> any help appreciated - TIA
> Bob
Navigation:
[Reply to this message]
|