Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with OPENRECORDSET

Posted on 2011-04-26
14
Medium Priority
?
49 Views
Last Modified: 2016-06-03
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

0
Comment
Question by:sassy168
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 6

Expert Comment

by:jonaska
ID: 35470281
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
 

Author Comment

by:sassy168
ID: 35470654
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
 
LVL 6

Expert Comment

by:jonaska
ID: 35470847
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:sassy168
ID: 35471421
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
 

Author Comment

by:sassy168
ID: 35471472
can you help me re-write this query without the CTE? i am crunched for time...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35471518
You are missing a ; before WITH CTE it should be:
; WITH CTE AS
0
 

Author Comment

by:sassy168
ID: 35471539
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35471599
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
 

Author Comment

by:sassy168
ID: 35476616
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35480509
>>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
 

Expert Comment

by:EE_AutoDeleter
ID: 41629667
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41629664
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 41629668
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 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