Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 998
  • Last Modified:

HELP with provider ran out of memory

Hi all i have a very big query that I am working on and itwas working fine past couple of days and today i ran and i got this error:

OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IRowset::GetNextRows returned 0x8007000e:  The provider ran out of memory.].
Msg 7399, Level 16, State 1, Line 6

what does this mean?
0
sassy168
Asked:
sassy168
  • 15
  • 14
1 Solution
 
Anthony PerkinsCommented:
It means it "ran out of memory".  It is time to refactor your query.
0
 
shalabhsharmaCommented:
it looks the Server resources is over-usage you should update your provider.

 
0
 
Anthony PerkinsCommented:
>>you should update your provider.<<
Huh? Do you think the provider is suffering from Alzheimer?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sassy168Author Commented:
so tthat means my query is too intensive?
0
 
Anthony PerkinsCommented:
No.  It means it needs to be optimized.
0
 
sassy168Author Commented:
what is the definition of "optimize" ..maybe less joins, and databases to get to the data? right now the query its getting data from 2 different databases, and its very long, perhaps that has something to do with it.

0
 
Anthony PerkinsCommented:
>>what is the definition of "optimize" ..maybe less joins, and databases to get to the data?<<
It has many meanings from:
Retructure your tables/data.
To:
Add additional indexes.
To:
Update statistics and or defrag indexes.
To:
Rewrite your query to ensure the SQL Server engine uses the best plan available.

Without seeing your query it is impossible to be more precise.
0
 
sassy168Author Commented:
here is a sample file for ONE workcenter, i create a stored procedure for 4 or 10 workcenters together. I can see how it would take a lot of resources but thats the way i have to do it right now. How can i solve the "memory" problem?  add more ram, or increase the limit somewhere in the PC? thanks
4120.sql
0
 
Anthony PerkinsCommented:
You need to refactor your query.  Start breaking up the query and inserting the data into temp tables (especially ones involving OPENROWSET.  You can then query the temp tables directly.
0
 
sassy168Author Commented:
acperkin:

theopenrowset is a query from another database. If i use the temp tables, at what point do i use it? begining in the query? sorry not too familiar with temp tables. I assume you're suggesting putting everything that is outside the current database in the temp tables which will improve performance?
0
 
Anthony PerkinsCommented:
Exactly.  

Instead of:
LEFT OUTER JOIN ( 
	 SELECT End_Date, Qty
		 FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
			  SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
  GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
) AS d ON n.Load_Date = d.End_Date

You would do something like this:
CREATE TABLE #Temp1 (
		End_Date datetime,
		Qty integer
		)
			
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)

INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
	 FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
			  SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')


Then your main select becomes:

SELECT ...

FROM ...
        LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date   
...

Open in new window

0
 
sassy168Author Commented:
okay that sounds great, but i have different queries bunched up together, for example i might run 6-10 repetition of the above query (the query i want to create is a summary file of 10 different workcenters, and this query is only for ONE work center) so can I replicate this code and repeat it for 10 times and run it all together?

would i run into problems if the temp table is the same name? I would have to probably create the temp table once, and then insert the rest of the instances? and when does the temp table get deleted? if i run this query hourly would it error out saying, the table has been already created? please advice, thanks!!


0
 
Anthony PerkinsCommented:
>>would i run into problems if the temp table is the same name?<<
No.

>>I would have to probably create the temp table once, and then insert the rest of the instances?<<
Yes you can.

>>and when does the temp table get deleted? <<
Either when you do a DROP TABLE #TempTable goes here or it goes out of scope at the end of the Stored Procedure.

>>if i run this query hourly would it error out saying, the table has been already created?<<
No.
0
 
sassy168Author Commented:
ok help me out here getting errors with this:
	LEFT OUTER JOIN ( 

DROP TABLE #Temp1
CREATE TABLE #Temp1 ( End_Date datetime, Qty integer	)
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)

