Solved

Passing parameters in SSRS OpenQuery - Urgent

Posted on 2012-03-19
26
1,248 Views
Last Modified: 2012-05-31
Hi ,

I am trying to pass parameters in SSRS  Opnquery , it's throwing error : incoeerect syntax near 'MM'

Following is the query......

DECLARE @TSQL varchar(8000)
   
SELECT @TSQL = 'SELECT * FROM Openquery (x , ''Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed from
(SELECT  TO_CHAR(ENDDATETIME , '''MM/DD/YY''') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,Round(0.3* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - ((6.67*ActualLength))))),2) AS RunSpeed
             FROM kgf.SEGMENT
       WHERE CURESAMPLE = '''False''' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= ''''' + @StartDate + ''''' And ENDDATETIME < ''''' + @EndDate + '''''
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , ' ' 'MM/DD/YY' ' ')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
      ) a
       group by ColorDate,StartOperatorID,equipmentname,colorcode       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode' ') c
INNER JOIN
OpenQuery(y , ' 'SELECT OperatorID , Name As Operator FROM PHD_USER.OPID_NAMESVIEW ' ')  h
on c.STARTOPERATORID = h.OperatorID'

SELECT @TSQL

EXEC (@TSQL)
0
Comment
Question by:CodingSucks
  • 11
  • 7
  • 6
26 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738018
Use two to get one.

Try:
...
ORDER BY TO_CHAR(ENDDATETIME , ' 'MM/DD/YY' ' )
...
0
 

Author Comment

by:CodingSucks
ID: 37738042
Hi Slightwv,

Thanks for quick response..i tried what you suggested still same message.

PN
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738045
Are you using sql server or oracle syntax?

If SQL Server, I don't think you can use TO_CHAR.

Also, your parameters should :param for instance :EndDate, not  @EndDate  or  @startDate
0
 

Author Comment

by:CodingSucks
ID: 37738104
Sammy,

I am uisng oracle syntax...when i run the query it prompts to input parameters, once i enter parameters it's throwing error "Incorrect Syntax near 'MM' "

Thanks,
PN.
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 37738110
Oops, sorry this is OpenQuery.

Ignore everything I said and try this instead:

(SELECT  TO_CHAR(ENDDATETIME , ''''MM/DD/YY'''') AS ColorDate
0
 

Author Comment

by:CodingSucks
ID: 37738140
Sammy,

with TO_CHAR(ENDDATETIME , ''''MM/DD/YY'''')    it's new error

An error occurred while preparing the query for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "x".
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738161
There is got to be more error messages than that, no?
0
 

Author Comment

by:CodingSucks
ID: 37738184
its showing the whole query in error message..
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738239
Copy and paste the entire error with code. We need more details from the error message.
0
 

Author Comment

by:CodingSucks
ID: 37738691
An error occurred while preparing the query "Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed from
(SELECT  TO_CHAR(ENDDATETIME , 'MM/DD/YY') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = 'False' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= '03/12/12' And ENDDATETIME < '03/13/12'
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , 'MM/DD/YY')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE ) a
       group by ColorDate,StartOperatorID,equipmentname,colorcode,color
       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode,color " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "XYZ".
0
 

Author Comment

by:CodingSucks
ID: 37738748
looks like its working ....but i have issue with group by

following is the query

Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed  , trim(round(avg(runspeed),2)) AS NormSpeed from
(SELECT  TO_CHAR(ENDDATETIME , 'MM/DD/YY') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,TRIM(Sum(Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2))) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = 'False' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= '03/13/12' And ENDDATETIME < '03/14/12'
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , 'MM/DD/YY')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738768
>>but i have issue with group by

you have to group by all non-aggregate columns in your outer query:

...
 ,EQUIPMENTNAME
       ,COLORCODE)
group by
ColorDate,StartOperatorID,equipmentname,colorcode
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37738794
Well, this is a different code from what you showed earlier.
0
 

Author Comment

by:CodingSucks
ID: 37738820
I have modified the query and it looks as below....still having issue with group by

Select ColorDate,StartOperatorID,equipmentname,colorcode, trim(round(avg(speed),2)) AS AvgSpeed ,  trim(round(avg(runspeed),2)) AS NormSpeed  from
(SELECT  TO_CHAR(ENDDATETIME , 'MM/DD/YY') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,
       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = 'False' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
 AND  ENDDATETIME is NOT NULL AND ENDDATETIME >= '03/12/12' And ENDDATETIME < '03/13/12'
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , 'MM/DD/YY')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE ) a
       group by ColorDate,StartOperatorID,equipmentname,colorcode
       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738838
>>still having issue with group by

Error? Not producing the correct results?  what?
0
 

Author Comment

by:CodingSucks
ID: 37738859
ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
0
 

Author Comment

by:CodingSucks
ID: 37742194
I've requested that this question be closed as follows:

Accepted answer: 0 points for CodingSucks's comment #37738140

for the following reason:

It worked as expected
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37738884
If http:#a37738140 was the final solution then I would expect you to accept sammySeltzer's post http:#a37738110
0
 

Author Comment

by:CodingSucks
ID: 37739431
Following is the final Work Solution.....

DECLARE @TSQL varchar(8000)
     
 SELECT @TSQL = 'SELECT * FROM Openquery (server, ''Select ColorDate,StartOperatorID,equipmentname,colorcode,trim(round(avg(speed),2)) AS AvgSpeed ,  trim(round(avg(runspeed),2)) AS NormSpeed  from
(SELECT  TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''') AS ColorDate
        ,STARTOPERATORID
       ,trim(EQUIPMENTNAME) AS EquipmentName
       ,TRIM(COLORCODE) AS COLORCODE ,

       TRIM(sum(ROUND((ACTUALLENGTH)/((ENDDATETIME-STARTDATETIME))/86400,2))) AS Speed
       ,trim(sum(Round((0.3)* ((ENDDATETIME - STARTDATETIME)*86400
                           - ( sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*ActualLength))))),2))) AS RunSpeed
         FROM OLS.COLORSEGMENT
       WHERE CURESAMPLE = ''''False'''' AND ACTUALLENGTH >4000 AND
      Trim(COLORCODE)> 0  AND ((ENDDATETIME-STARTDATETIME)*1440)>1
      AND (ENDDATETIME - STARTDATETIME)*86400  > sqrt(power((ENDDATETIME - STARTDATETIME)*86400,2)  - (((6.67)*(ActualLength)))) AND
           power((ENDDATETIME - STARTDATETIME)*86400,2) > (6.67)*(ActualLength)
 AND  ENDDATETIME is NOT NULL AND TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''')>= ''''' + @StartDate + ''''' And TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''')< ''''' + @EndDate +'''''
  GROUP BY ENDDATETIME
          ,STARTOPERATORID
         ,EQUIPMENTNAME
         ,ROUND((ENDDATETIME-STARTDATETIME)*1440 , 2)
         ,trim(COLORCODE)
         ORDER BY TO_CHAR(ENDDATETIME , ''''MM/DD/YYYY'''')
       ,STARTOPERATORID
       ,EQUIPMENTNAME
       ,COLORCODE)
       group by ColorDate,StartOperatorID,equipmentname,colorcode
       ORDER BY ColorDate,StartOperatorID,equipmentname,colorcode '') c
INNER JOIN
OpenQuery(server1 , ''SELECT OperatorID , Name As Operator FROM PHD_USER.OPID_NAMESVIEW'')  h
on c.STARTOPERATORID = h.OperatorID
INNER JOIN
OpenQuery(server1 , ''SELECT DISTINCT COLORCODE AS cCode , COLORNAME As Color FROM PHD_USER.COLORINGVIEW WHERE COLORNAME is NOT NULL'')  n
on c.colorcode = n.cCode'


EXEC (@TSQL)

Many thanks to Sammy and SlightWv  :)

PN.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37739462
Not sure that I helped much but it appears the main 'fix' was sammySeltzer's post http:#a37738110 that used 4 single quotes.

Please accept that post as the solution and not yours in http:#a37738140
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37742195
Since you have not taken my suggestion, I'll involve a Moderator.
0
 

Author Comment

by:CodingSucks
ID: 37742326
different answer
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37742345
>>different answer

Didn't the 4 single quotes not solve the original problem of "incoeerect syntax near 'MM'
"
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 37742459
CodingSucks, we still haven't seen anything you have done differently from the suggestions you were given.

If I were you, I would go ahead and accept the answer.

There is always another time you might need help.

Just bear this in mind as you argue over semantics.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now