Not able to pass Parameters in Stored Procedure through SSRS
-
Hello Sir
I have created a stored procedure (fetching data from wonderware Historian).I want to pass parameter @Para from SSRS.But its not working.
I have tags in Historian like Z1.energy,Z1.flow & Z2.energy,Z2.flow upto Z314.I desgined report in SSRS using this procedure but when i try to pass values in this parameter,outcome not work.See snapshot please.( i have used parameter + column right string in fields of shared dataset i.e. Flow=@Para.Flow where i want to pass Z1,Z2...Z314 as per requireemnt).
Also i have checked in Stored procedure via executing option of stored procedure.When i pass parameter @Para value Z1, or Z2..or Z314,result working,data i can see in result."BELOW I PASTED MY SP"
USE
[Runtime]
GO
/****** Object: StoredProcedure [dbo].[Z1_T1] Script Date: 08-06-2017 09:54:40 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Z1_T1]
@StartDateTime
datetime,
@EndDateTime
datetime,
@Para
NVARCHAR(50) OUTPUT
AS
BEGIN
SET
NOCOUNT ON
DECLARE
@SQLString varchar(1000)
SET
@SQLString = 'SET QUOTED_IDENTIFIER OFF '
SET
@SQLString = @SQLString + 'SELECT * FROM OPENQUERY(INSQL, '
SET
@SQLString = @SQLString + '"SELECT DateTime = convert(nvarchar, DateTime, 21), ['+@Para+'.ENERGY] , ['+@Para+'.FLOW], ['+@Para+'.POWER] '
SET
@SQLString = @SQLString + 'FROM WideHistory WHERE wwVersion = ''Latest'' AND wwQualityRule =''Extended'' and wwRetrievalMode = ''Cyclic'' AND wwResolution = 60000 '
SET
@SQLString = @SQLString + 'AND DateTime >= ' + CHAR(39) + CAST(@StartDateTime AS
varchar
(50)) + CHAR(39)
SET
@SQLString = @SQLString + ' AND DateTime < ' + CHAR(39) + CAST(@EndDateTime AS
varchar
(50)) + CHAR(39) + '")
'
EXEC
(@SQLString)
ENd
GO
-
Fields shows what columns are returned from the query
Parameters is used to map report parameters to stored procedure parametersFar away is close at hand in the images of elsewhere.
Anon. -
Sir
How should i pass values to stored procedure parameters?
Please can you tell me in detail.
-
To get this to work edit your procedure and change
SET @SQLString = @SQLString + '"SELECT DateTime = convert(nvarchar, DateTime, 21), ['+@Para+'.ENERGY] , ['+@Para+'.FLOW], ['+@Para+'.POWER] '
toSET @SQLString = @SQLString + '"SELECT DateTime, ['+@Para+'.ENERGY] AS [ ENERGY ] , ['+@Para+'.FLOW] AS [FLOW], ['+@Para+'.POWER] AS [POWER] '
In the Report
In the dataset select Text as the query type and enter queryClick on Refresh Fields
Click on Fields, you should see this
Click on Paremeters, you should see this
Add the fields to your report and change the headings
ie for ENERGY set the expression to
=Parameters!Para.Value & ".ENERGY"
Far away is close at hand in the images of elsewhere.
Anon. -
Sir
i applied same as above.But its giving error. (here i have configured shared database and dataset and then created report ,and used shared dataset in report.
SNAPSHOTS ARE IN SERIES
please see and help me.
-
SIR
I CHECKED FOR WITHOUT SHARED DAATSET TOO.
saME ERROR I AM GETTING.
-
I changed my SQL as it was flawed.
You do not need to convert the datetime in the query as you can format it in the report if needed.
The error indicates a problem with the procedure, please post the sql query in the procedureFar away is close at hand in the images of elsewhere.
Anon. -
SIR ITS MY PROCEDURE.WHAT SHOULD I DO??
USE
[Runtime]
GO
/****** Object: StoredProcedure [dbo].[Z_T] Script Date: 09-06-2017 16:19:18 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
PROCEDURE [dbo].[Z]
@StartDateTime datetime,
@EndDateTime
datetime,
@Para
NVARCHAR(50)
AS
BEGIN
SET
NOCOUNT ON
DECLARE
@SQLString varchar(1000)
SET
@SQLString = 'SET QUOTED_IDENTIFIER OFF '
SET
@SQLString = @SQLString + 'SELECT * FROM OPENQUERY(INSQL, '
SET
@SQLString = @SQLString + '"SELECT DateTime = convert(nvarchar, DateTime, 21), [ENERGY] , [FLOW] , [CURRENT] '
SET
@SQLString = @SQLString + 'FROM WideHistory WHERE wwVersion = ''Latest'' AND wwQualityRule =''Extended'' and wwRetrievalMode = ''Cyclic'' AND wwResolution = 60000 '
SET
@SQLString = @SQLString + 'AND DateTime >= ' + CHAR(39) + CAST(@StartDateTime AS
varchar
(50)) + CHAR(39)
SET
@SQLString = @SQLString + ' AND DateTime < ' + CHAR(39) + CAST(@EndDateTime AS
varchar
(50)) + CHAR(39) + '")
'
EXEC
(@SQLString)
END
GO
-
CREATE PROCEDURE [dbo].[Z]
@StartDateTime datetime,
@EndDateTime datetime,
@Para NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLString varchar(1000);
SET @SQLString = 'SET QUOTED_IDENTIFIER OFF;
SELECT * FROM OPENQUERY(INSQL, ''
SELECT DateTime,['+@Para+'.ENERGY] AS [ENERGY],[' + @Para + '.FLOW] AS [FLOW],[' + @Para + '.CURRENT] AS [CURRENT]
FROM WideHistory WHERE wwVersion = ''''Latest''''
AND wwQualityRule =''''Extended''''
AND wwRetrievalMode = ''''Cyclic''''
AND wwResolution = 60000
AND DateTime >= ''''' + CAST(@StartDateTime AS varchar(50)) + '''''
AND DateTime < ''''' + CAST(@EndDateTime AS varchar(50)) + '''''
'');'
EXEC (@SQLString)
ENDFar away is close at hand in the images of elsewhere.
Anon. -
Sir
This solution seems intresting and hope will work too.But in procedure when i try to execute first its giving an error of unclosed qutoation mark.
I am niot able to understand where i am lagging behind.
USE
[Runtime]
GO
/****** Object: StoredProcedure [dbo].[Z2] Script Date: 12-06-2017 09:55:13 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [dbo].[Z2]
@StartDateTime datetime,
@EndDateTime datetime,
@Para NVARCHAR(50)
AS
BEGIN
SET
NOCOUNT ON;
DECLARE
@SQLString varchar(1000);
SET
@SQLString = 'SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, ''
SELECT DateTime,['
+@Para+'.ENERGY] AS [ENERGY] FROM WideHistory WHERE wwVersion = ''''Latest'''' AND wwQualityRule = ''''Extended'''' AND wwRetrievalMode = ''''Cyclic'''' AND wwResolution = 60000 AND DateTime >= ''''' + CAST(@StartDateTime AS varchar(50)) + ''''' AND DateTime < ''''' + CAST(@EndDateTime AS varchar(50)) + ''''') '
EXEC
(@SQLString)
END
GO
ERROR:
Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark after the character string '
SELECT DateTime,[Z1_T1.ENERGY] AS [ENERGY] FROM WideHistory WHERE wwVersion = 'Latest' AND wwQualityRule = 'Extended' AND wwRetrievalMode = 'Cyclic' AND wwResolution = 60000 AND DateTime >= 'Jun 7 2017 12:00AM' AND DateTime < 'Jun 8 2017 12:00AM') '.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '
SELECT DateTime,[Z1_T1.ENERGY] AS [ENERGY] FROM WideHistory WHERE wwVersion = 'Latest' AND wwQualityRule = 'Extended' AND'.
(1 row(s) affected)
-
My bad :blush:
Changed my query to add missing last quote
Far away is close at hand in the images of elsewhere.
Anon. -
Sir
I tried a lot but could not find any solution for same.Please suggest where closed mark should assigned.
Sorry for inconveninece.
-
As I said I changed the query in my post
and added two single quotes on the third line from bottom before the close bracketFar away is close at hand in the images of elsewhere.
Anon. -
SIR
ok I GOT YOUR POINT.bUT STILL I GETTING THIS ERROR WHEN I USE UPDATED QUERY.OLE DB provider "INSQL" for linked server "INSQL" returned message "Incorrect syntax near 'ENERGY'".
Msg 7321, Level 16, State 2, Line 3
An error occurred while preparing the query "SELECT DateTime,[Z1_T1.ENERGY] AS [ENERGY] FROM WideHistory WHERE wwVersion = 'Latest' AND wwQualityRule ='Extended' AND wwRetrievalMode = 'Cyclic' AND wwResolution = 60000 AND DateTime >= 'Jun 7 2017 12:00AM' AND DateTime < 'Jun 8 2017 12:00AM'" for execution against OLE DB provider "INSQL" for linked server "INSQL".
-
OK try this
CREATE PROCEDURE [dbo].[Z]
@StartDateTime datetime,
@EndDateTime datetime,
@Para NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLString varchar(1000);
SET @SQLString = 'SET QUOTED_IDENTIFIER OFF;
SELECT DateTime,['+@Para+'.ENERGY] AS [ENERGY],[' + @Para + '.FLOW] AS [FLOW],[' + @Para + '.CURRENT] AS [CURRENT]
FROM OPENQUERY(INSQL, ''
SELECT DateTime,['+@Para+'.ENERGY],[' + @Para + '.FLOW],[' + @Para + '.CURRENT]
FROM WideHistory WHERE wwVersion = ''''Latest''''
AND wwQualityRule =''''Extended''''
AND wwRetrievalMode = ''''Cyclic''''
AND wwResolution = 60000
AND DateTime >= ''''' + CAST(@StartDateTime AS varchar(50)) + '''''
AND DateTime < ''''' + CAST(@EndDateTime AS varchar(50)) + '''''
'');'
EXEC (@SQLString)
ENDFar away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply