Syntax errors generating CSVs for user and group import during cloud migration

Rob Horan

Per https://support.atlassian.com/migration/docs/determine-your-user-migration-strategy/ I need to run the following SQL queries from the Jira Server and Confluence database to create CSV files of users and groups:

Users:

SELECT email_address AS "email", user_name AS "username", display_name AS "displayname", active AS "active" FROM cwd_user

Groups:

SELECT DISTINCT parent_name AS group, child_name AS username FROM cwd_membership;

 

When the groups query is run (MS SQL Server 2016) I get an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'group'.

 

Any ideas?

 

Also, i seen to be getting another error on one particular server when running the Users query:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'cwd_user'.

Phill Fox

Hi @Rob Horan 

The reason for failure is that Group is a reserved word for SQL. 

So try this version 

SELECT DISTINCT parent_name AS jiragroups, child_name AS username FROM cwd_membership;

 

With regard to the cwd_user this sounds like either a permission issue with the table or possibly that the table is missing which would be very unlikely if the solution is still running. 

I would start troubleshooting with checking the permissions on the table. If it is absent then you will know why you cannot include it in a query.

Hope this helps.

Phill