发生错误,ODBC驱动程序不支持所需的属性

[wyumening]

奇怪的问题，写的sql语句在sql server中能正常运行，放到dreamweaver中写成asp的样式就会报错

sql代码：

use sh2
select *,(convert(varchar,(cast(
(convert(decimal,pg1)*3+convert(decimal,pg2)*2
+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2
-convert(decimal,pg6)*3)/
(convert(decimal,zs)*3)*100 as decimal(15,2))))+'%')
as myd1,(cast(
(convert(decimal,pg1)*3+convert(decimal,pg2)*2
+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2
-convert(decimal,pg6)*3)/
(convert(decimal,zs)*3)*100 as decimal(15,2))) as myd,
convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl from  (
select 
a.serviceid,
a.xm,
a.zu,
count(b.id) as zs,
(select name from zu where id=a.zu) as name , 
(select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc,  
(select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz, 
(select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6 


from service as a left join wenti as b on a.serviceid=b.service where b.id>0
group by a.serviceid,a.xm,a.zu ) as table1 order by zu, myd desc

放到dreamweaver中，报错ODBC驱动程序不支持所需的属性，代码如下:

strSql= "select *,(convert(varchar,(cast((convert(decimal,pg1)*3+convert(decimal,pg2)*2+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2-convert(decimal,pg6)*3)/(convert(decimal,zs)*3)*100 as decimal(15,2))))+'%') as myd1,(cast((convert(decimal,pg1)*3+convert(decimal,pg2)*2+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2-convert(decimal,pg6)*3)/(convert(decimal,zs)*3)*100 as decimal(15,2))) as myd, convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl from (select a.serviceid,a.xm,a.zu,count(b.id) as zs,(select name from zu where id=a.zu) as name , (select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc, (select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk,(select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5,(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6  from service as a left join wenti as b on a.serviceid=b.service where b.id>0 group by a.serviceid,a.xm,a.zu ) as table1 order by zu,myd desc"
rs.open strSql,conn,1,1

vbscript的代码有点乱，不好意思，无法换行，不然会报错未结束的字符串常量
所以只能这样写

错误指向这一行 rs.open strSql,conn,1,1

代码在sql server中能够返回记录，检查了一下asp页面中的连接，也是对的，发现把这一句

convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl

移除，就不会报错，但是这一句必须要用到，而且在sqlserver中也能正常的查询到记录，然后我改成了rs.open strSql,conn,1,3
报的错不同
错误类型：
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]')' 附近有语法错误。

这该怎么解决呢？

[wzhiyuan]

楼主把你的sql 写成存储过程，然后asp直接调用存储过程

create proc myproc
as
use sh2
select *,(convert(varchar,(cast(
(convert(decimal,pg1)*3+convert(decimal,pg2)*2
+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2
-convert(decimal,pg6)*3)/
(convert(decimal,zs)*3)*100 as decimal(15,2))))+'%')
as myd1,(cast(
(convert(decimal,pg1)*3+convert(decimal,pg2)*2
+convert(decimal,pg3)*1-convert(decimal,pg4)*1-convert(decimal,pg5)*2
-convert(decimal,pg6)*3)/
(convert(decimal,zs)*3)*100 as decimal(15,2))) as myd,
convert(varchar,(convert(decimal(10,2),round(ywc*1.00/zs,2))*100))+'%') as wcl from  (
select 
a.serviceid,
a.xm,
a.zu,
count(b.id) as zs,
(select name from zu where id=a.zu) as name , 
(select count(wenti.id) from wenti where (typeid=4 ) and service=a.serviceid) as ywc,  
(select count(wenti.id) from wenti where (typeid<>4 ) and (typeid<>7) and service=a.serviceid) as clz, 
(select count(wenti.id) from wenti where (typeid=7 ) and service=a.serviceid) as dfk,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=1) as pg1,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=2) as pg2,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=3) as pg3,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=4)as pg4,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=5) as pg5,
(select count(wenti.id) from wenti where typeid=4 and service=a.serviceid and pg=6) as pg6 


from service as a left join wenti as b on a.serviceid=b.service where b.id>0
group by a.serviceid,a.xm,a.zu ) as table1 order by zu, myd desc

然后在asp里直接调用存储过程

 
set rs=conn.execute("myproc")