ADO - Stored Procedure - Generated SQL

-
ADO - Stored Procedure - Generated SQL
Is there a way to intercept and view the generated SQL of a stored procedure call?
We have a strange date off by 2 issue.

#sql

Comments

  1. Peter JosephMarch 18, 2014 at 6:58 AM

    If it's mssql Why don't you use the Sql profiler in Ssms

    ReplyDelete
  2. Lars FosdalMarch 18, 2014 at 7:13 AM

    Good point, Peter Joseph - Also just rediscovered that we have no less than five methods of firing stored procs in our wrappers, all processing arguments in different ways.

    ReplyDelete
  3. Giacomo Degli EspostiMarch 18, 2014 at 7:18 AM

    Double-check the parameters datatype: we had this problem when the parameter was interpreted as a double and not as a TDateTime. During the call, the date was converted to float, passed to the server  as a float and re-converted to a sql datetime. This leads to the 2 day offset, since in delphi TDateTime(0) = '1899-12-30' and in mssql cast(0 as datetime) = '1900-01-01'

    ReplyDelete
  4. Lars FosdalMarch 18, 2014 at 7:37 AM

    Giacomo Degli Esposti - That was indeed the problem.  What fixed it was formatting the date as a string in the format that the db was set up for.  The developer said "Don't tell anyone" as  this was the method used numerous other places in the code :)  The underlying cause appears to be that TDateTime turns to Double in a Variant array.

    ReplyDelete
  5. Giacomo Degli EspostiMarch 18, 2014 at 8:07 AM

    Lars Fosdal LOL. Don't worry, this will stay a secret between you and me! ;-)

    ReplyDelete

Post a Comment