别说4000行,上万的我也用BCP导出来过,强烈要求贴出BCP语句,关错误分析什么?
解决方案 »
- 合并表
- 请教一条存储过程语句,这样的查询能否通过?
- 在 SQL Profiler , 執行一個追蹤 , 很容易出現錯誤提示: 無法讀取追蹤資料.請問這是什么原因?
- 使用命令行运行isqlw执行SQL文件,但却没有得到结果。为什么。。。
- 数据日志满了怎么啊?
- 请问这样的sql怎么写?
- 哇,好久不来都换心面容了,散分给大家!!出一道题目大家一块帮忙看看,
- 安装Sql Server 2000 的问题
- 如何自定义一个无符号的64位整数???(SQL SERVER 7.0)有难度!!!
- 这个store procedure有难度,该怎么写呢?
- 存储过程运行出错.服务器: 消息 446,无法解决 equal to 操作的排序规则冲突。
- 求一段SQL(类似于文件夹间的关系,就是在文件夹中在建立子文件夹等等)
EXEC master..xp_cmdshell 'bcp "select * from dbname..tablename " queryout c:\DT.txt -c -Sservername -Usa -Ppassword'
导入:
EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'
exec(@sql)
',' 列分隔
',\n' 行分隔屡试不爽
exec master..xp_cmdshell 'bcp dbname.dbo.tb20030527 out e:\tablename.txt -c -t ; -r \n -S zhang-shuqing -U zndn -P kTc'
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Starting copy...
1000 行成功地批复制主文件。总记接收: 1000
1000 行成功地批复制主文件。总记接收: 2000
1000 行成功地批复制主文件。总记接收: 3000
1000 行成功地批复制主文件。总记接收: 4000
NULL
4335 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 4376 Avg 1 (990.63 rows per sec.)(10 row(s) affected)
导入:exec master..xp_cmdshell 'bcp dbname.dbo.tb20030527 in e:\tablename.txt -c -t ; -r \n -S zhang-shuqing -U zndn -P kTc'
output
NULL
Starting copy...
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]数值超出范围
NULL
16 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 511 Avg 31 (31.31 rows per sec.)(26 row(s) affected)
只用16行能还原,怎么回事?
select @table_name='tb20030527'
select @file_name='e:\tablename.txt'
select @sql=' BULK INSERT ['+@table_name+'] FROM '''+@file_name+''' WITH (FIELDTERMINATOR = '';'',ROWTERMINATOR =''\n'') '
exec(@sql)----------------------------------------
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 10 generated fatal exception c0000093 EXCEPTION_FLT_UNDERFLOW. SQL Server is terminating this process.
The statement has been terminated.
已找到问题的症结所在,文件里竟然有类似1.0761972E-42,1.7851972E-41的垃圾数据,把E-41,E-42都替换成0后,数据成功导入。
奇怪这种数据从何而来,如果存在于原数据表,为什么没有超出数值范围? 如果是BCP OUT造成的,这种情况又不可能? 最奇怪的是SQL自身的DTS工具导入能成功? 它怎么不提示‘数值超出范围’?
首先感谢各位的解答,弄清以上疑惑后,中午结帖!
bcp pubs..authors out c:\authors.txt -Sservername -Usa -Ppassword创建.fmt文件 最后会提示是否生成Format File,然后指定它的路径和文件名如下所示:
8.0
9
1 SQLCHAR 0 11 "" 1 au_id Chinese_PRC_CI_AS
2 SQLCHAR 0 40 "" 2 au_lname Chinese_PRC_CI_AS
3 SQLCHAR 0 20 "" 3 au_fname Chinese_PRC_CI_AS
4 SQLCHAR 0 12 "" 4 phone Chinese_PRC_CI_AS
5 SQLCHAR 0 40 "" 5 address Chinese_PRC_CI_AS
6 SQLCHAR 0 20 "" 6 city Chinese_PRC_CI_AS
7 SQLCHAR 0 2 "" 7 state Chinese_PRC_CI_AS
8 SQLCHAR 0 5 "" 8 zip Chinese_PRC_CI_AS
9 SQLBIT 0 1 "" 9 contract ""
导入时用:
bcp pubs..authors2 in c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword使用你刚刚生成的Format file去帮助导入数据。