|
|
Posted by Greg D. Moore \(Strider\) on 12/05/07 14:33
You need to open a ticket directly with Microsoft. Since this is almost
certainly a bug, it shouldn't cost you anything.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
"Taw" <tawfiq.choudhury@grameenphone.com> wrote in message
news:d6445d97-2d73-4b20-a479-65a695c31fe7@s12g2000prg.googlegroups.com...
> Hi,
> The following query which used to run regularly is crashing the
> server. The query is run on two tables which has aroung 60 million
> records each.
>
> Of course the query can be broken up and optimized I am just
> interested to know if any one has any clue why this is happening?
>
> DB: SQL Server 2000 enterprise edition SP4
> OS: Windows 2003 enterprise edition
>
> Error Log Message:
> ----------------------------
> SqlDumpExceptionHandler: Process 5396 generated fatal exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this
> process.
> *
> *******************************************************************************
> *
> * BEGIN STACK DUMP:
> * 12/05/07 19:35:09 spid 53
> *
> * Exception Address = 004245C9
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 00000000
> * Input Buffer 1890 bytes -
> * select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
> * en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
> else
> * 'Prob' end as CCCC from ( select case when
> right(left(filename,5),
> * 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
> max(
> * left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
> filename
> * FROM dbo.XXX20071201)t group by case when right(left(filename,5),
> 2)>10
> * then left(filename,9) else left(filename,5)end ) as a left join
> ( s
> * elect case when right(left(filename,5),2)>10 then left(filename,
> 9) els
> * e left(filename,5)end as XXX_Name, min(left(right(filename,17),
> 4)) as
> * S_Seq_No from (SELECT distinct filename FROM
> dbo.XXX20071202_online)s
> * group by case when right(left(filename,5),2)>10 then left(filename,
> 9) e
> * lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
> a.XXX
> * _Name
> *
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00CBAFFF 008bb000
> * ntdll 7C800000 7C8BFFFF 000c0000
> * kernel32 77E40000 77F41FFF 00102000
> * ADVAPI32 77F50000 77FEAFFF 0009b000
> * RPCRT4 77C50000 77CEEFFF 0009f000
> * Secur32 76F50000 76F62FFF 00013000
> * MSVCP71 7C3A0000 7C41AFFF 0007b000
> * MSVCR71 7C340000 7C395FFF 00056000
> * opends60 41060000 41065FFF 00006000
> * SHELL32 7C8D0000 7D0CDFFF 007fe000
> * msvcrt 77BA0000 77BF9FFF 0005a000
> * GDI32 77C00000 77C47FFF 00048000
> * USER32 77380000 77410FFF 00091000
> * SHLWAPI 77DA0000 77DF1FFF 00052000
> * sqlsort 42AE0000 42B6FFFF 00090000
> * ums 41070000 4107DFFF 0000e000
> * comctl32 77420000 77522FFF 00103000
> * sqlevn70 41080000 4108AFFF 0000b000
> * NETAPI32 02CA0000 02CF6FFF 00057000
> * AUTHZ 02D00000 02D13FFF 00014000
> * COMRES 031B0000 03275FFF 000c6000
> * ole32 03380000 034B8FFF 00139000
> * XOLEHLP 03540000 03545FFF 00006000
> * MSDTCPRX 03550000 035C8FFF 00079000
> * OLEAUT32 035D0000 0365AFFF 0008b000
> * msvcp60 03660000 036C4FFF 00065000
> * MTXCLU 036D0000 036E8FFF 00019000
> * VERSION 036F0000 036F7FFF 00008000
> * WSOCK32 03700000 03708FFF 00009000
> * WS2_32 03710000 03726FFF 00017000
> * WS2HELP 03730000 03737FFF 00008000
> * CLUSAPI 03780000 03791FFF 00012000
> * RESUTILS 037A0000 037B2FFF 00013000
> * USERENV 037C0000 03881FFF 000c2000
> * mswsock 038A0000 038E0FFF 00041000
> * DNSAPI 038F0000 03919FFF 0002a000
> * winrnr 03960000 03966FFF 00007000
> * WLDAP32 03970000 0399DFFF 0002e000
> * rasadhlp 039C0000 039C4FFF 00005000
> * SSNETLIB 00E50000 00E65FFF 00016000
> * NTMARTA 00E80000 00EA0FFF 00021000
> * SAMLIB 00EB0000 00EBEFFF 0000f000
> * security 044B0000 044B3FFF 00004000
> * hnetcfg 044C0000 04519FFF 0005a000
> * wshtcpip 04A60000 04A67FFF 00008000
> * SSmsLPCn 04B70000 04B77FFF 00008000
> * SSnmPN70 04BA0000 04BA6FFF 00007000
> * ntdsapi 04C30000 04C43FFF 00014000
> * kerberos 04C80000 04CD7FFF 00058000
> * cryptdll 04D70000 04D7BFFF 0000c000
> * MSASN1 04D80000 04D91FFF 00012000
> * rsaenh 048E0000 04914FFF 00035000
> * PSAPI 00F30000 00F3AFFF 0000b000
> * msv1_0 04930000 04956FFF 00027000
> * iphlpapi 051C0000 051D9FFF 0001a000
> * xpsp2res 051E0000 054A4FFF 002c5000
> * CLBCatQ 054B0000 05532FFF 00083000
> * SQLOLEDB 05550000 055D0FFF 00081000
> * MSDART 055E0000 055F9FFF 0001a000
> * MSDATL3 05600000 05614FFF 00015000
> * oledb32 05BA0000 05C18FFF 00079000
> * OLEDB32R 05C20000 05C30FFF 00011000
> * xpstar 05C40000 05C8CFFF 0004d000
> * SQLRESLD 05C90000 05C9BFFF 0000c000
> * SQLSVC 05CA0000 05CBAFFF 0001b000
> * ODBC32 05CC0000 05CFCFFF 0003d000
> * COMCTL32 05D00000 05D96FFF 00097000
> * comdlg32 05DA0000 05DE8FFF 00049000
> * odbcbcp 05DF0000 05DF5FFF 00006000
> * W95SCM 05E00000 05E0CFFF 0000d000
> * SQLUNIRL 05E10000 05E3CFFF 0002d000
> * WINSPOOL 05E40000 05E66FFF 00027000
> * SHFOLDER 05E70000 05E78FFF 00009000
> * odbcint 06150000 06166FFF 00017000
> * NDDEAPI 06280000 06287FFF 00008000
> * SQLSVC 06290000 06295FFF 00006000
> * xpstar 062A0000 062A8FFF 00009000
> * ACTIVEDS 063B0000 063E3FFF 00034000
> * adsldpc 063F0000 06417FFF 00028000
> * credui 06420000 0644DFFF 0002e000
> * ATL 06450000 06467FFF 00018000
> * adsldp 064D0000 064FDFFF 0002e000
> * SXS 06680000 0673CFFF 000bd000
> * xplog70 06740000 0674EFFF 0000f000
> * xplog70 06750000 06754FFF 00005000
> * xpsqlbot 041C0000 041C5FFF 00006000
> * dbghelp 06D70000 06E6FFFF 00100000
> *
> * Edi: 2D8CB018: 00000000 00000000 00000000 00001F00
> 07000000 8B008300
> * Esi: 2D8CA098: 2D8CA020 629222C8 00000000 00000014
> 2D8CB8B8 2D8CA9C0
> * Eax: 00000000:
> * Ebx: 00000000:
> * Ecx: 2D8C80C0: 620A1220 2D8C8618 2D8C8618 2D8C80CC
> 2D8C80CC 2D8C8038
> * Edx: 0696FED8: 00000000 06000000 005F87B3 00A99F4C
> 4107311B 000DBBA0
> * Eip: 004245C9: FF50088B 45C60851 646705FC 002C168B
> 488B028B A8558B08
> * Ebp: 0696EBD4: 0696EBE4 0087B831 0696FEB4 2D8CA688
> 0696FEC0 0087E635
> * SegCs: 0000001B:
> * EFlags: 00010286: 00690057 0064006E 0077006F 005F0073
> 0054004E 00500000
> * Esp: 0696EB70: 2D8CA098 2D8CA688 0087E5B5 0696FED8
> 7C000000 005F87B3
> * SegSs: 00000023:
> *
> *******************************************************************************
> *
> -------------------------------------------------------------------------------
> * Short Stack Dump
> * 004245C9 Module(sqlservr+000245C9)
> * 0087B831 Module(sqlservr+0047B831) (GetIMallocForMsxml+00086CB1)
> * 0087E635 Module(sqlservr+0047E635) (GetIMallocForMsxml+00089AB5)
> * 0055C692 Module(sqlservr+0015C692)
> * 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
> 456+00000000)
> * 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
> 263+00000007)
> * 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
> * 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
> *
> -------------------------------------------------------------------------------
> *Dump thread - spid = 53, PSS = 0x620a1220, EC = 0x620a1550
> *Stack Dump being sent to d:\datafiles\MSSQL\log\SQLDump0044.txt
> *
> *******************************************************************************
> *
> * BEGIN STACK DUMP:
> * 12/05/07 19:35:09 spid 53
> *
> * StackDump ()
> *
> * Input Buffer 1890 bytes -
> * select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
> * en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
> else
> * 'Prob' end as CCCC from ( select case when
> right(left(filename,5),
> * 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
> max(
> * left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
> filename
> * FROM dbo.XXX20071201)t group by case when right(left(filename,5),
> 2)>10
> * then left(filename,9) else left(filename,5)end ) as a left join
> ( s
> * elect case when right(left(filename,5),2)>10 then left(filename,
> 9) els
> * e left(filename,5)end as XXX_Name, min(left(right(filename,17),
> 4)) as
> * S_Seq_No from (SELECT distinct filename FROM
> dbo.XXX20071202_online)s
> * group by case when right(left(filename,5),2)>10 then left(filename,
> 9) e
> * lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
> a.XXX
> * _Name
> *
> *
> * Edi: 00000000:
> * Esi: 000004BC:
> * Eax: 00000000:
> * Ebx: 002A76A0: 41076770 00000005 00000002 00000000
> 00F7FBB4 00000004
> * Ecx: 00000000:
> * Edx: 00000000:
> * Eip: 7C8285EC: 24A48DC3 00000000 0024648D 0824548D
> 55C32ECD A48DEC8B
> * Ebp: 048DE088: 002A76F8 41074AEE 000004BC FFFFFFFF
> 00000001 002AE540
> * SegCs: 0000001B:
> * EFlags: 00000246:
> * Esp: 048DE018: 7C827D0B 77E61D1E 000004BC 00000001
> 00000000 77E61C96
> * SegSs: 00000023:
> *
> *******************************************************************************
> *
> -------------------------------------------------------------------------------
> * Short Stack Dump
> * 7C8285EC Module(ntdll+000285EC) (KiFastSystemCallRet+00000000)
> * 41074AEE Module(ums+00004AEE) (UmsThreadScheduler::Switch+0000004E
> Line 490+0000000A)
> * 410729F9 Module(ums+000029F9) (UmsScheduler::IdleLoop+00000119 Line
> 1373+0000000D)
> * 41072B26 Module(ums+00002B26) (UmsScheduler::Suspend+00000066 Line
> 1182+00000000)
> * 4107311B Module(ums+0000311B) (UmsEvent::Wait+0000007B Line
> 320+00000000)
> * 00401261 Module(sqlservr+00001261)
> * 0042341D Module(sqlservr+0002341D)
> * 00854827 Module(sqlservr+00454827) (GetIMallocForMsxml+0005FCA7)
> * 008554FB Module(sqlservr+004554FB) (GetIMallocForMsxml+0006097B)
> * 005FA6E2 Module(sqlservr+001FA6E2) (SQLExit+00092BEB)
> * 00429EAA Module(sqlservr+00029EAA)
> * 00415D04 Module(sqlservr+00015D04)
> * 00416214 Module(sqlservr+00016214)
> * 00415F28 Module(sqlservr+00015F28)
> * 0049C32E Module(sqlservr+0009C32E)
> * 0049C46A Module(sqlservr+0009C46A)
> * 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
> 456+00000000)
> * 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
> 263+00000007)
> * 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
> * 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
> *
> -------------------------------------------------------------------------------
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> *Dump thread - spid = 53, PSS = 0x620a1220, EC = 0x2d8c80c0
> *Stack Dump being sent to d:\datafiles\MSSQL\log\SQLDump0044.txt
> *
> *******************************************************************************
> *
> * BEGIN STACK DUMP:
> * 12/05/07 19:35:10 spid 53
> *
> * StackDump ()
> *
> * Input Buffer 1890 bytes -
> * select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
> * en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
> else
> * 'Prob' end as CCCC from ( select case when
> right(left(filename,5),
> * 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
> max(
> * left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
> filename
> * FROM dbo.XXX20071201)t group by case when right(left(filename,5),
> 2)>10
> * then left(filename,9) else left(filename,5)end ) as a left join
> ( s
> * elect case when right(left(filename,5),2)>10 then left(filename,
> 9) els
> * e left(filename,5)end as XXX_Name, min(left(right(filename,17),
> 4)) as
> * S_Seq_No from (SELECT distinct filename FROM
> dbo.XXX20071202_online)s
> * group by case when right(left(filename,5),2)>10 then left(filename,
> 9) e
> * lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
> a.XXX
> * _Name
> *
> *
> * Edi: 2D8C80C0: 620A1220 2D8C8618 2D8C8618 2D8C80CC
> 2D8C80CC 2D8C8038
> * Esi: 620A1220: 00040035 00000000 01417BB7 000099FB
> 00000000 00000000
> * Eax: 00000000:
> * Ebx: 0696E080: 00130D60 24203D20 20706265 20305424
> 2D203032 3D205E20
> * Ecx: 0696D178: 00000000 0001003F 00000000 00000000
> 00000000 00000000
> * Edx: 0011E878: 00000000 620A1220 2D8C80C0 00000002
> 0003002B 000E01DD
> * Eip: 009BA08C: 26EB0088 90909090 EC458B90 83E47D8B
> B3B904C7 8B000000
> * Ebp: 0696D0FC: 0696D93C 009BA9B5 620A1220 00BCD818
> 2D8C80C0 0000020B
> * SegCs: 0000001B:
> * EFlags: 00010246: 00530055 007E0054 00000031 0055004E
> 0042004D 00520045
> * Esp: 0696D0D4: 2D8C80C0 620A1220 0696E080 0696D178
> 0696D0D4 0696CD00
> * SegSs: 00000023:
> *
> *******************************************************************************
> *
> -------------------------------------------------------------------------------
> * Short Stack Dump
> * 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
> * 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
> * 00A59EA2 Module(sqlservr+00659EA2) (CDStream::PrintDumpHeader
> +0009ADC6)
> * 009B9F56 Module(sqlservr+005B9F56) (GetOSErrString+00004E32)
> * 009BA259 Module(sqlservr+005BA259) (GetOSErrString+00005135)
> * 00A9A049 Module(sqlservr+0069A049) (CDStream::PrintDumpHeader
> +000DAF6D)
> * 7C359C97 Module(MSVCR71+00019C97) (_CxxLongjmpUnwind+000000EA)
> * 7C359629 Module(MSVCR71+00019629) (_CxxExceptionFilter+000001C4)
> * 7C3598AD Module(MSVCR71+000198AD) (_CxxExceptionFilter+00000448)
> * 7C35994A Module(MSVCR71+0001994A) (_CxxExceptionFilter+000004E5)
> * 7C359B9D Module(MSVCR71+00019B9D) (_CxxFrameHandler+00000026)
> * 7C828752 Module(ntdll+00028752) (RtlRaiseStatus+000000E0)
> * 7C828723 Module(ntdll+00028723) (RtlRaiseStatus+000000B1)
> * 7C82855E Module(ntdll+0002855E) (KiUserExceptionDispatcher+0000000E)
> * 0087B831 Module(sqlservr+0047B831) (GetIMallocForMsxml+00086CB1)
> * 0087E635 Module(sqlservr+0047E635) (GetIMallocForMsxml+00089AB5)
> * 0055C692 Module(sqlservr+0015C692)
> * 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
> 456+00000000)
> * 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
> 263+00000007)
> * 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
> * 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
> *
> -------------------------------------------------------------------------------
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> ***Unable to get thread context for spid 53
> *Dump thread - spid = 53, PSS = 0x620a1220, EC = 0x2adf20c0
> *Stack Dump being sent to d:\datafiles\MSSQL\log\SQLDump0044.txt
> *
> *******************************************************************************
> *
> * BEGIN STACK DUMP:
> * 12/05/07 19:35:11 spid 53
> *
> * StackDump ()
> *
> * Input Buffer 1890 bytes -
> * select a.XXX_Name,F_Seq_No,S_Seq_No, case wh
> * en (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then 'OK'
> else
> * 'Prob' end as CCCC from ( select case when
> right(left(filename,5),
> * 2)>10 then left(filename,9) else left(filename,5)end as XXX_Name,
> max(
> * left(right(filename,17),4)) as F_Seq_No from (SELECT distinct
> filename
> * FROM dbo.XXX20071201)t group by case when right(left(filename,5),
> 2)>10
> * then left(filename,9) else left(filename,5)end ) as a left join
> ( s
> * elect case when right(left(filename,5),2)>10 then left(filename,
> 9) els
> * e left(filename,5)end as XXX_Name, min(left(right(filename,17),
> 4)) as
> * S_Seq_No from (SELECT distinct filename FROM
> dbo.XXX20071202_online)s
> * group by case when right(left(filename,5),2)>10 then left(filename,
> 9) e
> * lse left(filename,5)end )as b on a.XXX_Name=b.XXX_Name order by
> a.XXX
> * _Name
> *
> *
> * Edi: 00000000:
> * Esi: 000005DC:
> * Eax: 00000006:
> * Ebx: 002A70D0: 41076770 00000002 00000005 00F8BB2C
> 00F8B8CC 00000000
> * Ecx: 00000001:
> * Edx: 00000000:
> * Eip: 7C8285EC: 24A48DC3 00000000 0024648D 0824548D
> 55C32ECD A48DEC8B
> * Ebp: 04A5F9B8: 002A7128 41074AEE 000005DC FFFFFFFF
> 00000001 002AD2C8
> * SegCs: 0000001B:
> * EFlags: 00000246:
> * Esp: 04A5F948: 7C827D0B 77E61D1E 000005DC 00000001
> 00000000 77E61C96
> * SegSs: 00000023:
> *
> *******************************************************************************
> *
> -------------------------------------------------------------------------------
> * Short Stack Dump
> * 7C8285EC Module(ntdll+000285EC) (KiFastSystemCallRet+00000000)
> * 41074AEE Module(ums+00004AEE) (UmsThreadScheduler::Switch+0000004E
> Line 490+0000000A)
> * 410729F9 Module(ums+000029F9) (UmsScheduler::IdleLoop+00000119 Line
> 1373+0000000D)
> * 41072B26 Module(ums+00002B26) (UmsScheduler::Suspend+00000066 Line
> 1182+00000000)
> * 4107311B Module(ums+0000311B) (UmsEvent::Wait+0000007B Line
> 320+00000000)
> * 00401261 Module(sqlservr+00001261)
> * 0042341D Module(sqlservr+0002341D)
> * 0087F0C2 Module(sqlservr+0047F0C2) (GetIMallocForMsxml+0008A542)
> * 0087F5D3 Module(sqlservr+0047F5D3) (GetIMallocForMsxml+0008AA53)
> * 008814E2 Module(sqlservr+004814E2) (GetIMallocForMsxml+0008C962)
> * 0087B18B Module(sqlservr+0047B18B) (GetIMallocForMsxml+0008660B)
> * 0043288B Module(sqlservr+0003288B)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 0041D396 Module(sqlservr+0001D396)
> * 0087B9BB Module(sqlservr+0047B9BB) (GetIMallocForMsxml+00086E3B)
> * 0087E509 Module(sqlservr+0047E509) (GetIMallocForMsxml+00089989)
> * 0055C692 Module(sqlservr+0015C692)
> * 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line
> 456+00000000)
> * 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line
> 263+00000007)
> * 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
> * 77E64829 Module(kernel32+00024829) (GetModuleHandleA+000000DF)
> *
> -------------------------------------------------------------------------------
> ***Unable to get thread context for spid 53
>
>
> The query
> -------------------------------------------------------------
> select a.XXX_Name,F_Seq_No,S_Seq_No,
> case when (cast(S_Seq_No as bigint)-cast(F_Seq_No as bigint))=1 then
> 'OK' else 'Prob' end as CCCC
> from
> (
> select
> case when right(left(filename,5),2)>10 then left(filename,9) else
> left(filename,5)end as XXX_Name,
> max(left(right(filename,17),4)) as F_Seq_No
> from (SELECT distinct filename FROM dbo.XXX20071201)t
> group by case when right(left(filename,5),2)>10 then left(filename,9)
> else left(filename,5)end
> ) as a
> left join
> (
> select
> case when right(left(filename,5),2)>10 then left(filename,9) else
> left(filename,5)end as XXX_Name,
> min(left(right(filename,17),4)) as S_Seq_No
> from (SELECT distinct filename FROM dbo.XXX20071202_online)s
> group by case when right(left(filename,5),2)>10 then left(filename,9)
> else left(filename,5)end
> )as b
> on a.XXX_Name=b.XXX_Name
> order by a.XXX_Name
Navigation:
[Reply to this message]
|