Hi,
I had a problem for which I have a work around but would like to know if it can besolved in the right way.
I have a query with a Datetime parameter. My TMSConnection component uses the prAuto protocol.
When it's been executed on Windows Server 2008 64 bit with the Microsoft SQL Native Client I recieve the error: "Operand type clash: datetime2 is incompatible with tinyint".
I've upgraded to the last SDAC version but that did'nt solve it.
What I actually did to work around this is set the protocol property of the TMSConnection component to prSQL but in the future I would like te be able to use the NativeClient whenever it is availlable on the client system.
Is anyone familiar with this problem or does anyone know what to do?
Kind regards,
Frans van Meir
Datetime parameter problem on 64 bits OS using NativeClient
-
AndreyZ
Hello Andrey,
this is the SQL statement:
select
K.OrderNr
from
OrderKop K inner join Ordertype T on T.Ordertype = K.Ordertype
where
(K.AfleverDebNr = :AfleverDebNr) and
((:Leverdatum = 0) or (K.Leverdatum = :Leverdatum)) and
not (K.Gefaktureerd = 1) and
not (K.Uitgeleverd = 1) and
T.UitTeLeveren = 1
order by
K.OrderNr desc
The parameter Leverdatum (= deliverydate) is a datetime parameter which is passed as follows:
qryOpenOrdersAtDate.ParamByName('Leverdatum').AsDateTime := edLeverdatum.AsDateTime
The edit 'edLeverdatum' can be empty and will then produce a value 0 when reading it using the method 'AsDateTime'
As I am writing this I wonder if it will help to rewrite it as (:Leverdatum = 0.0) in order to instruct the SQL server to treat the constant as float in stead of tinyint. I'll check this and post the result back to this topic.
Regards,
Frans
this is the SQL statement:
select
K.OrderNr
from
OrderKop K inner join Ordertype T on T.Ordertype = K.Ordertype
where
(K.AfleverDebNr = :AfleverDebNr) and
((:Leverdatum = 0) or (K.Leverdatum = :Leverdatum)) and
not (K.Gefaktureerd = 1) and
not (K.Uitgeleverd = 1) and
T.UitTeLeveren = 1
order by
K.OrderNr desc
The parameter Leverdatum (= deliverydate) is a datetime parameter which is passed as follows:
qryOpenOrdersAtDate.ParamByName('Leverdatum').AsDateTime := edLeverdatum.AsDateTime
The edit 'edLeverdatum' can be empty and will then produce a value 0 when reading it using the method 'AsDateTime'
As I am writing this I wonder if it will help to rewrite it as (:Leverdatum = 0.0) in order to instruct the SQL server to treat the constant as float in stead of tinyint. I'll check this and post the result back to this topic.
Regards,
Frans
Solved!
Hello,
I was thinking in the right direction...
The solution is in the sql statement itself. I changed it as follows
select
K.OrderNr
from
OrderKop K inner join Ordertype T on T.Ordertype = K.Ordertype
where
(K.AfleverDebNr = :AfleverDebNr) and
((:Leverdatum = CAST(0.0 as datetime)) or (K.Leverdatum = :Leverdatum)) and
not (K.Gefaktureerd = 1) and
not (K.Uitgeleverd = 1) and
T.UitTeLeveren = 1
order by
K.OrderNr desc
and that just did it!
I was thinking in the right direction...
The solution is in the sql statement itself. I changed it as follows
select
K.OrderNr
from
OrderKop K inner join Ordertype T on T.Ordertype = K.Ordertype
where
(K.AfleverDebNr = :AfleverDebNr) and
((:Leverdatum = CAST(0.0 as datetime)) or (K.Leverdatum = :Leverdatum)) and
not (K.Gefaktureerd = 1) and
not (K.Uitgeleverd = 1) and
T.UitTeLeveren = 1
order by
K.OrderNr desc
and that just did it!
-
AndreyZ
It is good to see that this problem was solved. You can also find some additional information about this problem here: http://www.devart.com/forums/viewtopic.php?t=20018 . If any other questions come up, please contact us.