select count(*)  from  (  select * from openrowset('SQLNCLI','Server=192.168.1.200\test;UID=sa;PWD=ddd;DataBase=ii'
                                            ,'
                                            WITH DirReps(Deptid,DeptName,DeptFullName) AS 
                                            (
                                            select Deptid,DeptName,DeptFullName from   dbo.OUDeptInfo where ( DeptID = ''HB.SJ'' ) 
                                            UNION ALL
                                            SELECT d.Deptid,d.DeptName,d.DeptFullName
                                            FROM OUDeptInfo d inner join  DirReps on d.ParentDeptID=DirReps.Deptid         
                                            )
                                            SELECT u.*,DirReps.DeptName,DirReps.DeptFullName from DirReps
                                            inner join OUUserInfo u on u.Deptid=DirReps.Deptid
                                            WHERE ( (u.Name like ''%''%'' ) )
                                            ') as t  )  t  (u.Name like ''%''%'' ) 
这语句有问题,请问 怎么改?

解决方案 »

  1.   


    OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.".
    Msg 8180, Level 16, State 1, Line 1
    Statement(s) could not be prepared.
    Msg 102, Level 15, State 1, Line 11
    Incorrect syntax near ' ) )
                                                '.
    Msg 105, Level 15, State 1, Line 11
    Unclosed quotation  after the character string ' ) )这是错误提示。
      

  2.   

    SELECT u.*,DirReps.DeptName,DirReps.DeptFullName from DirReps
                                                inner join OUUserInfo u on u.Deptid=DirReps.Deptid
                                                WHERE ( (u.Name like '%'%' ) )相当于上面这句是出错的,你看一下
      

  3.   

    select count(*)  from  (  select * from openrowset('SQLNCLI','Server=192.168.1.200\test;UID=sa;PWD=ddd;DataBase=ii'
                                                ,'
                                                WITH DirReps(Deptid,DeptName,DeptFullName) AS 
                                                (
                                                    select Deptid,DeptName,DeptFullName from   dbo.OUDeptInfo where ( DeptID = ''HB.SJ'' ) 
                                                    UNION ALL
                                                    SELECT d.Deptid,d.DeptName,d.DeptFullName
                                                    FROM OUDeptInfo d inner join  DirReps on d.ParentDeptID=DirReps.Deptid         
                                                )
                                                SELECT u.*,DirReps.DeptName,DirReps.DeptFullName from DirReps
                                                inner join OUUserInfo u on u.Deptid=DirReps.Deptid
                                                WHERE ( (u.Name like ''%这里加一个比较的字符串%'' ) )
                                                ') as t  )  t 
    (u.Name like ''%''%'' )  
      

  4.   

    select count(*)  from  (  select * from openrowset('SQLNCLI','Server=192.168.1.200\test;UID=sa;PWD=ddd;DataBase=ii'
                                                ,'
                                                WITH DirReps(Deptid,DeptName,DeptFullName) AS 
                                                (
                                                    select Deptid,DeptName,DeptFullName from   dbo.OUDeptInfo where ( DeptID = ''HB.SJ'' ) 
                                                    UNION ALL
                                                    SELECT d.Deptid,d.DeptName,d.DeptFullName
                                                    FROM OUDeptInfo d inner join  DirReps on d.ParentDeptID=DirReps.Deptid         
                                                )
                                                SELECT u.*,DirReps.DeptName,DirReps.DeptFullName from DirReps
                                                inner join OUUserInfo u on u.Deptid=DirReps.Deptid
                                 --注意这里      WHERE ( (u.Name like ''%这里加一个比较的字符串%'' ) )
                                                ') as t  )  t 
    (u.Name like ''%''%'' )  
      

  5.   

    谢谢,也就是说 哪里 不能放空吗?
    (u.Name like ''%''%'' )
    这样就出错。。
      

  6.   

    替换为空为什么要用like???直接用replace就可以了
    replace(col,'aaa','')