Datasource, SQL procedure not working

[FrenkR]

HI,
using latest updates, I am trying to use SQL procedure(MSSQL) as a source of data. Parameters are filled, columns are empty.

ssSQLPar1.png (13.83 KiB) Viewed 1972 times

After clicking"Retrieve Columns" I am getting an error: "Object reference not set to an instance of an object".

ssPar2.png (12.93 KiB) Viewed 1972 times

Sample SQL procedure:

Code: Select all

alter procedure iwMESMachineCenterWorkGet
@cWcId varchar(20),
@dtFrom datetime,
@dtTo datetime,
@bDebug int = null
as
begin
	SET @bDebug = CASE WHEN @bDebug > 0 THEN 1 ELSE 0 ENd
	set @dtFrom = isnull(@dtFrom, getdate()-1 )
	set @dtTo = case when isnull(@dtTo, @dtFrom) > @dtFrom then @dtTo else @dtFrom + 1 end
	
		
	create table #mcWork( iId int identity primary key, cUserId varchar(20), cUserDesc varchar(100), iYear int, iPeriod int, dtLogDate datetime
		, cScheduleId varchar(20), cScheduleDesc varchar(100)
		, cProjectId varchar(20), cProjectDesc varchar(100)
		, cElementId varchar(20), cElementDesc varchar(100)
		, cTaskId varchar(20), cTaskDesc varchar(100)
		, cLaborType varchar(20), cLaborTypeDesc varchar(100)
		, cUM varchar(20), dWorkTime decimal(15,5)
		)

	insert into #mcWork
		select '52981', 'TOM SOYER', 2016, 5, '2016-02-02T14:07:36.000', '100', 'Test shift'
		, 'S74300183', 'DK6- parts', '10002', '982_02_08 hold', '45', 'CNC drilling', '151', '1.st shift', 'h', 1.00000

		
	select * from #mcWork
end

[Alex K.]

Hello,

In your procedure uses temporary table. In this case you need set the AllowRunStoredProc option.

Thank you.

[FrenkR]

Checked this but problem remains(object reference not set to an instance of an object).

[Alex K.]

Hello,

We couldn't reproduce this bug.
Please send us your report template for analysis.

Thank you.

[FrenkR]

I checked with SQL profiler. this error comes before any call is even sent to SQL. It has something do to with parameters and their settings. All parameters of SQL procedure have "defaults".
If I enter values to parameters:

ssPar1.png (38.73 KiB) Viewed 1953 times

I get an error

ssPar2.png (22.73 KiB) Viewed 1953 times

and no columns are retrieved from database.

If I leave empty parameters and click "OK"

ssPar3.png (10 KiB) Viewed 1953 times

then columns are retrieved:

ssPar4.png (25.65 KiB) Viewed 1953 times

Same happens when I click "View data"(if I fill parameters, I get an error, otherwise data retuned from SP is displayed)

[Alex K.]

Hello,

Thank you for the detail description. We need some aditional time for check the issue.

Thank you.