"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

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tweeks I can make right now will be of benefit.
Thanks
Steve ...
Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tweeks I can make right now will be of benefit.
> Thanks
> Steve ...
|||Thanks for reply,
Already done recommendations.
The only thing I can put it down to is an occasional recompile event because
of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
Some data requests will be far larger than others, and it appears that these
are the ones with occasional time-outs.
Does this sound plausible, and should I create Indexes on the #TempTables in
the procedure to get round this ?
Thanks
Steve ...
"Michael Rys [MSFT]" wrote:

> Doing the XML generation on the client-side may not be faster end-to-end
> than using the FOR XML EXPLICIT clause.
> Here are some questions:
> 1. You say that simply by copying and thus recompiling the stored proc, you
> are getting 6-8 sec execution? But if you run the existing stored proc it
> takes 3.5 mins?
> If you look at the query plan, do you see any difference between them?
> Can you force a recompile of the original stored proc?
> It is very unusual that a for xml explicit query runs that much worse
> compared to the same query without the FOR XML explicit clause. the overhead
> should be more in the 1 sec range for a 5 sec query.
> Best regards
> Michael
> "Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
> news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
>
>
|||For now I've added an option clause on each SQL Statement in the procedure.
(NB: no parallel processing so Maxdop not required)
OPTION(KEEP PLAN)
As the SProc builds #Temp Tables to use in the final query,
Would I be better off using the OPTION(KEEPFIXED PLAN) instead ?
Steve ...
"Steve [DBA-TC]" wrote:
[vbcol=seagreen]
> Thanks for reply,
> Already done recommendations.
> The only thing I can put it down to is an occasional recompile event because
> of Statistics on #TempTables (Event SubClass 2-Statistics Changed).
> Some data requests will be far larger than others, and it appears that these
> are the ones with occasional time-outs.
> Does this sound plausible, and should I create Indexes on the #TempTables in
> the procedure to get round this ?
> Thanks
> Steve ...
> "Michael Rys [MSFT]" wrote:

"FOR XML Explicit" why poor performance, and any "quick wins" ?

