I'm trying to convert some Delphi/FireDAC code that works with Firebird SQL to MSSQL. It should be straightforward, but of course it isn't :)

-
I'm trying to convert some Delphi/FireDAC code that works with Firebird SQL to MSSQL. It should be straightforward, but of course it isn't :)

For Firebird, I have a simple SQL statement: 

UPDATE MY_TABLE SET FIELD_A=:PARAM_A

I then set the FireDAC parameter and execute the statement. All good.

The above SQL doesn't work with MSSQL so I change the parameter declaration to:

UPDATE MY_TABLE SET FIELD_A=@PARAM_A

I set the FireDAC parameter and execute the statement and I get the following error:

Must declare the scalar variable "@PARAM_A".

Any idea what could be wrong?

Comments

  1. Massimo CarocciaFebruary 26, 2014 at 6:22 AM

    In SQL Server the @ prefix identify a variable to pass a param to query you must use the : and not the @

    ReplyDelete
  2. Lars FosdalFebruary 26, 2014 at 6:26 AM

    @Param is typical T-SQL notation for local variables or arguments.
    http://docwiki.embarcadero.com/RADStudio/XE5/en/Preprocessing_Command_Text_(FireDAC)#Substitution_Variables indicate that ! and & are used for substitution?

    ReplyDelete
  3. Paul ThorntonFebruary 26, 2014 at 6:30 AM

    Massimo Caroccia When I use a : I get the following error:

    "'[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error'"

    I assumed that the typically crptic Microsoft error was telling me that I was using the wrong prefix for the parameter. Obviously not!

    ReplyDelete
  4. Massimo CarocciaFebruary 26, 2014 at 6:43 AM

    Can you post all SQL command text?

    ReplyDelete
  5. Paul ThorntonFebruary 26, 2014 at 7:16 AM

    Massimo Caroccia . That's it I'm afraid. The command is running through RemObjects DataAbstract, but it doesn't alter the SQL.

    ReplyDelete
  6. Massimo CarocciaFebruary 26, 2014 at 7:25 AM

    Try to check the "UpdateOptions.CountUpdatedRecords" or "FetchOptions.RecordCountMode".

    ReplyDelete
  7. Phillip WoonMarch 7, 2014 at 10:58 AM

    Perhaps you have to preface your table name with the dbo owner?    like   dbo. MY_TABLE   ?

    ReplyDelete

Post a Comment