Help with OPENRECORDSET

Hi experts,

i have this problem using open recordset with this query. I get an error with the syntax around the * which is part of the sql code. how can I escape that but yet be part of the multiplication?

thanks
 
CREATE PROCEDURE <SP_Get_Prodction_Rpt_Completion> 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
 ----Query for Nichro Crowns
INSERT INTO t_Dim_Dashboard_1B(Wc_Code, Completion_Pct, Year_Month)
SELECT OH_Qty, BoardID
       FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw','



WITH CTE AS
     (SELECT Actual_Start_Datetime,  OOp.Actual_End_Datetime,
 DATEADD(minute, (((Default_Unit_Run_Time * 60) * Total_Material_In_Load_Qty)+120), Actual_Start_Datetime)  AS Due_Date, 
 Default_Unit_Run_Time, Total_Material_In_Load_Qty
         
     FROM    dbo.t_Mfg_Order_Output               AS OO
             INNER JOIN dbo.t_Mfg_Order_Operation AS OOp
             ON      OO.Mfg_Order_Nbr     = OOp.Mfg_Order_Nbr
             AND     OO.MMM_Facility_Code = OOp.MMM_Facility_Code
             INNER JOIN dbo.t_BOM AS B
             ON      OO.BOM_Id     = B.BOM_Id
             AND     OO.MMM_Id_Nbr = B.MMM_Id_Nbr
             INNER JOIN dbo.t_Routing_Operation AS ROp
             ON      B.Routing_Id        = ROp.Routing_Id
             AND     B.MMM_Facility_Code = ROp.MMM_Facility_Code
             INNER JOIN dbo.t_Routing_Operation_Resource AS ROR
             ON      ROp.Routing_Id        = ROR.Routing_Id
             AND     ROp.MMM_Facility_Code = ROR.MMM_Facility_Code
             INNER JOIN dbo.t_Mfg_Order AS MO
             ON      OO.Mfg_Order_Nbr = MO.Mfg_Order_Nbr
     WHERE   (
                     OOp.Work_Center_Code IN (''4902'',
                                              ''4907'',
                                              ''4913'',
                                              ''4926'',
                                              ''4950'',
                                              ''4953'',
                                              ''4990'',
                                              ''4996'',
                                              ''4998'',
                                              ''4914'',
                                              ''4938'',
                                              ''4941'',
                                              ''4960'',
                                              ''4937'',
                                              ''4983'',
                                              ''4962'',
                                              ''4936'')
             )
     AND
             ( OOp.Mfg_Order_Oper_Status_Code = ''Approved'')
 
	AND  (
	CONVERT(CHAR(6), Actual_Start_Datetime, 112) between 
convert(char(6), DATEADD(m, -5, getdate()) ,112) and  CONVERT(CHAR(6), getdate(), 112)
 
)
       )  ,
     CTE1 AS
     (SELECT CONVERT(CHAR(6), Due_Date, 112) AS new_date,
             CASE
                     WHEN DATEDIFF(dd, actual_end_datetime, Due_date) >= 0
                     THEN 1.00
                     ELSE 0.00
             END AS OnTime
     FROM    CTE AS x
     )
     ,
     CTE2 AS
     (SELECT  new_date             ,
              SUM(OnTime)                     AS Ontime,
              SUM(OnTime) * 100.00 / COUNT(*) AS PC_ontime
     FROM     CTE1                            AS CTE1_1
     GROUP BY new_date
     )
SELECT  ''pp'' AS wc_code,
         new_date                         ,
         PC_ontime
FROM     CTE2 AS CTE2_1
where (new_Date) between 
convert(char(6), DATEADD(m, -5, getdate()) ,112) and  CONVERT(CHAR(6), getdate(), 112)
ORDER BY new_date'


 


 
GO

Open in new window

sassy168Asked:
Who is Participating?
 
Anthony PerkinsCommented:
>>The sql server version is 8.0, not sure if thats 2005.<<
That is SQL Server 2000.

>>Looks like i need to re-write the query so that it doesn't use CTE?<<
That is correct.  CTEs are not supported in SQL Server 2000.
0
 
jonaskaCommented:
Sounds like you're having syntax error. Because * don't need to be escaped.
As far as I can see you're missing at least one ) before GO

Try extracting the SQL part from your OPENROWSET (replacing every double singelquote with one singlequote). And running it. That should give you the right line number for the error.
0
 
