How to run a Direct Query on an ArchivesSpace database with a Read Only account

Follow

 

Product  ArchivesSpace
Version N/A
Case 7802

 

Problem/Question

A user wants to generate a custom report using a SQL query.

Resolution

If you need to run a query directly on an ArchivesSpace database, you can use Microsoft Excel to pass the MySQL query without having to pre-load the tables.

You will need a Read-Only MySQL account and password (usually created by your hosting provider or if locally hosted your database administrator), Microsoft Excel with Power Query installed, and the ADO.NET Driver for MySQL (Connector/NET) installed on the local machine. You may need a local administrator account on your machine to install the supporting items listed above.

  1. Launch Microsoft Excel (you’ll need at least a version that supports Power Query
  2. Select the Data tab and then Get Data | From Database | From MySQL Database  (Some versions of Excel have New Query | From Database instead)                                                                                                                                                         
  3. On the MySQL database screen that appears, select the Advanced options, and complete the form by entering the Server IP, the database name (archivesspace), a Command timeout in minutes value, the SQL select statement/query, and the default ‘Include relationship columns’ left checked.  Then click OK.

Cause

n/a

 

 

 

Questions?

If this article didn’t resolve your issue, please take a moment and answer a few questions to help improve our documentation:

Feedback