创建存储过程 RemoteJetQuery ： CREATE PROCEDURE [dbo] . [RemoteJetQuery] @TableOrQueryString int = 1 , @Provider_Name nvarchar ( 30 )= N 'Microsoft.Jet.OLEDB.4.0' , @DataSource nvarchar ( 40 )= N 'D:/DEMO/SQLDemo/Test.mdb' , @User_Id nvarc
创建存储过程 RemoteJetQuery ：
CREATE PROCEDURE [dbo] . [RemoteJetQuery]
@TableOrQueryString int = 1 ,
@Provider_Name nvarchar ( 30 )= N 'Microsoft.Jet.OLEDB.4.0' ,
@DataSource nvarchar ( 40 )= N 'D:/DEMO/SQLDemo/Test.mdb' ,
@User_Id nvarchar ( 20 )= N 'Admin' ,
@Password nvarchar ( 20 )= N '' ,
@Object nvarchar ( 500 )= N 'PE_Article'
AS
DECLARE @SQLString nvarchar ( 610 )
if @TableOrQueryString = 1
SET @SQLString = N 'SELECT * FROM OPENROWSET(' + '''' + @PrOvider_Name + '''' + ',' + '''' + @DataSource + '''' + ';' + '''' + @User_Id + '''' + ';' + '''' + @Password + '''' + ',' + @Object + ')' ;
if @TableOrQueryString = 2
SET @SQLString = N 'SELECT * FROM OPENROWSET(' + '''' + @PrOvider_Name + '''' + ',' + '''' + @DataSource + '''' + ';' + '''' + @User_Id + '''' + ';' + '''' + @Password + '''' + ',' + @Object + ')' ;
ExEC sp_executesql @SQLString ;
GO
执行存储过程 RemoteJetQuery ：
sp_configure 'show advanced options' , 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'Ad Hoc Distributed Queries' , 1 ;
GO
RECONFIGURE ;
GO
EXEC Demo . dbo . RemoteJetQuery ;
GO
EXEC Demo . dbo . RemoteJetQuery
1 ,
DEFAult ,
DEFAULT ,
DEFAULT ,
DEFAULT ,
N 'PE_Class' ;
GO
EXEC Demo . dbo . RemoteJetQuery
2 ,
DEFAult ,
DEFAULT ,
DEFAULT ,
DEFAULT ,
N 'SELECT ClassName FROM PE_Class ORDER BY ClassID DESC' ;
GO
sp_configure 'Ad Hoc Distributed Queries' , 0 ;
GO
RECONFIGURE ;
GO
sp_configure 'show advanced options' , 0 ;
GO
RECONFIGURE ;
GO
查看更多关于存储过程实现SQL2005查询Access数据库的详细内容...