sassy168Author Commented:
hi there, what i did first is i created the query from the database directly first, and this works. But now i want to run this from another server..and it doesnt. here is the original code and it runs without any errors
WITH CTE AS
     (SELECT Actual_Start_Datetime,  OOp.Actual_End_Datetime,
 DATEADD(minute, (((Default_Unit_Run_Time * 60) * Total_Material_In_Load_Qty)+120), Actual_Start_Datetime)  AS Due_Date, 
 Default_Unit_Run_Time, Total_Material_In_Load_Qty
         
     FROM    dbo.t_Mfg_Order_Output               AS OO
             INNER JOIN dbo.t_Mfg_Order_Operation AS OOp
             ON      OO.Mfg_Order_Nbr     = OOp.Mfg_Order_Nbr
             AND     OO.MMM_Facility_Code = OOp.MMM_Facility_Code
             INNER JOIN dbo.t_BOM AS B
             ON      OO.BOM_Id     = B.BOM_Id
             AND     OO.MMM_Id_Nbr = B.MMM_Id_Nbr
             INNER JOIN dbo.t_Routing_Operation AS ROp
             ON      B.Routing_Id        = ROp.Routing_Id
             AND     B.MMM_Facility_Code = ROp.MMM_Facility_Code
             INNER JOIN dbo.t_Routing_Operation_Resource AS ROR
             ON      ROp.Routing_Id        = ROR.Routing_Id
             AND     ROp.MMM_Facility_Code = ROR.MMM_Facility_Code
             INNER JOIN dbo.t_Mfg_Order AS MO
             ON      OO.Mfg_Order_Nbr = MO.Mfg_Order_Nbr
     WHERE   (
                     OOp.Work_Center_Code IN ('4902',
                                              '4907',
                                              '4913',
                                              '4926',
                                              '4950',
                                              '4953',
                                              '4990',
                                              '4996',
                                              '4998',
                                              '4914',
                                              '4938',
                                              '4941',
                                              '4960',
                                              '4937',
                                              '4983',
                                              '4962',
                                              '4936')
             )
     AND
             ( OOp.Mfg_Order_Oper_Status_Code = 'Approved')
 
	AND  (
	CONVERT(CHAR(6), Actual_Start_Datetime, 112) between 
convert(char(6), DATEADD(m, -5, getdate()) ,112) and  CONVERT(CHAR(6), getdate(), 112)
 
)
       )  ,
     CTE1 AS
     (SELECT CONVERT(CHAR(6), Due_Date, 112) AS new_date,
             CASE
                     WHEN DATEDIFF(dd, actual_end_datetime, Due_date) >= 0
                     THEN 1.00
                     ELSE 0.00
             END AS OnTime
     FROM    CTE AS x
     )
     ,
     CTE2 AS
     (SELECT  new_date             ,
              SUM(OnTime)                     AS Ontime,
              SUM(OnTime) * 100.00 / COUNT(*) AS PC_ontime
     FROM     CTE1                            AS CTE1_1
     GROUP BY new_date
     )
SELECT  'pp' AS wc_code,
         new_date                         ,
         PC_ontime
FROM     CTE2 AS CTE2_1
where (new_Date) between 
convert(char(6), DATEADD(m, -5, getdate()) ,112) and  CONVERT(CHAR(6), getdate(), 112)
ORDER BY new_date

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jonaskaCommented:
I don't have any facts to back this up, but I would try to run a simple CTE query just to make sure that it is supported. What versions of SQL servers are you running on? Maybe you're missing the apropriate drivers for the target server?
0
 
sassy168Author Commented:
doesn't make sense..i took out the line where there was a problem. now the error reads:

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'WITH CTE AS
     (SELECT Actual_Start_Datetime,  OOp.Actual_End_Datetime, Default_Unit_Run_Time, Total_Material_In_Load_Qty FRO'.
0
 
sassy168Author Commented:
can you help me re-write this query without the CTE? i am crunched for time...
0
 
Anthony PerkinsCommented:
You are missing a ; before WITH CTE it should be:
; WITH CTE AS
0
 
sassy168Author Commented:
same thing: now i get :

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ';WITH CTE AS
     (SELECT Actual_Start_Datetime,  OOp.Actual_End_Datetime, Default_Unit_Run_Time, Total_Material_In_Load_Qty FR'.
0
 
Anthony PerkinsCommented:
Please post your entire script and as asked previously confirm you are using at least SQL Server 2005 and the database is set to at least a compatibility level of 90
0
 
sassy168Author Commented:
The sql server version is 8.0, not sure if thats 2005.

The other server that i can run on is 9.0.So i guess that could be where the problem is.

Looks like i need to re-write the query so that it doesn't use CTE?
0
 
EE_AutoDeleterCommented:
I've requested that this question be deleted for the following reason:

                           
No comment has been added to this question in more than 21 days, so it is now classified as abandoned and is now flagged for deletion.


If there is a valid solution, please OBJECT and indicate the comments that are, or would otherwise lead to, a solution.


Use the specific format https:#axxxxxxxx for comment ID(s).


Also, please don't object simply because the author did not respond to your comment. While we understand this is frustrating, unfortunately we cannot force the author to return to the question. Unless you feel you have presented a valid, verifiable solution we'll simply delete the question.


Experts-Exchange Auto Deleter
0
 
Anthony PerkinsCommented:
I believe my comment accurately answers the question, as in:
The experts told me “you can not do that”. What do I do now?
0
 
Anthony PerkinsCommented:
Let's try that again as I did not object:
I believe my comment accurately answers the question, as in:
The experts told me “you can not do that”. What do I do now?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.