Querying ODBC SQL Server

Question

Question

Richard Housham · Jul 16, 2018

Hi I've got a service setup to query a sql server via ODBC.

I've got all the parameters etc setup and everything is ok

My Query is

SELECT TOP 1
CI.ClientID,
Gender,
DateOfBirth,
MaritalStatus AS MartialStatusCode,
(SELECT CodeDescription FROM GenMaritalStatus WHERE Code = MaritalStatus) AS Martial,
Ethnicity AS EnthnicityCode,
(SELECT CodeDescription FROM GenEthnicity WHERE Code = Ethnicity) AS Ethnicity,
Nationality AS NationalityCode,
(SELECT CodeDescription FROM GenNationality WHERE Code = Nationality) AS Nationality,
DateOfDeath,
CN.Surname AS Surname,
ISNULL (CN.GivenName1, '')
+ ISNULL( CN.GivenName2, '')
+ ISNULL(CN.GivenName3, '')
+ ISNULL( CN.GivenName4, '')
+ ISNULL(CN.GivenName5, '') AS Forename,
ISNULL(CN.Prefix, '' ) AS Title,
LastUpdated
FROM [ClientIndex] CI
INNER JOIN ClientName CN ON CI.ClientID = CN.ClientID WHERE LastUpdated > ?
ORDER BY LastUpdated

If I have my parameter as

Do ..Adapter.SetPersistentValue(..%ConfigName,"LastModified","20080915")

This works.

But if I add in the time part like this.

Do ..Adapter.SetPersistentValue(..%ConfigName,"LastModified","20080915 10:00:00")

I get
ERROR #6022: Gateway failed: Execute. + ERROR <Ens>ErrGeneral: SQLState: (22001) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]String data, right truncation

I've tried lots of different date/time formats but nothing seems to work.

I've got a odbc sql browser and that works with this date format (yyyymmdd HH:ii:ss)

So what gives here?

Thanks

Richard

Discussion (11)1

Log in or sign up to continue

John Murray · Jul 17, 2018

Your SetPersistentValue calls refer to LastModified, but your query refers to LastUpdated. Is this a typo in your post?

If you use Portal (Explorer, SQL) to run a query that reports LastUpdated values, what do they look like? More technically, what datatype are they?

0 0

John Murray · Jul 17, 2018

Could you use the SQL Gateway feature to make your tables accessible there?

0 0

Robert Cemper · Aug 20, 2018

what about using real ODBC datetime format "2008-09-15 10:00:00" ?

0 0

score 0 · Answer 1 · 2018-07-17T06:27:05-04:00

Hi yeah, I could have them all the same name. But not sure if it'll make any difference.
(I'll give it a go though).

With regards data type LastUpdated is a datetime in SQL Server 2008 .

I've tried to get a more updated driver see if that would help (no). I also read some stuff here on C and odbc issues.
https://docs.microsoft.com/en-us/sql/relational-databases/native-client-...

If I remember correctly ensemble/healthshare has a C 'core' does it not?

score 0 · Answer 2 · 2018-07-17T06:33:14-04:00

Huh, renamed the variables and seems a bit more compliant/working.
Need to test it out but might have a winner!

score 0 · Answer 3 · 2018-07-17T06:42:43-04:00

Richard Housham · Jul 17, 2018

Nope my mistake, messed up my variables when renaming. Got them all ok now and still errors.

0 0

score 0 · Answer 4 · 2018-07-17T06:45:19-04:00

If you use Portal (Explorer, SQL) to run a query that reports LastUpdated values

It's an odbc connection so it's not in the explorer > SQL

score 0 · Answer 5 · 2018-07-18T09:30:03-04:00

Had a look, almost got it but then got this.

[SQLCODE: <-226>:<Gateway query Execute() failed>]

[%msg: <Connection 'xxxxx': Execute: SQLState: (22018) NativeError: [0] Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification >]

Yeah, it's not happy.

score 0 · Answer 6 · 2018-08-20T11:41:06-04:00

Richard Housham · Aug 20, 2018

I've thrown this one to WRC who are looking at it.

0 0

score 0 · Answer 7 · 2019-03-22T05:23:18-04:00

Using this format ( "2008-09-15 10:00:00" ) solved my issues with calling a stored procedure using ..Adapter.ExecuteProcedure

score 0 · Answer 8 · 2022-07-05T15:07:32-04:00

Hi Martin !

I am having the same problem with ODBC and SQL Server. Can you please show me an example of your ExecuteProcedure syntax?

For example :

Do you use ..Adapter.ExecuteProcedure ( PROCEDURE_NAME '2022-01-01 00:00:00', '2022-01-10 23:59:00') syntax ? I mean, do you ' as separators?

Thanks in advance...