ODBC--call failed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm having a query problem with linked tables in SQL Server 2005.
The query has two tables with the following SQL statement:

SELECT tblCheckStock.ChStPartID, tblCheckStock.MRBLogNo, tblMRBData.Problem
FROM tblCheckStock INNER JOIN tblMRBData ON tblCheckStock.MRBLogNo =
tblMRBData.MRBLogNo
GROUP BY tblCheckStock.ChStPartID, tblCheckStock.MRBLogNo, tblMRBData.Problem
HAVING (((tblCheckStock.ChStPartID)=[Forms]![frmCheckStock]![cboPartID]))
ORDER BY tblCheckStock.MRBLogNo DESC;

As is, I get "ODBC--call failed" error without any error number. If I take
out "GROUP BY" the query works fine.

I'm running XP with msjet40.dll version 4.0.8618.0

I've tried KB303257, but I'm unable to duplicate the problem (i.e. I have no
problem).

Any help is appreciated

Thanx!

Isbjornen
 
I was able to "create" an error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image
data types cannot be compared or sorted, except when using IS NULL or LIKE
operator. (#306)

I did not have this problem i SQL Server 7.0.
I'm using Access 2003

Isbjornen
 
Not sure why that should fail.

Have you tried the following, which should be equivalent to what you have
posted?

SELECT DISTINCT tblCheckStock.ChStPartID, tblCheckStock.MRBLogNo,
tblMRBData.Problem
FROM tblCheckStock INNER JOIN tblMRBData
ON tblCheckStock.MRBLogNo = tblMRBData.MRBLogNo
WHERE (((tblCheckStock.ChStPartID)=[Forms]![frmCheckStock]![cboPartID]))
ORDER BY tblCheckStock.MRBLogNo DESC;
 
Thanks, but it did not work. However, I found the solution:
tblMRBData.Problem is a Memo field and the SQL server does not GROUP BY on it
(GROUP BY Text fields are allowed though). BTW, Access has no problem with
GROUP BY on Memo fields. The solution for me was to sort/group as First
(i.e. FirstOfProblem)

Thank you for taking your time to look into it

John Spencer said:
Not sure why that should fail.

Have you tried the following, which should be equivalent to what you have
posted?

SELECT DISTINCT tblCheckStock.ChStPartID, tblCheckStock.MRBLogNo,
tblMRBData.Problem
FROM tblCheckStock INNER JOIN tblMRBData
ON tblCheckStock.MRBLogNo = tblMRBData.MRBLogNo
WHERE (((tblCheckStock.ChStPartID)=[Forms]![frmCheckStock]![cboPartID]))
ORDER BY tblCheckStock.MRBLogNo DESC;


isbjornen said:
Hello,

I'm having a query problem with linked tables in SQL Server 2005.
The query has two tables with the following SQL statement:

SELECT tblCheckStock.ChStPartID, tblCheckStock.MRBLogNo,
tblMRBData.Problem
FROM tblCheckStock INNER JOIN tblMRBData ON tblCheckStock.MRBLogNo =
tblMRBData.MRBLogNo
GROUP BY tblCheckStock.ChStPartID, tblCheckStock.MRBLogNo,
tblMRBData.Problem
HAVING (((tblCheckStock.ChStPartID)=[Forms]![frmCheckStock]![cboPartID]))
ORDER BY tblCheckStock.MRBLogNo DESC;

As is, I get "ODBC--call failed" error without any error number. If I
take
out "GROUP BY" the query works fine.

I'm running XP with msjet40.dll version 4.0.8618.0

I've tried KB303257, but I'm unable to duplicate the problem (i.e. I have
no
problem).

Any help is appreciated

Thanx!

Isbjornen
Click to expand...
Click to expand...
 
Back
Top