ODBC--call failed

ODBC--call failed

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

date: Mon, 20 Mar 2006 13:03:29 -0800 author: isbjornen

RE: ODBC--call failed

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

"isbjornen" wrote:

> 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
>

date: Mon, 20 Mar 2006 14:39:18 -0800 author: isbjornen

Re: ODBC--call failed

"isbjornen"  wrote in message
news:FA98BA0B-7E3D-4228-945C-F8A246E9C856@microsoft.com...
> 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
>
> "isbjornen" wrote:
>
> > 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
> >

Why are you using a GROUP BY clause without an aggregate function?   Surely
the following would give you exactly the same results, without the error:

SELECT 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;

date: Sat, 22 Apr 2006 11:16:25 +0100 author: Baz com

Re: ODBC--call failed

By default, you only see the last/top ODBC error message.

To see the other messages, you can use code like this:

for each obj in dao.errors
    msgbox obj.description
next obj

If you are testing from the database window, press Ctrl G to
get to the immediate window, the past in and run this line:

 for each o in dao.Errors: ?o.description : next

(david)

"isbjornen"  wrote in message
news:9FC2E57C-3757-4195-A291-19E9B74F28EC@microsoft.com...
> 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
>

date: Sun, 23 Apr 2006 13:43:26 +1000 author: david@epsomdotcomdotau

Re: ODBC--call failed

In case anyone is interested or wondering, I found this error to be
pretty simple to beat. 

First, I got this error after I upsized my Access 2003 db to SQL 2000
server.  I used linked tables in Access so I could use my queries and
forms as they were.  Unfortunately they didnt work.  After poking
around on forums like this to understand the error.  I was not seeing
the specific parameters I was facing with my setup.  
I upsized again, and created an "ADP" application db that is connected
to the same SQL server I originally upsized to when i used linked
tables.  In this ADP database I noticed that very few of my queries
transfered.  further digging revealed that the culprit to my original
error and the reason my queries died in the transfer, was the type of
table field definition i used.  
IN the queries that didnt make it, I had some fields defined as Memo -
apparently SQL doesnt like this when using to create "grouped queries".
SO I went back into the "ADP" database changed the fields to "text" in
access terms, or in SQL temrs "nvarchar".  THis resolved my current
problem.  In order to accomodate the size of the text in the field, I
increased to 100 from 50 chr size.

Its not much, but it worked for me.



--
Hotcupajo
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message2213549.html

date: Fri, 5 May 2006 10:31:15 -0500 author: Hotcupajo