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