Cannot create datawindow with SQL Server Stored Procedure as source

[Former Member]

Greetings all,

I have never tried to create a datawindow from a stored procedure and wondering if there is some magic necessary.

Any pointers will be sincerely appreciated.

Thanks!!!

Paul

The code for the SP is below along with the error message.  Oddly, the part of the message that is in italics is not in the stored procedure.

PB12.5.1 Build 4953 / SQL Server 2008 Native Mode

Datawindow Wizard - Error Message

Cannot Create Datawindow

SQLState=01000

[Microsoft][ODBC SQL Server Driver][SQL Server] Sendint request to determine location for pickup...

1 execute dbo.ft_rate_engine_wrapper; 1 @binding = :binding, @request_id = :request_id...                   

CREATE PROCEDURE dbo.ft_RateEngine_Wrapper
@binding nvarchar(max) = NULL,
@requestId nvarchar(max) = NULL,
@statisticLevel nvarchar(max) = NULL,
@account nvarchar(max) = NULL,
@pickupType nvarchar(max) = NULL,
@dropoffType nvarchar(max) = NULL,
@carType nvarchar(max) = NULL,
@pax nvarchar(max) = NULL,
@bookingOrRateRequest nvarchar(max) = NULL,
@company nvarchar(max) = NULL,
@specialInstructions nvarchar(max) = NULL,
@numberOfStops nvarchar(max) = NULL,
@password nvarchar(max) = NULL,
@userId nvarchar(max) = NULL,
@hourlyJob nvarchar(max) = NULL,
@requestedHours numeric(18,0) = NULL,
@pickupStreet nvarchar(max) = NULL,
@pickupCity nvarchar(max) = NULL,
@pickupCountryCode nvarchar(max) = NULL,
@pickupPostalCode nvarchar(max) = NULL,
@pickupStateProvince nvarchar(max) = NULL,
@arrivalAirport nvarchar(max) = NULL,
@arrivalTrainStation nvarchar(max) = NULL,
@dropoffStreet nvarchar(max) = NULL,
@dropoffCity nvarchar(max) = NULL,
@dropoffCountryCode nvarchar(max) = NULL,
@dropoffPostalCode nvarchar(max) = NULL,
@dropoffStateProvince nvarchar(max) = NULL,
@departureAirport nvarchar(max) = NULL,
@flightTrainDepartureDateTime datetime = NULL,
@arriveAheadMinutes nvarchar(max) = NULL,
@departureTrainStation nvarchar(max) = NULL,
@asDirected nvarchar(max) = NULL,
@specifiedPickupDateTime datetime = NULL,
@alephCarType nvarchar(max) = NULL,
@source nvarchar(max) = NULL,
@arrivalDomIntlFlightIndicator nvarchar(max) = NULL,
@departureDomIntlFlightIndicator nvarchar(max) = NULL,
@pickupStreetNo nvarchar(max) = NULL,
@dropoffStreetNo nvarchar(max) = NULL,
@paxId nvarchar(max) = NULL,
@stopNoArray nvarchar(max) = NULL,
@stopTypeArray nvarchar(max) = NULL,
@stopPostalCodeArray nvarchar(max) = NULL,
@stopCityAirportArray nvarchar(max) = NULL,
@puInstructions nvarchar(max) = NULL,
@stopStreetAddressArray NVARCHAR (max) = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE @version nvarchar(max);
DECLARE @wsAddress nvarchar(max);
DECLARE @googleServiceUrl NVARCHAR (max);
DECLARE @googleServiceUserId NVARCHAR (max);
DECLARE @googleServicePassword NVARCHAR (max);

SELECT TOP 1
    @wsAddress = LTRIM(RTRIM(rate_engine_ws_url)),
    @version = LTRIM(RTRIM(rate_engine_ws_version)),
    @googleServiceUrl = LTRIM(RTRIM(gmws_url)),
    @googleServiceUserId = LTRIM(RTRIM(gmws_user_id)),
    @googleServicePassword = LTRIM(RTRIM(gmws_password))
FROM   dbo.ft_rate_engine_ws_url_version
WHERE  rate_engine_consumer = 'GX90';

EXECUTE [dbo].[ft_usp_RateEngineWithGoogle]
  @wsAddress
  ,@binding
  ,@requestId
  ,@statisticLevel
  ,@version
  ,@account
  ,@pickupType
  ,@dropoffType
  ,@carType
  ,@pax
  ,@bookingOrRateRequest
  ,@company
  ,@specialInstructions
  ,@numberOfStops
  ,@password
  ,@userId
  ,@hourlyJob
  ,@requestedHours
  ,@pickupStreet
  ,@pickupCity
  ,@pickupCountryCode
  ,@pickupPostalCode
  ,@pickupStateProvince
  ,@arrivalAirport
  ,@arrivalTrainStation
  ,@dropoffStreet
  ,@dropoffCity
  ,@dropoffCountryCode
  ,@dropoffPostalCode
  ,@dropoffStateProvince
  ,@departureAirport
  ,@flightTrainDepartureDateTime
  ,@arriveAheadMinutes
  ,@departureTrainStation
  ,@asDirected
  ,@specifiedPickupDateTime
  ,@alephCarType
  ,@source
  ,@arrivalDomIntlFlightIndicator
  ,@departureDomIntlFlightIndicator
  ,@pickupStreetNo
  ,@dropoffStreetNo
  ,@paxId
  ,@stopNoArray
  ,@stopTypeArray
  ,@stopPostalCodeArray
  ,@stopCityAirportArray
  ,@puInstructions
  ,@stopStreetAddressArray
  ,@googleServiceUrl
  ,@googleServiceUserId
  ,@googleServicePassword;

