利用openrowset进行两个SQL2000之间的数据库的查询,在SQL的HELP里是这样写的:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
没有问题可是在实际应用中会在Select中增加条件,也就是说是要这样写的:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors where au_lname='' ORDER BY au_lname, au_fname') AS a
当以上述格式执行时就会报语法错误,错误点在WHERE条件上,现在就这个条件怎么写才可以通过?
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
没有问题可是在实际应用中会在Select中增加条件,也就是说是要这样写的:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors where au_lname='' ORDER BY au_lname, au_fname') AS a
当以上述格式执行时就会报语法错误,错误点在WHERE条件上,现在就这个条件怎么写才可以通过?
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors where pubs.dbo.authors.au_lname='' ORDER BY pubs.dbo.authors.au_lname, pubs.dbo.authors.au_fname') AS a
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
where au_lname=''
SELECT register_diag_tb.office_diag,
count(*)
FROM register_diag_tb
WHERE ( register_diag_tb.register_datetime between '2006-12-01' and '2006-12-10' ) AND
( register_diag_tb.return_register <> '1' ) AND
( register_diag_tb.return_sick_code = '' )
GROUP BY register_diag_tb.office_diag 这样的,所以条件没办法写在外面,
除非是把表里的所有数据都取过来再去做GROUP BY ,但原表的数据量非常大,所以不太现实.
以下示例使用 SQL Native Client OLE DB 访问接口 (SQLNCLI) 访问 HumanResources.Department 表,该表位于远程服务器 Seattle1 上的 AdventureWorks 数据库中。使用 SELECT 语句定义返回的行集。访问接口字符串包含 Server 和 Trusted_Connection 关键字。这些关键字由 SQL Native Client OLE DB 访问接口识别。 复制代码
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name') AS a;看下这个
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors where au_lname='''' ORDER BY au_lname, au_fname') AS a--改成'''',引号内部的引号'用''表示
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors where au_lname='''' ORDER BY au_lname, au_fname') AS a
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors where au_lname='''' ORDER BY au_lname, au_fname') AS a
开始我也想到了这个了.但怎么加也没加好..刚才我试了一个简的语句,是可以的,我再试试我的这条语句.