通过参数化的存储过程将数据从Azure SQL数据库提取到Excel中 [英] Pulling data from Azure SQL database into an Excel through a parameterised stored procedure

问题描述

我在Azure SQL数据库中有数据，我想通过使用需要参数的存储过程将其提取到Excel工作表中.

I have data in an Azure SQL database, which I would like to pull into an Excel sheet through the use of stored procedure(s) which require parameters.

但是，我无法做到这一点，因为我无法使它与本地SQL Server一起工作.通常，这是通过Microsoft Query完成的，定义与数据库的连接，然后输入{ CALL sproc (?) }，然后使用Connections命令将单元格映射到参数.

However, I'm struggling to do this, as I cannot get it to work how it works with a local SQL server. Ordinarily this is done through Microsoft Query, defining the connection to the database, and entering { CALL sproc (?) } and then using the Connections command to map a cell to the parameter.

我无法在MS Query中建立与数据库的连接.当我输入xxx.database.windows.net作为服务器时，输入我的用户名/密码，然后尝试切换到数据库，我需要使用以下消息

I cannot set up a connection to the database in MS Query. When I enter the xxx.database.windows.net as the server, my username / password, and then try to switch to database I need to use I get the message below

填充列表框失败:

Filling listbox failed:

SQLState:"37000"

SQLState: '37000'

SQL Server错误:40511

SQL Server Error: 40511

此版本的SQL Server不支持[Microsoft] [ODBC SQL Server驱动程序] [SQL Server]内置函数'has_dbaccess'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Built-in function 'has_dbaccess' is not supported in this version of SQL Server.

任何人都可以提供帮助吗?

Can anyone offer any assistance please?

推荐答案

创建一个扩展名为.DSN的文本文件，然后向其中添加如下内容...

Create a text file with the .DSN extension then add content like the following into it...

[ODBC]
DRIVER=SQL Server
SERVER=xxxxxxxx.database.windows.net,1234
APP=Microsoft Office 2010
WSID=NA
DATABASE=DB12345
[Microsoft Office]
UID=myUsername
PWD=myPassword

然后，您可以只使用Microsoft Query的此连接，而无需处理使您感到悲伤的对话框.

You can then just use this connection from Microsoft Query without dealing with the dialogs that are giving you grief.

这篇关于通过参数化的存储过程将数据从Azure SQL数据库提取到Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！