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 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