"the data provider or other service returned an E_FAIL status"!?!?! WT-???

[Bob]

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

[aaron.kempf]

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

[Bob]

Aaron-

TX much for your reply!

interesting suggestion - I didn't think of that - I'll give it a try.

as for mdb ball sucking - you're preaching to the choir!!!
I had simply used up everything else I could think of, and decided to
try it diagnostically - with surprising results!

aa*********@gmail.com wrote:

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