存储过程实现SQL2005查询Access数据库

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