END

[Former Member]

Hi Paul.

Try to uncheck the option "Use regional settings" in the ODBC configuration.

Cheers.

[Former Member]

Thanks Luiz,

I checked my ODBC settings and 'Use Regional settings' was already unchecked.

Paul

[Former Member]

When you do this, PB is sending default values as arguments to the procedure in order to execute it and interrogate the resultset. Off hand I don't know if PB supplies NULL or empty strings - but your procedure must work with these defaults and return a resultset (an empty one at a minimum).  The easiest thing to do here is to temporarily "adjust" the procedure code to allow PB to do its interrogation - usually by ignoring the supplied arguments and hardcoding something that is known to "work".

Note - your picture is blank so I'm guessing that it did not load correctly during your post.

[Former Member]

Thanks Scott!!

I ended up checking the box for manual result set.  I punched in everything (a pain because there are some 60 outputs).

I saved the datawindow, opened it, entered the arguments and got the the following error:

---------------------------

Preview - dw_gd90_rate_engine_sp2

---------------------------

Select Error: Decimal conversion error. PowerBuilder supports only 18 decimal digits.

---------------------------

OK  

---------------------------

After getting the error, me and the creator of the stored procedure went through EVERY output and made sure it was rounded to 2 decimal places.  I suppose it it possible that there are some intermediate values that have more than 18 decimal places, but I am not aware of them.

After rounding one value, I had to create the DW from the stored procedure all over again and manually enter the definitions for the 60 outputs. Ugh.

No help. I am still getting the error.

And then when I logged in, SCN got cute on me and decided to change my name to the name of a person whose credit card I used once a long time ago to purchase a license.  So now my old SCN account is useless and my alert email have to go to my gmail account because I can no longer use my pemurray@interrasys.com account.  What a pain.

[Former Member]

Note - simply "rounding" in your tsql code does not change any datatypes.  To do that you must use cast/convert.  Don't know what "intermediate values" refers to but PB only cares about the first (generally) resultset that is generated by the stored procedure - what happens inside the procedure is not visible to PB.

If you know the shape of the resultset, you can always create a dummy procedure that simply does something like:

select cast('' as varchar(10)) as col1, cast(2 as decimal(14,2)) as col2, ...

You can use that as a basis for creating the datawindow and then change the procedure name to your wrapper.  That requires that the resultsets be identical between the 2 procedures.  It is concerning that all of your procedure arguments are defined as varchar(max) with null defaults - I can't see that as realistic nor will the defaults be any help in your PB code but it doesn't yet have any bearing on the dw generation.

[Former Member]

Thanks Gentlemen!!

Is there anyway to know if the error is associated with an input or output?

We did find that mileage was coming back with probably more than 18 decimals and rounded it.  But is not something that gets called in the initial inputs of the stored procedure.  It is a value that is returned by a web service which is called in a second part of the C# procedure which is called by the first part of the stored procedure.

So I don't think I can do select cast unless it is changed in the C# procedure?  Does that make sense?

Paul

[Former Member]

This is needed so the datawindow engine knows what datatypes to assign to the columns.  In keeping with Scott Morris' 'known to work' idea, make sure the results are of the datatype desired (ie, don't return a null if you want an integer, etc.)

[Former Member]

Thanks Matt,

Regarding 'Known to Work', I know that it works in SQL Manager.

Paul

DECLARE @binding nvarchar(max)

DECLARE @requestId nvarchar(max)

DECLARE @statisticLevel nvarchar(max)

DECLARE @account nvarchar(max)

DECLARE @pickupType nvarchar(max)

DECLARE @dropoffType nvarchar(max)

DECLARE @carType nvarchar(max)

DECLARE @pax nvarchar(max)

DECLARE @bookingOrRateRequest nvarchar(max)

DECLARE @company nvarchar(max)

DECLARE @specialInstructions nvarchar(max)

DECLARE @numberOfStops nvarchar(max)

DECLARE @password nvarchar(max)

DECLARE @userId nvarchar(max)

DECLARE @hourlyJob nvarchar(max)

DECLARE @requestedHours decimal(5,2)

DECLARE @pickupStreet nvarchar(max)

DECLARE @pickupCity nvarchar(max)

DECLARE @pickupCountryCode nvarchar(max)

DECLARE @pickupPostalCode nvarchar(max)

DECLARE @pickupStateProvince nvarchar(max)

DECLARE @arrivalAirport nvarchar(max)

