DTS for insert/update

I'm new to DTS. I read some docs before adventuring into this matter.
I still haven't found in all the docs I read if there is some "built-in" DTS
task or function or wathever, to do a mixed "insert/update" import from a
source, giving a unique field as primary key.
I'll try to be more specific. The problem I would like to solve is this:
I have a source file (csv) but it could be any source. I must check this
file for all the records and compare them with the ones in the db (giving a
unique field as a way of checking), so that all the records that already
exist, are UPDATED, and the others are INSERTED.
I guess this is one most common task to accomplish, when you have a local
based application that you regularly update and then you want to export the
data to another "slave" application without using replication. But even if
this sounds to me like a common task, I found no "buil-in" function for that
in DTS. I read something about "lookups" but it don't know if it's related
somehow.. it wasn't very clean.
Thanks in advance for suggestions.

--
:: Massimiliano Mattei
:: Project Leader
:: E.xtranet V.irtual A.pplication
:: www.evagroup.net :: www.commy.bizMassimiliano,
Yes, that is a very common task. The best way to handle this is to
import directly into a temp table and then run a procedure to
check/insert/update your data into a production table.
If you need a specific example to follow, we have one on our website at
www.TechnicalVideos.net. $19.99 buys all of our videos for 3 months. Our
DTS videos will have an expert show you step by step how to do this.

Hope that helps,
Best Regards,
Chuck Conover
www.TechnicalVideos.net

"xMANIGHTx" <manight@.ufologia.net> wrote in message
news:c2aav5$noi$1@.lacerta.tiscalinet.it...
> I'm new to DTS. I read some docs before adventuring into this matter.
> I still haven't found in all the docs I read if there is some "built-in"
DTS
> task or function or wathever, to do a mixed "insert/update" import from a
> source, giving a unique field as primary key.
> I'll try to be more specific. The problem I would like to solve is this:
> I have a source file (csv) but it could be any source. I must check this
> file for all the records and compare them with the ones in the db (giving
a
> unique field as a way of checking), so that all the records that already
> exist, are UPDATED, and the others are INSERTED.
> I guess this is one most common task to accomplish, when you have a local
> based application that you regularly update and then you want to export
the
> data to another "slave" application without using replication. But even if
> this sounds to me like a common task, I found no "buil-in" function for
that
> in DTS. I read something about "lookups" but it don't know if it's related
> somehow.. it wasn't very clean.
> Thanks in advance for suggestions.
> --
> :: Massimiliano Mattei
> :: Project Leader
> :: E.xtranet V.irtual A.pplication
> :: www.evagroup.net :: www.commy.biz|||Thanks Chuck!
Your videos are really a good idea!! I checked the demo one and they are
kool... maybe I'm going to subscribe, even if I'm italian.
I must say that the language is well understandable even if a bit "American"
:)
You say that the best pratice is to import first all teh data in a temp
table (maybe to use the bulk inserta advantages) but what if the records are
really too much? Say I have to import 100.000 records or more.. Would you
use a #temp_table, just a "memory" table or a real table?
I don't know if SQL Server writes down #temp_tables just like it does with
real ones, otherwise the server would have to mantain 100.000 records in
memory...
Wich type of table would you suggest? Maybe SQL Server has memory
optimization routines wich write to disk data of #tables not to hold them
only in the RAM?
Do you think this is the best method? DTS is for data trasformation so it's
ODD it doesn't have something that lets you
import/check/transform/insert-update data without writing a specific
procedure for that.

--
:: Massimiliano Mattei
:: Project Leader
:: E.xtranet V.irtual A.pplication
:: www.evagroup.net :: www.commy.biz|||Massimiliano,
Sorry. I know we are too American. We really need to get out more.
My explanation was not very good. By "temp" table, I mean a table that
is not a production table. We can call this a "working" table. So, I would
use a real table to temporarily store the data for your bulk upload, not an
actual temp table that gets stored in memory. I would do it this way even
if you only have a few records. Then, my DTS package would look like this:

- delete all records from the working table
- upload my data file to my working table
- run stored procedure to check/insert/update data row by row from the
working table to my production table

This way, we can execute each step one at a time, and browse the working
table to make sure the data went into each field correctly.
Hope this helps,
Chuck Conover
www.TechnicalVideos.net

"xMANIGHTx" <manight@.ufologia.net> wrote in message
news:c2f4ku$ccj$1@.lacerta.tiscalinet.it...
> Thanks Chuck!
> Your videos are really a good idea!! I checked the demo one and they are
> kool... maybe I'm going to subscribe, even if I'm italian.
> I must say that the language is well understandable even if a bit
"American"
> :)
> You say that the best pratice is to import first all teh data in a temp
> table (maybe to use the bulk inserta advantages) but what if the records
are
> really too much? Say I have to import 100.000 records or more.. Would you
> use a #temp_table, just a "memory" table or a real table?
> I don't know if SQL Server writes down #temp_tables just like it does with
> real ones, otherwise the server would have to mantain 100.000 records in
> memory...
> Wich type of table would you suggest? Maybe SQL Server has memory
> optimization routines wich write to disk data of #tables not to hold them
> only in the RAM?
> Do you think this is the best method? DTS is for data trasformation so
it's
> ODD it doesn't have something that lets you
> import/check/transform/insert-update data without writing a specific
> procedure for that.
> --
> :: Massimiliano Mattei
> :: Project Leader
> :: E.xtranet V.irtual A.pplication
> :: www.evagroup.net :: www.commy.bizsql

DTS Date Problem

I have a DTS package that imports from a text file into SQL 2k SP3. I am trying to insert a field (along with the imported fields) during the DTS execution that will be the current date. Does anyone know of a way to do this? I really didn't think I should have to schedule a seperate job to UPDATE each record with the current date...

Make sense?Make a datetime column in the imported table default to getdate().|||Originally posted by joejcheng
Make a datetime column in the imported table default to getdate().

hehe, thanks. That's what I was trying to do, but for some stupid reason I wasn't putting the () on the end of getdate.

Thanks

DTS bulk load fails

