Skip to Content
avatar image
Former Member

Cannot create datawindow with SQL Server Stored Procedure as source

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Dec 24, 2014 at 03:50 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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.

  • avatar image
    Former Member
    Dec 29, 2014 at 02:31 PM

    Hi Paul.

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


    Cheers.

    Add comment
    10|10000 characters needed characters exceeded