Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2002
    Posts
    63

    Red face Unanswered: SP with Temporary table cause to EXCEPTION_ACCESS_VIOLATION

    Hi ,
    I got Error of - EXCEPTION_ACCESS_VIOLATION while using SP with Temporary table declaration
    (On SQL 2000 SP 2 on Windows 2000 SP 3 2*CPU 4 GB memory ).

    The Error starts when I have created and Execute S.P that declare on temporary table (please see below).
    This SP calls by ASP pages from different End User. (The IIS use one Login In for Data base connection)

    In BOL it is mention that each call to a S.P will created New temporary table so I should not have any access violation problem but I do have.

    I have not found any reason for it. Please post any idea for the root cause

    Thanks In Advance ,
    Eyal

    The details:
    (SP Structure, Error from the SQL log)

    The SP structure is ~

    CREATE PROCEDURE RS_SQL_CMD_GET_INVOICE_REPORT_HASH_FORMAT

    AS


    --Create Temporary Table
    CREATE TABLE #TempInvoiceHash (
    TabIndex int ,
    AccountID INT NOT NULL ,
    HashCurrencyName nvarchar (50),
    )


    CREATE NONCLUSTERED INDEX IX_AccountID ON #TempInvoiceHash
    (
    AccountID
    ) ON [PRIMARY]


    INSERT INTO #TempInvoiceHash (TabIndex,AccountID, HashCurrencyName )

    .....other tables

    INSERT INTO #TempInvoiceHash (TabIndex,AccountID, HashCurrencyName )

    ...other tables

    INSERT INTO #TempInvoiceHash (TabIndex,AccountID, HashCurrencyName )
    SELECT * FROM #TempInvoiceHash ...
    ...


    SELECT * FROM #TempInvoiceHash
    GO




    The Error are :
    1. language_exec: Process 60 generated an access violation. SQL Server is terminating this process..
    2. Error: 0, Severity: 19, State: 0
    3. Using 'dbghelp.dll' version '4.0.5'
    4. *Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQL00029.dmp
    5. * ************************************************** *****************************
    6. *
    7. * BEGIN STACK DUMP:
    8. * 03/19/03 16:59:44 spid 70
    9. *
    10. * Exception Address = 1B9DF0B4
    11. * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
    12. * Access Violation occurred reading address 1B9DF0B4
    13. * Input Buffer 128 bytes -
    14. * EXEC RS_SQL_CMD_GET_INVOICE_REPORT_HASH_FORMAT 200301 ,-1,-1
    15. *
    16. *
    17. * MODULE BASE END SIZE
    18. * sqlservr 00400000 00B19FFF 0071a000
    19. * ntdll 77F80000 77FFAFFF 0007b000
    20. * KERNEL32 77E80000 77F35FFF 000b6000
    21. * ADVAPI32 77DB0000 77E0CFFF 0005d000
    22. * RPCRT4 77D30000 77DA0FFF 00071000
    23. * USER32 77E10000 77E74FFF 00065000
    24. * GDI32 77F40000 77F7BFFF 0003c000
    25. * OPENDS60 41060000 41065FFF 00006000
    26. * MSVCRT 78000000 78045FFF 00046000
    27. * UMS 41070000 4107CFFF 0000d000
    28. * SQLSORT 42AE0000 42B6FFFF 00090000
    29. * MSVCIRT 780A0000 780B1FFF 00012000
    30. * sqlevn70 41080000 41086FFF 00007000
    31. * NETAPI32 75170000 751BEFFF 0004f000
    32. * SECUR32 77BE0000 77BEEFFF 0000f000
    33. * NETRAP 751C0000 751C5FFF 00006000
    34. * SAMLIB 75150000 7515FFFF 00010000
    35. * WS2_32 75030000 75042FFF 00013000
    36. * WS2HELP 75020000 75027FFF 00008000
    37. * WLDAP32 77950000 77979FFF 0002a000
    38. * DNSAPI 77980000 779A3FFF 00024000
    39. * WSOCK32 75050000 75057FFF 00008000
    40. * wmi 76110000 76113FFF 00004000
    41. * ole32 77A50000 77B44FFF 000f5000
    42. * XOLEHLP 65450000 65457FFF 00008000
    43. * MSDTCPRX 6DF80000 6E032FFF 000b3000
    44. * MTXCLU 6A7A0000 6A7AFFFF 00010000
    45. * VERSION 77820000 77826FFF 00007000
    46. * LZ32 759B0000 759B5FFF 00006000
    47. * CLUSAPI 73930000 7393FFFF 00010000
    48. * RESUTILS 689D0000 689DCFFF 0000d000
    49. * USERENV 77C10000 77C6DFFF 0005e000
    50. * rnr20 782C0000 782CBFFF 0000c000
    51. * iphlpapi 77340000 77352FFF 00013000
    52. * ICMP 77520000 77524FFF 00005000
    53. * MPRAPI 77320000 77336FFF 00017000
    54. * OLEAUT32 779B0000 77A4AFFF 0009b000
    55. * ACTIVEDS 773B0000 773DDFFF 0002e000
    56. * ADSLDPC 77380000 773A1FFF 00022000
    57. * RTUTILS 77830000 7783DFFF 0000e000
    58. * SETUPAPI 77880000 7790CFFF 0008d000
    59. * RASAPI32 774E0000 77511FFF 00032000
    60. * RASMAN 774C0000 774D0FFF 00011000
    61. * TAPI32 77530000 77551FFF 00022000
    62. * COMCTL32 77B50000 77BD8FFF 00089000
    63. * SHLWAPI 77C70000 77CB9FFF 0004a000
    64. * DHCPCSVC 77360000 77378FFF 00019000
    65. * winrnr 777E0000 777E7FFF 00008000
    66. * rasadhlp 777F0000 777F4FFF 00005000
    67. * SSNMPN70 410D0000 410D5FFF 00006000
    68. * SSNETLIB 42CF0000 42D05FFF 00016000
    69. * security 75500000 75503FFF 00004000
    70. * msafd 74FD0000 74FECFFF 0001d000
    71. * wshtcpip 75010000 75016FFF 00007000
    72. * SSmsLPCn 42CD0000 42CD6FFF 00007000
    73. * ntdsapi 77BF0000 77C00FFF 00011000
    74. * mswsock 74FF0000 75001FFF 00012000
    75. * kerberos 78280000 782B3FFF 00034000
    76. * CRYPTDLL 76670000 7667DFFF 0000e000
    77. * MSASN1 77430000 7743FFFF 00010000
    78. * rsabase 7CA00000 7CA21FFF 00022000
    79. * CRYPT32 77440000 774B6FFF 00077000
    80. * SQLFTQRY 41020000 41051FFF 00032000
    81. * CLBCATQ 775A0000 77624FFF 00085000
    82. * sqloledb 75370000 753E8FFF 00079000
    83. * MSDART 3B0B0000 3B0D2FFF 00023000
    84. * comdlg32 76B30000 76B6CFFF 0003d000
    85. * SHELL32 782F0000 78535FFF 00246000
    86. * MSDATL3 3B0E0000 3B0F5FFF 00016000
    87. * oledb32 3B400000 3B46EFFF 0006f000
    88. * OLEDB32R 3B470000 3B480FFF 00011000
    89. * msv1_0 782D0000 782EDFFF 0001e000
    90. * xpsqlbot 42F00000 42F05FFF 00006000
    91. * xpstar 410F0000 41135FFF 00046000
    92. * SQLRESLD 42AC0000 42AC6FFF 00007000
    93. * SQLSVC 42C40000 42C56FFF 00017000
    94. * ODBC32 3B4D0000 3B504FFF 00035000
    95. * odbcbcp 41150000 41156FFF 00007000
    96. * W95SCM 41140000 4114BFFF 0000c000
    97. * SQLUNIRL 41090000 410BCFFF 0002d000
    98. * WINSPOOL 77800000 7781DFFF 0001e000
    99. * MPR 76620000 7662FFFF 00010000
    100. * SHFOLDER 75AA0000 75AA7FFF 00008000
    101. * odbcint 3B650000 3B665FFF 00016000
    102. * NDDEAPI 769A0000 769A6FFF 00007000
    103. * SQLSVC 43970000 43975FFF 00006000
    104. * xpstar 439E0000 439E8FFF 00009000
    105. * impprov 41900000 41919FFF 0001a000
    106. * adsldp 74D90000 74DB0FFF 00021000
    107. * adsmsext 74D70000 74D81FFF 00012000
    108. * xplog70 42EA0000 42EB1FFF 00012000
    109. * xplog70 439D0000 439D3FFF 00004000
    110. * srchadm 60000000 6003CFFF 0003d000
    111. * mssws 3D2D0000 3D2D8FFF 00009000
    112. * athprxy 3D600000 3D607FFF 00008000
    113. * dbghelp 72A00000 72A2CFFF 0002d000
    114. *
    115. * Edi: 3ED18954: 00000000 00000000 00000000 00000000 00000000 00000000
    116. * Esi: 0000003A:
    117. * Eax: 00000001:
    118. * Ebx: 3ED18B34: 35006A00 CCCCCCCD 3FE4CCCC 00000000 00000000 00000000
    119. * Ecx: 00000000:
    120. * Edx: 3ED18800: 399E2508 3D01FB74 00000200 3D01FD30 77F82B95 77F839B8
    121. * Eip: 1B9DF0B4:
    122. * Ebp: F1153942:
    123. * SegCs: 0000001B:
    124. * EFlags: 00010206: 00520045 004F005F 005F0046 00520050 0043004F 00530045
    125. * Esp: 3ED188E4: C348362B D22BC08A E0A59469 0061A4AB 84BAB9AD 1C3D4FCA
    126. * SegSs: 00000023:
    127. * ************************************************** *****************************
    128. * -------------------------------------------------------------------------------
    129. * Short Stack Dump
    130. * 1B9DF0B4 Module(UNKNOWN+00000000)
    131. * C348362B Module(UNKNOWN+00000000)
    132. * -----------------------------

  2. #2
    Join Date
    Jul 2002
    Posts
    63
    FRom BOL :

    If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    first,

    Code:
    --Create Temporary Table 
    CREATE TABLE #TempInvoiceHash (
    TabIndex int ,
    AccountID INT NOT NULL ,
    HashCurrencyName nvarchar (50),
    )
    drop the last ','

    Do you get the error when running the SP or compileing the sp?
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jul 2002
    Posts
    63
    Hi ,

    The SP is running perfectly but from time to time the SQL server got the Error (what I have post earlier ) and than until I change the SP with any change ( even alter with new space ) it work does not work again ( I got in the query analyzer Network Connection Error and From the IIS Time out message )

    ( BTW - The last , was just in the sample that I have copy and paste from the real SP )

    Thanks,
    Eyal

  5. #5
    Join Date
    Jul 2002
    Posts
    63
    Any Idea ? (Thanks In advance . Eyal)

  6. #6
    Join Date
    Jul 2002
    Posts
    63
    No one got this Error / behavior before ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •