topic Re: SQL Error in Qlik Only in QlikView

SQL Error in Qlik Only

Anonymous — Mon, 12 Sep 2016 14:53:22 GMT

I have a part of my load script that calls a stored procedure:

Hours:

SQL

EXECUTE Finance_Budgeting_2.dbo.spNewFY17Rollup 2, NULL, NULL;

Store Hours INTO Hours.qvd

;

When I reload the script, I get the following error:

SQL##f - SqlState: 22005, ErrorCode: 245, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the nvarchar value 'Org Level 1 Code' to data type int.

Hours:

SQL

EXECUTE Finance_Budgeting_2.dbo.spNewFY17Rollup 2, NULL, NULL

However, when I execute the stored procedure outside of Qlik, I get no error at all. Has anyone else ran into this issue?

Re: SQL Error in Qlik Only

Clever_Anjos — Mon, 12 Sep 2016 16:04:32 GMT

Do you have that procedure signature? Please post here

Re: SQL Error in Qlik Only

Anonymous — Mon, 12 Sep 2016 16:05:08 GMT

Sorry, what do you mean by procedure signature?

Re: SQL Error in Qlik Only

Clever_Anjos — Mon, 12 Sep 2016 16:33:51 GMT

for example

Create Procedure spNewFY17Rollup (

@param1 varchar,

@param2 int

)

Re: SQL Error in Qlik Only

Anonymous — Mon, 12 Sep 2016 16:44:15 GMT

CREATE PROCEDURE [dbo].[spNewFY17Rollup]
  @Type int = 0,
  @Area varchar(50) = NULL,
  @SubRegion varchar(50) = NULL

If @Type = 2 --Salary - Base, OT, On Call Hour against current employees
BEGIN

  Select Distinct sh.*, CASE WHEN sh.Base = 0 AND sh.OnCall = 0 AND sh.Overtime = 0 THEN 'No Hours Entered' ELSE 'Hours Entered' END AS [Review Status]
  From vwNewFY17SalaryHoursCurrentPicture sh Inner Join
  vwDistributionList dl ON /*sh.BusUnit = dl.BusUnit AND */ sh.CostCenter = dl.CostCenter
  Where ((@Area IS NULL OR @Area = dl.SDrive_Area )
  AND (@SubRegion IS NULL OR @SubRegion = dl.SDrive_Region))

END

This is the detail in the stored procedure.

Re: SQL Error in Qlik Only

Clever_Anjos — Mon, 12 Sep 2016 17:05:41 GMT

Is it possible to run this and post here?

Select Distinct sh.*, CASE WHEN sh.Base = 0 AND sh.OnCall = 0 AND sh.Overtime = 0 THEN 'No Hours Entered' ELSE 'Hours Entered' END AS [Review Status]
From vwNewFY17SalaryHoursCurrentPicture sh Inner Join
vwDistributionList dl ON sh.CostCenter = dl.CostCenter

Re: SQL Error in Qlik Only

Anonymous — Mon, 12 Sep 2016 17:53:57 GMT

I got it to work by pasting the code from the stored proc. No idea why, but it now works. Thanks.

Re: SQL Error in Qlik Only

Clever_Anjos — Tue, 13 Sep 2016 18:50:50 GMT

You´re welcome