MSSQL 导入导出的时候提示 无法检索源数据和目标数据的列信息

刘兄弟 成都大学 SDD  2015-12-09 05:13:20
MSSQL 导入导出的时候提示 无法检索源数据和目标数据的列信息
,或者源列的数据类型未正确地映射到目标提供程序上可以用的数据类型。

《查询> D:\TXT.txt
列'Tradingtime“:在数据类型映射文件中找不到源数据类型:-1"


另外 某一部分JOB 任务 提示
Executed as user: NT AUTHORITY\SYSTEM. The operating system returned error 23(failed to retrieve text for this error. Reason: 15105) to SQL Server during a read at offset 0x000000da53c000 in file 'F:\DataFile\CWDATA201512.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. [SQLSTATE HY000] (Error 823). The step failed.
...全文
538 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
刘兄弟 2015-12-11
引用 8 楼 yupeigu 的回复:
(CWDATA, repair_allow_data_loss). 这个是说,对于找到的错误用了最小的恢复级别,这个只是消息,不是报错信息,报错的信息都会有error的字样
经过修复之后checkdb之后,解决了DB数据库某一些表日期索引的问题,并且能正常访问了。 总结下: 问题发生: JOB任务在执行的时候只有有关日期的检索都会反馈下面的错误: 1:Executed as user: NT AUTHORITY\SYSTEM. The operating system returned error 23(failed to retrieve text for this error. Reason: 15105) to SQL Server during a read at offset 0x000000da53c000 in file 'F:\DataFile\CWDATA201512.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. [SQLSTATE HY000] (Error 823). The step failed. 2:正在复制到 D:\ttl_Order.txt (错误) 消息 错误 0xc0202009: 数据流任务 1: SSIS 错误代码 DTS_E_OLEDBERROR。出现 OLE DB 错误。错误代码: 0x80004005。 已获得 OLE DB 记录。源:“Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 说明:“The operating system returned error 23(failed to retrieve text for this error. Reason: 15105) to SQL Server during a read at offset 0x000000da53c000 in file 'F:\DataFile\CWDATA201512.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.”。 (SQL Server 导入和导出向导) 错误 0xc0047038: 数据流任务 1: SSIS 错误代码 DTS_E_PRIMEOUTPUTFAILED。组件“源 - 查询”(1) 的 PrimeOutput 方法返回了错误代码 0xC0202009。管道引擎调用 PrimeOutput() 时该组件返回了一个失败代码。虽然该失败代码只与该组件有关,但这个错误是致命的,管道已停止执行。 解决过程: 1.立即关闭了所有访问的账户,并进入SINGLE USER 模式. 2.根据错误1中的.ndf 数据分组文件,确定问题应该属于和分组文件有关的表。同时发现服务器硬盘坏了(黄灯)一块。 3.备份更新服务器上的文件和数据,并更换了坏掉的硬盘。 3.使用checktable 检查了相关的6张表,并修复了错误。 4.使用checkdb 检查其余的表格(CHECKDB会检查所有的表,我这里耗时一共7.5小时),并修复了错误。 5.补倒入最近3天的销售库存调整等文件。 此外斑竹对我的指导和帮助,谢谢。
回复
刘兄弟 2015-12-10
Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. Msg 8966, Level 16, State 2, Line 1 Unable to read and latch page (71:447134) with latch type SH. 23(failed to retrieve text for this error. Reason: 15105) failed. CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object. DBCC results for 'sys.sysrscols'. There are 208473 rows in 2137 pages for object "sys.sysrscols". DBCC results for 'sys.sysrowsets'. There are 14590 rows in 177 pages for object "sys.sysrowsets". DBCC results for 'sys.sysallocunits'. There are 14602 rows in 231 pages for object "sys.sysallocunits". DBCC results for 'sys.sysfiles1'. There are 71 rows in 8 pages for object "sys.sysfiles1". DBCC results for 'sys.syspriorities'. There are 0 rows in 0 pages for object "sys.syspriorities". DBCC results for 'sys.sysfgfrag'. There are 140 rows in 1 pages for object "sys.sysfgfrag". DBCC results for 'sys.sysphfg'. There are 70 rows in 1 pages for object "sys.sysphfg". DBCC results for 'sys.sysprufiles'. There are 71 rows in 4 pages for object "sys.sysprufiles". DBCC results for 'sys.sysftinds'. There are 0 rows in 0 pages for object "sys.sysftinds". DBCC results for 'TTL_ORDER'. Repair: The Clustered index successfully rebuilt for the object "dbo.TTL_ORDER" in database "CWDATA". Repair: The page (71:447134) has been deallocated from object ID 757577737, index ID 1, partition ID 72057597679370240, alloc unit ID 72057599277400064 (type In-row data). Msg 8945, Level 16, State 1, Line 1 Table error: Object ID 757577737, index ID 1 will be rebuilt. The error has been repaired. Msg 2533, Level 16, State 1, Line 1 Table error: page (71:447134) allocated to object ID 757577737, index ID 1, partition ID 72057597679370240, alloc unit ID 72057599277400064 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. The error has been repaired. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 757577737, index ID 1, partition ID 72057597679370240, alloc unit ID 72057599277400064 (type In-row data). Page (71:447134) was not seen in the scan although its parent (71:450822) and previous (71:447133) refer to it. Check any previous errors. The error has been repaired. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 757577737, index ID 1, partition ID 72057597679370240, alloc unit ID 72057599277400064 (type In-row data). Page (71:447135) is missing a reference from previous page (71:447134). Possible chain linkage problem. The error has been repaired. There are 162360926 rows in 962367 pages for object "TTL_ORDER". CHECKDB found 0 allocation errors and 3 consistency errors in table 'TTL_ORDER' (object ID 757577737). CHECKDB fixed 0 allocation errors and 3 consistency errors in table 'TTL_ORDER' (object ID 757577737). DBCC results for 'web.LossSituationByStore'. There are 49996 rows in 1132 pages for object "web.LossSituationByStore". DBCC results for 'imp.TTL_EJMemberCardUsed'. There are 0 rows in 0 pages for object "imp.TTL_EJMemberCardUsed". DBCC results for 'imp.ISP'. There are 0 rows in 0 pages for object "imp.ISP". DBCC results for 'web.pkgAvailable'. There are 62 rows in 1 pages for object "web.pkgAvailable". DBCC results for 'imp.MinusMarinTop100'. There are 1482632 rows in 29710 pages for object "imp.MinusMarinTop100". DBCC results for 'imp.NewIncrease'. There are 62910 rows in 749 pages for object "imp.NewIncrease". DBCC results for 'imp.ITA'. is the minimum repair level for the errors found by DBCC CHECKDB (CWDATA, repair_allow_data_loss). DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC results for 'CWDATA'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. DBCC results for 'sys.sysrscols'. There are 208473 rows in 2403 pages for object "sys.sysrscols".
引用 6 楼 yupeigu 的回复:
上面提示:Complete a full database consistency check (DBCC CHECKDB) 说明可能是数据库有损坏,你现在查出来是硬盘有问题,建议你运行 dbcc checkdb 命令,完整的检查一下数据库,除了那个ndf文件外,是否还有其他的文件也损坏了。
请帮我看下红字部分的问题,我不明白我要怎么处理,能指点下吗?
回复
上面提示:Complete a full database consistency check (DBCC CHECKDB) 说明可能是数据库有损坏,你现在查出来是硬盘有问题,建议你运行 dbcc checkdb 命令,完整的检查一下数据库,除了那个ndf文件外,是否还有其他的文件也损坏了。
回复
刘兄弟 2015-12-10
引用 4 楼 xiaoxiangqing 的回复:
先少导一些数据试下,看行不行,如果可以,再多导一些,先一步步找原因
原因找到了,有一一块硬盘坏掉了。 导致部分索引失效。 目前单击模式下,在备份导入的文本的文档,之后换一块新的硬盘。 话说这个硬盘时间大概是5年多了,差不多应该都要换了吧。
回复
xiaoxiangqing 2015-12-10
先少导一些数据试下,看行不行,如果可以,再多导一些,先一步步找原因
回复
1、There are 14602 rows in 231 pages for object "sys.sysallocunits". 这个你可以不用管,这个表示sql server内部的分配单元表。 2、Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 757577737, index ID 1, partition ID 72057597679370240, alloc unit ID 72057599277400064 (type In-row data). Page (71:447134) was not seen in the scan although its parent (71:450822) and previous (71:447133) refer to it. Check any previous errors. The error has been repaired. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 757577737, index ID 1, partition ID 72057597679370240, alloc unit ID 72057599277400064 (type In-row data). Page (71:447135) is missing a reference from previous page (71:447134). Possible chain linkage problem. The error has been repaired. 表错误,但是这个错误sql server已经帮你修复了。 3、DBCC results for 'imp.ITA'. is the minimum repair level for the errors found by DBCC CHECKDB (CWDATA, repair_allow_data_loss). DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC results for 'CWDATA'. 这个是说,对于找到的错误用了最小的恢复级别,这个只是消息,不是报错信息,报错的信息都会有error的字样
回复
刘兄弟 2015-12-09
引用 1 楼 roy_88 的回复:
数据库列数少了 导入时选择的分隔符是否正确 --格式不规范时,用以下语句生成临时表,处理好数据再把临时表导入表
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=E:\;','select * from roy.txt')
可用bcp导入语法如下
EXEC master..xp_cmdshell 'bcp "test..make" in  E:\roy.txt -c -S"SHAREPOINT" -U"sa" -P"xxx"'
也可用格式化导入 参照联机 BULK INSERT
之前一直都是正确的啊,其他服务器下载文本文档导入进去的。 我今天发现这个错误,在使用CHECKDB 和CHECKTABLE
回复
刘兄弟 2015-12-09
但是时间太长了,先回家了,明天再弄。 我数据库使用文件组的, 提示NSF那个文件出问题了,我想是不是我删除了这个文件,重新生成一次会好起来。
回复
中国风 2015-12-09
数据库列数少了 导入时选择的分隔符是否正确 --格式不规范时,用以下语句生成临时表,处理好数据再把临时表导入表
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=E:\;','select * from roy.txt')
可用bcp导入语法如下
EXEC master..xp_cmdshell 'bcp "test..make" in  E:\roy.txt -c -S"SHAREPOINT" -U"sa" -P"xxx"'
也可用格式化导入 参照联机 BULK INSERT
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-12-09 05:13
社区公告
暂无公告