'SQL STATE 37000 [Microsoft][ODBC Microsoft Access Driver] Syntax Error or Access Violation

Good day!

I get this error:

SQL STATE 37000 [Microsoft][ODBC Microsoft Access Driver] Syntax Error or Access Violation, when trying to run an embedded SQL statement on Powerscript.

I am using MsSQL Server 2008 and PowerBuilder 10.5, the OS is Windows 7. I was able to determine one of the queries that is causing the problem: 

SELECT top 1 CONVERT(DATETIME,:ls_datetime)
into :ldtme_datetime
from employee_information
USING SQLCA;

if SQLCA.SQLCODE = -1 then
    Messagebox('SQL ERROR',SQLCA.SQLERRTEXT)
    return -1 
end if

I was able to come up with a solution to this by just using the datetime() function of PowerBuilder. But there are other parts of the program that is causing this and I am having a hard time in identifying which part of the program causes this. I find this very weird because I am running the same scripts here in my dev-pc with no problems at all, but when trying to run the program on my client's workstation I am getting this error. I haven't found any differences in the workstation and my dev-pc. I also tried following the instructions here, but the problem still occurs.

UPDATE: I was able to identify the other script that is causing the problem:

/////////////////////////////////////////////////////////////////////////////
//  f_datediff
//  Computes the time difference (in number of minutes) between adtme_datefrom and adtme_dateto
////////////////////////////

decimal ld_time_diff

SELECT top 1 DATEDIFF(MINUTE,:adtme_datefrom,:adtme_dateto)
into :ld_time_diff
FROM EMPLOYEE_INFORMATION
USING SQLCA;

    if SQLCA.SQLCODE = -1 then
        Messagebox('SQL ERROR',SQLCA.SQLERRTEXT)
        return -1
    end if

return ld_time_diff

Seems like passing datetime variables causes the error above. Other scripts are working fine.

sqlodbcpowerbuilder


Solution 1:[1]

  • Create a transaction user object inherited trom transaction.
  • Put logic in the sqlpreview of your object to capture and log the sql statement being sent to the db.
  • Instantiate it, connect to the db, and use it in your embedded sql.
  • Assuming the user gets the error you can then check what was being sent to the db and go from there.

Solution 2:[2]

The error in your first statement should be the second parameter to CONVERT function. It's type is not a string, it's type is an valid expression https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

So I would expect that your 

CONVERT(DATETIME,:ls_datetime)

would evaluate to

CONVERT(DATETIME, 'ls_datetime')

but it should be

CONVERT(DATETIME, DateTimeColumn)

The error in your second statement could be that you're providing an wrong datetime format. So please check if your error still occurs when you use this function https://docs.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql

with the correct datetime format you're using

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 zinking
Solution 2

Related Questions