INSERT #Temp1(End_Date, Qty)
	SELECT End_Date, Qty
                            FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
                            SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date                            , CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.                            t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.                                Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
                            dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code                            WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m,                            DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <=    GETDATE()) AND ((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
                            GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')


select End_Date, Qty from #Temp1
           
) AS d ON n.Load_Date = d.End_Date   

Open in new window

0
 
Anthony PerkinsCommented:
First step insert into the temp table:
CREATE TABLE #Temp1 (
            End_Date datetime,
            Qty integer
            )
                  
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)

INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
       FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
                    SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')

Second execute your query with the temp table.
SELECT ...

FROM ...
        LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date  
...
0
 
sassy168Author Commented:
your example works by itself but i need it to work with the left outer join. I get an error when I leave it on

SELECT n.Load_Date ,
				ISNULL(d.Qty, 0) AS Qty,
				b.Factory_Name ,
				b.Operation_Name ,
				b.Total_Target  
	
				FROM   ( 

						SELECT     CONVERT(VARCHAR(8), dt, 112) AS Load_Date FROM  RTC.dbo.Corp_Calendar  
						WHERE     ( dt >= DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0) )
						AND ( dt < DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 1, 0) )
						) AS n
						LEFT OUTER JOIN ( 

							CREATE TABLE #Temp1 (
							 End_Date datetime, Qty integer
							)
                  
							CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)

							INSERT #Temp1(End_Date, Qty)
							SELECT End_Date, Qty
							FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
                    SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')
        

							 SELECT End_Date, Qty
							 FROM    #Temp1
           
							) AS d ON n.Load_Date = d.End_Date

Open in new window

0
 
Anthony PerkinsCommented:
That is not what I stated, so let me try one more time:
First create the temp table and populate it:
CREATE TABLE #Temp1 (
            End_Date datetime,
            Qty integer
            )
                  
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)

INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
       FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
                    SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')


Next use it in your query:
SELECT  n.Load_Date,
        ISNULL(d.Qty, 0) AS Qty,
        b.Factory_Name,
        b.Operation_Name,
        b.Total_Target
FROM    (SELECT CONVERT(VARCHAR(8), dt, 112) AS Load_Date
         FROM   RTC.dbo.Corp_Calendar
         WHERE  (dt >= DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0))
                AND (dt < DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 1, 0))
        ) AS n
        LEFT OUTER JOIN #Temp1 AS d ON n.Load_Date = d.End_Date
...

If that is not clear, I suggest you hire a reputable DBA in your area to help you.

Open in new window

0
 
sassy168Author Commented:
that works now, but it wasn't clear in the beginning because you didn't say clearly to do the create temp table outside of the query.
0
 
sassy168Author Commented:
now should i put all my querys in the temp tables and avoid the openrowset altogether?
0
 
Anthony PerkinsCommented:
>>but it wasn't clear in the beginning because you didn't say clearly to do the create temp table outside of the query. <<
How much clearer could I have made it (from http:#a33758977)  Can you not see those are two separate steps?
You would do something like this:
CREATE TABLE #Temp1 (
		End_Date datetime,
		Qty integer
		)
			
CREATE INDEX #IX_Temp1 ON #Temp1(End_Date)

INSERT #Temp1(End_Date, Qty)
SELECT End_Date, Qty
	 FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
			  SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''4120''))
GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')


Then your main select becomes:

SELECT ...

FROM ...
        LEFT JOIN #Temp1 AS d ON n.Load_Date = d.End_Date   
...
 

Open in new window

0
 
sassy168Author Commented:
because you put :

