创建存储过程 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