· Jul 16, 2018

Querying ODBC SQL Server

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

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,
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,
  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?



Discussion (11)1
Log in or sign up to continue

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.

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