Instead of:
LEFT OUTER JOIN (
       SELECT End_Date, Qty
             FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
                   etc

I assume you wanted me to replace that whole left outer join section so if you see what i did to follow your instructions is i put CREATE TEMP Table inside the left outer join.  If you say Instead of  you should put what is going to replace. but what you should probably have said is to put the CREATE TEMP table instructions on the top of the query, and then use the temp table as a regular table as usual. NOt completely my fault!!
0
 
Anthony PerkinsCommented:
>>now should i put all my querys in the temp tables and avoid the openrowset altogether?<<
No I did not say that and you can see that I am not avoiding them.  I just think you should refactor your derived tables and instead use temp tables instead.
0
 
Anthony PerkinsCommented:
>>NOt completely my fault!!<<
I agree.  I assume that members who post here are conversant with the T-SQL language and obviously that is not the case here.
0
 
sassy168Author Commented:
if we were good at it we wouldn't be posting questions here.
0
 
Anthony PerkinsCommented:
I did not say good.  I just said "conversant".  That is someone that understands the basic T-SQL syntax for a SELECT or at the very least is prepared to look up the syntax in BOL.
0
 
sassy168Author Commented:
well i know basic sql. I am just not good at doing temp tables, so i thought maybe its possible to create the table within a query or something. So i guess NOT .are we gonna continue arguing about how little I know? if you're unhappy with my level of SQL knowledge you can probably skip my question and not answer.
0
 
Anthony PerkinsCommented:
Fair enough.

Good luck.
0
 
sassy168Author Commented:
one last question and i am going to close this question. THis relates to the original question about my query taking too much memory. If i follow your suggestion to restructure the query to put some of them into temp tables but I  put 20 temp tables in one single stored procedure, would it still run into memory issues? Here is my revised query for ONE workcenter, but I need to put 10-20 of this instance in one Stored procedure.....just wondering if this would still put me in the same predicament after I make all the changes.

CREATE TABLE #5003_DAILY (
            End_Date datetime,
            Qty integer
            )
                  
CREATE INDEX #IX_5003_DAILY ON #5003_DAILY(End_Date)

 CREATE TABLE #5003_WIP (
            
            WIPQty integer
            )

CREATE TABLE #5003_HOURLY (
			Load_Hour datetime,
            End_Date datetime,
            Qty integer
            )
CREATE INDEX #IX_HOURLY ON #5003_HOURLY(End_Date)

INSERT #5003_DAILY(End_Date, Qty)
SELECT End_Date, Qty
       FROM   OPENROWSET('SQLOLEDB',
                             'IDDB02'; 'opra'; 'oprapw',
                             '
                    SELECT     CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date, CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty) as INT) AS Qty FROM dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code WHERE (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)) AND (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= GETDATE()) AND
((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''5003''))
GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112)')

 


INSERT #5003_WIP(WIPQty)
SELECT WIPQty
FROM   OPENROWSET('SQLOLEDB',
                                       'IDDB02'; 'opra'; 'oprapw',
                                       '
					SELECT    IsNull( CAST(SUM(Order_Operation_Qty) as INT),0) AS WIPQty
					FROM         dimdbmc.dbo.t_Mfg_Order_Operation
					WHERE     (Mfg_Order_Oper_Status_Code = ''ACTIVE'')
				 	AND      (Work_Center_Code = ''5003'')')
 

INSERT #5003_HOURLY(Load_Hour,End_Date, Qty)
SELECT Load_Hour, End_Date, Qty
            FROM   OPENROWSET('SQLOLEDB',
            'IDDB02'; 'opra'; 'oprapw',
                                      
   		    'SELECT    CONVERT(VARCHAR(2), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 108) AS Load_Hour,
			CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) AS End_Date,
			dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code,			 
            CAST(SUM(dimdbmc.dbo.t_Mfg_Order_Output.Total_Material_In_Load_Qty)as int) AS Qty			
			FROM       dimdbmc.dbo.t_Mfg_Order_Output INNER JOIN
            dimdbmc.dbo.t_Mfg_Order_Operation ON dimdbmc.dbo.t_Mfg_Order_Output.Mfg_Order_Nbr = dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Nbr AND 
            dimdbmc.dbo.t_Mfg_Order_Output.MMM_Facility_Code = dimdbmc.dbo.t_Mfg_Order_Operation.MMM_Facility_Code
			 WHERE      (dimdbmc.dbo.t_Mfg_Order_Operation.Mfg_Order_Oper_Status_Code = ''APPROVED'') AND
			(CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) >= CONVERT(varchar(8), GETDATE(), 112) 
            AND (CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112) <= CONVERT(varchar(8), GETDATE(), 112)))
			AND
			((dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code = ''5003''))
			GROUP BY   CONVERT(VARCHAR(8), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 112),
			CONVERT(VARCHAR(2), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 108),
			dimdbmc.dbo.t_Mfg_Order_Operation.Work_Center_Code Order by  CONVERT(VARCHAR(2), dimdbmc.dbo.t_Mfg_Order_Operation.Actual_End_Datetime, 108) ')


