How to update Fixed Asset Labels using Microsoft SQL Server Management Studio

If you have numerous Fixed Asset Labels you need to update, here is another option you can use instead of manually updating them through the Asset General window in Microsoft Dynamics GP. 

Note: Currently Microsoft Dynamics GP Integration Manager does not have the capability to run an update on Fixed Assets.

1. Create a test company and test the next steps in the test company to verify the results are what you want. 

871973 Set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, SQL Server 2005, or SQL Server 2008
support.microsoft.com/default.aspx?scid=kb;EN-US;871973

885542 How to create a test or historical company in Payroll or in Canadian Payroll in Microsoft Dynamics GP
support.microsoft.com/default.aspx?scid=kb;en-US;885542

2. Open Microsoft SQL Server Management Studio and then click New Query.

3. Run the following statement against the Company Database and save it to a file (*.rpt). In Microsoft SQL Server Management Studio, on the Query menu, point to Results To and then click Results to File. Click Execute. Then save the results to a file (*.rpt).

   select Asset_Label, rtrim(ASSETID) as ASSETID, rtrim(ASSETIDSUF) as ASSETIDSUF from FA00100

4. Open Microsoft Excel.

5. In Microsoft Excel, on the File menu, click Open, and then browse to the file you created in Step 3, select it, and then click Open. 

6. The Text Import Wizard in Microsoft Excel should open. Select the Fixed width Original data type, and then click Next.

7. The break lines should auto populate. Review them and adjust as needed. Click Next.

8. With the first column highlighted, change the Column data format from General to Text. Highlight the second column and change the Column data format from General to Text.  Highlight the third column and change the Column data format from General to Text. Click Finish.

9. Highlight the Asset_Label column, right click over the highlighted column, and then click Insert. 

10. Highlight the ASSETID column, right click over the column, and then click Insert. Repeat this two more times. 

11. Highlight the ASSETIDSUF column, right click over the column, and then click Insert. Repeat this one more time. 

12. Add the following information to the columns:

a. In Column A, copy and paste the following in each row with an Asset_Label:

         update FA00100 set Asset_Label=
  
b. In Column C, enter the new Asset_Label you are switching to.
 
c. In Column D, copy and paste the following in the first row with an Asset_Label (shown here as row 3 which may need to be changed based on your results), and then copy the forumula down through the remaining Asset_Label's.
 
=CONCATENATE("'",C3,"'")

Note: For Row 4 if the Asset Label is going to be changed, the formula would be as follows:

=CONCATENATE("'",C4,"'")

d. In Column E, copy and paste the following in each row with an Asset_Label:
 
where ASSETID=
  
e. In Column G, copy and paste the following in the first row with an Asset_Label (shown here as row 3 which may need to be changed based on your results), and then copy the forumula down through the remaining Asset_Label's.
 
=CONCATENATE("'",F3,"'")
  
f. In Column H, enter the following in each row with an Asset_Label:
 
and ASSETIDSUF=

13. Highlight Column B, right click over it, and then click Hide. Repeat this step for columns C and F.

14. Select Columns A through I in each row with an Asset_Label change. Copy them to the clipboard.

15. Back in Microsoft SQL Server Management Studio, paste the update statements into a new query against your company database, and then click Execute.

16. Open Microsoft Dynamics GP and review the changes in the Asset General window (On the Cards menu, point to Fixed Assets, and then click Asset).

17. If the results are what you want, repeat the steps in the live company after making a complete restorable backup and having all users out of the system when running the update statement(s).

Enjoy!
Angela M.