Stored Procedure in SQL for Wonderware Historian not Working*********URGENT

Question

Stored Procedure in SQL for Wonderware Historian not Working*********URGENT

projectengineering

Say Hey Kid

Points: 673
More actions
March 6, 2017 at 12:06 am

#322287

Dear All
I am creating a stored procedure in SQL 2008 R2 in runtime database for reading data from Wonderware Historian.(as I want use this stored procedure in my SSRS report as dataquery).
After execution of this procedure in SQL,its giving an error."
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Latest'.
"
Here I am writing my stored procedure..
CREATE PROCEDURE TESTPROCEDURE

@StartDateTime datetime,

@EndDateTime datetime
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), [SysTimeMin] '

SET @SQLString = @SQLString + 'FROM WideHistory WHERE wwVersion = "Latest" AND wwQualityRule = "Extended" and wwRetrievalMode = "Delta" '

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
PLEASE HELP ME OUTtttttttttttttttttt.............I am confused and not able to understand where I am lagging.
Thanks in advance..

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

reinhardcilliers SSC Eights! Points: 891 More actions · Answer 1

SET @SQLString = @SQLString + 'FROM WideHistory WHERE wwVersion = "Latest" AND wwQualityRule = "Extended" and wwRetrievalMode = "Delta" '

Your values for the where conditions should be in double single qoutes.

SET @SQLString = @SQLString + 'FROM WideHistory WHERE wwVersion = ''Latest'' AND wwQualityRule = ''Extended'' and wwRetrievalMode = ''Delta'' '

projectengineering Say Hey Kid Points: 673 More actions · Answer 2

Thankssssssssssss a lot sir...

You help worked for me..

Your suggestions working...

Thank you sooooo much sir..

Have a beautiful day sir..

I tried first time posting my issue on SQLservercentral and I am very thankful with quick feedback of my query..

:rolleyes:

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Whenever doing dynamic SQL, PRINT is your friend!! I NEVER execute something I am creating dynamically without first making sure it can be copied/pasted in SSMS and actually give a me an estimated query plan. And I have been doing SQL Server for over 20 years. 😉

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

TheSQLGuru - Monday, March 6, 2017 6:56 AM
Whenever doing dynamic SQL, PRINT is your friend!! I NEVER execute something I am creating dynamically without first making sure it can be copied/pasted in SSMS and actually give a me an estimated query plan. And I have been doing SQL Server for over 20 years. 😉

+1000
PRINT is your best friend when working with Dynamic SQL.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2