--//The following code belongs to SIM PASTE Operation//
SELECT a.Factory_Name collate SQL_Latin1_General_CP1_CI_AS as'Factory',
       a.Operation_Name collate SQL_Latin1_General_CP1_CI_AS as 'Operation',  
       '' as 'IC-Code',
       'DAILY' as 'Daily/Hourly',
	   a.Load_Date as 'Date',
       '' as 'Hour',
	   a.Qty as 'Qty-out',
       a.GoalQty  as 'Goal Qty',
	   CONVERT(CHAR(9), GETDATE(), 1) + 'at ' + CONVERT(CHAR(5), GETDATE(), 108) AS 'RunDate/Time',
       a.Total_Target 'Total Monthly Goal',
       '' 'IC-Code Desc',
       a.WIPQty 'WIP-Qty'

       
FROM
(

SELECT
	   e.Factory_Name,
       e.Operation_Name,
	   e.Load_Date,       
	   e.Qty,
	   CASE WHEN ( ( c.isWeekday = 1 )
                    AND ( c.isHoliday = 0 )
                  ) THEN e.Total_Target / w.Working_Days
        ELSE 0
        END AS GoalQty ,
	    e.Total_Target,           
		g.WIPQty  
       
FROM
(
			
				SELECT n.Load_Date ,
				ISNULL(d.Qty, 0) AS Qty,
				b.Factory_Name ,
				b.Operation_Name ,
				b.Total_Target  
	
				FROM   ( 

						SELECT     CONVERT(VARCHAR(8), dt, 112) AS Load_Date FROM  RTC.dbo.Corp_Calendar  
						WHERE     ( dt >= DATEADD(M, DATEDIFF(M, 0, GETDATE()), 0) )
						AND ( dt < DATEADD(M, DATEDIFF(M, 0, GETDATE()) + 1, 0) )
						) AS n
						LEFT OUTER JOIN ( 
                            

							 SELECT End_Date, Qty
							 FROM  #5003_DAILY
        
           
							) AS d ON n.Load_Date = d.End_Date   


					  CROSS JOIN ( SELECT Factory_Name, Operation_Name, SUM(Monthly_Target) AS Total_Target 
                                        FROM dbo.WC_Target WHERE (WC_Code = 5003) and Factory_Name='CASSI' GROUP BY												Factory_Name, Operation_Name
------End Query 3
                   ) b        


)e

 LEFT OUTER JOIN ( 
------Begin Query 4: Check if date is weekday or holiday
Select dt, isWeekday, isHoliday from RTC.dbo.Corp_Calendar where dt >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)and dt < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)
------End Query 4         

												) AS c ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
        LEFT OUTER JOIN ( 
------Begin Query 5: Check how many working days in a month
					 SELECT COUNT(*) as Working_Days FROM dbo.Corp_Calendar WHERE isWeekday = 1 AND isHoliday = 0 
												AND dt >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)and dt < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0) 
                                   
------End Query 5

                        ) AS w ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date                             

		
		
		 CROSS JOIN (

					 
					 SELECT IsNull(WIPQty,0) as WIPQty
                     FROM   #5003_WIP
                          
          
                                   
------End Query 6

					) g
								
) a

 
 UNION ALL

