Long Description:
This is a procedure we use to export a table content from a Dynamics database into an Excel sheet. In this case, I will be showing you how to extract the complete users list from Dynamics Great Plains using the SQL Server Enterprise Manager.
Tip: You can also run the "Import and Export Data" program directly. The advantage of launching it from Enterprise Manager is that when you right click the database from the Ent. Manager the correct options will be automatically set for you.
You can use this same procedure for exporting other data. Just make sure you know the tables and column names you want to export.
STEPS:
1. Drilldown through the Microsoft SQL Servers, SQL Server Group, Server, downto the databases until you see your DYNAMICS database.
2. Right click DYNAMICS database, then choose "All Tasks" - "Export Data". This will launch the DTS Import/Export Wizard.
3. When you see the "Data Transformation Services Import/Export Wizard" screen: Click Next.
4. On Choose a Data Source window, the correct "Data Source" option, the Server and the Database should all be already correctly selected. This is so because the system already detected it based on the database you right-clicked on to launch the DTS Import/Export. Click Next.
5. You will now select the "Destination" option. Click the pulldown menu to display the list of available formats. Find "Mirosoft Excel 97/2000". Click the browse button to select where you will save the file, type the filename then click "Save". After you have specified the destination and the name of your output file, click "Next" to proceed to next window.
6. You will now see the "Specify Table Copy or Query" window. Choose "Use a query to specify the data to transfer".
Tip: If you want to export the whole table contents then you can also choose the "Copy table(s) from the source database" option. This option will export all rows and all columns. This option is not always going to work because the whole table contents may be too big to fit in your output file. By using the "query" option instead of a whole table you will be able to filter only the columns and rows you want.
7. After choosing the "Use a query to specify the data to transfer", you will now see the "Type SQL Statement" screen. In order to extract the Dynamics users list, type this:
SELECT USERID, USERNAME, USRCLASS FROM SY01400
(For my purposes, I am extracting only 3 columns. The User ID, Name and Class.)
After typing the query, click "Next".
8. You will now see the "Select Source Tables and Views" screen. Click "Next" to accept the defaults and move on.
9. Finally you will see the "Save, schedule and replicate package". Make sure the "Run Immediately" option is checked. Click "Next", then click "Finish" on the final screen. Click the "Finish", then "Done" confirmation screens.
10. Browse the folder where you saved the file and you should now have your users list in an Excel document.
This is a procedure we use to export a table content from a Dynamics database into an Excel sheet. In this case, I will be showing you how to extract the complete users list from Dynamics Great Plains using the SQL Server Enterprise Manager.
Tip: You can also run the "Import and Export Data" program directly. The advantage of launching it from Enterprise Manager is that when you right click the database from the Ent. Manager the correct options will be automatically set for you.
You can use this same procedure for exporting other data. Just make sure you know the tables and column names you want to export.
STEPS:
1. Drilldown through the Microsoft SQL Servers, SQL Server Group, Server, downto the databases until you see your DYNAMICS database.
2. Right click DYNAMICS database, then choose "All Tasks" - "Export Data". This will launch the DTS Import/Export Wizard.
3. When you see the "Data Transformation Services Import/Export Wizard" screen: Click Next.
4. On Choose a Data Source window, the correct "Data Source" option, the Server and the Database should all be already correctly selected. This is so because the system already detected it based on the database you right-clicked on to launch the DTS Import/Export. Click Next.
5. You will now select the "Destination" option. Click the pulldown menu to display the list of available formats. Find "Mirosoft Excel 97/2000". Click the browse button to select where you will save the file, type the filename then click "Save". After you have specified the destination and the name of your output file, click "Next" to proceed to next window.
6. You will now see the "Specify Table Copy or Query" window. Choose "Use a query to specify the data to transfer".
Tip: If you want to export the whole table contents then you can also choose the "Copy table(s) from the source database" option. This option will export all rows and all columns. This option is not always going to work because the whole table contents may be too big to fit in your output file. By using the "query" option instead of a whole table you will be able to filter only the columns and rows you want.
7. After choosing the "Use a query to specify the data to transfer", you will now see the "Type SQL Statement" screen. In order to extract the Dynamics users list, type this:
SELECT USERID, USERNAME, USRCLASS FROM SY01400
(For my purposes, I am extracting only 3 columns. The User ID, Name and Class.)
After typing the query, click "Next".
8. You will now see the "Select Source Tables and Views" screen. Click "Next" to accept the defaults and move on.
9. Finally you will see the "Save, schedule and replicate package". Make sure the "Run Immediately" option is checked. Click "Next", then click "Finish" on the final screen. Click the "Finish", then "Done" confirmation screens.
10. Browse the folder where you saved the file and you should now have your users list in an Excel document.
No comments:
Post a Comment