sqlstate 22003: Numeric value out of range
shock
I am using a powerbuilder application. Recently changes have been made
to some queries to support sql server. For one query I get the error:
Select Error: SQLSTATE = 22003
[Microsoft][ODBC SQL Server Driver]Numeric value out of range
Executing the query in sql server management studio doesn't give
problems. Using the retrieve in the datawindow designer works too. But
since a recent change to the connection string the query doesn't work
anymore and gives the above error.
code for the connectionstring:
ls_connect="ConnectString='"+&
"Driver="+ls_driver+";"+&
"Server="+ls_eng+";"+&
"Database="+ls_dbf+";"+&
"APP="+wf_as_Connect_Id()+";"+&
"autostop=Yes'"+&
",DisableBind=1,ConnectOption=DelimitIdentifier='No',OJSyntax='ANSI',
Secure=1"
recently the DisableBind property changed from 0 to 1 to support
unicode. This caused the query to fail, it worked without problems
before.
Anyone have any ideas?
thanks
Scott Morris
>
> recently the DisableBind property changed from 0 to 1 to support
> unicode. This caused the query to fail, it worked without problems
> before.
First, you should always specify which version and build of PB you are
using. For topics that might be dbms-related, you should include the same
for your server/db as well as the values you set for sqlca. Posting code
that generates a string (as you did above) doesn't help much since we don't
know what values are contained in your variables or are returned from your
functions. In addition, we don't know how you are using ls_connect once it
is generated.
When you are trying to diagnose an specific dbms error, it is good to see
the complete and actual error message. You did that quite well.
Unfortunately, you did not post the statement that was causing this. It
would also be helpful to know how you are executing this statement when the
error occurs - dynamic sql, datawindow retrieval, datawindow update, etc.?
Lastly, it appears that a simple connection setting change has caused the
problem. Is this correct? This isn't a "was migrating and saw an issue
somewhere which seemd to be something that I might run into and therefore I
implemented this suggestion without really knowing if I need to" situation,
is it? If not, can you tell us why you implemented this change? I've
usually seen the opposite suggestion in relation to unicode issues.
shock
disablebind from connection was changed from 1 to 0, sorry I typed
wrong
PB version 11.5.3050
query:
SELECT bci_answer.nr_system,
bci_answer.nr_bci_ans,
bci_answer.nm_bci_ans,
bci_answer.typ_bci_ari,
'1234567890;12345' cc_pk_answer,
(select bci_answer_tra.nm_anstrans from bci_answer_tra where
bci_answer_tra.nr_bci_ans = bci_answer.nr_bci_ans and
bci_answer_tra.nr_system = bci_answer.nr_system and
bci_answer_tra.cod_lang = ?) cc_answer_tra
FROM bci_answer
where bci_answer.nr_system = ?
and exists ( select 1
from bci_operand
where bci_operand.nr_bci_ans =
bci_answer.nr_bci_ans
and bci_operand.nr_system_an =
bci_answer.nr_system )
the retrieve problem comes from the subquery in the select
executing the query in sql server studio directly works fine.
I've heavily reworked the query and removed the subquery and it
retrieves succesfully now
shock
SELECT crr_report_definition.tnr_crr_report ,
crr_report_definition.typ_crr_report ,
crr_report_definition.des_crr_description ,
crr_report_definition.nm_crr_filename ,
(select des_crr_description from crr_reportdef_tra where
crr_reportdef_tra.tnr_crr_report =
crr_report_definition.tnr_crr_report and crr_reportdef_tra.cod_lang =
'EN') des_crr_description,
crr_report_definition.des_crr_criteria
FROM crr_report_definition
WHERE ( crr_report_definition.typ_crr_report = 1 )
Select Error: SQLSTATE = 22005
[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when
converting the nvarchar value '1.0' to data type int.
It also worked fine when the old connectionstring where disablebind=1
Scott Morris
news:8051a222-32a9-4b2f...@l2g2000yqd.googlegroups.com...
Is it correct to assume that you are forced to generate these queries in
syntax mode? If so, a couple of suggestions. First, it is a best practice
and highly recommended to qualify database objects (tables in this case)
with the owner. For readability, it is easier to include a nice, short
alias for each table and use it instead of your rather cryptic table names.
This makes it much easier to associate columns with tables, especially when
tables have similar wordy names.
As for your error, the quick assumption is that the problem lies with your
retrieval arguments. So, where exactly in the query are they? What are
their datatypes as defined in the datawindow? What are the datatypes of the
columns against which they are compared? It may help to simply compare the
statements generated with disablebind on and off to help highlight the
problem. This can be done by tracing your connection (prepend "TRA" or
"TRACE" to the value you use for dbms, e.g., "TRACE ODB").
As an alternative, you can convert your query from using a subquery to a
simple join (outer join if needed).
SELECT rptdef.tnr_crr_report , ...
abc.des_crr_description,
rptdef.des_crr_criteria
from dbo.crr_report_definition as rptdef
left join dbo.crr_reportdef_tra as abc -- replace this with something
meaningful
on rptdef.tnr_crr_report = abc.tnr_crr_report
and crr_reportdef_tra.cod_lang = 'EN'
Since you can't use retrieval arguments as part of the join criteria (using
ansi-style joins), you will still need to define the query in syntax mode -
which you are already doing - if you want to "properly" join the tables.
You could also join on tnr_crr_report alone and put the "EN" criteria in the
where clause. This would allow you to use graphic mode - either should
generate the same query plan (which you should verify before committing).
While you are researching, the following script demonstrates one method of
reproducing the error. Your cause may be different, but it may help you
find the problem. For whatever reason, sql server can easily convert "1" to
an int, but cannot convert "1.0" to an int. I suspect the query you posted
may not be the **exact** query that is executed.
set nocount on;
declare @test nvarchar(5)
set @test = N'1.0'
select @test, cast(@test as int)
go
declare @test nvarchar(5)
set @test = N'1'
select @test, cast(@test as int)
go
Scott Morris
> '1234567890;12345' cc_pk_answer,
It would be best (and safest) to cast this constant to the appropriate
datatype, rather than simply assume that the dbms engine will always choose
the same datatype (and will do so in a manner consistent with the choice PB
made when you last updated the datawindow source).
> I've heavily reworked the query and removed the subquery and it
> retrieves succesfully now
That's the key. Avoid subqueries that require retrieval arguments. Another
choice is to use a stored procedure.