Strange out of memory error with SQL 2005 - what to do??

Hi,

I've been monitoring SQL 2005 out of memory error threads and the same has been happening to me.

My development environment includes a Pentium 3.2 Ghz single processor with 3GB memory. Loaded on the pc are SQL server 2005 development version, IIS 6.0, .NET 2.0. The OS is MS SERVER 2003 SP1 standard.

My production environment includes a DELL PowerEdge 6650 with 4 XEON 1.9Ghz processors and 4GB memory. Loaded are MS server 2003 sp1 r2 standard, SQL Server 2005 Standard Edition, IIS 6.0, .NET2.0.

Here's my situation. I run a stored proc needed to process data during a database refresh. It runs fine in the development environment but I get the Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION in the production environment.

I've looked at just about everything I can think of. Both servers, and databases are similarly configured. Both contain a linked server and yes I tried running the SP without the linked server in an attempt to troubleshoot the error. I've even looked at TEMPDB on both servers to see if that
had something to do with it.

There's nothing complicated about the SP. The volume of records is about 100K.

I'm at a loss here. Can someone please help me with this..

Here's a copy of the error log and a copy of the troubled SP.

2006-05-29 12:00:46.91 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-05-29 12:00:46.91 Server (c) 2005 Microsoft Corporation.
2006-05-29 12:00:46.91 Server All rights reserved.
2006-05-29 12:00:46.91 Server Server process ID is 3280.
2006-05-29 12:00:46.91 Server Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-05-29 12:00:46.93 Server This instance of SQL Server last reported using a process ID of 1988 at 5/29/2006 12:00:43 PM (local) 5/29/2006 4:00:43 PM (UTC). This is an informational message only; no user action is required.
2006-05-29 12:00:46.93 Server Registry startup parameters:
2006-05-29 12:00:46.93 Server -d D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-05-29 12:00:46.93 Server -e D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-05-29 12:00:46.93 Server -l D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-05-29 12:00:46.93 Server SQL Server is starting at high priority base (=13). This is an informational message only. No user action is required.
2006-05-29 12:00:46.93 Server Detected 8 CPUs. This is an informational message; no user action is required.
2006-05-29 12:00:47.10 Server Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2006-05-29 12:00:47.18 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-05-29 12:00:47.32 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2006-05-29 12:00:49.39 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2006-05-29 12:00:49.39 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-05-29 12:00:49.39 spid5s Starting up database 'master'.
2006-05-29 12:00:49.73 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-05-29 12:00:49.78 spid5s SQL Trace ID 1 was started by login "sa".
2006-05-29 12:00:49.81 spid5s Starting up database 'mssqlsystemresource'.
2006-05-29 12:00:50.00 spid5s Server name is 'DAS01'. This is an informational message only. No user action is required.
2006-05-29 12:00:50.00 spid9s Starting up database 'model'.
2006-05-29 12:00:50.14 spid9s Clearing tempdb database.
2006-05-29 12:00:50.30 spid9s Starting up database 'tempdb'.
2006-05-29 12:00:50.34 Server A self-generated certificate was successfully loaded for encryption.
2006-05-29 12:00:50.34 spid12s The Service Broker protocol transport is disabled or not configured.
2006-05-29 12:00:50.34 spid12s The Database Mirroring protocol transport is disabled or not configured.
2006-05-29 12:00:50.34 Server Server is listening on [ 'any' <ipv4> 1433].
2006-05-29 12:00:50.34 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2006-05-29 12:00:50.34 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2006-05-29 12:00:50.36 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2006-05-29 12:00:50.36 Server Dedicated admin connection support was established for listening locally on port 1434.
2006-05-29 12:00:50.36 spid12s Service Broker manager has started.
2006-05-29 12:00:50.42 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-05-29 12:00:50.58 spid23s Starting up database 'msdb'.
2006-05-29 12:00:50.58 spid21s Starting up database 'DAReportServer'.
2006-05-29 12:00:50.58 spid22s Starting up database 'DiamondDSDW'.
2006-05-29 12:00:51.46 spid51 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-05-29 12:00:51.80 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2006-05-29 12:00:51.80 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-05-29 12:02:12.62 spid53 Using 'dbghelp.dll' version '4.0.5'
2006-05-29 12:02:12.64 spid53 ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0031.txt
2006-05-29 12:02:12.64 spid53 SqlDumpExceptionHandler: Process 53 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-05-29 12:02:12.64 spid53 * *******************************************************************************
2006-05-29 12:02:12.64 spid53 *
2006-05-29 12:02:12.64 spid53 * BEGIN STACK DUMP:
2006-05-29 12:02:12.64 spid53 * 05/29/06 12:02:12 spid 53
2006-05-29 12:02:12.64 spid53 *
2006-05-29 12:02:12.64 spid53 *
2006-05-29 12:02:12.64 spid53 * Exception Address = 0102576C Module(sqlservr+0002576C)
2006-05-29 12:02:12.64 spid53 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-05-29 12:02:12.64 spid53 * Access Violation occurred reading address 0000010C
2006-05-29 12:02:12.64 spid53 * Input Buffer 510 bytes -
2006-05-29 12:02:12.64 spid53 * SELECT sp.name AS [Name], sp.object_id AS [ID], sp.create_dat
2006-05-29 12:02:12.64 spid53 * e AS [CreateDate], sp.modify_date AS [DateLastModified], SCHEMA_NAME(sp.
2006-05-29 12:02:12.64 spid53 * schema_id) AS [Schema], CAST( case when sp.is_ms_shipped = 1 the
2006-05-29 12:02:12.64 spid53 * n 1 when ( select major_id from
2006-05-29 12:02:12.64 spid53 * sys.extended_properties where major_
2006-05-29 12:02:12.64 spid53 * id = sp.object_id and minor_id = 0 and class
2006-05-29 12:02:12.64 spid53 * = 1 and name = N'microsoft_database_tools_support')
2006-05-29 12:02:12.64 spid53 * is not null then 1 else 0 end AS bit)
2006-05-29 12:02:12.64 spid53 * AS [IsSystemObject], CAST(OBJECTPROPERTYEX(sp.object_id,N'ExecIsAnsiNul
2006-05-29 12:02:12.64 spid53 * lsOn') AS bit) AS [AnsiNullsStatus], CAST(OBJECTPROPERTYEX(sp.object_id,
2006-05-29 12:02:12.64 spid53 * N'ExecIsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus], CAST(CASE W
2006-05-29 12:02:12.64 spid53 * HEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END
2006-05-29 12:02:12.64 spid53 * AS bit) AS [IsEncrypted], CAST(ISNULL(smsp.is_recompiled, ssmsp.is_recom
2006-05-29 12:02:12.64 spid53 * piled) AS bit) AS [Recompile], case when amsp.object_id is null then N''
2006-05-29 12:02:12.64 spid53 * else asmblsp.name end AS [AssemblyName], case when amsp.object_id is nu
2006-05-29 12:02:12.64 spid53 * ll then N'' else amsp.assembly_class end AS [ClassName], case when amsp.
2006-05-29 12:02:12.64 spid53 * object_id is null then N'' else amsp.assembly_method end AS [MethodName]
2006-05-29 12:02:12.64 spid53 * , case when amsp.object_id is null then case isnull(smsp.execute_as_prin
2006-05-29 12:02:12.64 spid53 * cipal_id, -1) when -1 then 1 when -2 then 2 else 3 end else case isnull(
2006-05-29 12:02:12.64 spid53 * amsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 e
2006-05-29 12:02:12.64 spid53 * nd end AS [ExecutionContext], case when amsp.object_id is null then ISNU
2006-05-29 12:02:12.64 spid53 * LL(user_name(smsp.execute_as_principal_id),N'') else user_name(amsp.exec
2006-05-29 12:02:12.64 spid53 * ute_as_principal_id) end AS [ExecutionContextPrincipal], CAST(ISNULL(spp
2006-05-29 12:02:12.64 spid53 * .is_auto_executed,0) AS bit) AS [Startup], CAST(CASE sp.type WHEN N'RF'
2006-05-29 12:02:12.64 spid53 * THEN 1 ELSE 0 END AS bit) AS [ForReplication], CASE WHEN sp.type = N'P'
2006-05-29 12:02:12.64 spid53 * THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType] FR
2006-05-29 12:02:12.64 spid53 * OM sys.all_objects AS sp LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp
2006-05-29 12:02:12.64 spid53 * .object_id = sp.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssms
2006-05-29 12:02:12.64 spid53 * p ON ssmsp.object_id = sp.ob
2006-05-29 12:02:12.64 spid53 *
2006-05-29 12:02:12.64 spid53 *
2006-05-29 12:02:12.64 spid53 * MODULE BASE END SIZE
2006-05-29 12:02:12.64 spid53 * sqlservr 01000000 02BA7FFF 01ba8000
2006-05-29 12:02:12.64 spid53 * ntdll 7C800000 7C8BFFFF 000c0000
2006-05-29 12:02:12.64 spid53 * kernel32 77E40000 77F41FFF 00102000
2006-05-29 12:02:12.64 spid53 * MSVCR80 78130000 781CAFFF 0009b000
2006-05-29 12:02:12.64 spid53 * msvcrt 77BA0000 77BF9FFF 0005a000
2006-05-29 12:02:12.64 spid53 * MSVCP80 7C420000 7C4A6FFF 00087000
2006-05-29 12:02:12.64 spid53 * ADVAPI32 77F50000 77FEBFFF 0009c000
2006-05-29 12:02:12.64 spid53 * RPCRT4 77C50000 77CEEFFF 0009f000
2006-05-29 12:02:12.64 spid53 * USER32 77380000 77411FFF 00092000
2006-05-29 12:02:12.64 spid53 * GDI32 77C00000 77C47FFF 00048000
2006-05-29 12:02:12.64 spid53 * CRYPT32 761B0000 76242FFF 00093000
2006-05-29 12:02:12.64 spid53 * MSASN1 76190000 761A1FFF 00012000
2006-05-29 12:02:12.64 spid53 * Secur32 76F50000 76F62FFF 00013000
2006-05-29 12:02:12.64 spid53 * MSWSOCK 71B20000 71B60FFF 00041000
2006-05-29 12:02:12.64 spid53 * WS2_32 71C00000 71C16FFF 00017000
2006-05-29 12:02:12.64 spid53 * WS2HELP 71BF0000 71BF7FFF 00008000
2006-05-29 12:02:12.64 spid53 * USERENV 76920000 769E3FFF 000c4000
2006-05-29 12:02:12.64 spid53 * opends60 333E0000 333E6FFF 00007000
2006-05-29 12:02:12.64 spid53 * NETAPI32 71C40000 71C97FFF 00058000
2006-05-29 12:02:12.64 spid53 * SHELL32 7C8D0000 7D0D2FFF 00803000
2006-05-29 12:02:12.64 spid53 * SHLWAPI 77DA0000 77DF1FFF 00052000
2006-05-29 12:02:12.64 spid53 * comctl32 77420000 77522FFF 00103000
2006-05-29 12:02:12.64 spid53 * psapi 76B70000 76B7AFFF 0000b000
2006-05-29 12:02:12.64 spid53 * instapi 48060000 48069FFF 0000a000
2006-05-29 12:02:12.64 spid53 * sqlevn70 4F610000 4F7A0FFF 00191000
2006-05-29 12:02:12.64 spid53 * SQLOS 344D0000 344D4FFF 00005000
2006-05-29 12:02:12.64 spid53 * rsaenh 05B10000 05B3EFFF 0002f000
2006-05-29 12:02:12.64 spid53 * AUTHZ 76C40000 76C53FFF 00014000
2006-05-29 12:02:12.64 spid53 * MSCOREE 065D0000 06614FFF 00045000
2006-05-29 12:02:12.64 spid53 * ole32 06810000 06943FFF 00134000
2006-05-29 12:02:12.64 spid53 * msv1_0 76C90000 76CB6FFF 00027000
2006-05-29 12:02:12.64 spid53 * iphlpapi 76CF0000 76D09FFF 0001a000
2006-05-29 12:02:12.64 spid53 * kerberos 06980000 069D7FFF 00058000
2006-05-29 12:02:12.64 spid53 * cryptdll 766E0000 766EBFFF 0000c000
2006-05-29 12:02:12.64 spid53 * schannel 76750000 76776FFF 00027000
2006-05-29 12:02:12.64 spid53 * COMRES 06A40000 06B05FFF 000c6000
2006-05-29 12:02:12.64 spid53 * XOLEHLP 06B10000 06B15FFF 00006000
2006-05-29 12:02:12.64 spid53 * MSDTCPRX 06B20000 06B97FFF 00078000
2006-05-29 12:02:12.64 spid53 * msvcp60 780C0000 78120FFF 00061000
2006-05-29 12:02:12.64 spid53 * MTXCLU 06BA0000 06BB8FFF 00019000
2006-05-29 12:02:12.64 spid53 * VERSION 77B90000 77B97FFF 00008000
2006-05-29 12:02:12.64 spid53 * WSOCK32 71BB0000 71BB8FFF 00009000
2006-05-29 12:02:12.64 spid53 * OLEAUT32 77D00000 77D8BFFF 0008c000
2006-05-29 12:02:12.64 spid53 * CLUSAPI 06BC0000 06BD1FFF 00012000
2006-05-29 12:02:12.64 spid53 * RESUTILS 06BE0000 06BF2FFF 00013000
2006-05-29 12:02:12.64 spid53 * DNSAPI 76ED0000 76EF8FFF 00029000
2006-05-29 12:02:12.64 spid53 * winrnr 06C50000 06C56FFF 00007000
2006-05-29 12:02:12.64 spid53 * WLDAP32 76F10000 76F3DFFF 0002e000
2006-05-29 12:02:12.64 spid53 * rasadhlp 06C80000 06C84FFF 00005000
2006-05-29 12:02:12.64 spid53 * security 07050000 07053FFF 00004000
2006-05-29 12:02:12.64 spid53 * msfte 079B0000 07C07FFF 00258000
2006-05-29 12:02:12.64 spid53 * dbghelp 07C10000 07D27FFF 00118000
2006-05-29 12:02:12.64 spid53 * WINTRUST 76BB0000 76BDAFFF 0002b000
2006-05-29 12:02:12.64 spid53 * imagehlp 76C10000 76C38FFF 00029000
2006-05-29 12:02:12.64 spid53 * dssenh 08010000 08033FFF 00024000
2006-05-29 12:02:12.64 spid53 * hnetcfg 08060000 080B8FFF 00059000
2006-05-29 12:02:12.64 spid53 * wshtcpip 71AE0000 71AE7FFF 00008000
2006-05-29 12:02:12.64 spid53 * NTMARTA 77E00000 77E21FFF 00022000
2006-05-29 12:02:12.64 spid53 * SAMLIB 08200000 0820EFFF 0000f000
2006-05-29 12:02:12.64 spid53 * ntdsapi 766F0000 76704FFF 00015000
2006-05-29 12:02:12.64 spid53 * xpsp2res 10000000 102C4FFF 002c5000
2006-05-29 12:02:12.64 spid53 * CLBCatQ 083A0000 08422FFF 00083000
2006-05-29 12:02:12.64 spid53 * sqlncli 08430000 0864DFFF 0021e000
2006-05-29 12:02:12.64 spid53 * COMCTL32 08650000 086E6FFF 00097000
2006-05-29 12:02:12.64 spid53 * comdlg32 007A0000 007E9FFF 0004a000
2006-05-29 12:02:12.64 spid53 * SQLNCLIR 08700000 08732FFF 00033000
2006-05-29 12:02:12.64 spid53 * msftepxy 08840000 08854FFF 00015000
2006-05-29 12:02:12.64 spid53 * xpstar90 08AD0000 08B14FFF 00045000
2006-05-29 12:02:12.64 spid53 * SQLSCM90 08B30000 08B38FFF 00009000
2006-05-29 12:02:12.64 spid53 * ODBC32 08B50000 08B8CFFF 0003d000
2006-05-29 12:02:12.64 spid53 * BatchParser90 08B90000 08BADFFF 0001e000
2006-05-29 12:02:12.64 spid53 * SQLSVC90 08BC0000 08BD9FFF 0001a000
2006-05-29 12:02:12.64 spid53 * SqlResourceLoader 08BF0000 08BF5FFF 00006000
2006-05-29 12:02:12.64 spid53 * ATL80 7C630000 7C64AFFF 0001b000
2006-05-29 12:02:12.64 spid53 * odbcint 08ED0000 08EE6FFF 00017000
2006-05-29 12:02:12.64 spid53 * SQLSVC90 08EF0000 08EF2FFF 00003000
2006-05-29 12:02:12.64 spid53 * xpstar90 08F00000 08F25FFF 00026000
2006-05-29 12:02:12.64 spid53 * dbghelp 090B0000 091C7FFF 00118000
2006-05-29 12:02:12.64 spid53 *
2006-05-29 12:02:12.65 spid53 * Edi: 16A88A98: 00200000 00000000 00000000 00000000 00000000 00000000
2006-05-29 12:02:12.65 spid53 * Esi: 01000000: 00905A4D 00000003 00000004 0000FFFF 000000B8 00000000
2006-05-29 12:02:12.65 spid53 * Eax: 00000100:
2006-05-29 12:02:12.65 spid53 * Ebx: 00000000:
2006-05-29 12:02:12.65 spid53 * Ecx: 01005EA0: 023E422D 010078DB 010078B4 023E5385 023E53A5 0105E1A9
2006-05-29 12:02:12.65 spid53 * Edx: 00000100:
2006-05-29 12:02:12.65 spid53 * Eip: 0102576C: 8B0C408B 558B5208 558B5214 558B5210 8B50520C D0FF3C41
2006-05-29 12:02:12.65 spid53 * Ebp: 072FEE64: 072FEECC 010629A6 00000100 1681E040 010622D8 00000461
2006-05-29 12:02:12.65 spid53 * SegCs: 0000001B:
2006-05-29 12:02:12.65 spid53 * EFlags: 00010202: 0042004D 00520045 004F005F 005F0046 00520050 0043004F
2006-05-29 12:02:12.65 spid53 * Esp: 072FEE48: 01005BDE 1681E040 00000100 010622D8 00000461 00000005
2006-05-29 12:02:12.65 spid53 * SegSs: 00000023:
2006-05-29 12:02:12.65 spid53 * *******************************************************************************
2006-05-29 12:02:12.65 spid53 * -------------------------------------------------------------------------------
2006-05-29 12:02:12.65 spid53 * Short Stack Dump
2006-05-29 12:02:12.65 spid53 0102576C Module(sqlservr+0002576C)
2006-05-29 12:02:12.65 spid53 010629A6 Module(sqlservr+000629A6)
2006-05-29 12:02:12.65 spid53 01065338 Module(sqlservr+00065338)
2006-05-29 12:02:12.65 spid53 01067E98 Module(sqlservr+00067E98)
2006-05-29 12:02:12.65 spid53 01067F24 Module(sqlservr+00067F24)
2006-05-29 12:02:12.65 spid53 01065EC7 Module(sqlservr+00065EC7)
2006-05-29 12:02:12.65 spid53 01065DB4 Module(sqlservr+00065DB4)
2006-05-29 12:02:12.65 spid53 01428F77 Module(sqlservr+00428F77)
2006-05-29 12:02:12.65 spid53 01428E7E Module(sqlservr+00428E7E)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 0106C3F8 Module(sqlservr+0006C3F8)
2006-05-29 12:02:12.65 spid53 0106BEEA Module(sqlservr+0006BEEA)
2006-05-29 12:02:12.65 spid53 01428F77 Module(sqlservr+00428F77)
2006-05-29 12:02:12.65 spid53 01428E7E Module(sqlservr+00428E7E)
2006-05-29 12:02:12.65 spid53 01065D13 Module(sqlservr+00065D13)
2006-05-29 12:02:12.65 spid53 01065D13 Module(sqlservr+00065D13)
2006-05-29 12:02:12.65 spid53 01E035BF Module(sqlservr+00E035BF)
2006-05-29 12:02:12.65 spid53 01E0345F Module(sqlservr+00E0345F)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 01438B0E Module(sqlservr+00438B0E)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 01438B0E Module(sqlservr+00438B0E)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 01438B0E Module(sqlservr+00438B0E)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 01438B0E Module(sqlservr+00438B0E)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 01438B0E Module(sqlservr+00438B0E)
2006-05-29 12:02:12.65 spid53 0106C038 Module(sqlservr+0006C038)
2006-05-29 12:02:12.65 spid53 01438B0E Module(sqlservr+00438B0E)
2006-05-29 12:02:12.65 spid53 01065D13 Module(sqlservr+00065D13)
2006-05-29 12:02:12.65 spid53 01066AC6 Module(sqlservr+00066AC6)
2006-05-29 12:02:12.65 spid53 01066714 Module(sqlservr+00066714)
2006-05-29 12:02:12.65 spid53 01066C2C Module(sqlservr+00066C2C)
2006-05-29 12:02:12.65 spid53 0103D2FE Module(sqlservr+0003D2FE)
2006-05-29 12:02:12.65 spid53 0103D23D Module(sqlservr+0003D23D)
2006-05-29 12:02:12.65 spid53 0103D0C6 Module(sqlservr+0003D0C6)
2006-05-29 12:02:12.65 spid53 0102DB52 Module(sqlservr+0002DB52)
2006-05-29 12:02:12.65 spid53 0102E0D0 Module(sqlservr+0002E0D0)
2006-05-29 12:02:12.65 spid53 0102C5F8 Module(sqlservr+0002C5F8)
2006-05-29 12:02:12.65 spid53 010438E5 Module(sqlservr+000438E5)
2006-05-29 12:02:12.65 spid53 01041C35 Module(sqlservr+00041C35)
2006-05-29 12:02:12.65 spid53 0100889F Module(sqlservr+0000889F)
2006-05-29 12:02:12.65 spid53 010089C5 Module(sqlservr+000089C5)
2006-05-29 12:02:12.65 spid53 010086E7 Module(sqlservr+000086E7)
2006-05-29 12:02:12.65 spid53 010D764A Module(sqlservr+000D764A)
2006-05-29 12:02:12.65 spid53 010D7B71 Module(sqlservr+000D7B71)
2006-05-29 12:02:12.65 spid53 010D746E Module(sqlservr+000D746E)
2006-05-29 12:02:12.67 spid53 010D83F0 Module(sqlservr+000D83F0)
2006-05-29 12:02:12.67 spid53 781329AA Module(MSVCR80+000029AA)
2006-05-29 12:02:12.67 spid53 78132A36 Module(MSVCR80+00002A36)
2006-05-29 12:02:12.68 spid53 Stack Signature for the dump is 0xBE5C6732
2006-05-29 12:02:13.14 spid53 External dump process return code 0x20000001.
External dump process returned no errors.

2006-05-29 12:02:13.16 Server Error: 17310, Severity: 20, State: 1.
2006-05-29 12:02:13.16 Server A user request from the session with SPID 53 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
2006-05-29 12:03:14.54 spid55 Using 'dbghelp.dll' version '4.0.5'
2006-05-29 12:03:14.54 spid55 ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0032.txt
2006-05-29 12:03:14.54 spid55 SqlDumpExceptionHandler: Process 2192 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-05-29 12:03:14.54 spid55 * *******************************************************************************
2006-05-29 12:03:14.54 spid55 *
2006-05-29 12:03:14.54 spid55 * BEGIN STACK DUMP:
2006-05-29 12:03:14.56 spid55 * 05/29/06 12:03:14 spid 55
2006-05-29 12:03:14.56 spid55 *
2006-05-29 12:03:14.56 spid55 *
2006-05-29 12:03:14.56 spid55 * Exception Address = 0143826B Module(sqlservr+0043826B)
2006-05-29 12:03:14.56 spid55 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-05-29 12:03:14.56 spid55 * Access Violation occurred reading address 00000000
2006-05-29 12:03:14.56 spid55 * Input Buffer 510 bytes -
2006-05-29 12:03:14.56 spid55 * -- ALTER PROCEDURE [dbo].[SpAggrSGLInventorySumma
2006-05-29 12:03:14.56 spid55 * ry] AS -- Form item and time period template to fill in accounts and t
2006-05-29 12:03:14.56 spid55 * heir respective balances DECLARE @minBalRange AS smalldatetime,@maxB
2006-05-29 12:03:14.56 spid55 * alRange as smalldatetime SELECT @minBalRange = dbo.convert_period(MIN(
2006-05-29 12:03:14.56 spid55 * DocumentDate)) FROM FactInventory SELECT @maxBalRange = dbo.convert_pe
2006-05-29 12:03:14.56 spid55 * riod(GETDATE()) SELECT DISTINCT itemnumber INTO #ItemRef FROM F
2006-05-29 12:03:14.56 spid55 * actInventory CREATE CLUSTERED INDEX ir ON #ItemRef(ItemNumber) /* -
2006-05-29 12:03:14.56 spid55 * ------------------------------------------------------------------------
2006-05-29 12:03:14.56 spid55 * ----------*/ -- Identify and process new item / monthend combination r
2006-05-29 12:03:14.56 spid55 * ecords to add to aggregate staging by item. /* ------------------------
2006-05-29 12:03:14.56 spid55 * -----------------------------------------------------------*/ SELECT
2006-05-29 12:03:14.56 spid55 * di.ItemNumber ,dt.monthend INTO #ItemTemplate FROM DimTime dt,#I
2006-05-29 12:03:14.56 spid55 * temRef fi INNER JOIN DimItem di ON fi.ItemNumber = di.ItemNumber
2006-05-29 12:03:14.56 spid55 * WHERE dt.MonthEnd BETWEEN @minBalRange AND @maxBalRange EXCEPT S
2006-05-29 12:03:14.56 spid55 * ELECT ItemNumber ,Monthend FROM stgAggrInvenItemDistrib IF @@R
2006-05-29 12:03:14.56 spid55 * OWCOUNT > 0 -- If the month has turned over or new items added BEGIN
2006-05-29 12:03:14.56 spid55 * INSERT stgAggrInvenItemDistrib SELECT di.ItemNumber ,di.ItemC
2006-05-29 12:03:14.56 spid55 * lassCode ,di.dvGLRollup ,YearMonthID = CAST('' AS NCHAR(10))
2006-05-29 12:03:14.56 spid55 * ,MonthFormat = dbo.Mth3YearFormat(0,fi.monthend) ,fi.monthend
2006-05-29 12:03:14.56 spid55 * ,MonthQtySales = 0 ,MonthQtyCost = 0 ,MonthActivityCost =
2006-05-29 12:03:14.56 spid55 * 0 ,MonthActivitySales = 0 ,MonthQtySalesBalance = 0 ,Mon
2006-05-29 12:03:14.56 spid55 * thlyQtyCostBalance = 0 ,MonthBalance = 0 ,MonthBalanceRnd = 0
2006-05-29 12:03:14.56 spid55 * FROM #ItemTemplate fi INNER JOIN DimItem di ON fi.ItemNumber
2006-05-29 12:03:14.56 spid55 * = di.ItemNumber SELECT ItemNumber ,MinMonth = MIN(MonthEnd)
2006-05-29 12:03:14.56 spid55 * ,Maxmonth = MAX(MonthEnd) INTO #ym FROM #ItemTemplate GROUP B
2006-05-29 12:03:14.56 spid55 * Y ItemNumber UPDATE stgAggrInvenItemDistrib SET YearMonthID = 'YM'
2006-05-29 12:03:14.56 spid55 * + CASE WHEN DATEDIFF(mm,monthend,d.maxmonth) < 10 THEN '0' ELSE ''
2006-05-29 12:03:14.56 spid55 * END + CAST(DATEDIFF(mm,monthend,d.maxmonth) AS VARCHAR(3)) FROM
2006-05-29 12:03:14.56 spid55 * stgAggrInvenItemDistrib a IN
2006-05-29 12:03:14.56 spid55 *
2006-05-29 12:03:14.56 spid55 *
2006-05-29 12:03:14.56 spid55 * MODULE BASE END SIZE
2006-05-29 12:03:14.56 spid55 * sqlservr 01000000 02BA7FFF 01ba8000
2006-05-29 12:03:14.56 spid55 * ntdll 7C800000 7C8BFFFF 000c0000
2006-05-29 12:03:14.56 spid55 * kernel32 77E40000 77F41FFF 00102000
2006-05-29 12:03:14.56 spid55 * MSVCR80 78130000 781CAFFF 0009b000
2006-05-29 12:03:14.56 spid55 * msvcrt 77BA0000 77BF9FFF 0005a000
2006-05-29 12:03:14.56 spid55 * MSVCP80 7C420000 7C4A6FFF 00087000
2006-05-29 12:03:14.56 spid55 * ADVAPI32 77F50000 77FEBFFF 0009c000
2006-05-29 12:03:14.56 spid55 * RPCRT4 77C50000 77CEEFFF 0009f000
2006-05-29 12:03:14.56 spid55 * USER32 77380000 77411FFF 00092000
2006-05-29 12:03:14.56 spid55 * GDI32 77C00000 77C47FFF 00048000
2006-05-29 12:03:14.56 spid55 * CRYPT32 761B0000 76242FFF 00093000
2006-05-29 12:03:14.56 spid55 * MSASN1 76190000 761A1FFF 00012000
2006-05-29 12:03:14.56 spid55 * Secur32 76F50000 76F62FFF 00013000
2006-05-29 12:03:14.56 spid55 * MSWSOCK 71B20000 71B60FFF 00041000
2006-05-29 12:03:14.56 spid55 * WS2_32 71C00000 71C16FFF 00017000
2006-05-29 12:03:14.56 spid55 * WS2HELP 71BF0000 71BF7FFF 00008000
2006-05-29 12:03:14.56 spid55 * USERENV 76920000 769E3FFF 000c4000
2006-05-29 12:03:14.56 spid55 * opends60 333E0000 333E6FFF 00007000
2006-05-29 12:03:14.56 spid55 * NETAPI32 71C40000 71C97FFF 00058000
2006-05-29 12:03:14.56 spid55 * SHELL32 7C8D0000 7D0D2FFF 00803000
2006-05-29 12:03:14.56 spid55 * SHLWAPI 77DA0000 77DF1FFF 00052000
2006-05-29 12:03:14.56 spid55 * comctl32 77420000 77522FFF 00103000
2006-05-29 12:03:14.56 spid55 * psapi 76B70000 76B7AFFF 0000b000
2006-05-29 12:03:14.56 spid55 * instapi 48060000 48069FFF 0000a000
2006-05-29 12:03:14.56 spid55 * sqlevn70 4F610000 4F7A0FFF 00191000
2006-05-29 12:03:14.56 spid55 * SQLOS 344D0000 344D4FFF 00005000
2006-05-29 12:03:14.56 spid55 * rsaenh 05B10000 05B3EFFF 0002f000
2006-05-29 12:03:14.56 spid55 * AUTHZ 76C40000 76C53FFF 00014000
2006-05-29 12:03:14.56 spid55 * MSCOREE 065D0000 06614FFF 00045000
2006-05-29 12:03:14.56 spid55 * ole32 06810000 06943FFF 00134000
2006-05-29 12:03:14.56 spid55 * msv1_0 76C90000 76CB6FFF 00027000
2006-05-29 12:03:14.56 spid55 * iphlpapi 76CF0000 76D09FFF 0001a000
2006-05-29 12:03:14.56 spid55 * kerberos 06980000 069D7FFF 00058000
2006-05-29 12:03:14.56 spid55 * cryptdll 766E0000 766EBFFF 0000c000
2006-05-29 12:03:14.56 spid55 * schannel 76750000 76776FFF 00027000
2006-05-29 12:03:14.56 spid55 * COMRES 06A40000 06B05FFF 000c6000
2006-05-29 12:03:14.56 spid55 * XOLEHLP 06B10000 06B15FFF 00006000
2006-05-29 12:03:14.56 spid55 * MSDTCPRX 06B20000 06B97FFF 00078000
2006-05-29 12:03:14.56 spid55 * msvcp60 780C0000 78120FFF 00061000
2006-05-29 12:03:14.56 spid55 * MTXCLU 06BA0000 06BB8FFF 00019000
2006-05-29 12:03:14.56 spid55 * VERSION 77B90000 77B97FFF 00008000
2006-05-29 12:03:14.56 spid55 * WSOCK32 71BB0000 71BB8FFF 00009000
2006-05-29 12:03:14.56 spid55 * OLEAUT32 77D00000 77D8BFFF 0008c000
2006-05-29 12:03:14.56 spid55 * CLUSAPI 06BC0000 06BD1FFF 00012000
2006-05-29 12:03:14.56 spid55 * RESUTILS 06BE0000 06BF2FFF 00013000
2006-05-29 12:03:14.56 spid55 * DNSAPI 76ED0000 76EF8FFF 00029000
2006-05-29 12:03:14.56 spid55 * winrnr 06C50000 06C56FFF 00007000
2006-05-29 12:03:14.56 spid55 * WLDAP32 76F10000 76F3DFFF 0002e000
2006-05-29 12:03:14.56 spid55 * rasadhlp 06C80000 06C84FFF 00005000
2006-05-29 12:03:14.56 spid55 * security 07050000 07053FFF 00004000
2006-05-29 12:03:14.56 spid55 * msfte 079B0000 07C07FFF 00258000
2006-05-29 12:03:14.56 spid55 * dbghelp 07C10000 07D27FFF 00118000
2006-05-29 12:03:14.56 spid55 * WINTRUST 76BB0000 76BDAFFF 0002b000
2006-05-29 12:03:14.56 spid55 * imagehlp 76C10000 76C38FFF 00029000
2006-05-29 12:03:14.56 spid55 * dssenh 08010000 08033FFF 00024000
2006-05-29 12:03:14.56 spid55 * hnetcfg 08060000 080B8FFF 00059000
2006-05-29 12:03:14.56 spid55 * wshtcpip 71AE0000 71AE7FFF 00008000
2006-05-29 12:03:14.56 spid55 * NTMARTA 77E00000 77E21FFF 00022000
2006-05-29 12:03:14.56 spid55 * SAMLIB 08200000 0820EFFF 0000f000
2006-05-29 12:03:14.56 spid55 * ntdsapi 766F0000 76704FFF 00015000
2006-05-29 12:03:14.56 spid55 * xpsp2res 10000000 102C4FFF 002c5000
2006-05-29 12:03:14.56 spid55 * CLBCatQ 083A0000 08422FFF 00083000
2006-05-29 12:03:14.56 spid55 * sqlncli 08430000 0864DFFF 0021e000
2006-05-29 12:03:14.56 spid55 * COMCTL32 08650000 086E6FFF 00097000
2006-05-29 12:03:14.56 spid55 * comdlg32 007A0000 007E9FFF 0004a000
2006-05-29 12:03:14.56 spid55 * SQLNCLIR 08700000 08732FFF 00033000
2006-05-29 12:03:14.56 spid55 * msftepxy 08840000 08854FFF 00015000
2006-05-29 12:03:14.56 spid55 * xpstar90 08AD0000 08B14FFF 00045000
2006-05-29 12:03:14.56 spid55 * SQLSCM90 08B30000 08B38FFF 00009000
2006-05-29 12:03:14.56 spid55 * ODBC32 08B50000 08B8CFFF 0003d000
2006-05-29 12:03:14.56 spid55 * BatchParser90 08B90000 08BADFFF 0001e000
2006-05-29 12:03:14.56 spid55 * SQLSVC90 08BC0000 08BD9FFF 0001a000
2006-05-29 12:03:14.56 spid55 * SqlResourceLoader 08BF0000 08BF5FFF 00006000
2006-05-29 12:03:14.56 spid55 * ATL80 7C630000 7C64AFFF 0001b000
2006-05-29 12:03:14.56 spid55 * odbcint 08ED0000 08EE6FFF 00017000
2006-05-29 12:03:14.56 spid55 * SQLSVC90 08EF0000 08EF2FFF 00003000
2006-05-29 12:03:14.56 spid55 * xpstar90 08F00000 08F25FFF 00026000
2006-05-29 12:03:14.56 spid55 * dbghelp 09980000 09A97FFF 00118000
2006-05-29 12:03:14.56 spid55 *
2006-05-29 12:03:14.56 spid55 * Edi: 0102F1C0: 0181AEFF 0162DF00 0162DF08 0181AF07 0181AF0F 0162DF18
2006-05-29 12:03:14.56 spid55 * Esi: 1B305E58: 00000000 00000000 00000000 00000000 00000000 00000000
2006-05-29 12:03:14.56 spid55 * Eax: 00000000:
2006-05-29 12:03:14.56 spid55 * Ebx: 1B305E50: 1B305E58 00000000 00000000 00000000 00000000 00000000
2006-05-29 12:03:14.56 spid55 * Ecx: 1B2EEAE0: 0102F1C0 19EB50D0 19EB4830 0002A2F5 000004AA 00000000
2006-05-29 12:03:14.56 spid55 * Edx: 1B306350: 00000000 1A9D635C 00000000 1B31EBE0 00000002 1A9A8A44
2006-05-29 12:03:14.56 spid55 * Eip: 0143826B: 4E3B088B 8B5B7410 4E8B3056 8B018B04 8D525440 8B521856
2006-05-29 12:03:14.56 spid55 * Ebp: 07FFF814: 07FFF828 01069F66 19EB47DC 19EB4778 1B308298 07FFF85C
2006-05-29 12:03:14.56 spid55 * SegCs: 0000001B:
2006-05-29 12:03:14.56 spid55 * EFlags: 00010207: 5F005200 46004F00 50005F00 4F005200 45004300 53005300
2006-05-29 12:03:14.56 spid55 * Esp: 07FFF788: 0102E9E1 1B306350 07FFF7B4 1B305E58 07FFF7B4 1B305E28
2006-05-29 12:03:14.56 spid55 * SegSs: 00000023:
2006-05-29 12:03:14.56 spid55 * *******************************************************************************
2006-05-29 12:03:14.56 spid55 * -------------------------------------------------------------------------------
2006-05-29 12:03:14.56 spid55 * Short Stack Dump
2006-05-29 12:03:14.56 spid55 0143826B Module(sqlservr+0043826B)
2006-05-29 12:03:14.56 spid55 01069F66 Module(sqlservr+00069F66)
2006-05-29 12:03:14.56 spid55 0106A953 Module(sqlservr+0006A953)
2006-05-29 12:03:14.56 spid55 01069682 Module(sqlservr+00069682)
2006-05-29 12:03:14.56 spid55 01069432 Module(sqlservr+00069432)
2006-05-29 12:03:14.56 spid55 0142EFE9 Module(sqlservr+0042EFE9)
2006-05-29 12:03:14.56 spid55 0142DADD Module(sqlservr+0042DADD)
2006-05-29 12:03:14.56 spid55 0142DB82 Module(sqlservr+0042DB82)
2006-05-29 12:03:14.56 spid55 0142D77E Module(sqlservr+0042D77E)
2006-05-29 12:03:14.56 spid55 0142E0A2 Module(sqlservr+0042E0A2)
2006-05-29 12:03:14.56 spid55 0100889F Module(sqlservr+0000889F)
2006-05-29 12:03:14.56 spid55 010089C5 Module(sqlservr+000089C5)
2006-05-29 12:03:14.56 spid55 010086E7 Module(sqlservr+000086E7)
2006-05-29 12:03:14.56 spid55 010D764A Module(sqlservr+000D764A)
2006-05-29 12:03:14.56 spid55 010D7B71 Module(sqlservr+000D7B71)
2006-05-29 12:03:14.56 spid55 010D746E Module(sqlservr+000D746E)
2006-05-29 12:03:14.57 spid55 010D83F0 Module(sqlservr+000D83F0)
2006-05-29 12:03:14.57 spid55 781329AA Module(MSVCR80+000029AA)
2006-05-29 12:03:14.59 spid55 78132A36 Module(MSVCR80+00002A36)
2006-05-29 12:03:14.65 spid55 Stack Signature for the dump is 0xED92B535
2006-05-29 12:03:15.58 spid55 External dump process return code 0x20000001.
External dump process returned no errors.

Here's the stored procedure:

---------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[SpAggrSGLInventorySummary] AS

-- Form item and time period template to fill in accounts and their respective balances
DECLARE @minBalRange AS smalldatetime,@maxBalRange as smalldatetime
SELECT @minBalRange = dbo.convert_period(MIN(DocumentDate)) FROM FactInventory
SELECT @maxBalRange = dbo.convert_period(GETDATE())

SELECT DISTINCT itemnumber
INTO #ItemRef
FROM FactInventory

CREATE CLUSTERED INDEX ir ON #ItemRef(ItemNumber)
/* -----------------------------------------------------------------------------------*/
-- Identify and process new item / monthend combination records to add to aggregate staging by item.
/* -----------------------------------------------------------------------------------*/
SELECT di.ItemNumber
,dt.monthend
INTO #ItemTemplate
FROM DimTime dt,#ItemRef fi INNER JOIN DimItem di
ON fi.ItemNumber = di.ItemNumber
WHERE dt.MonthEnd BETWEEN @minBalRange AND @maxBalRange
EXCEPT
SELECT ItemNumber
,Monthend
FROM stgAggrInvenItemDistrib

IF @@ROWCOUNT > 0 -- If the month has turned over or new items added
BEGIN
INSERT stgAggrInvenItemDistrib
SELECT di.ItemNumber
,di.ItemClassCode
,di.dvGLRollup
,YearMonthID = CAST('' AS NCHAR(10))
,MonthFormat = dbo.Mth3YearFormat(0,fi.monthend)
,fi.monthend
,MonthQtySales = 0
,MonthQtyCost = 0
,MonthActivityCost = 0
,MonthActivitySales = 0
,MonthQtySalesBalance = 0
,MonthlyQtyCostBalance = 0
,MonthBalance = 0
,MonthBalanceRnd = 0
FROM #ItemTemplate fi INNER JOIN DimItem di
ON fi.ItemNumber = di.ItemNumber

SELECT ItemNumber
,MinMonth = MIN(MonthEnd)
,Maxmonth = MAX(MonthEnd)
INTO #ym
FROM #ItemTemplate
GROUP BY ItemNumber

UPDATE stgAggrInvenItemDistrib
SET YearMonthID = 'YM' +
CASE WHEN DATEDIFF(mm,monthend,d.maxmonth) < 10 THEN '0' ELSE '' END +
CAST(DATEDIFF(mm,monthend,d.maxmonth) AS VARCHAR(3))
FROM stgAggrInvenItemDistrib a INNER JOIN #ym d
ON a.ItemNumber = d.ItemNumber
END

/* -----------------------------------------------------------------------------------*/
-- For accurate comparison create temp table with distinct accounts from
-- Gl Aggregate inventory summary. This is done to pull the right trx from
-- the subgl side.
/* -----------------------------------------------------------------------------------*/
SELECT *
INTO #ReconcileAccounts
FROM DimGlAccount dg
WHERE dg.AccountInvClass <> ''
AND dg.AccountID NOT IN(31,39,43) -- Exclude WIP accounts

/* -----------------------------------------------------------------------------------*/
-- Identify unique trx information from inventory transactions amounts
-- history table iv30300 and to identify unique trx from inventory
-- distribution history iv30500. This is done because trx from iv30300
-- directly post to gl based on docdate and some trx post through from
-- iv30300 to iv30500 which contains a posted date. Some trx are in
-- one but not the other and vica versa and some match but all are in
-- the gl distribution table gl20000 (current year) and gl30000 (historical)
/* -----------------------------------------------------------------------------------*/
SELECT AggKey = CAST(f.InvenAcctIndex as char(5))+f.DocumentNumber+f.ItemNumber+f.Source
,AggKeyG = CAST(F.InvenAcctIndex as char(5))+f.DocumentNumber+f.Source+CAST(f.DocumentDate as char(20))
,AccountId = f.InvenAcctIndex
,DexRowIDHistDet
,AggAccountOffSet = 0
INTO #keys
FROM FactInventory f
WHERE LEFT(f.DocumentNumber,3) <> 'XFR' AND f.ExtendedCost <> 0
UNION ALL
SELECT AggKey = CAST(f.InvenAcctOffIndex as char(5))+f.DocumentNumber+f.ItemNumber+f.Source
,AggKeyG = CAST(F.InvenAcctOffIndex as char(5))+f.DocumentNumber+f.Source+CAST(f.DocumentDate as char(20))
,f.InvenAcctOffIndex
,DexRowIDHistDet
,AggAccountOffset
FROM FactInventory f
WHERE LEFT(f.DocumentNumber,3) <> 'XFR'
AND f.ExtendedCost <> 0 AND AggAccountOffSet = 1

SELECT k.AggKey
,k.AggKeyG
,k.AccountID
,dt.MonthEnd
,f.DocumentNumber
,f.ItemNumber
,f.DocumentDate
,DocumentType = f.QtyType
,f.AggExclude
,f.AggAccountOffset
,TotQty = SUM(f.Qty)
,TotCost = SUM(f.ExtendedCost)
,TotalSalesQty = SUM(f.SalesQty)
,TotalSalesAmt = SUM(f.SalesAmount)
INTO #AggrKeys1
FROM FactInventory f INNER JOIN #keys k
ON k.DexRowIDHistDet = f.DexRowIDHistDet
INNER JOIN DimTime dt
ON dt.PeriodKey = f.PeriodKey
GROUP BY k.AggKey
,k.AggKeyG
,k.AccountID
,dt.MonthEnd
,f.DocumentNumber
,f.ItemNumber
,f.DocumentDate
,f.QtyType
,f.AggExclude
,f.AggAccountOffset

CREATE INDEX abc
ON #AggrKeys1 (AggKey)

SELECT i1.AggKey,isJoin = 'I'
INTO #keyjoin
FROM dbo.stgAggrInvenDistrib i2 INNER JOIN #AggrKeys1 i1
ON i2.AggKey = i1.AggKey
UNION ALL
SELECT i1.aggkey,isJoin = COALESCE(i2.aggkey,'L')
FROM #AggrKeys1 i1 LEFT OUTER JOIN dbo.stgAggrInvenDistrib i2
ON i1.aggkey = i2.aggkey
WHERE i1.AggExclude = 1
UNION ALL
SELECT i1.aggkey,isJoin = COALESCE(i2.aggkey,'R')
FROM dbo.stgAggrInvenDistrib i1 LEFT OUTER JOIN #AggrKeys1 i2
ON i2.aggkey = i1.aggkey

-- trx from inventory transaction amts and inventory transaction details where the keys match
-- AND trx from inventory transaction amts not in transaction details table, primarily receipts
SELECT i1.AccountID
,i1.MonthEnd
,i1.DocumentNumber
,i1.ItemNumber
,i1.DocumentDate
,i1.DocumentType
,i1.TotQty
,i1.TotalSalesQty
,i1.TotalSalesAmt
,i1.TotCost
INTO #InvFinal
FROM #AggrKeys1 i1,#keyjoin i2
WHERE i1.AggKey = i2.AggKey AND i2.isJoin IN('I','L')
UNION ALL
-- trx from inventory transaction details not in transaction amounts table, anomalies but assuming
-- the transactions details table is the one used to post to the gl.
SELECT i1.AccountID
,i1.MonthEnd
,i1.DocumentNumber
,i1.ItemNumber
,i1.DocumentDate
,i1.DocumentType
,i1.TotQty
,i1.TotalSalesQty
,i1.TotalSalesAmt
,i1.TotCost
FROM dbo.stgAggrInvenDistrib i1,#keyjoin i2
WHERE i1.AggKey = i2.AggKey AND i2.isJoin = 'R'

/* -----------------------------------------------------------------------------------*/
-- Aggregate transactions to item / perioddate level.
/* -----------------------------------------------------------------------------------*/
SELECT i.ItemNumber
,i.ItemClassCode
,i.dvGLRollup
,fi.MonthEnd
,TotalSalesQty = SUM(fi.TotalSalesQty)
,TotalQty = SUM(fi.TotQty)
,TotCost = SUM(fi.TotCost)
,TotalSalesAmt = SUM(fi.TotalSalesAmt)
INTO #InvItemValSummary
FROM DimItem i INNER JOIN #InvFinal fi
ON i.ItemNumber = fi.ItemNumber
GROUP BY i.ItemNumber,i.ItemClassCode,i.dvGLRollup,fi.MonthEnd
ORDER BY i.ItemNumber,i.ItemClassCode,i.dvGLRollup,fi.MonthEnd

/* -----------------------------------------------------------------------------------*/
-- Update item level aggregate stagin with summed values linked by item and time period.
/* -----------------------------------------------------------------------------------*/
UPDATE stgAggrInvenItemDistrib
SET MonthQtySales = i.TotalSalesQty
,MonthQtyCost = i.TotalQty
,MonthActivityCost = i.TotCost
,MonthActivitySales = i.TotalSalesAmt
,MonthQtySalesBalance = 0
,MonthlyQtyCostBalance = 0
,MonthBalance = 0
,MonthBalanceRnd = 0
FROM stgAggrInvenItemDistrib t, #InvItemValSummary i
WHERE t.ItemNumber = i.ItemNumber AND
t.MonthEnd = i.MonthEnd

/* -----------------------------------------------------------------------------------*/
-- Calculate running totals in preparation for updating item level aggregate table.
/* -----------------------------------------------------------------------------------*/
-- Apply activity for every period from the beginning for each account.
-- Declare variables to store account number and running totals.
DECLARE @ItemNumber AS CHAR(31)
,@MonthEnd AS smalldatetime
,@MonthQtyCost AS numeric(38,5)
,@MonthActivityCost as numeric(38,5)
,@RunningBalCost as numeric(38,5)
,@RunningBalQtyCost as numeric(38,5)
,@ItemPlaceHolder AS char(31)

-- Declare cursor to store account period values for beginning bal
DECLARE ItemTempCurs CURSOR FOR
SELECT ItemNumber
,monthend
,MonthQtyCost
,MonthActivityCost
FROM stgAggrInvenItemDistrib

-- Open the with item beginning balances
OPEN ItemTempCurs

-- Process the first fetch and store the first item number
FETCH NEXT FROM ItemTempCurs
INTO @ItemNumber ,@MonthEnd ,@MonthQtyCost,@MonthActivityCost

SELECT @ItemPlaceHolder = @ItemNumber
,@RunningBalCost = @MonthActivityCost
,@RunningBalQtyCost = @MonthQtyCost

WHILE @@FETCH_STATUS = 0
-- Loop through acct number periods to update running balance
BEGIN
-- If first entry or docdate month is 01 store current value to pbalance and variable and reset first.
-- pbalance to variable.
-- otherwise store running totals
IF @ItemNumber <> @ItemPlaceHolder
BEGIN
-- replace item placeholder with item if item changes
SELECT @ItemPlaceHolder = @ItemNumber
,@RunningBalCost = @MonthActivityCost
,@RunningBalQtyCost = @MonthQtyCost
END
ELSE
BEGIN
SELECT @RunningBalCost = @RunningBalCost + @MonthActivityCost
,@RunningBalQtyCost = @RunningBalQtyCost + @MonthQtyCost
END

UPDATE stgAggrInvenItemDistrib
SET MonthlyQtyCostBalance = @RunningBalQtyCost
,MonthBalance = @RunningBalCost
,MonthBalanceRnd = ROUND(@RunningBalCost,0)
WHERE ItemNumber = @ItemPlaceHolder AND
MonthEnd = @MonthEnd

-- Pull next record
FETCH NEXT FROM ItemTempCurs
INTO @ItemNumber ,@MonthEnd ,@MonthQtyCost,@MonthActivityCost
END

CLOSE ItemTempCurs
DEALLOCATE ItemTempCurs

/* -----------------------------------------------------------------------------------*/
-- Gather seial and lot items qoh to update current qoh cost balances.
/* -----------------------------------------------------------------------------------*/

SELECT ItemNumber,MonthEnd = MAX(MonthEnd)
INTO #LinkItem
FROM stgAggrInvenItemDistrib
GROUP BY ItemNumber

SELECT l.MonthEnd,q.itemnmbr,
qtyonhnd = SUM(q.qtyrecvd - q.qtysold),
qtycost = SUM(ROUND((q.qtyrecvd - q.qtysold) * q.unitcost,2))
INTO #CurQtyCost
FROM data_mrp.diamd.dbo.iv10200 q,#LinkItem l
WHERE q.itemnmbr = l.ItemNumber AND
q.rcptsold = 0 AND (q.qtyrecvd - q.qtysold) > 0
GROUP BY l.MonthEnd,q.itemnmbr
UNION ALL
SELECT l.MonthEnd,d.ItemNumber,
q.qtyonhnd,
qtycost = ROUND(q.qtyonhnd * d.ItemCurrentCost,2)
FROM DimItem d,stgItemMstrQty q,data_mrp.diamd.dbo.iv40201 m,#LinkItem l
WHERE d.ItemNumber = l.ItemNumber AND
d.ItemNumber = q.itemnmbr AND
d.UOMSchedule = m.uomschdl AND
d.ItemNumber NOT IN(SELECT DISTINCT itemnmbr
FROM data_mrp.diamd.dbo.iv10200
WHERE rcptsold = 0 AND (qtyrecvd - qtysold) > 0 )

/* Make updates for current period for trx */
UPDATE stgAggrInvenItemDistrib
SET MonthBalance = c.qtycost,
MonthlyQtyCostBalance = c.qtyonhnd
FROM stgAggrInvenItemDistrib i INNER JOIN #CurQtyCost c
ON i.ItemNumber = c.itemnmbr AND i.MonthEnd = c.MonthEnd

/* -----------------------------------------------------------------------------------*/
-- Insert or update into aggregate tables. */.
/* -----------------------------------------------------------------------------------*/
DECLARE @mxperiod_s as smalldatetime,@mxperiod_d as smalldatetime,
@rwCountItem as bigint, @rwCountClass as bigint

SELECT @rwCountItem = COUNT(*) FROM AggrSGLInventoryItemSummary
SELECT @rwCountClass = COUNT(*) FROM AggrSGLInventoryItemClassSummary

SELECT @mxperiod_s = MAX(MonthEnd)
FROM stgAggrInvenItemDistrib

SELECT @mxperiod_d = MAX(MonthEnd)
FROM AggrSGLInventoryItemSummary

SELECT ItemClassCode,dvGLRollup,MonthEnd
,MonthActivityCost = ROUND(SUM(MonthActivityCost),2)
,MonthActivitySales = ROUND(SUM(ABS(MonthActivitySales)),2)
,MonthBalance = ROUND(SUM(MonthBalance),2)
INTO #icu
FROM stgAggrInvenItemDistrib
GROUP BY ItemClassCode,dvGLRollup,MonthEnd
ORDER BY ItemClassCode,dvGLRollup,MonthEnd

IF (@mxperiod_s <> @mxperiod_d) OR @mxperiod_d IS NULL
BEGIN
IF @rwCountItem > 0
BEGIN
INSERT dbo.AggrSGLInventoryItemSummary
SELECT ItemNumber , ItemClassCode
, dvGLRollup , YearMonthID
, MonthFormat , MonthEnd
, MonthQtySales , MonthQtyCost
, MonthActivityCost , MonthActivitySales
, MonthQtySalesBalance , MonthlyQtyCostBalance
, MonthBalance , MonthBalanceRnd
FROM stgAggrInvenItemDistrib
WHERE MonthEnd = @mxperiod_s
END
ELSE
BEGIN
INSERT dbo.AggrSGLInventoryItemSummary
SELECT ItemNumber , ItemClassCode
, dvGLRollup , YearMonthID
, MonthFormat , MonthEnd
, MonthQtySales , MonthQtyCost
, MonthActivityCost , MonthActivitySales
, MonthQtySalesBalance , MonthlyQtyCostBalance
, MonthBalance , MonthBalanceRnd
FROM stgAggrInvenItemDistrib
END

IF @rwCountClass > 0
BEGIN
INSERT dbo.AggrSGLInventoryItemClassSummary
SELECT ItemClassCode,dvGLRollup
,' '
,dbo.Mth3YearFormat(0,monthend)
,MonthEnd
, MonthActivityCost
, MonthActivitySales
, MonthBalance,0
FROM #icu
WHERE MonthEnd = @mxperiod_s
END
ELSE
BEGIN
INSERT dbo.AggrSGLInventoryItemClassSummary
SELECT ItemClassCode,dvGLRollup
,' '
,dbo.Mth3YearFormat(0,monthend)
,MonthEnd
, MonthActivityCost
, MonthActivitySales
, MonthBalance,0
FROM #icu
END

/* --------------------------------------------------------------------------- */
SELECT ItemNumber
,MinMonth = MIN(MonthEnd)
,Maxmonth = MAX(MonthEnd)
INTO #ymIIS
FROM dbo.AggrSGLInventoryItemSummary
GROUP BY ItemNumber

UPDATE dbo.AggrSGLInventoryItemSummary
SET YearMonthID = 'YM' +
CASE WHEN DATEDIFF(mm,monthend,d.maxmonth) < 10 THEN '0' ELSE '' END +
CAST(DATEDIFF(mm,monthend,d.maxmonth) AS VARCHAR(3))
FROM dbo.AggrSGLInventoryItemSummary a INNER JOIN #ymIIS d
ON a.ItemNumber = d.ItemNumber

/* --------------------------------------------------------------------------- */
SELECT ItemClassCode
,MinMonth = MIN(MonthEnd)
,Maxmonth = MAX(MonthEnd)
INTO #ymIIC
FROM dbo.AggrSGLInventoryItemClassSummary
GROUP BY ItemClassCode

UPDATE dbo.AggrSGLInventoryItemClassSummary
SET YearMonthID = 'YM' +
CASE WHEN DATEDIFF(mm,monthend,d.maxmonth) < 10 THEN '0' ELSE '' END +
CAST(DATEDIFF(mm,monthend,d.maxmonth) AS VARCHAR(3))
FROM dbo.AggrSGLInventoryItemClassSummary a INNER JOIN #ymIIC d
ON a.ItemClassCode = d.ItemClassCode

END
ELSE
BEGIN
UPDATE dbo.AggrSGLInventoryItemSummary
SET MonthBalance = s.MonthBalance
,MonthActivityCost = s.MonthActivityCost
,MonthlyQtyCostBalance = s.MonthlyQtyCostBalance
,MonthActivitySales = s.MonthActivitySales
FROM dbo.AggrSGLInventoryItemSummary a INNER JOIN stgAggrInvenItemDistrib s
ON a.ItemNumber = s.ItemNumber
AND a.MonthEnd = s.MonthEnd
AND a.MonthEnd = @mxperiod_s

UPDATE dbo.AggrSGLInventoryItemClassSummary
SET ItemClassCode = i.ItemClassCode
,MonthEnd = i.MonthEnd
,dvGLRollup = i.dvGLRollup
,MonthActivityCost = i.MonthActivityCost
,MonthActivitySales = i.MonthActivitySales
,MonthBalance = i.MonthBalance
FROM #icu i
WHERE dbo.AggrSGLInventoryItemClassSummary.ItemClassCode = i.ItemClassCode AND
dbo.AggrSGLInventoryItemClassSummary.MonthEnd = i.MonthEnd
END
-----------------------------------------------------------------------------------------------------------------------------------------------

Thanks,

-- Jim