SQL Server 2000 错误的解决方法（ Error:The identifier that starts with 'xxxxx' is too long. Maximum

今天在更新数据库字段信息的时候，在SQL Server 2000 中试图使用如下sql语句：
UPDATE tables1 SET sql= "select id  from table2 with(nolock)    
inner join table3 request with(nolock) on table3 .id = table2.column1Id   
inner join table4 bundle with(nolock) on table4 .id = table2..column2Id        
where table1.series in('220-602','220-603','220-604','JK0-602','JK0-603',         
                    'JK0-604','N10-003','JK0-012','PK0-002','SY0-101','JK0-010','HT0-201',         
                    '221-602','221-603','221-604','JK1-602','JK1-603','JK1-604','N11-103',         
                    'JK1-012','PK1-002','SY1-101','JK1-010','HT1-201','PD0-001','PD1-001',         
                    'SK0-002','RF0-001')"

报错：The identifier that starts with 'xxxxx' is too long. Maximum length is 128. 此sql字段为varchar(8000), 显然长度并没有超出8000. Google了一下，发现并不是字段长度的问题，原来是引号上面出了问题。
解决方法：用两个单引号将要更新的值括起来，值中有引号的地方全部替换为两个双引号，修改后的sql为：
UPDATE tables1 SET sql= 'select id  from table2 with(nolock)    
inner join table3 request with(nolock) on table3 .id = table2.column1Id   
inner join table4 bundle with(nolock) on table4 .id = table2..column2Id        
where table1.series in(""220-602"",""220-603"",""220-604"",""JK0-602"",""JK0-603"",         
                    ""JK0-604"",""N10-003"",""JK0-012"",""PK0-002"",""SY0-101"",""JK0-010"",""HT0-201"",         
                    ""221-602"",""221-603"",""221-604"",""JK1-602"",""JK1-603"",""JK1-604"",""N11-103"",         
                    ""JK1-012"",""PK1-002"",""SY1-101"",""JK1-010"",""HT1-201"",""PD0-001"",""PD1-001"",         
                    ""SK0-002"",""RF0-001"")'

执行后解决问题。

seal 2008-04-22 23:36 发表评论