Just started a new job and need a quick fix if possible.
I need to enhance performance of XML data being returned from a SProc which
uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
Front End Application timeouts occasionally occur, which I believe can only
be caused by the SProc creating XML Data.
Without the "For XML Explicit" clause the DB returns about 5000 data rows in
approx 5 seconds.
With the "For XML Explicit" clause, same request will take 3.5 minutes !!
HOWEVER ... If I create copy of the procedure, and run the same request
parameters, it only takes about 6-8 seconds to complete.
No recompile going on as far a I can tell.
No more than 10 calls against this proc per minute.
There are several #Temp tables in the procedure.
I will improve performance by changing the whole process to return record
sets for the front end code to manipulate as required, which will take time
to integrate.
Any ideas on why this "apparent" performance issue happens ?
Any performance tws I can make right now will be of benefit.
Thanks
Steve ...Doing the XML generation on the client-side may not be faster end-to-end
than using the FOR XML EXPLICIT clause.
Here are some questions:
1. You say that simply by copying and thus recompiling the stored proc, you
are getting 6-8 sec execution? But if you run the existing stored proc it
takes 3.5 mins?
If you look at the query plan, do you see any difference between them?
Can you force a recompile of the original stored proc?
It is very unusual that a for xml explicit query runs that much worse
compared to the same query without the FOR XML explicit clause. the overhead
should be more in the 1 sec range for a 5 sec query.
Best regards
Michael
"Steve [DBA-TC]" <Steve [DBA-TC]@.discussions.microsoft.com> wrote in message
news:FD6648EA-CC33-44EA-B7B2-001FEA1139CE@.microsoft.com...
> Just started a new job and need a quick fix if possible.
> I need to enhance performance of XML data being returned from a SProc
> which
> uses "FOR XML EXPLICIT". (Not something I've done in 10 years as a DBA !)
> Front End Application timeouts occasionally occur, which I believe can
> only
> be caused by the SProc creating XML Data.
> Without the "For XML Explicit" clause the DB returns about 5000 data rows
> in
> approx 5 seconds.
> With the "For XML Explicit" clause, same request will take 3.5 minutes !!
> HOWEVER ... If I create copy of the procedure, and run the same request
> parameters, it only takes about 6-8 seconds to complete.
> No recompile going on as far a I can tell.
> No more than 10 calls against this proc per minute.
> There are several #Temp tables in the procedure.
> I will improve performance by changing the whole process to return record
> sets for the front end code to manipulate as required, which will take
> time
> to integrate.
> Any ideas on why this "apparent" performance issue happens ?
> Any performance tws I can make right now will be of benefit.
> Thanks
> Steve ...

"Concatenate Null Yields Null"

Thanks to the good graces of members I have found the COALESECE() FUNCTION,
and it should fix my problem but still, I'm confused.
I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
question and according to what I read in the Documents on Line for Sql Server
2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
Sadly it returns a NULL value. What gives'
--
Ed Warren> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
Current session settings override default settings specified at the database
or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> Thanks to the good graces of members I have found the COALESECE()
> FUNCTION,
> and it should fix my problem but still, I'm confused.
> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
> --
> Ed Warren|||Thanks for the enlightenment. That's what is happening. Now how does one
set the value for an (OLe DB session). From the caller or can this be done
on the server side (by setting options for the user', session). I must
admit I'm new to SQL server 2005 and have overlooked many of the 'gottchas'
Again, thanks.
--
Ed Warren
"Dan Guzman" wrote:
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> Current session settings override default settings specified at the database
> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
> connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> > Thanks to the good graces of members I have found the COALESECE()
> > FUNCTION,
> > and it should fix my problem but still, I'm confused.
> >
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> >
> > --
> > Ed Warren
>|||You'll need to set SET CONCAT_NULL_YIELDS_NULL OFF from the client
application after connecting using ODBC/OLE DB. It can be set on the server
side for the duration of a stored procedure execute by including the SET
statement in the proc. However, as you hinted in your original message, the
proper way to handle NULLs is with COALESCE, et. al.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:B59289CA-EA9A-413A-9290-9D12D2C772D0@.microsoft.com...
> Thanks for the enlightenment. That's what is happening. Now how does one
> set the value for an (OLe DB session). From the caller or can this be
> done
> on the server side (by setting options for the user', session). I must
> admit I'm new to SQL server 2005 and have overlooked many of the
> 'gottchas'
> Again, thanks.
> --
> Ed Warren
>
> "Dan Guzman" wrote:
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> Current session settings override default settings specified at the
>> database
>> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default
>> when
>> connecting via OLE DB or ODBC. This includes SQL Server Management
>> Studio.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
>> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
>> > Thanks to the good graces of members I have found the COALESECE()
>> > FUNCTION,
>> > and it should fix my problem but still, I'm confused.
>> >
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> >
>> > --
>> > Ed Warren

"Attempt to repair any minor problems." What is the point?

What is the point of having this checkbox on the Database Mainenance Plan
properties (Integrity tab) window?
For the plan to fix minor problems, it requires the db to be in Single User
Mode.
"[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode."
For the plan to execute it requires SQLAgent to be running.
For SqlAgent to be running, it requires NOT Single User Mode.
The message indicates that the *database* needs to be in single-user mode.
This should not be confused with the *server* being in single-user mode.
Personally, I prefer to fix errors manually rather than do this in a
maintenance plan.
Hope this helps.
Dan Guzman
SQL Server MVP
"Travis" <travis@.c*Y*C*l*Eutah.com> wrote in message
news:%23GMp%238tMEHA.1608@.TK2MSFTNGP12.phx.gbl...
> What is the point of having this checkbox on the Database Mainenance Plan
> properties (Integrity tab) window?
>
> For the plan to fix minor problems, it requires the db to be in Single
User
> Mode.
> "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode."
> For the plan to execute it requires SQLAgent to be running.
> For SqlAgent to be running, it requires NOT Single User Mode.
>
>

"Attempt to repair any minor problems." What is the point?

What is the point of having this checkbox on the Database Mainenance Plan
properties (Integrity tab) window?
For the plan to fix minor problems, it requires the db to be in Single User
Mode.
"[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode."
For the plan to execute it requires SQLAgent to be running.
For SqlAgent to be running, it requires NOT Single User Mode.The message indicates that the *database* needs to be in single-user mode.
This should not be confused with the *server* being in single-user mode.
Personally, I prefer to fix errors manually rather than do this in a
maintenance plan.
Hope this helps.
Dan Guzman
SQL Server MVP
"Travis" <travis@.c*Y*C*l*Eutah.com> wrote in message
news:%23GMp%238tMEHA.1608@.TK2MSFTNGP12.phx.gbl...
> What is the point of having this checkbox on the Database Mainenance Plan
> properties (Integrity tab) window?
>
> For the plan to fix minor problems, it requires the db to be in Single
User
> Mode.
> "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair stateme
nt not
> processed. Database needs to be in single user mode."
> For the plan to execute it requires SQLAgent to be running.
> For SqlAgent to be running, it requires NOT Single User Mode.
>
>

"Attempt to repair any minor problems." What is the point?

What is the point of having this checkbox on the Database Mainenance Plan
properties (Integrity tab) window?
For the plan to fix minor problems, it requires the db to be in Single User
Mode.
"[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode."
For the plan to execute it requires SQLAgent to be running.
For SqlAgent to be running, it requires NOT Single User Mode.The message indicates that the *database* needs to be in single-user mode.
This should not be confused with the *server* being in single-user mode.
Personally, I prefer to fix errors manually rather than do this in a
maintenance plan.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Travis" <travis@.c*Y*C*l*Eutah.com> wrote in message
news:%23GMp%238tMEHA.1608@.TK2MSFTNGP12.phx.gbl...
> What is the point of having this checkbox on the Database Mainenance Plan
> properties (Integrity tab) window?
>
> For the plan to fix minor problems, it requires the db to be in Single
User
> Mode.
> "[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode."
> For the plan to execute it requires SQLAgent to be running.
> For SqlAgent to be running, it requires NOT Single User Mode.
>
>|||I have done a maintenance plan and I have this error:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'Cluster instance' as 'User' (trusted)
Starting maintenance plan 'DB Maintenance Plan5' on 31/05/2004 18:59:00
[1] Database xxxxxx: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'AccessStatusMap'
......................
Rebuilding indexes for table 'TransactionPropertyMap'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.
=BFDo Anybody know, why is it the cause of this error?
Sincerely,
Ignacio
>--Original Message--
>What is the point of having this checkbox on the Database Mainenance Plan
>properties (Integrity tab) window?
>
>For the plan to fix minor problems, it requires the db to be in Single User
>Mode.
>"[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
>processed. Database needs to be in single user mode."
>For the plan to execute it requires SQLAgent to be running.
>For SqlAgent to be running, it requires NOT Single User Mode.
>
>.
>|||My guess is you have some invalid float data in the table/index. Turn on
trace flag 2570 and run DBCC CHECKTABLE on the table in question. 2570
switches on some extra data range value checking of things like floats and
dates. FYI: this functionality is controlled by documented T-SQL in SQL
Server 2005 rather than a trace flag.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ignacio" <anonymous@.discussions.microsoft.com> wrote in message
news:1bfe01c44737$1dc83550$3501280a@.phx.gbl...
I have done a maintenance plan and I have this error:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'Cluster instance' as 'User' (trusted)
Starting maintenance plan 'DB Maintenance Plan5' on
31/05/2004 18:59:00
[1] Database xxxxxx: Index Rebuild (leaving 10%% free
space)...
Rebuilding indexes for table 'AccessStatusMap'
......................
Rebuilding indexes for table 'TransactionPropertyMap'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628:
[Microsoft][ODBC SQL Server Driver][SQL Server]A floating
point exception occurred in the user process. Current
transaction is canceled.
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
execution completed. If DBCC printed error messages,
contact your system administrator.
¿Do Anybody know, why is it the cause of this error?
Sincerely,
Ignacio
>--Original Message--
>What is the point of having this checkbox on the Database
Mainenance Plan
>properties (Integrity tab) window?
>
>For the plan to fix minor problems, it requires the db to
be in Single User
>Mode.
>"[Microsoft][ODBC SQL Server Driver][SQL Server]Repair
statement not
>processed. Database needs to be in single user mode."
>For the plan to execute it requires SQLAgent to be
running.
>For SqlAgent to be running, it requires NOT Single User
Mode.
>
>.
>