Solved

HELP with provider ran out of memory

Posted on 2010-09-03
30
948 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Microsoft SQL query 7 39
Convert column to int 13 27
how to fix this error 14 46
Trouble connecting to SqlServer database 4 31
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 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

15 Experts available now in Live!

Get 1:1 Help Now