HomePowerBuilderAppeon Documentation Center

Error when converting DataWindow to data model

The following error occurs when generating the data model from the DataWindow that uses the stored procedure as a data source. 

Error: The string '"getdate"()' was not recognized as a valid DateTime.

Cause:

The stored procedure calls a function to set the value of its parameter; while PowerServer failed to identify the data type of the function and failed to generate the model.

For example, the following stored procedure is unsupported, as it calls the getdate() function to set the parameter value: 

create procedure usp_testnextday
  @as_of_dt datetime = getdate()
as
begin
  DECLARE @next_dt datetime

  SELECT @next_dt = dateadd(day, 1, @as_of_dt )

  SELECT next_dt = @next_dt
End

Solution:

You can work around this error by assigning a null value to the parameter, and then call the function later to assign a different value to the parameter. For example, you can rewrite the above stored procedure as below: 

create procedure usp_testnextday
  @as_of_dt datetime = null
as
begin
  DECLARE @next_dt datetime
  
  If @as_of_dt is null
     SELECT @as_of_dt = getdate()

  SELECT @next_dt = dateadd(day, 1, @as_of_dt )

  SELECT next_dt = @next_dt
End
Prev  Up  Next
Changed PBL list  Home  Failed to load database driver for PowerServer project