Hi,
while executing a bulk insert DTS task, we sometimes experience an
'EXCEPTION_ACCESS_VIOLATION' error. We load several text files
into sql tables parrallel. The error occurs does not necessarily occur
on the same bulk load task, every we test the dts package. Sometimes
we don't even get the error at all.
We're running on sql server 2000 sp3 , any input or advice on this matter
would be highly appreciated.
Please find below the stack dump file...
Regards,
Erwin
=====================================================================
BugCheck Dump
=====================================================================
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is IBM - 1000
IBM BIOS Ver 1.05
Current time is 03:00:33 12/24/05.
4 Intel x86 level 15, 3000 Mhz processor(s).
Windows NT 5.2 Build 3790 CSD .
Memory
MemoryLoad = 60%
Total Physical = 3583 MB
Available Physical = 1403 MB
Total Page File = 5474 MB
Available Page File = 3466 MB
Total Virtual = 2047 MB
Available Virtual = 236 MB
*Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
*
*****************************************************************************
**
*
* BEGIN STACK DUMP:
* 12/24/05 03:00:33 spid 61
*
* Exception Address = 06B824C5 (DDCS_Enter + 00000004 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 06E1FF2C
* Input Buffer 468 bytes -
* BULK INSERT S_Import_IncrementalStock_InsertUpdates FROM 'e:\MerRep_Data
* \Work\DwMerchandise_UKwms_Batch00481_IncrementalStock_InsertUpdates.txt'
* WITH (CODEPAGE = 'RAW' , FIELDTERMINATOR = '¿¿' , ROWTERMINATOR = ' ' ,
* MAXERRORS = 10 )
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
* ntdll 77F40000 77FF9FFF 000ba000
* kernel32 77E40000 77F33FFF 000f4000
* ADVAPI32 77DA0000 77E2FFFF 00090000
* RPCRT4 77C50000 77CF4FFF 000a5000
* USER32 77D00000 77D8EFFF 0008f000
* GDI32 77C00000 77C43FFF 00044000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF3FFF 00054000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 02C60000 02CB2FFF 00053000
* ole32 030D0000 031F4FFF 00125000
* XOLEHLP 03200000 03205FFF 00006000
* MSDTCPRX 03210000 03281FFF 00072000
* OLEAUT32 03290000 0330CFFF 0007d000
* MSVCP60 03310000 03370FFF 00061000
* MTXCLU 03380000 03397FFF 00018000
* VERSION 033A0000 033A7FFF 00008000
* WSOCK32 033B0000 033B8FFF 00009000
* WS2_32 033C0000 033D7FFF 00018000
* WS2HELP 033E0000 033E7FFF 00008000
* CLUSAPI 03430000 03440FFF 00011000
* RESUTILS 03450000 03461FFF 00012000
* USERENV 03470000 03529FFF 000ba000
* MFC42u 03530000 03623FFF 000f4000
* secur32 036B0000 036C2FFF 00013000
* mswsock 036E0000 03722FFF 00043000
* DNSAPI 03730000 03756FFF 00027000
* winrnr 037A0000 037A6FFF 00007000
* WLDAP32 037B0000 037DEFFF 0002f000
* rasadhlp 03800000 03804FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* SSNMPN70 00C90000 00C95FFF 00006000
* security 042C0000 042C3FFF 00004000
* wshtcpip 04310000 04317FFF 00008000
* SSmsLPCn 04320000 04326FFF 00007000
* ntdsapi 04340000 04355FFF 00016000
* kerberos 04770000 047C5FFF 00056000
* cryptdll 047D0000 047DBFFF 0000c000
* MSASN1 047E0000 047F1FFF 00012000
* SQLFTQRY 04630000 04661FFF 00032000
* CLBCatQ 04B20000 04B9DFFF 0007e000
* COMRes 04BA0000 04C65FFF 000c6000
* sqloledb 04C90000 04D15FFF 00086000
* MSDART 04D20000 04D48FFF 00029000
* COMCTL32 04D50000 04DDFFFF 00090000
* comdlg32 04DE0000 04E26FFF 00047000
* SHLWAPI 04E30000 04E78FFF 00049000
* SHELL32 04E80000 0565DFFF 007de000
* MSDATL3 05660000 05677FFF 00018000
* comctl32 05690000 05775FFF 000e6000
* oledb32 05B20000 05B9CFFF 0007d000
* OLEDB32R 05BA0000 05BB0FFF 00011000
* msv1_0 05C50000 05C73FFF 00024000
* xpsqlbot 05C80000 05C85FFF 00006000
* xpstar 05CB0000 05CF6FFF 00047000
* SQLRESLD 05D00000 05D06FFF 00007000
* SQLSVC 05D10000 05D26FFF 00017000
* ODBC32 05D30000 05D69FFF 0003a000
* odbcbcp 05D70000 05D75FFF 00006000
* W95SCM 05D90000 05D9BFFF 0000c000
* SQLUNIRL 05DA0000 05DCCFFF 0002d000
* WINSPOOL 05DD0000 05DF5FFF 00026000
* SHFOLDER 05E00000 05E08FFF 00009000
* odbcint 05FF0000 06006FFF 00017000
* NDDEAPI 06110000 06116FFF 00007000
* SQLSVC 06120000 06125FFF 00006000
* xpstar 06130000 06138FFF 00009000
* rsaenh 0FFD0000 0FFFCFFF 0002d000
* PSAPI 05D80000 05D8AFFF 0000b000
* impprov 065D0000 065E8FFF 00019000
* sqlmap70 06580000 065A9FFF 0002a000
* MAPI32 065B0000 065CEFFF 0001f000
* MSMAPI32 06B80000 06CCDFFF 0014e000
* mso 07510000 080C1FFF 00bb2000
* iphlpapi 06F20000 06F36FFF 00017000
* MPRAPI 08270000 08286FFF 00017000
* ACTIVEDS 08290000 082C1FFF 00032000
* adsldpc 082D0000 082F5FFF 00026000
* credui 08300000 0832CFFF 0002d000
* ATL 08330000 08347FFF 00018000
* rtutils 08350000 0835AFFF 0000b000
* SAMLIB 08360000 0836FFFF 00010000
* SETUPAPI 08370000 0846FFFF 00100000
* netman 084D0000 08506FFF 00037000
* RASAPI32 08510000 0854AFFF 0003b000
* rasman 08550000 08560FFF 00011000
* TAPI32 08570000 0859DFFF 0002e000
* WINMM 085A0000 085CBFFF 0002c000
* CRYPT32 085D0000 08667FFF 00098000
* WZCSvc 08670000 086B6FFF 00047000
* WMI 08470000 08474FFF 00005000
* DHCPCSVC 086C0000 086DBFFF 0001c000
* WTSAPI32 086E0000 086E7FFF 00008000
* WINSTA 086F0000 086FFFFF 00010000
* ESENT 08700000 08807FFF 00108000
* WZCSAPI 08810000 08818FFF 00009000
* xplog70 06560000 06571FFF 00012000
* xplog70 09160000 09163FFF 00004000
* sqlvdi 09170000 0917BFFF 0000c000
* msi 0AA10000 0AC23FFF 00214000
* MAPIR 06F50000 0700FFFF 000c0000
* contab32 030B0000 030CEFFF 0001f000
* EMSABP32 06DA0000 06DD7FFF 00038000
* EMSMDB32 08F60000 09019FFF 000ba000
* uxtheme 09120000 09152FFF 00033000
* dbghelp 09720000 097D4FFF 000b5000
*
* Edi: 06E1FF14:
* Esi: 06E1FEF8:
* Eax: C0000000:
* Ebx: 00000000:
* Ecx: 06E1FF14:
* Edx: 00000020:
* Eip: 06B824C5:
* Ebp: 0A30EDF4: 0A30EE20 77D0612F 00EC019E 00000507 06E1FEF8
00000
000
* SegCs: 0000001B:
* EFlags: 00010246: 006D0065 00320033 0057005C 00650062 003B006D
003A0
043
* Esp: 0A30EDD8: 06B86F6F 06E1FF14 0A30EE6C 06E1FEF8 06B8510A
06E1F
EF8
* SegSs: 00000023:
*
*****************************************************************************
**
*
----
--
* Short Stack Dump
* 06B824C5 Module(MSMAPI32+000024C5) (DDCS_Enter+00000004)
* 77D0612F Module(USER32+0000612F)
* 77D069A5 Module(USER32+000069A5) (GetMessageW+000001EE)
* 77D06689 Module(USER32+00006689) (TranslateMessage+00000520)
* 77D0FAE4 Module(USER32+0000FAE4) (DispatchMessageA+0000000B)
* 065D2BB3 Module(impprov+00002BB3)
* 065DE84A Module(impprov+0000E84A) (DllRegisterServer+0000AA3A)
* 065E145C Module(impprov+0001145C) (DllRegisterServer+0000D64C)
* 065DE1DC Module(impprov+0000E1DC) (DllRegisterServer+0000A3CC)
* 0047E663 Module(sqlservr+0007E663)
(CBcpImport::CreateInprocProvider+000002EF
)
* 0047DB5A Module(sqlservr+0007DB5A) (CBcpImport::Init+0000029D)
* 0047D854 Module(sqlservr+0007D854) (BcpImportMain+0000014D)
* 0047D714 Module(sqlservr+0007D714) (CStmtBulkIns::XretExecute+0000005F)
* 0041B442 Module(sqlservr+0001B442)
(CMsqlExecContext::ExecuteStmts+000003B9)
* 0041AA88 Module(sqlservr+0001AA88) (CMsqlExecContext::Execute+000001B6)
* 0041B9B6 Module(sqlservr+0001B9B6) (CSQLSource::Execute+00000357)
* 0048A63B Module(sqlservr+0008A63B) (language_exec+000003E1)
* 0042708C Module(sqlservr+0002708C) (process_commands+0000010E)
* 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
* 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
* 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
* 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
----
* Location : 06B824C5 Module(MSMAPI32+000024C5) (DDCS_Enter+00000004)
* Return Addr: 77D0612F Module(USER32+0000612F)
Frame : 0A30EDF4
Parameters:
[1] 00EC019E: 00000240 00000000 00000000 00000000 00000000 00000000
[2] 00000507:
[3] 06E1FEF8:
[4] 00000000:
160 bytes of stack data from 0A30ED54 to 0A30EDF4
0A30ED54: 00000000 00000000 00000000 00000000 [............]
:::: 7 Duplicate lines detected
0A30EDD4: 00000000 06B86F6F 06E1FF14 0A30EE6C [...oo.....l.0.]
0A30EDE4: 06E1FEF8 06B8510A 06E1FEF8 06B850F4 [....Q......P..]
----
* Location : 77D0612F Module(USER32+0000612F)
* Return Addr: 77D069A5 Module(USER32+000069A5) (GetMessageW+000001EE)
Frame : 0A30EE20
Parameters:
[1] 06B850F4:
[2] 00EC019E: 00000240 00000000 00000000 00000000 00000000 00000000
[3] 00000507:
[4] 06E1FEF8:
44 bytes of stack data from 0A30EDF4 to 0A30EE20
0A30EDF4: 0A30EE20 77D0612F 00EC019E 00000507 [ .0./a.w......]
0A30EE04: 06E1FEF8 00000000 06B850F4 DCBAABCD [.......P.....]
0A30EE14: 00000000 0A30EE6C 06B850F4 [...l.0..P..]
..
PSS @.0x76D8D258
--
pspid = 61 m_dwLoginFlags = 0x1083e0 plsid = 1717878624
pbackground = 0
pbSid
--
01010000 00000005 12000000 .........
sSecFlags = 0x1e
pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0
ptickcount = 1352914718 pcputickcount = 4059555816433772
ploginstamp = 10002 ptimestamp = 2005-12-24 03:00:33.530
plangid = 0 pdateformat = 1 pdatefirst = 7
Language = us_english UserName = NT AUTHORITY\SYSTEM
poptions = 0x28000020 poptions2 = 0x13f438 pline = 1
pcurstepno = 1 prowcount = 0 pstatlist = 0
pcurcmd = 240 pseqstat = 0 ptextsize = 2147483647
pretstat = 0 CNestLevel = 0 @.@.procid = 0
Xact_Begin_Time = 2005-12-24 03:00:33.543
pslastbatchstarttime = 2005-12-24 03:00:33.543 pmemusage = 5
hLicense = 0 tpFlags = 0x1 isolation_level = 0
fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0
pfetchstat = 0 pcrsrows = 0
pslastbatchtime = 2005-12-24 03:00:33.543 pNtUser = SYSTEM
pNtDomain = NT AUTHORITY pubexecdb = 0 fInReplicatedProcExec = 0
pMsqlXact = 0x76D8DE60 presSemCount = [0]170977488
presSemCount = [0]170977488 pcputot = 0
pcputotstart = 0 pcpucmdstart = 0 pbufread = 0
pbufreadstart = 0 plogbufread = 178 plogbufreadstart = 162
pbufwrite = 0 pbufwritestart = 0 pLockTimeout = 4294967295
pUtilResultSet = 0x00000000
pec
--
ec_pss->pspid = 61 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100001 ec_atomic = 0x0 pcurdb = 9
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 2408031
ec_cmderrs = 0 ec_timeslice = 84 ec_dbtable = 0x19EB0030
ec_reswait = 0x0 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x2
ec_umsContext->m_workercntxt->m_id (threadId) = 0x9fc
ExecutionContext Summary @.0x76D8D580
--
ec_pss->pspid = 61 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100001 ec_atomic = 0x0 pcurdb = 9
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 2408031
ec_cmderrs = 0 ec_timeslice = 84 ec_dbtable = 0x19EB0030
ec_reswait = 0x0 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x2
ec_umsContext->m_workercntxt->m_id (threadId) = 0x9fc
psrvproc->eclClient = 4 psrvproc->status = 8
psrvproc->state = SRV_RUNNING psrvproc->bNewPacket = 0
psrvproc->pmo = 0x76D84030
psrvproc->ums_context = 0x038A1950 psrvproc->threadId = 0x9fc
client threadId = 0xdc8
...Hi
This http://support.microsoft.com/default.aspx?scid=kb;en-us;839458 was
fixed after SP3, although you don't give any information about identity
values and I would not expect it to perform inconsistently as you describe;
but you may want to see if loading SP4 removes this issue. Something else you
may wish to try could be to run them all on the main thread.
John
"Erwin" wrote:
> Hi,
> while executing a bulk insert DTS task, we sometimes experience an
> 'EXCEPTION_ACCESS_VIOLATION' error. We load several text files
> into sql tables parrallel. The error occurs does not necessarily occur
> on the same bulk load task, every we test the dts package. Sometimes
> we don't even get the error at all.
> We're running on sql server 2000 sp3 , any input or advice on this matter
> would be highly appreciated.
> Please find below the stack dump file...
> Regards,
> Erwin
> =====================================================================> BugCheck Dump
> =====================================================================>
> This file is generated by Microsoft SQL Server 8.00.760
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
>
> Computer type is AT/AT COMPATIBLE.
> Bios Version is IBM - 1000
> IBM BIOS Ver 1.05
> Current time is 03:00:33 12/24/05.
> 4 Intel x86 level 15, 3000 Mhz processor(s).
> Windows NT 5.2 Build 3790 CSD .
>
> Memory
> MemoryLoad = 60%
> Total Physical = 3583 MB
> Available Physical = 1403 MB
> Total Page File = 5474 MB
> Available Page File = 3466 MB
> Total Virtual = 2047 MB
> Available Virtual = 236 MB
> *Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
> *
> *****************************************************************************
> **
> *
> * BEGIN STACK DUMP:
> * 12/24/05 03:00:33 spid 61
> *
> * Exception Address = 06B824C5 (DDCS_Enter + 00000004 Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 06E1FF2C
> * Input Buffer 468 bytes -
> * BULK INSERT S_Import_IncrementalStock_InsertUpdates FROM 'e:\MerRep_Data
> * \Work\DwMerchandise_UKwms_Batch00481_IncrementalStock_InsertUpdates.txt'
> * WITH (CODEPAGE = 'RAW' , FIELDTERMINATOR = '¿¿' , ROWTERMINATOR = ' ' ,
> * MAXERRORS = 10 )
> *
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> * ntdll 77F40000 77FF9FFF 000ba000
> * kernel32 77E40000 77F33FFF 000f4000
> * ADVAPI32 77DA0000 77E2FFFF 00090000
> * RPCRT4 77C50000 77CF4FFF 000a5000
> * USER32 77D00000 77D8EFFF 0008f000
> * GDI32 77C00000 77C43FFF 00044000
> * OPENDS60 41060000 41065FFF 00006000
> * MSVCRT 77BA0000 77BF3FFF 00054000
> * UMS 41070000 4107CFFF 0000d000
> * SQLSORT 42AE0000 42B6FFFF 00090000
> * MSVCIRT 60020000 6002FFFF 00010000
> * sqlevn70 41080000 41086FFF 00007000
> * NETAPI32 02C60000 02CB2FFF 00053000
> * ole32 030D0000 031F4FFF 00125000
> * XOLEHLP 03200000 03205FFF 00006000
> * MSDTCPRX 03210000 03281FFF 00072000
> * OLEAUT32 03290000 0330CFFF 0007d000
> * MSVCP60 03310000 03370FFF 00061000
> * MTXCLU 03380000 03397FFF 00018000
> * VERSION 033A0000 033A7FFF 00008000
> * WSOCK32 033B0000 033B8FFF 00009000
> * WS2_32 033C0000 033D7FFF 00018000
> * WS2HELP 033E0000 033E7FFF 00008000
> * CLUSAPI 03430000 03440FFF 00011000
> * RESUTILS 03450000 03461FFF 00012000
> * USERENV 03470000 03529FFF 000ba000
> * MFC42u 03530000 03623FFF 000f4000
> * secur32 036B0000 036C2FFF 00013000
> * mswsock 036E0000 03722FFF 00043000
> * DNSAPI 03730000 03756FFF 00027000
> * winrnr 037A0000 037A6FFF 00007000
> * WLDAP32 037B0000 037DEFFF 0002f000
> * rasadhlp 03800000 03804FFF 00005000
> * SSNETLIB 00C70000 00C84FFF 00015000
> * SSNMPN70 00C90000 00C95FFF 00006000
> * security 042C0000 042C3FFF 00004000
> * wshtcpip 04310000 04317FFF 00008000
> * SSmsLPCn 04320000 04326FFF 00007000
> * ntdsapi 04340000 04355FFF 00016000
> * kerberos 04770000 047C5FFF 00056000
> * cryptdll 047D0000 047DBFFF 0000c000
> * MSASN1 047E0000 047F1FFF 00012000
> * SQLFTQRY 04630000 04661FFF 00032000
> * CLBCatQ 04B20000 04B9DFFF 0007e000
> * COMRes 04BA0000 04C65FFF 000c6000
> * sqloledb 04C90000 04D15FFF 00086000
> * MSDART 04D20000 04D48FFF 00029000
> * COMCTL32 04D50000 04DDFFFF 00090000
> * comdlg32 04DE0000 04E26FFF 00047000
> * SHLWAPI 04E30000 04E78FFF 00049000
> * SHELL32 04E80000 0565DFFF 007de000
> * MSDATL3 05660000 05677FFF 00018000
> * comctl32 05690000 05775FFF 000e6000
> * oledb32 05B20000 05B9CFFF 0007d000
> * OLEDB32R 05BA0000 05BB0FFF 00011000
> * msv1_0 05C50000 05C73FFF 00024000
> * xpsqlbot 05C80000 05C85FFF 00006000
> * xpstar 05CB0000 05CF6FFF 00047000
> * SQLRESLD 05D00000 05D06FFF 00007000
> * SQLSVC 05D10000 05D26FFF 00017000
> * ODBC32 05D30000 05D69FFF 0003a000
> * odbcbcp 05D70000 05D75FFF 00006000
> * W95SCM 05D90000 05D9BFFF 0000c000
> * SQLUNIRL 05DA0000 05DCCFFF 0002d000
> * WINSPOOL 05DD0000 05DF5FFF 00026000
> * SHFOLDER 05E00000 05E08FFF 00009000
> * odbcint 05FF0000 06006FFF 00017000
> * NDDEAPI 06110000 06116FFF 00007000
> * SQLSVC 06120000 06125FFF 00006000
> * xpstar 06130000 06138FFF 00009000
> * rsaenh 0FFD0000 0FFFCFFF 0002d000
> * PSAPI 05D80000 05D8AFFF 0000b000
> * impprov 065D0000 065E8FFF 00019000
> * sqlmap70 06580000 065A9FFF 0002a000
> * MAPI32 065B0000 065CEFFF 0001f000
> * MSMAPI32 06B80000 06CCDFFF 0014e000
> * mso 07510000 080C1FFF 00bb2000
> * iphlpapi 06F20000 06F36FFF 00017000
> * MPRAPI 08270000 08286FFF 00017000
> * ACTIVEDS 08290000 082C1FFF 00032000
> * adsldpc 082D0000 082F5FFF 00026000
> * credui 08300000 0832CFFF 0002d000
> * ATL 08330000 08347FFF 00018000
> * rtutils 08350000 0835AFFF 0000b000
> * SAMLIB 08360000 0836FFFF 00010000
> * SETUPAPI 08370000 0846FFFF 00100000
> * netman 084D0000 08506FFF 00037000
> * RASAPI32 08510000 0854AFFF 0003b000
> * rasman 08550000 08560FFF 00011000
> * TAPI32 08570000 0859DFFF 0002e000
> * WINMM 085A0000 085CBFFF 0002c000
> * CRYPT32 085D0000 08667FFF 00098000
> * WZCSvc 08670000 086B6FFF 00047000
> * WMI 08470000 08474FFF 00005000
> * DHCPCSVC 086C0000 086DBFFF 0001c000
> * WTSAPI32 086E0000 086E7FFF 00008000
> * WINSTA 086F0000 086FFFFF 00010000
> * ESENT 08700000 08807FFF 00108000
> * WZCSAPI 08810000 08818FFF 00009000
> * xplog70 06560000 06571FFF 00012000
> * xplog70 09160000 09163FFF 00004000
> * sqlvdi 09170000 0917BFFF 0000c000
>

DTS bulk load fails

Hi,
while executing a bulk insert DTS task, we sometimes experience an
'EXCEPTION_ACCESS_VIOLATION' error. We load several text files
into sql tables parrallel. The error occurs does not necessarily occur
on the same bulk load task, every we test the dts package. Sometimes
we don't even get the error at all.
We're running on sql server 2000 sp3 , any input or advice on this matter
would be highly appreciated.
Please find below the stack dump file...
Regards,
Erwin
========================================
=============================
BugCheck Dump
========================================
=============================
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is IBM - 1000
IBM BIOS Ver 1.05
Current time is 03:00:33 12/24/05.
4 Intel x86 level 15, 3000 Mhz processor(s).
Windows NT 5.2 Build 3790 CSD .
Memory
MemoryLoad = 60%
Total Physical = 3583 MB
Available Physical = 1403 MB
Total Page File = 5474 MB
Available Page File = 3466 MB
Total Virtual = 2047 MB
Available Virtual = 236 MB
*Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
*
****************************************
************************************
*
**
*
* BEGIN STACK DUMP:
* 12/24/05 03:00:33 spid 61
*
* Exception Address = 06B824C5 (DDCS_Enter + 00000004 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 06E1FF2C
* Input Buffer 468 bytes -
* BULK INSERT S_Import_IncrementalStock_InsertUpdates FROM 'e:\MerRep_Data
* \Work\DwMerchandise_UKwms_Batch00481_Inc
rementalStock_InsertUpdates.txt'
* WITH (CODEPAGE = 'RAW' , FIELDTERMINATOR = '??' , ROWTERMINATOR = ' '
,
* MAXERRORS = 10 )
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
* ntdll 77F40000 77FF9FFF 000ba000
* kernel32 77E40000 77F33FFF 000f4000
* ADVAPI32 77DA0000 77E2FFFF 00090000
* RPCRT4 77C50000 77CF4FFF 000a5000
* USER32 77D00000 77D8EFFF 0008f000
* GDI32 77C00000 77C43FFF 00044000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF3FFF 00054000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 02C60000 02CB2FFF 00053000
* ole32 030D0000 031F4FFF 00125000
* XOLEHLP 03200000 03205FFF 00006000
* MSDTCPRX 03210000 03281FFF 00072000
* OLEAUT32 03290000 0330CFFF 0007d000
* MSVCP60 03310000 03370FFF 00061000
* MTXCLU 03380000 03397FFF 00018000
* VERSION 033A0000 033A7FFF 00008000
* WSOCK32 033B0000 033B8FFF 00009000
* WS2_32 033C0000 033D7FFF 00018000
* WS2HELP 033E0000 033E7FFF 00008000
* CLUSAPI 03430000 03440FFF 00011000
* RESUTILS 03450000 03461FFF 00012000
* USERENV 03470000 03529FFF 000ba000
* MFC42u 03530000 03623FFF 000f4000
* secur32 036B0000 036C2FFF 00013000
* mswsock 036E0000 03722FFF 00043000
* DNSAPI 03730000 03756FFF 00027000
* winrnr 037A0000 037A6FFF 00007000
* WLDAP32 037B0000 037DEFFF 0002f000
* rasadhlp 03800000 03804FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* SSNMPN70 00C90000 00C95FFF 00006000
* security 042C0000 042C3FFF 00004000
* wshtcpip 04310000 04317FFF 00008000
* SSmsLPCn 04320000 04326FFF 00007000
* ntdsapi 04340000 04355FFF 00016000
* kerberos 04770000 047C5FFF 00056000
* cryptdll 047D0000 047DBFFF 0000c000
* MSASN1 047E0000 047F1FFF 00012000
* SQLFTQRY 04630000 04661FFF 00032000
* CLBCatQ 04B20000 04B9DFFF 0007e000
* COMRes 04BA0000 04C65FFF 000c6000
* sqloledb 04C90000 04D15FFF 00086000
* MSDART 04D20000 04D48FFF 00029000
* COMCTL32 04D50000 04DDFFFF 00090000
* comdlg32 04DE0000 04E26FFF 00047000
* SHLWAPI 04E30000 04E78FFF 00049000
* SHELL32 04E80000 0565DFFF 007de000
* MSDATL3 05660000 05677FFF 00018000
* comctl32 05690000 05775FFF 000e6000
* oledb32 05B20000 05B9CFFF 0007d000
* OLEDB32R 05BA0000 05BB0FFF 00011000
* msv1_0 05C50000 05C73FFF 00024000
* xpsqlbot 05C80000 05C85FFF 00006000
* xpstar 05CB0000 05CF6FFF 00047000
* SQLRESLD 05D00000 05D06FFF 00007000
* SQLSVC 05D10000 05D26FFF 00017000
* ODBC32 05D30000 05D69FFF 0003a000
* odbcbcp 05D70000 05D75FFF 00006000
* W95SCM 05D90000 05D9BFFF 0000c000
* SQLUNIRL 05DA0000 05DCCFFF 0002d000
* WINSPOOL 05DD0000 05DF5FFF 00026000
* SHFOLDER 05E00000 05E08FFF 00009000
* odbcint 05FF0000 06006FFF 00017000
* NDDEAPI 06110000 06116FFF 00007000
* SQLSVC 06120000 06125FFF 00006000
* xpstar 06130000 06138FFF 00009000
* rsaenh 0FFD0000 0FFFCFFF 0002d000
* PSAPI 05D80000 05D8AFFF 0000b000
* impprov 065D0000 065E8FFF 00019000
* sqlmap70 06580000 065A9FFF 0002a000
* MAPI32 065B0000 065CEFFF 0001f000
* MSMAPI32 06B80000 06CCDFFF 0014e000
* mso 07510000 080C1FFF 00bb2000
* iphlpapi 06F20000 06F36FFF 00017000
* MPRAPI 08270000 08286FFF 00017000
* ACTIVEDS 08290000 082C1FFF 00032000
* adsldpc 082D0000 082F5FFF 00026000
* credui 08300000 0832CFFF 0002d000
* ATL 08330000 08347FFF 00018000
* rtutils 08350000 0835AFFF 0000b000
* SAMLIB 08360000 0836FFFF 00010000
* SETUPAPI 08370000 0846FFFF 00100000
* netman 084D0000 08506FFF 00037000
* RASAPI32 08510000 0854AFFF 0003b000
* rasman 08550000 08560FFF 00011000
* TAPI32 08570000 0859DFFF 0002e000
* WINMM 085A0000 085CBFFF 0002c000
* CRYPT32 085D0000 08667FFF 00098000
* WZCSvc 08670000 086B6FFF 00047000
* WMI 08470000 08474FFF 00005000
* DHCPCSVC 086C0000 086DBFFF 0001c000
* WTSAPI32 086E0000 086E7FFF 00008000
* WINSTA 086F0000 086FFFFF 00010000
* ESENT 08700000 08807FFF 00108000
* WZCSAPI 08810000 08818FFF 00009000
* xplog70 06560000 06571FFF 00012000
* xplog70 09160000 09163FFF 00004000
* sqlvdi 09170000 0917BFFF 0000c000
* msi 0AA10000 0AC23FFF 00214000
* MAPIR 06F50000 0700FFFF 000c0000
* contab32 030B0000 030CEFFF 0001f000
* EMSABP32 06DA0000 06DD7FFF 00038000
* EMSMDB32 08F60000 09019FFF 000ba000
* uxtheme 09120000 09152FFF 00033000
* dbghelp 09720000 097D4FFF 000b5000
*
* Edi: 06E1FF14:
* Esi: 06E1FEF8:
* Eax: C0000000:
* Ebx: 00000000:
* Ecx: 06E1FF14:
* Edx: 00000020:
* Eip: 06B824C5:
* Ebp: 0A30EDF4: 0A30EE20 77D0612F 00EC019E 00000507 06E1FEF8
00000
000
* SegCs: 0000001B:
* EFlags: 00010246: 006D0065 00320033 0057005C 00650062 003B006D
003A0
043
* Esp: 0A30EDD8: 06B86F6F 06E1FF14 0A30EE6C 06E1FEF8 06B8510A
06E1F
EF8
* SegSs: 00000023:
*
****************************************
************************************
*
**
*
----
-
--
* Short Stack Dump
* 06B824C5 Module(MSMAPI32+000024C5) (DDCS_Enter+00000004)
* 77D0612F Module(USER32+0000612F)
* 77D069A5 Module(USER32+000069A5) (GetMessageW+000001EE)
* 77D06689 Module(USER32+00006689) (TranslateMessage+00000520)
* 77D0FAE4 Module(USER32+0000FAE4) (DispatchMessageA+0000000B)
* 065D2BB3 Module(impprov+00002BB3)
* 065DE84A Module(impprov+0000E84A) (DllRegisterServer+0000AA3A)
* 065E145C Module(impprov+0001145C) (DllRegisterServer+0000D64C)
* 065DE1DC Module(impprov+0000E1DC) (DllRegisterServer+0000A3CC)
* 0047E663 Module(sqlservr+0007E663)
(CBcpImport::CreateInprocProvider+000002
EF
)
* 0047DB5A Module(sqlservr+0007DB5A) (CBcpImport::Init+0000029D)
* 0047D854 Module(sqlservr+0007D854) (BcpImportMain+0000014D)
* 0047D714 Module(sqlservr+0007D714) (CStmtBulkIns::XretExecute+0000005F)
* 0041B442 Module(sqlservr+0001B442)
(CMsqlExecContext::ExecuteStmts+000003B9
)
* 0041AA88 Module(sqlservr+0001AA88) (CMsqlExecContext::Execute+000001B6)
* 0041B9B6 Module(sqlservr+0001B9B6) (CSQLSource::Execute+00000357)
* 0048A63B Module(sqlservr+0008A63B) (language_exec+000003E1)
* 0042708C Module(sqlservr+0002708C) (process_commands+0000010E)
* 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
* 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
* 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
* 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
----
--
* Location : 06B824C5 Module(MSMAPI32+000024C5) (DDCS_Enter+00000004)
* Return Addr: 77D0612F Module(USER32+0000612F)
Frame : 0A30EDF4
Parameters:
[1] 00EC019E: 00000240 00000000 00000000 00000000 00000000 00000000
[2] 00000507:
[3] 06E1FEF8:
[4] 00000000:
160 bytes of stack data from 0A30ED54 to 0A30EDF4
0A30ED54: 00000000 00000000 00000000 00000000 [............]
:::: 7 Duplicate lines detected
0A30EDD4: 00000000 06B86F6F 06E1FF14 0A30EE6C [...oo.....l.0.]
0A30EDE4: 06E1FEF8 06B8510A 06E1FEF8 06B850F4 [....Q......P..]
----
--
* Location : 77D0612F Module(USER32+0000612F)
* Return Addr: 77D069A5 Module(USER32+000069A5) (GetMessageW+000001EE)
Frame : 0A30EE20
Parameters:
[1] 06B850F4:
[2] 00EC019E: 00000240 00000000 00000000 00000000 00000000 00000000
[3] 00000507:
[4] 06E1FEF8:
44 bytes of stack data from 0A30EDF4 to 0A30EE20
0A30EDF4: 0A30EE20 77D0612F 00EC019E 00000507 [ .0./a.w......]
0A30EE04: 06E1FEF8 00000000 06B850F4 DCBAABCD [.......P.....]
0A30EE14: 00000000 0A30EE6C 06B850F4 [...l.0..P..]
.
PSS @.0x76D8D258
--
pspid = 61 m_dwLoginFlags = 0x1083e0 plsid = 1717878624
pbackground = 0
pbSid
--
01010000 00000005 12000000 .........
sSecFlags = 0x1e
pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0
ptickcount = 1352914718 pcputickcount = 4059555816433772
ploginstamp = 10002 ptimestamp = 2005-12-24 03:00:33.530
plangid = 0 pdateformat = 1 pdatefirst = 7
Language = us_english UserName = NT AUTHORITY\SYSTEM
poptions = 0x28000020 poptions2 = 0x13f438 pline = 1
pcurstepno = 1 prowcount = 0 pstatlist = 0
pcurcmd = 240 pseqstat = 0 ptextsize = 2147483647
pretstat = 0 CNestLevel = 0 @.@.procid = 0
Xact_Begin_Time = 2005-12-24 03:00:33.543
pslastbatchstarttime = 2005-12-24 03:00:33.543 pmemusage = 5
hLicense = 0 tpFlags = 0x1 isolation_level = 0
fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0
pfetchstat = 0 pcrsrows = 0
pslastbatchtime = 2005-12-24 03:00:33.543 pNtUser = SYSTEM
pNtDomain = NT AUTHORITY pubexecdb = 0 fInReplicatedProcExec =
0
pMsqlXact = 0x76D8DE60 presSemCount = [0]170977488
presSemCount = [0]170977488 pcputot = 0
pcputotstart = 0 pcpucmdstart = 0 pbufread = 0
pbufreadstart = 0 plogbufread = 178 plogbufreadstart = 162
pbufwrite = 0 pbufwritestart = 0 pLockTimeout =
4294967295
pUtilResultSet = 0x00000000
pec
--
ec_pss->pspid = 61 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100001 ec_atomic = 0x0 pcurdb = 9
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 2408031
ec_cmderrs = 0 ec_timeslice = 84 ec_dbtable = 0x19EB0030
ec_reswait = 0x0 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x2
ec_umsContext->m_workercntxt->m_id (threadId) = 0x9fc
ExecutionContext Summary @.0x76D8D580
--
ec_pss->pspid = 61 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100001 ec_atomic = 0x0 pcurdb = 9
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 2408031
ec_cmderrs = 0 ec_timeslice = 84 ec_dbtable = 0x19EB0030
ec_reswait = 0x0 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x2
ec_umsContext->m_workercntxt->m_id (threadId) = 0x9fc
psrvproc->eclClient = 4 psrvproc->status = 8
psrvproc->state = SRV_RUNNING psrvproc->bNewPacket = 0
psrvproc->pmo = 0x76D84030
psrvproc->ums_context = 0x038A1950 psrvproc->threadId =
0x9fc
client threadId = 0xdc8
...Hi
This http://support.microsoft.com/defaul...kb;en-us;839458 was
fixed after SP3, although you don't give any information about identity
values and I would not expect it to perform inconsistently as you describe;
but you may want to see if loading SP4 removes this issue. Something else yo
u
may wish to try could be to run them all on the main thread.
John
"Erwin" wrote:

> Hi,
> while executing a bulk insert DTS task, we sometimes experience an
> 'EXCEPTION_ACCESS_VIOLATION' error. We load several text files
> into sql tables parrallel. The error occurs does not necessarily occur
> on the same bulk load task, every we test the dts package. Sometimes
> we don't even get the error at all.
> We're running on sql server 2000 sp3 , any input or advice on this matter
> would be highly appreciated.
> Please find below the stack dump file...
> Regards,
> Erwin
> ========================================
=============================
> BugCheck Dump
> ========================================
=============================
>
> This file is generated by Microsoft SQL Server 8.00.760
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
>
> Computer type is AT/AT COMPATIBLE.
> Bios Version is IBM - 1000
> IBM BIOS Ver 1.05
> Current time is 03:00:33 12/24/05.
> 4 Intel x86 level 15, 3000 Mhz processor(s).
> Windows NT 5.2 Build 3790 CSD .
>
> Memory
> MemoryLoad = 60%
> Total Physical = 3583 MB
> Available Physical = 1403 MB
> Total Page File = 5474 MB
> Available Page File = 3466 MB
> Total Virtual = 2047 MB
> Available Virtual = 236 MB
> *Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
> *
> ****************************************
**********************************
***
> **
> *
> * BEGIN STACK DUMP:
> * 12/24/05 03:00:33 spid 61
> *
> * Exception Address = 06B824C5 (DDCS_Enter + 00000004 Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 06E1FF2C
> * Input Buffer 468 bytes -
> * BULK INSERT S_Import_IncrementalStock_InsertUpdates FROM 'e:\MerRep_Dat
a
> * \Work\DwMerchandise_UKwms_Batch00481_Inc
rementalStock_InsertUpdates.txt
'
> * WITH (CODEPAGE = 'RAW' , FIELDTERMINATOR = '??' , ROWTERMINATOR = '
' ,
> * MAXERRORS = 10 )
> *
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> * ntdll 77F40000 77FF9FFF 000ba000
> * kernel32 77E40000 77F33FFF 000f4000
> * ADVAPI32 77DA0000 77E2FFFF 00090000
> * RPCRT4 77C50000 77CF4FFF 000a5000
> * USER32 77D00000 77D8EFFF 0008f000
> * GDI32 77C00000 77C43FFF 00044000
> * OPENDS60 41060000 41065FFF 00006000
> * MSVCRT 77BA0000 77BF3FFF 00054000
> * UMS 41070000 4107CFFF 0000d000
> * SQLSORT 42AE0000 42B6FFFF 00090000
> * MSVCIRT 60020000 6002FFFF 00010000
> * sqlevn70 41080000 41086FFF 00007000
> * NETAPI32 02C60000 02CB2FFF 00053000
> * ole32 030D0000 031F4FFF 00125000
> * XOLEHLP 03200000 03205FFF 00006000
> * MSDTCPRX 03210000 03281FFF 00072000
> * OLEAUT32 03290000 0330CFFF 0007d000
> * MSVCP60 03310000 03370FFF 00061000
> * MTXCLU 03380000 03397FFF 00018000
> * VERSION 033A0000 033A7FFF 00008000
> * WSOCK32 033B0000 033B8FFF 00009000
> * WS2_32 033C0000 033D7FFF 00018000
> * WS2HELP 033E0000 033E7FFF 00008000
> * CLUSAPI 03430000 03440FFF 00011000
> * RESUTILS 03450000 03461FFF 00012000
> * USERENV 03470000 03529FFF 000ba000
> * MFC42u 03530000 03623FFF 000f4000
> * secur32 036B0000 036C2FFF 00013000
> * mswsock 036E0000 03722FFF 00043000
> * DNSAPI 03730000 03756FFF 00027000
> * winrnr 037A0000 037A6FFF 00007000
> * WLDAP32 037B0000 037DEFFF 0002f000
> * rasadhlp 03800000 03804FFF 00005000
> * SSNETLIB 00C70000 00C84FFF 00015000
> * SSNMPN70 00C90000 00C95FFF 00006000
> * security 042C0000 042C3FFF 00004000
> * wshtcpip 04310000 04317FFF 00008000
> * SSmsLPCn 04320000 04326FFF 00007000
> * ntdsapi 04340000 04355FFF 00016000
> * kerberos 04770000 047C5FFF 00056000
> * cryptdll 047D0000 047DBFFF 0000c000
> * MSASN1 047E0000 047F1FFF 00012000
> * SQLFTQRY 04630000 04661FFF 00032000
> * CLBCatQ 04B20000 04B9DFFF 0007e000
> * COMRes 04BA0000 04C65FFF 000c6000
> * sqloledb 04C90000 04D15FFF 00086000
> * MSDART 04D20000 04D48FFF 00029000
> * COMCTL32 04D50000 04DDFFFF 00090000
> * comdlg32 04DE0000 04E26FFF 00047000
> * SHLWAPI 04E30000 04E78FFF 00049000
> * SHELL32 04E80000 0565DFFF 007de000
> * MSDATL3 05660000 05677FFF 00018000
> * comctl32 05690000 05775FFF 000e6000
> * oledb32 05B20000 05B9CFFF 0007d000
> * OLEDB32R 05BA0000 05BB0FFF 00011000
> * msv1_0 05C50000 05C73FFF 00024000
> * xpsqlbot 05C80000 05C85FFF 00006000
> * xpstar 05CB0000 05CF6FFF 00047000
> * SQLRESLD 05D00000 05D06FFF 00007000
> * SQLSVC 05D10000 05D26FFF 00017000
> * ODBC32 05D30000 05D69FFF 0003a000
> * odbcbcp 05D70000 05D75FFF 00006000
> * W95SCM 05D90000 05D9BFFF 0000c000
> * SQLUNIRL 05DA0000 05DCCFFF 0002d000
> * WINSPOOL 05DD0000 05DF5FFF 00026000
> * SHFOLDER 05E00000 05E08FFF 00009000
> * odbcint 05FF0000 06006FFF 00017000
> * NDDEAPI 06110000 06116FFF 00007000
> * SQLSVC 06120000 06125FFF 00006000
> * xpstar 06130000 06138FFF 00009000
> * rsaenh 0FFD0000 0FFFCFFF 0002d000
> * PSAPI 05D80000 05D8AFFF 0000b000
> * impprov 065D0000 065E8FFF 00019000
> * sqlmap70 06580000 065A9FFF 0002a000
> * MAPI32 065B0000 065CEFFF 0001f000
> * MSMAPI32 06B80000 06CCDFFF 0014e000
> * mso 07510000 080C1FFF 00bb2000
> * iphlpapi 06F20000 06F36FFF 00017000
> * MPRAPI 08270000 08286FFF 00017000
> * ACTIVEDS 08290000 082C1FFF 00032000
> * adsldpc 082D0000 082F5FFF 00026000
> * credui 08300000 0832CFFF 0002d000
> * ATL 08330000 08347FFF 00018000
> * rtutils 08350000 0835AFFF 0000b000
> * SAMLIB 08360000 0836FFFF 00010000
> * SETUPAPI 08370000 0846FFFF 00100000
> * netman 084D0000 08506FFF 00037000
> * RASAPI32 08510000 0854AFFF 0003b000
> * rasman 08550000 08560FFF 00011000
> * TAPI32 08570000 0859DFFF 0002e000
> * WINMM 085A0000 085CBFFF 0002c000
> * CRYPT32 085D0000 08667FFF 00098000
> * WZCSvc 08670000 086B6FFF 00047000
> * WMI 08470000 08474FFF 00005000
> * DHCPCSVC 086C0000 086DBFFF 0001c000
> * WTSAPI32 086E0000 086E7FFF 00008000
> * WINSTA 086F0000 086FFFFF 00010000
> * ESENT 08700000 08807FFF 00108000
> * WZCSAPI 08810000 08818FFF 00009000
> * xplog70 06560000 06571FFF 00012000
> * xplog70 09160000 09163FFF 00004000
> * sqlvdi 09170000 0917BFFF 0000c000
>

DTS bulk load fails

Hi,
while executing a bulk insert DTS task, we sometimes experience an
'EXCEPTION_ACCESS_VIOLATION' error. We load several text files
into sql tables parrallel. The error occurs does not necessarily occur
on the same bulk load task, every we test the dts package. Sometimes
we don't even get the error at all.
We're running on sql server 2000 sp3 , any input or advice on this matter
would be highly appreciated.
Please find below the stack dump file...
Regards,
Erwin
================================================== ===================
BugCheck Dump
================================================== ===================
This file is generated by Microsoft SQL Server 8.00.760
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE.
Bios Version is IBM - 1000
IBM BIOS Ver 1.05
Current time is 03:00:33 12/24/05.
4 Intel x86 level 15, 3000 Mhz processor(s).
Windows NT 5.2 Build 3790 CSD .
Memory
MemoryLoad = 60%
Total Physical = 3583 MB
Available Physical = 1403 MB
Total Page File = 5474 MB
Available Page File = 3466 MB
Total Virtual = 2047 MB
Available Virtual = 236 MB
*Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 12/24/05 03:00:33 spid 61
*
* Exception Address = 06B824C5 (DDCS_Enter + 00000004 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 06E1FF2C
* Input Buffer 468 bytes -
* BULK INSERT S_Import_IncrementalStock_InsertUpdates FROM 'e:\MerRep_Data
* \Work\DwMerchandise_UKwms_Batch00481_IncrementalSt ock_InsertUpdates.txt'
* WITH (CODEPAGE = 'RAW' , FIELDTERMINATOR = '??' , ROWTERMINATOR = ' ' ,
* MAXERRORS = 10 )
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B2CFFF 0072d000
* ntdll 77F40000 77FF9FFF 000ba000
* kernel32 77E40000 77F33FFF 000f4000
* ADVAPI32 77DA0000 77E2FFFF 00090000
* RPCRT4 77C50000 77CF4FFF 000a5000
* USER32 77D00000 77D8EFFF 0008f000
* GDI32 77C00000 77C43FFF 00044000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF3FFF 00054000
* UMS 41070000 4107CFFF 0000d000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 41080000 41086FFF 00007000
* NETAPI32 02C60000 02CB2FFF 00053000
* ole32 030D0000 031F4FFF 00125000
* XOLEHLP 03200000 03205FFF 00006000
* MSDTCPRX 03210000 03281FFF 00072000
* OLEAUT32 03290000 0330CFFF 0007d000
* MSVCP60 03310000 03370FFF 00061000
* MTXCLU 03380000 03397FFF 00018000
* VERSION 033A0000 033A7FFF 00008000
* WSOCK32 033B0000 033B8FFF 00009000
* WS2_32 033C0000 033D7FFF 00018000
* WS2HELP 033E0000 033E7FFF 00008000
* CLUSAPI 03430000 03440FFF 00011000
* RESUTILS 03450000 03461FFF 00012000
* USERENV 03470000 03529FFF 000ba000
* MFC42u 03530000 03623FFF 000f4000
* secur32 036B0000 036C2FFF 00013000
* mswsock 036E0000 03722FFF 00043000
* DNSAPI 03730000 03756FFF 00027000
* winrnr 037A0000 037A6FFF 00007000
* WLDAP32 037B0000 037DEFFF 0002f000
* rasadhlp 03800000 03804FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* SSNMPN70 00C90000 00C95FFF 00006000
* security 042C0000 042C3FFF 00004000
* wshtcpip 04310000 04317FFF 00008000
* SSmsLPCn 04320000 04326FFF 00007000
* ntdsapi 04340000 04355FFF 00016000
* kerberos 04770000 047C5FFF 00056000
* cryptdll 047D0000 047DBFFF 0000c000
* MSASN1 047E0000 047F1FFF 00012000
* SQLFTQRY 04630000 04661FFF 00032000
* CLBCatQ 04B20000 04B9DFFF 0007e000
* COMRes 04BA0000 04C65FFF 000c6000
* sqloledb 04C90000 04D15FFF 00086000
* MSDART 04D20000 04D48FFF 00029000
* COMCTL32 04D50000 04DDFFFF 00090000
* comdlg32 04DE0000 04E26FFF 00047000
* SHLWAPI 04E30000 04E78FFF 00049000
* SHELL32 04E80000 0565DFFF 007de000
* MSDATL3 05660000 05677FFF 00018000
* comctl32 05690000 05775FFF 000e6000
* oledb32 05B20000 05B9CFFF 0007d000
* OLEDB32R 05BA0000 05BB0FFF 00011000
* msv1_0 05C50000 05C73FFF 00024000
* xpsqlbot 05C80000 05C85FFF 00006000
* xpstar 05CB0000 05CF6FFF 00047000
* SQLRESLD 05D00000 05D06FFF 00007000
* SQLSVC 05D10000 05D26FFF 00017000
* ODBC32 05D30000 05D69FFF 0003a000
* odbcbcp 05D70000 05D75FFF 00006000
* W95SCM 05D90000 05D9BFFF 0000c000
* SQLUNIRL 05DA0000 05DCCFFF 0002d000
* WINSPOOL 05DD0000 05DF5FFF 00026000
* SHFOLDER 05E00000 05E08FFF 00009000
* odbcint 05FF0000 06006FFF 00017000
* NDDEAPI 06110000 06116FFF 00007000
* SQLSVC 06120000 06125FFF 00006000
* xpstar 06130000 06138FFF 00009000
* rsaenh 0FFD0000 0FFFCFFF 0002d000
* PSAPI 05D80000 05D8AFFF 0000b000
* impprov 065D0000 065E8FFF 00019000
* sqlmap70 06580000 065A9FFF 0002a000
* MAPI32 065B0000 065CEFFF 0001f000
* MSMAPI32 06B80000 06CCDFFF 0014e000
* mso 07510000 080C1FFF 00bb2000
* iphlpapi 06F20000 06F36FFF 00017000
* MPRAPI 08270000 08286FFF 00017000
* ACTIVEDS 08290000 082C1FFF 00032000
* adsldpc 082D0000 082F5FFF 00026000
* credui 08300000 0832CFFF 0002d000
* ATL 08330000 08347FFF 00018000
* rtutils 08350000 0835AFFF 0000b000
* SAMLIB 08360000 0836FFFF 00010000
* SETUPAPI 08370000 0846FFFF 00100000
* netman 084D0000 08506FFF 00037000
* RASAPI32 08510000 0854AFFF 0003b000
* rasman 08550000 08560FFF 00011000
* TAPI32 08570000 0859DFFF 0002e000
* WINMM 085A0000 085CBFFF 0002c000
* CRYPT32 085D0000 08667FFF 00098000
* WZCSvc 08670000 086B6FFF 00047000
* WMI 08470000 08474FFF 00005000
* DHCPCSVC 086C0000 086DBFFF 0001c000
* WTSAPI32 086E0000 086E7FFF 00008000
* WINSTA 086F0000 086FFFFF 00010000
* ESENT 08700000 08807FFF 00108000
* WZCSAPI 08810000 08818FFF 00009000
* xplog70 06560000 06571FFF 00012000
* xplog70 09160000 09163FFF 00004000
* sqlvdi 09170000 0917BFFF 0000c000
* msi 0AA10000 0AC23FFF 00214000
* MAPIR 06F50000 0700FFFF 000c0000
* contab32 030B0000 030CEFFF 0001f000
* EMSABP32 06DA0000 06DD7FFF 00038000
* EMSMDB32 08F60000 09019FFF 000ba000
* uxtheme 09120000 09152FFF 00033000
* dbghelp 09720000 097D4FFF 000b5000
*
* Edi: 06E1FF14:
* Esi: 06E1FEF8:
* Eax: C0000000:
* Ebx: 00000000:
* Ecx: 06E1FF14:
* Edx: 00000020:
* Eip: 06B824C5:
* Ebp: 0A30EDF4: 0A30EE20 77D0612F 00EC019E 00000507 06E1FEF8
00000
000
* SegCs: 0000001B:
* EFlags: 00010246: 006D0065 00320033 0057005C 00650062 003B006D
003A0
043
* Esp: 0A30EDD8: 06B86F6F 06E1FF14 0A30EE6C 06E1FEF8 06B8510A
06E1F
EF8
* SegSs: 00000023:
*
************************************************** ***************************
**
*
* Short Stack Dump
* 06B824C5 Module(MSMAPI32+000024C5) (DDCS_Enter+00000004)
* 77D0612F Module(USER32+0000612F)
* 77D069A5 Module(USER32+000069A5) (GetMessageW+000001EE)
* 77D06689 Module(USER32+00006689) (TranslateMessage+00000520)
* 77D0FAE4 Module(USER32+0000FAE4) (DispatchMessageA+0000000B)
* 065D2BB3 Module(impprov+00002BB3)
* 065DE84A Module(impprov+0000E84A) (DllRegisterServer+0000AA3A)
* 065E145C Module(impprov+0001145C) (DllRegisterServer+0000D64C)
* 065DE1DC Module(impprov+0000E1DC) (DllRegisterServer+0000A3CC)
* 0047E663 Module(sqlservr+0007E663)
(CBcpImport::CreateInprocProvider+000002EF
)
* 0047DB5A Module(sqlservr+0007DB5A) (CBcpImport::Init+0000029D)
* 0047D854 Module(sqlservr+0007D854) (BcpImportMain+0000014D)
* 0047D714 Module(sqlservr+0007D714) (CStmtBulkIns::XretExecute+0000005F)
* 0041B442 Module(sqlservr+0001B442)
(CMsqlExecContext::ExecuteStmts+000003B9)
* 0041AA88 Module(sqlservr+0001AA88) (CMsqlExecContext::Execute+000001B6)
* 0041B9B6 Module(sqlservr+0001B9B6) (CSQLSource::Execute+00000357)
* 0048A63B Module(sqlservr+0008A63B) (language_exec+000003E1)
* 0042708C Module(sqlservr+0002708C) (process_commands+0000010E)
* 41075002 Module(UMS+00005002) (ProcessWorkRequests+00000272)
* 41074698 Module(UMS+00004698) (ThreadStartRoutine+00000098)
* 77BC90A2 Module(MSVCRT+000290A2) (endthread+000000A6)
* 77E4A990 Module(kernel32+0000A990) (FlsSetValue+00000779)
* Location : 06B824C5 Module(MSMAPI32+000024C5) (DDCS_Enter+00000004)
* Return Addr: 77D0612F Module(USER32+0000612F)
Frame : 0A30EDF4
Parameters:
[1] 00EC019E: 00000240 00000000 00000000 00000000 00000000 00000000
[2] 00000507:
[3] 06E1FEF8:
[4] 00000000:
160 bytes of stack data from 0A30ED54 to 0A30EDF4
0A30ED54: 00000000 00000000 00000000 00000000 [............]
:::: 7 Duplicate lines detected
0A30EDD4: 00000000 06B86F6F 06E1FF14 0A30EE6C [...oo.....l.0.]
0A30EDE4: 06E1FEF8 06B8510A 06E1FEF8 06B850F4 [....Q......P..]
* Location : 77D0612F Module(USER32+0000612F)
* Return Addr: 77D069A5 Module(USER32+000069A5) (GetMessageW+000001EE)
Frame : 0A30EE20
Parameters:
[1] 06B850F4:
[2] 00EC019E: 00000240 00000000 00000000 00000000 00000000 00000000
[3] 00000507:
[4] 06E1FEF8:
44 bytes of stack data from 0A30EDF4 to 0A30EE20
0A30EDF4: 0A30EE20 77D0612F 00EC019E 00000507 [ .0./a.w......]
0A30EE04: 06E1FEF8 00000000 06B850F4 DCBAABCD [.......P.....]
0A30EE14: 00000000 0A30EE6C 06B850F4 [...l.0..P..]
..
PSS @.0x76D8D258
pspid = 61 m_dwLoginFlags = 0x1083e0 plsid = 1717878624
pbackground = 0
pbSid
01010000 00000005 12000000 .........
sSecFlags = 0x1e
pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0
ptickcount = 1352914718 pcputickcount = 4059555816433772
ploginstamp = 10002 ptimestamp = 2005-12-24 03:00:33.530
plangid = 0 pdateformat = 1 pdatefirst = 7
Language = us_english UserName = NT AUTHORITY\SYSTEM
poptions = 0x28000020 poptions2 = 0x13f438 pline = 1
pcurstepno = 1 prowcount = 0 pstatlist = 0
pcurcmd = 240 pseqstat = 0 ptextsize = 2147483647
pretstat = 0 CNestLevel = 0 @.@.procid = 0
Xact_Begin_Time = 2005-12-24 03:00:33.543
pslastbatchstarttime = 2005-12-24 03:00:33.543 pmemusage = 5
hLicense = 0 tpFlags = 0x1 isolation_level = 0
fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0
pfetchstat = 0 pcrsrows = 0
pslastbatchtime = 2005-12-24 03:00:33.543 pNtUser = SYSTEM
pNtDomain = NT AUTHORITY pubexecdb = 0 fInReplicatedProcExec =
0
pMsqlXact = 0x76D8DE60 presSemCount = [0]170977488
presSemCount = [0]170977488 pcputot = 0
pcputotstart = 0 pcpucmdstart = 0 pbufread = 0
pbufreadstart = 0 plogbufread = 178 plogbufreadstart = 162
pbufwrite = 0 pbufwritestart = 0 pLockTimeout =
4294967295
pUtilResultSet = 0x00000000
pec
ec_pss->pspid = 61 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100001 ec_atomic = 0x0 pcurdb = 9
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 2408031
ec_cmderrs = 0 ec_timeslice = 84 ec_dbtable = 0x19EB0030
ec_reswait = 0x0 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x2
ec_umsContext->m_workercntxt->m_id (threadId) = 0x9fc
ExecutionContext Summary @.0x76D8D580
ec_pss->pspid = 61 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x100001 ec_atomic = 0x0 pcurdb = 9
ec_lasterror = 0 ec_preverror = 0 ec_cpucur = 2408031
ec_cmderrs = 0 ec_timeslice = 84 ec_dbtable = 0x19EB0030
ec_reswait = 0x0 ec_dbindex = -1 ec_waittype = 0x0000
Waittime = 0 ec_umsContext->m_pSched->m_id (SchedulerId) = 0x2
ec_umsContext->m_workercntxt->m_id (threadId) = 0x9fc
psrvproc->eclClient = 4 psrvproc->status = 8
psrvproc->state = SRV_RUNNING psrvproc->bNewPacket = 0
psrvproc->pmo = 0x76D84030
psrvproc->ums_context = 0x038A1950 psrvproc->threadId =
0x9fc
client threadId = 0xdc8
...
Hi
This http://support.microsoft.com/default...b;en-us;839458 was
fixed after SP3, although you don't give any information about identity
values and I would not expect it to perform inconsistently as you describe;
but you may want to see if loading SP4 removes this issue. Something else you
may wish to try could be to run them all on the main thread.
John
"Erwin" wrote:

> Hi,
> while executing a bulk insert DTS task, we sometimes experience an
> 'EXCEPTION_ACCESS_VIOLATION' error. We load several text files
> into sql tables parrallel. The error occurs does not necessarily occur
> on the same bulk load task, every we test the dts package. Sometimes
> we don't even get the error at all.
> We're running on sql server 2000 sp3 , any input or advice on this matter
> would be highly appreciated.
> Please find below the stack dump file...
> Regards,
> Erwin
> ================================================== ===================
> BugCheck Dump
> ================================================== ===================
>
> This file is generated by Microsoft SQL Server 8.00.760
> upon detection of fatal unexpected error. Please return this file,
> the query or program that produced the bugcheck, the database and
> the error log, and any other pertinent information with a Service Request.
>
> Computer type is AT/AT COMPATIBLE.
> Bios Version is IBM - 1000
> IBM BIOS Ver 1.05
> Current time is 03:00:33 12/24/05.
> 4 Intel x86 level 15, 3000 Mhz processor(s).
> Windows NT 5.2 Build 3790 CSD .
>
> Memory
> MemoryLoad = 60%
> Total Physical = 3583 MB
> Available Physical = 1403 MB
> Total Page File = 5474 MB
> Available Page File = 3466 MB
> Total Virtual = 2047 MB
> Available Virtual = 236 MB
> *Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
> *
> ************************************************** ***************************
> **
> *
> * BEGIN STACK DUMP:
> * 12/24/05 03:00:33 spid 61
> *
> * Exception Address = 06B824C5 (DDCS_Enter + 00000004 Line 0+00000000)
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred reading address 06E1FF2C
> * Input Buffer 468 bytes -
> * BULK INSERT S_Import_IncrementalStock_InsertUpdates FROM 'e:\MerRep_Data
> * \Work\DwMerchandise_UKwms_Batch00481_IncrementalSt ock_InsertUpdates.txt'
> * WITH (CODEPAGE = 'RAW' , FIELDTERMINATOR = '??' , ROWTERMINATOR = ' ' ,
> * MAXERRORS = 10 )
> *
> *
> * MODULE BASE END SIZE
> * sqlservr 00400000 00B2CFFF 0072d000
> * ntdll 77F40000 77FF9FFF 000ba000
> * kernel32 77E40000 77F33FFF 000f4000
> * ADVAPI32 77DA0000 77E2FFFF 00090000
> * RPCRT4 77C50000 77CF4FFF 000a5000
> * USER32 77D00000 77D8EFFF 0008f000
> * GDI32 77C00000 77C43FFF 00044000
> * OPENDS60 41060000 41065FFF 00006000
> * MSVCRT 77BA0000 77BF3FFF 00054000
> * UMS 41070000 4107CFFF 0000d000
> * SQLSORT 42AE0000 42B6FFFF 00090000
> * MSVCIRT 60020000 6002FFFF 00010000
> * sqlevn70 41080000 41086FFF 00007000
> * NETAPI32 02C60000 02CB2FFF 00053000
> * ole32 030D0000 031F4FFF 00125000
> * XOLEHLP 03200000 03205FFF 00006000
> * MSDTCPRX 03210000 03281FFF 00072000
> * OLEAUT32 03290000 0330CFFF 0007d000
> * MSVCP60 03310000 03370FFF 00061000
> * MTXCLU 03380000 03397FFF 00018000
> * VERSION 033A0000 033A7FFF 00008000
> * WSOCK32 033B0000 033B8FFF 00009000
> * WS2_32 033C0000 033D7FFF 00018000
> * WS2HELP 033E0000 033E7FFF 00008000
> * CLUSAPI 03430000 03440FFF 00011000
> * RESUTILS 03450000 03461FFF 00012000
> * USERENV 03470000 03529FFF 000ba000
> * MFC42u 03530000 03623FFF 000f4000
> * secur32 036B0000 036C2FFF 00013000
> * mswsock 036E0000 03722FFF 00043000
> * DNSAPI 03730000 03756FFF 00027000
> * winrnr 037A0000 037A6FFF 00007000
> * WLDAP32 037B0000 037DEFFF 0002f000
> * rasadhlp 03800000 03804FFF 00005000
> * SSNETLIB 00C70000 00C84FFF 00015000
> * SSNMPN70 00C90000 00C95FFF 00006000
> * security 042C0000 042C3FFF 00004000
> * wshtcpip 04310000 04317FFF 00008000
> * SSmsLPCn 04320000 04326FFF 00007000
> * ntdsapi 04340000 04355FFF 00016000
> * kerberos 04770000 047C5FFF 00056000
> * cryptdll 047D0000 047DBFFF 0000c000
> * MSASN1 047E0000 047F1FFF 00012000
> * SQLFTQRY 04630000 04661FFF 00032000
> * CLBCatQ 04B20000 04B9DFFF 0007e000
> * COMRes 04BA0000 04C65FFF 000c6000
> * sqloledb 04C90000 04D15FFF 00086000
> * MSDART 04D20000 04D48FFF 00029000
> * COMCTL32 04D50000 04DDFFFF 00090000
> * comdlg32 04DE0000 04E26FFF 00047000
> * SHLWAPI 04E30000 04E78FFF 00049000
> * SHELL32 04E80000 0565DFFF 007de000
> * MSDATL3 05660000 05677FFF 00018000
> * comctl32 05690000 05775FFF 000e6000
> * oledb32 05B20000 05B9CFFF 0007d000
> * OLEDB32R 05BA0000 05BB0FFF 00011000
> * msv1_0 05C50000 05C73FFF 00024000
> * xpsqlbot 05C80000 05C85FFF 00006000
> * xpstar 05CB0000 05CF6FFF 00047000
> * SQLRESLD 05D00000 05D06FFF 00007000
> * SQLSVC 05D10000 05D26FFF 00017000
> * ODBC32 05D30000 05D69FFF 0003a000
> * odbcbcp 05D70000 05D75FFF 00006000
> * W95SCM 05D90000 05D9BFFF 0000c000
> * SQLUNIRL 05DA0000 05DCCFFF 0002d000
> * WINSPOOL 05DD0000 05DF5FFF 00026000
> * SHFOLDER 05E00000 05E08FFF 00009000
> * odbcint 05FF0000 06006FFF 00017000
> * NDDEAPI 06110000 06116FFF 00007000
> * SQLSVC 06120000 06125FFF 00006000
> * xpstar 06130000 06138FFF 00009000
> * rsaenh 0FFD0000 0FFFCFFF 0002d000
> * PSAPI 05D80000 05D8AFFF 0000b000
> * impprov 065D0000 065E8FFF 00019000
> * sqlmap70 06580000 065A9FFF 0002a000
> * MAPI32 065B0000 065CEFFF 0001f000
> * MSMAPI32 06B80000 06CCDFFF 0014e000
> * mso 07510000 080C1FFF 00bb2000
> * iphlpapi 06F20000 06F36FFF 00017000
> * MPRAPI 08270000 08286FFF 00017000
> * ACTIVEDS 08290000 082C1FFF 00032000
> * adsldpc 082D0000 082F5FFF 00026000
> * credui 08300000 0832CFFF 0002d000
> * ATL 08330000 08347FFF 00018000
> * rtutils 08350000 0835AFFF 0000b000
> * SAMLIB 08360000 0836FFFF 00010000
> * SETUPAPI 08370000 0846FFFF 00100000
> * netman 084D0000 08506FFF 00037000
> * RASAPI32 08510000 0854AFFF 0003b000
> * rasman 08550000 08560FFF 00011000
> * TAPI32 08570000 0859DFFF 0002e000
> * WINMM 085A0000 085CBFFF 0002c000
> * CRYPT32 085D0000 08667FFF 00098000
> * WZCSvc 08670000 086B6FFF 00047000
> * WMI 08470000 08474FFF 00005000
> * DHCPCSVC 086C0000 086DBFFF 0001c000
> * WTSAPI32 086E0000 086E7FFF 00008000
> * WINSTA 086F0000 086FFFFF 00010000
> * ESENT 08700000 08807FFF 00108000
> * WZCSAPI 08810000 08818FFF 00009000
> * xplog70 06560000 06571FFF 00012000
> * xplog70 09160000 09163FFF 00004000
> * sqlvdi 09170000 0917BFFF 0000c000
>

DTS Bulk Insert task Date problem?

Really Don't understand this and have run out of hair to pull out.

DTS package whihc has a Bulk Insert Task. The task is populated with its source filename dynamically. It has a format file which is stored locally to the box its running on.

The output file is from a unix box pipe delimited. Great dandy. All works a treat as long as I treat everything as either an INT or CHAR. But I have a couple of columns that are DATES in the format 13/02/2004.

I want these inserted into smalldatetime columns. Will settle for datetime columns though.

I get an error on every row Bulk Insert data conversion error (type mismatch) for Row X Column 11 (PostingDate) which happens to be the first date column.

Tried loading it as CHAR, SQLDATETIME and SQLDATETIM4 in the format file all to no avail. TRied it with columns in the dest table as DATETIME and SMALLDATETIME

Anyone have any ideas?What happens when you use the import wizard and make all of the column varchar and create a stage table?

Then do Audits against the data

LIKE SELECT * FROM Stage WHERE ISDATE(Col1)=0

...|||Originally posted by Brett Kaiser
What happens when you use the import wizard and make all of the column varchar and create a stage table?

Then do Audits against the data

LIKE SELECT * FROM Stage WHERE ISDATE(Col1)=0

...

Done that. All the data in the column concerned is valid. They are all dates formatted as DD/MM/YYYY.|||You sure it's considered a valid date?

DECLARE @.X varchar(10)
SELECT @.x = '23/12/2003'
SELECT ISDATE(@.x)
SELECT CONVERT(datetime,@.x,120)

Doesn't seem to be...is it a collation thing?|||Originally posted by Brett Kaiser
You sure it's considered a valid date?

DECLARE @.X varchar(10)
SELECT @.x = '23/12/2003'
SELECT ISDATE(@.x)
SELECT CONVERT(datetime,@.x,120)

Doesn't seem to be...is it a collation thing?

Don't think so. Always use US LATIN 1 General code page 437 case Sensitive for everyuthing.

Will check.

Must be something to do with the DD/MM rather than MM/DD as the column inserts fine as a CHAR.

Stupid #@.*&%$! thing|||I think (ok, well SOMETIMES...not always) that's right...it's not recognizing it as a date...

How many records are we talking about?|||Originally posted by steve@.powell.ne
Really Don't understand this and have run out of hair to pull out.

Anyone have any ideas? I resemble that hair remark!

You are using BCP's -R option, right?

-PatP|||Originally posted by Brett Kaiser
I think (ok, well SOMETIMES...not always) that's right...it's not recognizing it as a date...

How many records are we talking about?

Its booting every single one. Even when I format the data so the dates are something innofensive like 01/01/2004.

Was using the Bulk Insert task in a DTS not using the BCP command line at all.

Have abandoned Bulk Insert in favour of a scripted transform instead.

Works a treat. The files arrive daily with between 2000 and 30000 lines. So the scripted transform is fine. Even at 30,000 it's not exactly taking very long.

Ho hum.|||Did you try bcp with a fromat file?

Or how about BULK INSERT..

I rarely use DTS...|||Originally posted by Brett Kaiser
Did you try bcp with a fromat file?

Or how about BULK INSERT..

I rarely use DTS...

Didn't try the BCP or Bulk INsert route. Not much interested in command line solutions though I geuss I could chron them.

We integrate into legacy systems that spit PSV files out after each nights proocessing. We import the results each morning before we arrive to provide us with off line access to the data.

I usually use scripted routines as it lets me check for nulls and such and do some clever tricks to reduce the number of replicated columns that come out.

Just thought I'd play with something that I don't normally use.

I've got XML to bring in next.sql

dts alternative?

I need to give the ability to only somewhat technically minded people to
insert/ update into 1 SQL table. They have data that comes in frequently
from Excel files that needs to be imported and we want to make it so they
don't need IT. There's no way to install just DTS without installing
Enterprise Manager so thats out. Is there another tool?
--
SQL2K SP3
TIA, ChrisRHi
I would write yourself a sceduled dts job that takes the file from a
specific location, processes it, and then archives it to a different
location. Therefore the only thing the user will have to do is move the file
into the location where it will be processed. See
http://www.sqldts.com/default.aspx?246 If you are wanting to cut out the
middle man totally you could use SQLMail and xp_readmail to recieve the
file.
John
"ChrisR" <bla@.noemail.com> wrote in message
news:en0uVgVyEHA.3024@.TK2MSFTNGP14.phx.gbl...
>I need to give the ability to only somewhat technically minded people to
> insert/ update into 1 SQL table. They have data that comes in frequently
> from Excel files that needs to be imported and we want to make it so they
> don't need IT. There's no way to install just DTS without installing
> Enterprise Manager so thats out. Is there another tool?
> --
> SQL2K SP3
> TIA, ChrisR
>|||The problem though is that the file format of the file isn't constant. The
people that send them to us rename columns, dont have the data the way they
really want it, etc. Thats why I need a GUI that isn't DTS.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#MRC2dWyEHA.3024@.TK2MSFTNGP14.phx.gbl...
> Hi
> I would write yourself a sceduled dts job that takes the file from a
> specific location, processes it, and then archives it to a different
> location. Therefore the only thing the user will have to do is move the
file
> into the location where it will be processed. See
> http://www.sqldts.com/default.aspx?246 If you are wanting to cut out the
> middle man totally you could use SQLMail and xp_readmail to recieve the
> file.
> John
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:en0uVgVyEHA.3024@.TK2MSFTNGP14.phx.gbl...
> >I need to give the ability to only somewhat technically minded people to
> > insert/ update into 1 SQL table. They have data that comes in frequently
> > from Excel files that needs to be imported and we want to make it so
they
> > don't need IT. There's no way to install just DTS without installing
> > Enterprise Manager so thats out. Is there another tool?
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >
>|||Chris,
There are numerous third-party data transformation products available...
Here's one that I have not used before but that looks pretty interesting
based on the website:
http://www.idera.com/Products/DTx/Default.aspx
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ChrisR" <bla@.noemail.com> wrote in message
news:uzpwfbZyEHA.1524@.TK2MSFTNGP09.phx.gbl...
> The problem though is that the file format of the file isn't constant. The
> people that send them to us rename columns, dont have the data the way
they
> really want it, etc. Thats why I need a GUI that isn't DTS.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:#MRC2dWyEHA.3024@.TK2MSFTNGP14.phx.gbl...
> > Hi
> >
> > I would write yourself a sceduled dts job that takes the file from a
> > specific location, processes it, and then archives it to a different
> > location. Therefore the only thing the user will have to do is move the
> file
> > into the location where it will be processed. See
> > http://www.sqldts.com/default.aspx?246 If you are wanting to cut out the
> > middle man totally you could use SQLMail and xp_readmail to recieve the
> > file.
> >
> > John
> >
> >
> > "ChrisR" <bla@.noemail.com> wrote in message
> > news:en0uVgVyEHA.3024@.TK2MSFTNGP14.phx.gbl...
> > >I need to give the ability to only somewhat technically minded people
to
> > > insert/ update into 1 SQL table. They have data that comes in
frequently
> > > from Excel files that needs to be imported and we want to make it so
> they
> > > don't need IT. There's no way to install just DTS without installing
> > > Enterprise Manager so thats out. Is there another tool?
> > >
> > > --
> > > SQL2K SP3
> > >
> > > TIA, ChrisR
> > >
> > >
> >
> >
>|||How about a simple .net app?
If you use a datagrid then the users should be able to copy the contents of
the spreadsheet into the grid, click a button to import.
I have a system where the users put the data into a spreadsheet then a macro
does the copy to a table and am planning to replace it with the above. Should
only take a few hours to write so sometime middle of next year I guess :).
"ChrisR" wrote:
> I need to give the ability to only somewhat technically minded people to
> insert/ update into 1 SQL table. They have data that comes in frequently
> from Excel files that needs to be imported and we want to make it so they
> don't need IT. There's no way to install just DTS without installing
> Enterprise Manager so thats out. Is there another tool?
> --
> SQL2K SP3
> TIA, ChrisR
>
>

DTS Across Network Errors

I have a database and web server (physically different boxes) and I am
trying to get DTS to read in a file from the web server and insert the
data into the database. I get permissions errors when I try to execute
the job, so I am thinking if I could some how get the file over from
the web server on to the database server, then it would run fine.
This is a job, and it will happen every day.
Any suggestions would be very appreciated.
Check your security on that folder
If anything write a batch file that brings the file to you server
Schedule to run before DTS
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> I have a database and web server (physically different boxes) and I am
> trying to get DTS to read in a file from the web server and insert the
> data into the database. I get permissions errors when I try to execute
> the job, so I am thinking if I could some how get the file over from
> the web server on to the database server, then it would run fine.
> This is a job, and it will happen every day.
> Any suggestions would be very appreciated.
>
|||Mr. Sanbaka,
Here is the exact error I am getting...maybe this can help. I gave
Everyone full rights to the folder and it still doesnt work right.
Should I try creating a batch file to copy it to the DB server?
Error string: The Microsoft Jet database engine cannot open the file
'\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
another user, or you need permission to view its data. Error
source: Microsoft JET Database Engine Help file:... Process Exit
Code 7. The step failed.
|||Michael
Make sure the databsae is not open by a user at the time DTS is trying to
access it.
If you were to use a batch file yes copy it to the server
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> Mr. Sanbaka,
> Here is the exact error I am getting...maybe this can help. I gave
> Everyone full rights to the folder and it still doesnt work right.
> Should I try creating a batch file to copy it to the DB server?
> Error string: The Microsoft Jet database engine cannot open the file
> '\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
> another user, or you need permission to view its data. Error
> source: Microsoft JET Database Engine Help file:... Process Exit
> Code 7. The step failed.
>
|||Thats the problem I am having, I get permissions errors when I try to
use a batch file to move it over

DTS Across Network Errors

I have a database and web server (physically different boxes) and I am
trying to get DTS to read in a file from the web server and insert the
data into the database. I get permissions errors when I try to execute
the job, so I am thinking if I could some how get the file over from
the web server on to the database server, then it would run fine.
This is a job, and it will happen every day.
Any suggestions would be very appreciated.Check your security on that folder
If anything write a batch file that brings the file to you server
Schedule to run before DTS
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:
> I have a database and web server (physically different boxes) and I am
> trying to get DTS to read in a file from the web server and insert the
> data into the database. I get permissions errors when I try to execute
> the job, so I am thinking if I could some how get the file over from
> the web server on to the database server, then it would run fine.
> This is a job, and it will happen every day.
> Any suggestions would be very appreciated.
>|||Mr. Sanbaka,
Here is the exact error I am getting...maybe this can help. I gave
Everyone full rights to the folder and it still doesnt work right.
Should I try creating a batch file to copy it to the DB server?
Error string: The Microsoft Jet database engine cannot open the file
'\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
another user, or you need permission to view its data. Error
source: Microsoft JET Database Engine Help file:... Process Exit
Code 7. The step failed.|||Michael
Make sure the databsae is not open by a user at the time DTS is trying to
access it.
If you were to use a batch file yes copy it to the server
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:
> Mr. Sanbaka,
> Here is the exact error I am getting...maybe this can help. I gave
> Everyone full rights to the folder and it still doesnt work right.
> Should I try creating a batch file to copy it to the DB server?
> Error string: The Microsoft Jet database engine cannot open the file
> '\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
> another user, or you need permission to view its data. Error
> source: Microsoft JET Database Engine Help file:... Process Exit
> Code 7. The step failed.
>|||Thats the problem I am having, I get permissions errors when I try to
use a batch file to move it over

DTS Across Network Errors

I have a database and web server (physically different boxes) and I am
trying to get DTS to read in a file from the web server and insert the
data into the database. I get permissions errors when I try to execute
the job, so I am thinking if I could some how get the file over from
the web server on to the database server, then it would run fine.
This is a job, and it will happen every day.
Any suggestions would be very appreciated.Check your security on that folder
If anything write a batch file that brings the file to you server
Schedule to run before DTS
--
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> I have a database and web server (physically different boxes) and I am
> trying to get DTS to read in a file from the web server and insert the
> data into the database. I get permissions errors when I try to execute
> the job, so I am thinking if I could some how get the file over from
> the web server on to the database server, then it would run fine.
> This is a job, and it will happen every day.
> Any suggestions would be very appreciated.
>|||Mr. Sanbaka,
Here is the exact error I am getting...maybe this can help. I gave
Everyone full rights to the folder and it still doesnt work right.
Should I try creating a batch file to copy it to the DB server?
Error string: The Microsoft Jet database engine cannot open the file
'\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
another user, or you need permission to view its data. Error
source: Microsoft JET Database Engine Help file:... Process Exit
Code 7. The step failed.|||Michael
Make sure the databsae is not open by a user at the time DTS is trying to
access it.
If you were to use a batch file yes copy it to the server
Paul Zanbaka
Sharepoint Adminstrator and DBA
www.mycodekb.com
"michael.esposito@.gmail.com" wrote:

> Mr. Sanbaka,
> Here is the exact error I am getting...maybe this can help. I gave
> Everyone full rights to the folder and it still doesnt work right.
> Should I try creating a batch file to copy it to the DB server?
> Error string: The Microsoft Jet database engine cannot open the file
> '\\abc123\d$\mdb\WVL2000Mike.mdb'. It is already opened exclusively by
> another user, or you need permission to view its data. Error
> source: Microsoft JET Database Engine Help file:... Process Exit
> Code 7. The step failed.
>|||Thats the problem I am having, I get permissions errors when I try to
use a batch file to move it over

DTS "Can Not Insert Null Value"

Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.

DTS "Can Not Insert Null Value"

Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.