"ODBCcall failed" Error

Hi!
It has been weeks now that I have been trying to fix an error that I
encounter everytime it gets to the code at runtime:

SQLStmt = "SELECT Sum([Quantity in Stock]) As TotalInStock,
Sum([Cost] * [Quantity in Stock]) AS TotalCost " & _
"FROM [Inventory Products] WHERE [Item
Number] = '" & ItemNum & "'"

Set InvP = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET) '
==>it gets the error here

I tried also to implement the same code using ADODB but still go the same
error: "ODBC--call failed".
There are other events where similar code is executed without any problems
at all.
Any guidance would be helpful.

ThanksBen,

The error is not very intuitive. You might want to try a SQL Trace and an
ODBC trace to get more information. Profiler has an Errors and Warnings /
Exception event you can trace, and the following link will show you how to
set up an ODBC trace:

http://support.microsoft.com/kb/274551
-- Bill

"Ben" <pillars4@.sbcglobal.netwrote in message
news:C7zxh.71351$qO4.32683@.newssvr13.news.prodigy. net...

Quote:

Originally Posted by

Hi!
It has been weeks now that I have been trying to fix an error that I
encounter everytime it gets to the code at runtime:
>
SQLStmt = "SELECT Sum([Quantity in Stock]) As TotalInStock,
Sum([Cost] * [Quantity in Stock]) AS TotalCost " & _
"FROM [Inventory Products] WHERE [Item
Number] = '" & ItemNum & "'"
>
Set InvP = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
' ==>it gets the error here
>
I tried also to implement the same code using ADODB but still go the same
error: "ODBC--call failed".
There are other events where similar code is executed without any problems
at all.
Any guidance would be helpful.
>
>
Thanks
>

|||Ben (pillars4@.sbcglobal.net) writes:

Quote:

Originally Posted by

It has been weeks now that I have been trying to fix an error that I
encounter everytime it gets to the code at runtime:
>
SQLStmt = "SELECT Sum([Quantity in Stock]) As TotalInStock,
Sum([Cost] * [Quantity in Stock]) AS TotalCost " & _
"FROM [Inventory Products] WHERE [Item
Number] = '" & ItemNum & "'"
>
Set InvP = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET) '
>==>it gets the error here
>
I tried also to implement the same code using ADODB but still go the same
error: "ODBC--call failed".
There are other events where similar code is executed without any problems
at all.

It would be interesting to see a little more of the code. How you set
up the command and so. Particularly when you do it with ADO. (Since I know
ADO better this other thing (DAO?)).

Have you extracted what is in SQLStmt an tried to run that in Query
Analyzer? Maybe there is some simple error?

Also, an advice on how you get ItemNum into the query string. Most APIs
support parameterised commands, for instance ADO does. Parameterised
is much simpler to use than interpolated strings, and it protexts you
against SQL Injection. It also uses the query cache in SQL Server better.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

"Load report failed"

Everytime after I'm printing a report or exporting one and I try to open the report again I'm getting an exception "Load report failed".
Any ideas why?Just an idea, but have you downloaded all of the hotfixes, and SP's for the version you are using.

GJ|||what is the os that you are using and what is the version of crystal reports?|||i just downloaded the hotfix and it still happens...
i'm using cr 9.0 build-in visual studio 2003
and have an xp pro.

Something seriously wrong

We have one database that is actually quite small, about 1.8GB. Upon moving
this db to SQL2K5 we get an error everytime we attempt to view the "Views"
folder. The error is:
Value cannot be null.
Parameter name: context (ObjectExplorer)
No more detail is available. I've gone back to the source database, removed
all views and repeated the process. Even with no views in the database we
get the same error when clicking on the "Views" folder in SQL Managment
Studio. All other db's have migrated fine.
NOW I figured I'd use the compare utility we have (We use Red Gates SQL
Compare) to compare the schema from that database to another server with an
old copy. This worked a few weeks ago, now it gives the error "Specified
cast is invalid" which sounds very likely to be related. This happens on
all servers running a copy of this database regardless of SQL Server
version. So at some point something bad was introduced. I'm just not sure
where to look, it sounds metadata related possibly but I'd hope there's some
other way of determining this other than going to a 6 week old backup and
hoping the issue isn't there.
Any help is greeted with humble thanks...we're really stuck.Tim,
Did you run the Upgrade Advisor on the DB before the move to 2005? This
might tell you something that does not migrate.
Chris Wood
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uLgna8WaGHA.504@.TK2MSFTNGP03.phx.gbl...
> We have one database that is actually quite small, about 1.8GB. Upon
> moving this db to SQL2K5 we get an error everytime we attempt to view the
> "Views" folder. The error is:
> Value cannot be null.
> Parameter name: context (ObjectExplorer)
> No more detail is available. I've gone back to the source database,
> removed all views and repeated the process. Even with no views in the
> database we get the same error when clicking on the "Views" folder in SQL
> Managment Studio. All other db's have migrated fine.
> NOW I figured I'd use the compare utility we have (We use Red Gates SQL
> Compare) to compare the schema from that database to another server with
> an old copy. This worked a few weeks ago, now it gives the error
> "Specified cast is invalid" which sounds very likely to be related. This
> happens on all servers running a copy of this database regardless of SQL
> Server version. So at some point something bad was introduced. I'm just
> not sure where to look, it sounds metadata related possibly but I'd hope
> there's some other way of determining this other than going to a 6 week
> old backup and hoping the issue isn't there.
> Any help is greeted with humble thanks...we're really stuck.
>|||Yes I did. It only flagged a couple of sprocs with a syntax change (using
aliases in the order by clause)....
I might not have been clear. We encountered the issue during migration, BUT
then going back to our SQL 2000 servers currently in production this error
was encountered when using a specific tool. So, the issue is present BEFORE
migration.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eZTYegXaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> Tim,
> Did you run the Upgrade Advisor on the DB before the move to 2005? This
> might tell you something that does not migrate.
> Chris Wood
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uLgna8WaGHA.504@.TK2MSFTNGP03.phx.gbl...
>> We have one database that is actually quite small, about 1.8GB. Upon
>> moving this db to SQL2K5 we get an error everytime we attempt to view the
>> "Views" folder. The error is:
>> Value cannot be null.
>> Parameter name: context (ObjectExplorer)
>> No more detail is available. I've gone back to the source database,
>> removed all views and repeated the process. Even with no views in the
>> database we get the same error when clicking on the "Views" folder in SQL
>> Managment Studio. All other db's have migrated fine.
>> NOW I figured I'd use the compare utility we have (We use Red Gates SQL
>> Compare) to compare the schema from that database to another server with
>> an old copy. This worked a few weeks ago, now it gives the error
>> "Specified cast is invalid" which sounds very likely to be related. This
>> happens on all servers running a copy of this database regardless of SQL
>> Server version. So at some point something bad was introduced. I'm just
>> not sure where to look, it sounds metadata related possibly but I'd hope
>> there's some other way of determining this other than going to a 6 week
>> old backup and hoping the issue isn't there.
>> Any help is greeted with humble thanks...we're really stuck.
>