SELECT b.Factory_Name as 'Factory',
       b.Operation_Name as 'Operation',
       '' as 'IC-Code',
        'HOURLY' as 'Daily/Hourly',      
       b.Load_Date as 'Date',
       b.Load_Hour as 'Hour',
	   b.Qty as 'Qty-out',
       b.GoalQty as 'Goal Qty',
	   CONVERT(CHAR(9), GETDATE(), 1) + 'at ' + CONVERT(CHAR(5), GETDATE(), 108) AS 'RunDate/Time',
       b.Total_Target as 'Total Monthly Goal',
       '' as 'IC-Code Desc',
       b.WIPQty as 'WIP-Qty'
   
FROM
(

SELECT    
		   e.Factory_Name, 
           e.Operation_Name,            
		   e.Load_Date, 
		   e.Load_Hour, 
		   e.Qty,		  		  
		   CASE WHEN ( ( c.isWeekday = 1 )
                    AND ( c.isHoliday = 0 )
                  ) THEN ((e.Total_Target / w.Working_Days)/24)
           ELSE 0
		   END AS GoalQty,			   
		   e.Total_Target,
		   g.WIPQty



FROM(

SELECT     n.Load_Hour, 
			  ISNULL(d.End_Date, 
			  CONVERT(varchar(8), GETDATE(), 112)) AS Load_Date, 
			  ISNULL(d.Qty, 0) AS Qty,
			    b.Factory_Name, b.Operation_Name, 
			  b.Total_Target
         
              
			  FROM          
						(

------Query 1: This uses a system table to grab numbers from 0-23 (denoting 24 hours in a day)

						 SELECT     RIGHT('0' + CAST(number AS varchar(2)), 2) AS Load_Hour
                         FROM          master.dbo.spt_values
                         WHERE      (type = 'p') AND (number BETWEEN 0 AND 23)
------End Query 1

						) AS n 

LEFT OUTER JOIN
            (
------Query 2: This calculates the produced quantity from IMES for current day only

			SELECT Load_Hour, End_Date, Qty
            FROM   #5003_HOURLY )
			
------End Query 2
			
			 AS d ON n.Load_Hour = d.Load_Hour 
			 
		 

CROSS JOIN
------Query 3: This imports Target from excel to a table in IDDB01/RTC It needs to be automatically scheduled/imported 
            (SELECT    Factory_Name, Operation_Name, SUM(Monthly_Target) AS Total_Target 
                                       FROM dbo.WC_Target WHERE (WC_Code = 5003)  and Factory_Name='CASSI' GROUP BY Factory_Name, Operation_Name
		     )
                                       AS b) AS e


LEFT OUTER JOIN ( 

------Query 4: This checks the calendar table in IDDB01/RTC if current day is weekday or holiday
			Select dt, isWeekday, isHoliday from dbo.Corp_Calendar where CONVERT(varchar(8), dt, 112)  = CONVERT(varchar(8), GETDATE(), 112)
										) AS c ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
------End Query 4

LEFT OUTER JOIN (

 ------Begin Query 5: This links to IDDB01/ RTC. THis will count the number of working days in the current month

	 					 SELECT COUNT(*) as Working_Days FROM dbo.Corp_Calendar WHERE isWeekday = 1 AND isHoliday = 0 
												AND dt >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)and dt < DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0) 
                                    
                        ) AS w ON CONVERT(VARCHAR(8), c.dt, 112) = e.Load_Date
------End Query 5



		 CROSS JOIN (	
 					
					  SELECT IsNull(WIPQty,0) as WIPQty
                     FROM   #5003_WIP
                                                                      
------End Query 6
					) g


) b
Order By Hour


DROP TABLE #5003_DAILY
DROP TABLE #5003_HOURLY
DROP TABLE #5003_WIP		

 
 
			

	 
 
					
             
 
         

Open in new window

0
 
Anthony PerkinsCommented:
My guess is it will not. And if coded correctly should require less memory and at the same time be more performant.
0
 
sassy168Author Commented:
thanks for your help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 15
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now