Solved

HELP with provider ran out of memory

Posted on 2010-09-03
30
967 Views
Last Modified: 2012-06-27
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
Comment
Question by:sassy168
  • 15
  • 14
30 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33601253
It means it "ran out of memory".  It is time to refactor your query.
0
 
LVL 9

Expert Comment

by:shalabhsharma
ID: 33624101
it looks the Server resources is over-usage you should update your provider.

 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33632829
>>you should update your provider.<<
Huh? Do you think the provider is suffering from Alzheimer?
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.

 

Author Comment

by:sassy168
ID: 33678272
so tthat means my query is too intensive?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33678353
No.  It means it needs to be optimized.
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Author Comment

by:sassy168
ID: 33784871
now should i put all my querys in the temp tables and avoid the openrowset altogether?
0
 
LVL 75

Expert Comment

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

Author Comment

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

Expert Comment

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

Expert Comment

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

Author Comment

by:sassy168
ID: 33790793
if we were good at it we wouldn't be posting questions here.
0
 
LVL 75

Expert Comment

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

Author Comment

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

Expert Comment

by:Anthony Perkins
ID: 33794379
Fair enough.

Good luck.
0
 

Author Comment

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

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 33804134
My guess is it will not. And if coded correctly should require less memory and at the same time be more performant.
0
 

Author Closing Comment

by:sassy168
ID: 33842975
thanks for your help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Refresh Dev server with Production database 8 28
string fuctions 4 26
How to search for strings inside db views 4 27
SQL Server Error 21 8 24
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

773 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