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..

• reinhardcilliers

  SSC Eights!

  Points: 891

  More actions

  March 6, 2017 at 12:24 am

  #1931731

  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

  March 6, 2017 at 1:05 am

  #1931732

  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

  March 6, 2017 at 6:56 am

  #1931777

  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

  March 6, 2017 at 7:05 am

  #1931778

  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