查询提示超过了最大目录缓冲区 1023 字节（1024 字节输入）。
sql_text="select * from table......."---这个语句超长^_^ ,此语句可以查出正确结果
再把这个sql_text放进下面的语句中就报错:
EXEC master..xp_cmdshell 'bcp "select * from table......." queryout c:\miClinTemp.txt -c -Sgeonado -Usa -P'
----------------------------------------------------------------------------
查询提示超过了最大目录缓冲区 1023 字节（1024 字节输入）。
: No error
NULL
----------------------------------------------------------------------------如何解决^_^
想将上面的SQL语句做成一个试图,但是提示;查询分析器不支持CAS构造,郁闷:(
问题点数：100、回复次数：10Top
1 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-01-11 11:25:46 得分 0
EXEC master..xp_cmdshell N'bcp ...Top
2 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-01-11 11:27:07 得分 0
xp_cmdshell后边跟的命令字符串最大支持varchar(255) 或 nvarchar(4000)。Top
3 楼pbsql（风云）回复于 2005-01-11 11:27:31 得分 0
最大为1024 字节Top
4 楼flashasp（flashasp）回复于 2005-01-11 11:27:52 得分 0
EXEC master..xp_cmdshell N'bcp ... 错误依然
Top
5 楼flashasp（flashasp）回复于 2005-01-11 11:31:14 得分 0
select * from table......难道我要在这个语句上面下功夫缩短其长度吗^_^
我试着将每个字段名的长度缩短看看^_^
select * from table.......还没超过最大长度可以正常运新,那么下面的字符也不是很多^_^
EXEC master..xp_cmdshell 'bcp "" queryout c:\miClinTemp.txt -c -Sgeonado -Usa -P'
很可能就是多拉几个字符呢^_^
Top
6 楼flashasp（flashasp）回复于 2005-01-11 11:37:56 得分 0
^_^,果真可以,我只去掉拉"select * from table......"中的一些多余的空格就可以拉
大家帮我看看还能不能简化
select
case
when (jsdh is null and czrq is null) then cast(class as char(1))
else ltrim(rtrim(cast(jsdh as char(10))))
end as jsdh,
case
when (jsdh is null and czrq is null) then cast(code as char(20))
else ltrim(rtrim(cast(czrq as char(10))))
end as czrq,
case
when (jsdh is null and czrq is null) then cast(unit as char(20))
else cast(unit as char(1))
end as unti,cast(kong as char(1)) as kong,
case
when (jsdh is null and czrq is null) then cast(num as char(20))
else cast(num as char(1))
end as num,
case
when (jsdh is null and czrq is null) then cast(price as char(20))
else cast(price as char(20))
end as price,
case
when (jsdh is null and czrq is null) then cast(doctor as char(20))
else cast(doctor as char(1))
end as doctor,
case
when (jsdh is null and czrq is null) then cast(keshi as char(40))
else cast(keshi as char(1))
end as keshi,
case
when (jsdh is null and czrq is null) then cast(fsrq as char(10))
else cast(fsrq as char(1))
end as fsrq
from
his..miClinTempTop
7 楼pbsql（风云）回复于 2005-01-11 11:39:13 得分 0
EXEC master..xp_cmdshell 'bcp "SQL语句" queryout c:\miClinTemp.txt -c -Sgeonado -Usa -P'
把SQL语句写短些试一下Top
8 楼pbsql（风云）回复于 2005-01-11 11:47:32 得分 20
如果语句不能简短，那就去掉一些空格和不必要的字符使之少于1024字符
rtrim似乎无必要，别名前的as也无必要Top
9 楼libin_ftsafe（子陌红尘：TS for Banking Card）回复于 2005-01-11 11:52:48 得分 80
select
ltrim(rtrim(cast(jsdh as char(10)))) ,
ltrim(rtrim(cast(czrq as char(10)))),
cast(unit as char(1)),
cast(kong as char(1)),
cast(num as char(1)) ,
cast(price as char(20)),
cast(doctor as char(1)),
cast(keshi as char(1)),
cast(fsrq as char(1))
from
his..miClinTemp
where
jsdh is not null or czrq is not null
union all
select
cast(class as char(1)) as jsdh,
cast(code as char(20)) as czrq,
cast(unit as char(20)) as unti,
cast(kong as char(1)) as kong,
cast(num as char(20)) as num,
cast(price as char(20)) as price,
cast(doctor as char(20)) as doctor,
cast(keshi as char(40)) as keshi,
cast(fsrq as char(10)) as fsrq
from
his..miClinTemp
where
jsdh is null and czrq is nullTop
10 楼flashasp（flashasp）回复于 2005-01-11 12:17:20 得分 0
^_^,舒服Top
