在Access/SQL Server中以编程方式导出视图数据 [英] Export view data programmatically in Access/SQL Server

问题描述

我们有一个连接到SQL Server 2000数据库的Access应用程序前端.我们希望能够以编程方式将某些视图的结果导出为我们可以使用的任何格式(理想情况下为Excel，但是用CSV/制表符分隔是可以的).到现在为止，我们只是单击F11，打开视图，然后单击File-> Save As，但是我们开始获得包含超过16,000个结果的结果，这些结果无法导出.

We have an Access application front-end connected to a SQL Server 2000 database. We would like to be able to programmatically export the results of some views to whatever format we can (ideally Excel, but CSV / tab delimited is fine). Up until now we've just hit F11, opened up the view, and hit File->Save As, but we're starting to get results with more than 16,000 results, which can't be exported.

我想要某种服务器端存储过程，我们可以触发该操作.我知道执行此操作的sp_makewebtask过程，但是它需要服务器上的管理权限，并且出于明显的原因，我们不能将其授予所有人.

I'd like some sort of server side stored procedure we can trigger that will do this. I'm aware of the sp_makewebtask procedure that does this, however it requires administrative rights on the server, and for obvious reasons we can't give that to everyone.

有什么想法吗?

推荐答案

您可能无法授予所有人管理权限，但也许您可以:

You might not be able to give everyone administrative rights, but maybe you could:

1. 创建一个特殊用户，例如"WebTaskUser"，具有读取权限 从所需的视图并执行存储的sp_makewebtask 程序.您将授予一个用户的权限，而不是每个人的权限.

1. Create a special user, e.g. 'WebTaskUser', with permissions to read from the desired views and to execute the sp_makewebtask stored procedure. You would be giving permissions to one user-not to everyone.

然后创建一个包装器存储过程(在下面)，使您的用户可以执行该存储过程，而该存储过程包含用于执行对sp_makewebtask过程的特定预定义调用的代码，每个视图一个，仅授予该单个sp_makewebtask过程的执行权限，仅授予一个用户帐户-并非所有管理权限都仅授予一个帐户:-)仅执行.

Then create a wrapper stored procedure (below) that allows your users to execute it, while it contains code to execute specific predefined calls to the sp_makewebtask procedure, one per view, granting only execute permissions for that single sp_makewebtask procedure, to one single user account-not all administrative permissions are granted-only execute-only to one account :-).

根据您对SSMS的喜好测试和优化存储过程， Access-vba或最适合您的

Test and refine the stored procedure to your liking from SSMS, Access-vba, or whatever suits you best

当您对proc感到满意时，请授予其他必要的条件 您的用户或用户角色的权限，以便他们可以执行为 好吧.

When you're happy with the proc, grant any further necessary permissions to your users or user roles, so they can execute it as well.

`

--Example code to create user, and add permissions I might be able to add later

USE [some_database_x];

CREATE PROCEDURE EXPORT_VIEWS_TO_EXCEL
@TARGET_FOLDER NVARCHAR(100) DEFAULT 'C:\temp';
@FILE_TAG NVARCHAR(20) DEFAULT '';
@VIEWNAME NVARCHAR(100);
WITH EXECUTE AS 'WebTaskUser'
AS
BEGIN
IF @VIEWNAME IS NOT NULL
BEGIN
     DECLARE @myOUTPUTFILE NVARCHAR(100);
     SET @myOUTPUTFILE = @TARGET_FOLDER + '\' + @VIEWNAME + COALESCE(@FILE_TAG,'');
     DECLARE @myQUERY NVARCHAR(150);
     IF @VIEWNAME = 'mydb.dbo.firstview'
     BEGIN
          SET @myQUERY = 'Select * from mydb.dbo.firstview',
     END
     IF @VIEWNAME = 'mydb.dbo.secondview'
     BEGIN
          SET @myQUERY = 'Select * from mydb.dbo.secondview'
     END
     EXECUTE sp_makewebtask
     @outputfile = @OUTPUTFILE,
     @query = @myQUERY,
     @colheaders = 1, @FixedFont = 0, @lastupdated = 0, @resultstitle='My Title'
END

RETURN 0;
END
GO

`

这篇关于在Access/SQL Server中以编程方式导出视图数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！