/
How to run an MS Query

How to run an MS Query

Summary

This is a check list for the steps to create an MSQuery connecting to a Synergetic Database. This covers :

1 - Creating an MSQuery User

2 - Creating an MS Query group/Role

3 - Executing an MSQuery and returning results to MSExcel

Method

In SSMS create a SQL user (Note this user does not need to exist as a Synergetic User for MSQuery purposes)

Select Security → Logins → Right Click [New Login..]

 

 

In SSMS - map the user to the Synergetic database (no role memberships)

 

 

In Synergetic create a MSQuery group in Group/User Security

 

Synergetic

In Group User Security select the newly created group

Select Resources → SQL Server Objects

Select Create SQL Group Permissions

 

If it displays DELETE SQL Group then Create SQL Group/Permissions has already been done

Select for the objects (views and/or tables) you want this MSQuery group to have access to ie.

SSMS

Note : these steps to be actioned by the Synergetic Database administrator.

Select Database Name → Security → Roles → Database Roles

A new role will be created called Syn_{GroupName_from_Synergetic}_dbo   (* will be either '..._dbo' or '..._finance' depending on schema)

Select that role and properties

You will see the objects that role has access to which should match Synergetic group user security (zspSynergeticReservered may be auto assigned)

In SSMS select database→Security → Users → (msquery user)

select properties of user → Membership

Add user to the MSQuery Role

OR

If the user is a Synergetic User you can add them to the group in Synergetic

Result - you have now created a user, a group (DB role) and assigned that user to that Role

As a test log into SSMS as that user

Select Database → Tables and Views

You should only see the tables and views assigned to that role earlier (note Constituencies and pvSynDatabases are public so auto assigned)

Now the Query part

Run MS Excel and select a Blank Workbook

Select Data Menu

NOTE : Newer versions of MS Excel may need to add the legacy MS Query option

(Expand to see instructions)

Select Microsoft Workbook > Go to Files> Options> Data> Tick Show legacy data import wizards

image-20240423-033852.png

 

 Once saved open a Blank MS Excel Spreadsheet

Select Data tab

Select Get Data > Legacy Wizards > From Microsoft Query (Legacy)

 

image-20240423-033711.png

 

Select Get Data → From Other Sources → From Microsoft Query

 

Select Data Source = Synergetic - it uses the same datasource you use to connect to SynMain so for most users this is Synergetic (this comes from your ODBC data sources)

select OK

For SQL Server Login

Enter the Login ID and Password you created in First Step

The database should be prepopulated (from the datasource)

If you wish to login as your Windows account then select Use Trusted Connection

In Choose Columns Wizard - Select Options

Filter on the relevant tables Show Tables , Views and Owner = dbo (or finance)

Select the tables and columns you wish to display

 

Set your filters (Where clauses)

and Sort Order (if required)

Return to Excel will display the result back into MSExcel

View data or edit query in Microsoft Query allows to to continue manipulating the data

At the top menu select SQL to show the SQL query

Select the Return Data - to return to Excel (Cell A1 is a good starting point)

Data is now in Excel

To Return to MS Query

Right Click a cell and Select Table → Edit Query

This will return you back to the MS Query Wizard

Considerations

Tips to tweak the connection string

This is useful if you have received an Excel Spreadsheet containing an MSQuery. This will be preconfigured to link to the schools database. You may wish to run this query on another database or datasource

Open the Query in MSExcel (you may get errors due to invalid database name/connection string)

To point to a new datasource

Select Data → Properties

Select the connection Properties Icon(Top RHS)

 

Select Definition

Connection String - change DATABASE= to the Local database

Command Text - this may also contain the database name. It is ok to remove the ‘database.dbo’ section

Select OK to save