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 ''%''%'' )
这语句有问题,请问 怎么改?
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 ' ) )这是错误提示。
inner join OUUserInfo u on u.Deptid=DirReps.Deptid
WHERE ( (u.Name like '%'%' ) )相当于上面这句是出错的,你看一下
,'
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 ''%''%'' )
,'
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 ''%''%'' )
(u.Name like ''%''%'' )
这样就出错。。
replace(col,'aaa','')