DECLARE @arrivalTrainStation nvarchar(max)

DECLARE @dropoffStreet nvarchar(max)

DECLARE @dropoffCity nvarchar(max)

DECLARE @dropoffCountryCode nvarchar(max)

DECLARE @dropoffPostalCode nvarchar(max)

DECLARE @dropoffStateProvince nvarchar(max)

DECLARE @departureAirport nvarchar(max)

DECLARE @flightTrainDepartureDateTime datetime

DECLARE @arriveAheadMinutes nvarchar(max)

DECLARE @departureTrainStation nvarchar(max)

DECLARE @asDirected nvarchar(max)

DECLARE @specifiedPickupDateTime datetime

DECLARE @alephCarType nvarchar(max)

DECLARE @source nvarchar(max)

DECLARE @arrivalDomIntlFlightIndicator nvarchar(max)

DECLARE @departureDomIntlFlightIndicator nvarchar(max)

DECLARE @pickupStreetNo nvarchar(max)

DECLARE @dropoffStreetNo nvarchar(max)

DECLARE @paxId nvarchar(max)

DECLARE @stopNoArray nvarchar(max)

DECLARE @stopTypeArray nvarchar(max)

DECLARE @stopPostalCodeArray nvarchar(max)

DECLARE @stopCityAirportArray nvarchar(max)

DECLARE @puInstructions nvarchar(max)

DECLARE @stopStreetAddressArray nvarchar(max)

DECLARE @DEBUG bit

SET @binding = 'TEST'

SET @requestId = 'T1234567890'

SET @statisticLevel = 'low'

SET @account = 'IBM'

SET @pickupType = 'A'

SET @dropoffType = 'F'

SET @carType = NULL

SET @pax = '3'

SET @bookingOrRateRequest = 'R'

SET @company = '1'

SET @specialInstructions = NULL

SET @numberOfStops = '0'

SET @password = '123'

SET @userId = '456'

SET @hourlyJob = 'N'

SET @requestedHours = 0

SET @pickupStreet = 'MAIN ST'

SET @pickupCity = 'FAR HILLS'

SET @pickupCountryCode = 'US'

SET @pickupPostalCode = '07931'

SET @pickupStateProvince = 'NJ'

SET @arrivalAirport = NULL

SET @arrivalTrainStation = NULL

SET @dropoffStreet = NULL

SET @dropoffCity = NULL

SET @dropoffCountryCode = NULL

SET @dropoffPostalCode = NULL

SET @dropoffStateProvince = NULL

SET @departureAirport = 'EWR'

SET @flightTrainDepartureDateTime = NULL

SET @arriveAheadMinutes = NULL

SET @departureTrainStation = NULL

SET @asDirected = 'N'

SET @specifiedPickupDateTime = CAST('2014-12-15T11:00:00' AS datetime)

SET @alephCarType = '1'

SET @source = 'GD90'

SET @arrivalDomIntlFlightIndicator = NULL

SET @departureDomIntlFlightIndicator = 'D'

SET @pickupStreetNo = '100'

SET @dropoffStreetNo = NULL

SET @paxId = NULL

SET @stopNoArray = NULL

SET @stopTypeArray = NULL

SET @stopPostalCodeArray = NULL

SET @stopCityAirportArray = NULL

SET @puInstructions = NULL

SET @stopStreetAddressArray = NULL

SET @DEBUG = 1

EXECUTE [dbo].[ft_RateEngine_Wrapper]

   @binding

  ,@requestId

  ,@statisticLevel

  ,@account

  ,@pickupType

  ,@dropoffType

  ,@carType

  ,@pax

  ,@bookingOrRateRequest

  ,@company

  ,@specialInstructions

  ,@numberOfStops

  ,@password

  ,@userId

  ,@hourlyJob

  ,@requestedHours

  ,@pickupStreet

  ,@pickupCity

  ,@pickupCountryCode

  ,@pickupPostalCode

  ,@pickupStateProvince

  ,@arrivalAirport

  ,@arrivalTrainStation

  ,@dropoffStreet

  ,@dropoffCity

  ,@dropoffCountryCode

  ,@dropoffPostalCode

  ,@dropoffStateProvince

  ,@departureAirport

  ,@flightTrainDepartureDateTime

  ,@arriveAheadMinutes

  ,@departureTrainStation

  ,@asDirected

  ,@specifiedPickupDateTime

  ,@alephCarType

  ,@source

  ,@arrivalDomIntlFlightIndicator

  ,@departureDomIntlFlightIndicator

  ,@pickupStreetNo

  ,@dropoffStreetNo

  ,@paxId

  ,@stopNoArray

  ,@stopTypeArray

  ,@stopPostalCodeArray

  ,@stopCityAirportArray

  ,@puInstructions

  ,@stopStreetAddressArray

  ,@DEBUG

GO

[Former Member]

Perhaps - and it is not pretty.  You can insert the resultset of a stored procedure into a temp table and then generate the "corrected" resultset by casting the columns of the temp table.  But that has limitations and it would be better to update the original procedure.