a.duquette@Agropur

Error while running report in SSRS with PI OLEDB

Discussion created by a.duquette@Agropur on Dec 19, 2012
Latest reply on Dec 22, 2012 by mhalhead

Hi,

 

The setup:
SQL SERVER 2008 EXPRESS with report services on local machine
PI OLEDB Enterprise on local machine
LINKED SERVER to AF server with Pi OLEDB Povider (Name : pioledb)

 

IN connection String : i've specified ALLOW EXPENSIVE=True to be sure that it is not the problem. 

 


In Sql Server Management, i've created a table function in a temporary database, without data, just to make requests in PIOLEDBENT:

 

CREATE FUNCTION [dbo].[ObtenirNoBatchesPourDate]
(
-- Add the parameters for the function here
@dateBatch NVARCHAR(20),
@pathReservoir NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT ValueStr
FROM pioledb.NatrelProd.Data.Archive
WHERE ElementAttributeID IN (
SELECT ID
FROM pioledb.NatrelProd.Asset.ElementAttribute
WHERE elementID IN (SELECT distinct ElementID
FROM pioledb.NatrelProd.Asset.ElementHierarchy
WHERE Path LIKE @pathReservoir)
AND Name='NuméroLot')
AND ValueStr like @dateBatch
)

 

 IN VISUAL STUDIO (SSRS) 

 

 DATA SET : SELECT ValueStr FROM ObtenirNoBatchesPourDate (@BatchDateCode, @PathReservoir)
The attributes are set in the report parameters

 

The result :

 

 0844.erreur-pi-oledb.jpg

 

 

 

When no parameters are specified (hard code parameters in SQL request, in stored function), the results are shown.
When this function is called directly in sql server management, the result is good.
Same SQL request called directly in the dataset in SSRS doesn't work
Same SQL request with hard coded parameters in SSRS dataset work! 

 

Did I missed something?
What i've done wrong? 

 

Thanks,
Alex

Outcomes