Smartlist favorites are traditionally stored in the ASIEXP81 table on a user-company basis. It's important to keep this in mind, as you can tailor this script to copy favorites from one user to another in the same company or the same user across various companies. However, this script replicates all favorites, across all companies for one user to another user.
USE DYNAMICS; GO -- remove any records for the destination user prior to synchronizing DELETE FROM ASIEXP81 WHERE USERID = '<DestinationUserID>'; -- execute synchronization INSERT INTO ASIEXP81 (ASI_Favorite_Dict_ID ,ASI_Favorite_Type ,ASI_Favorite_Save_Level ,CMPANYID ,USRCLASS ,USERID ,ASI_Favorite_Name ,ASI_Field_Dict_ID_1 ,ASI_Field_Dict_ID_2 ,ASI_Field_Dict_ID_3 ,ASI_Field_Dict_ID_4 ,ASI_Field_List_1 ,ASI_Field_List_2 ,ASI_Field_List_3 ,ASI_Field_List_4 ,ASI_Search_Type_1 ,ASI_Search_Type_2 ,ASI_Search_Type_3 ,ASI_Search_Type_4 ,ASI_Match_Case_1 ,ASI_Match_Case_2 ,ASI_Match_Case_3 ,ASI_Match_Case_4 ,ASI_Start_Account_Number_1_1 ,ASI_Start_Account_Number_1_2 ,ASI_Start_Account_Number_1_3 ,ASI_Start_Account_Number_1_4 ,ASI_Start_Account_Number_1_5 ,ASI_Start_Account_Number_1_6 ,ASI_Start_Account_Number_1_7 ,ASI_Start_Account_Number_1_8 ,ASI_Start_Account_Number_1_9 ,ASI_Start_Account_Number_1_10 ,ASI_Start_Account_Number_2_1 ,ASI_Start_Account_Number_2_2 ,ASI_Start_Account_Number_2_3 ,ASI_Start_Account_Number_2_4 ,ASI_Start_Account_Number_2_5 ,ASI_Start_Account_Number_2_6 ,ASI_Start_Account_Number_2_7 ,ASI_Start_Account_Number_2_8 ,ASI_Start_Account_Number_2_9 ,ASI_Start_Account_Number_2_10 ,ASI_Start_Account_Number_3_1 ,ASI_Start_Account_Number_3_2 ,ASI_Start_Account_Number_3_3 ,ASI_Start_Account_Number_3_4 ,ASI_Start_Account_Number_3_5 ,ASI_Start_Account_Number_3_6 ,ASI_Start_Account_Number_3_7 ,ASI_Start_Account_Number_3_8 ,ASI_Start_Account_Number_3_9 ,ASI_Start_Account_Number_3_10 ,ASI_Start_Account_Number_4_1 ,ASI_Start_Account_Number_4_2 ,ASI_Start_Account_Number_4_3 ,ASI_Start_Account_Number_4_4 ,ASI_Start_Account_Number_4_5 ,ASI_Start_Account_Number_4_6 ,ASI_Start_Account_Number_4_7 ,ASI_Start_Account_Number_4_8 ,ASI_Start_Account_Number_4_9 ,ASI_Start_Account_Number_4_10 ,ASI_Start_Date_Token_DDL_1 ,ASI_Start_Date_Token_DDL_2 ,ASI_Start_Date_Token_DDL_3 ,ASI_Start_Date_Token_DDL_4 ,ASI_Start_Date_1 ,ASI_Start_Date_2 ,ASI_Start_Date_3 ,ASI_Start_Date_4 ,ASI_Start_DDL_1 ,ASI_Start_DDL_2 ,ASI_Start_DDL_3 ,ASI_Start_DDL_4 ,ASI_String_Start_1 ,ASI_String_Start_2 ,ASI_String_Start_3 ,ASI_String_Start_4 ,ASI_End_Account_Number_1_1 ,ASI_End_Account_Number_1_2 ,ASI_End_Account_Number_1_3 ,ASI_End_Account_Number_1_4 ,ASI_End_Account_Number_1_5 ,ASI_End_Account_Number_1_6 ,ASI_End_Account_Number_1_7 ,ASI_End_Account_Number_1_8 ,ASI_End_Account_Number_1_9 ,ASI_End_Account_Number_1_10 ,ASI_End_Account_Number_2_1 ,ASI_End_Account_Number_2_2 ,ASI_End_Account_Number_2_3 ,ASI_End_Account_Number_2_4 ,ASI_End_Account_Number_2_5 ,ASI_End_Account_Number_2_6 ,ASI_End_Account_Number_2_7 ,ASI_End_Account_Number_2_8 ,ASI_End_Account_Number_2_9 ,ASI_End_Account_Number_2_10 ,ASI_End_Account_Number_3_1 ,ASI_End_Account_Number_3_2 ,ASI_End_Account_Number_3_3 ,ASI_End_Account_Number_3_4 ,ASI_End_Account_Number_3_5 ,ASI_End_Account_Number_3_6 ,ASI_End_Account_Number_3_7 ,ASI_End_Account_Number_3_8 ,ASI_End_Account_Number_3_9 ,ASI_End_Account_Number_3_10 ,ASI_End_Account_Number_4_1 ,ASI_End_Account_Number_4_2 ,ASI_End_Account_Number_4_3 ,ASI_End_Account_Number_4_4 ,ASI_End_Account_Number_4_5 ,ASI_End_Account_Number_4_6 ,ASI_End_Account_Number_4_7 ,ASI_End_Account_Number_4_8 ,ASI_End_Account_Number_4_9 ,ASI_End_Account_Number_4_10 ,ASI_End_Date_Token_DDL_1 ,ASI_End_Date_Token_DDL_2 ,ASI_End_Date_Token_DDL_3 ,ASI_End_Date_Token_DDL_4 ,ASI_End_Date_1 ,ASI_End_Date_2 ,ASI_End_Date_3 ,ASI_End_Date_4 ,ASI_End_DDL_1 ,ASI_End_DDL_2 ,ASI_End_DDL_3 ,ASI_End_DDL_4 ,ASI_String_End_1 ,ASI_String_End_2 ,ASI_String_End_3 ,ASI_String_End_4 ,ASI_Search_From_Str_1 ,ASI_Search_From_Str_2 ,ASI_Search_From_Str_3 ,ASI_Search_From_Str_4 ,ASI_Search_To_Str_1 ,ASI_Search_To_Str_2 ,ASI_Search_To_Str_3 ,ASI_Search_To_Str_4 ,ASI_Search_Logic_Type ,ASI_Max_Records ,ASI_Sort_Dict_ID ,ASI_Sort_Field ,ASI_Sort_Type ,ASI_Field_Comparison_1 ,ASI_Field_Comparison_2 ,ASI_Field_Comparison_3 ,ASI_Field_Comparison_4 ,ASI_Start_Comp_Field_ID_1 ,ASI_Start_Comp_Field_ID_2 ,ASI_Start_Comp_Field_ID_3 ,ASI_Start_Comp_Field_ID_4 ,ASI_Start_Comp_Field_Dic_1 ,ASI_Start_Comp_Field_Dic_2 ,ASI_Start_Comp_Field_Dic_3 ,ASI_Start_Comp_Field_Dic_4 ,ASI_End_Comp_Field_ID_1 ,ASI_End_Comp_Field_ID_2 ,ASI_End_Comp_Field_ID_3 ,ASI_End_Comp_Field_ID_4 ,ASI_End_Comp_Field_Dict_1 ,ASI_End_Comp_Field_Dict_2 ,ASI_End_Comp_Field_Dict_3 ,ASI_End_Comp_Field_Dict_4) SELECT ASI_Favorite_Dict_ID ,ASI_Favorite_Type ,ASI_Favorite_Save_Level ,CMPANYID ,USRCLASS ,'<DestinationUserID>' ,ASI_Favorite_Name ,ASI_Field_Dict_ID_1 ,ASI_Field_Dict_ID_2 ,ASI_Field_Dict_ID_3 ,ASI_Field_Dict_ID_4 ,ASI_Field_List_1 ,ASI_Field_List_2 ,ASI_Field_List_3 ,ASI_Field_List_4 ,ASI_Search_Type_1 ,ASI_Search_Type_2 ,ASI_Search_Type_3 ,ASI_Search_Type_4 ,ASI_Match_Case_1 ,ASI_Match_Case_2 ,ASI_Match_Case_3 ,ASI_Match_Case_4 ,ASI_Start_Account_Number_1_1 ,ASI_Start_Account_Number_1_2 ,ASI_Start_Account_Number_1_3 ,ASI_Start_Account_Number_1_4 ,ASI_Start_Account_Number_1_5 ,ASI_Start_Account_Number_1_6 ,ASI_Start_Account_Number_1_7 ,ASI_Start_Account_Number_1_8 ,ASI_Start_Account_Number_1_9 ,ASI_Start_Account_Number_1_10 ,ASI_Start_Account_Number_2_1 ,ASI_Start_Account_Number_2_2 ,ASI_Start_Account_Number_2_3 ,ASI_Start_Account_Number_2_4 ,ASI_Start_Account_Number_2_5 ,ASI_Start_Account_Number_2_6 ,ASI_Start_Account_Number_2_7 ,ASI_Start_Account_Number_2_8 ,ASI_Start_Account_Number_2_9 ,ASI_Start_Account_Number_2_10 ,ASI_Start_Account_Number_3_1 ,ASI_Start_Account_Number_3_2 ,ASI_Start_Account_Number_3_3 ,ASI_Start_Account_Number_3_4 ,ASI_Start_Account_Number_3_5 ,ASI_Start_Account_Number_3_6 ,ASI_Start_Account_Number_3_7 ,ASI_Start_Account_Number_3_8 ,ASI_Start_Account_Number_3_9 ,ASI_Start_Account_Number_3_10 ,ASI_Start_Account_Number_4_1 ,ASI_Start_Account_Number_4_2 ,ASI_Start_Account_Number_4_3 ,ASI_Start_Account_Number_4_4 ,ASI_Start_Account_Number_4_5 ,ASI_Start_Account_Number_4_6 ,ASI_Start_Account_Number_4_7 ,ASI_Start_Account_Number_4_8 ,ASI_Start_Account_Number_4_9 ,ASI_Start_Account_Number_4_10 ,ASI_Start_Date_Token_DDL_1 ,ASI_Start_Date_Token_DDL_2 ,ASI_Start_Date_Token_DDL_3 ,ASI_Start_Date_Token_DDL_4 ,ASI_Start_Date_1 ,ASI_Start_Date_2 ,ASI_Start_Date_3 ,ASI_Start_Date_4 ,ASI_Start_DDL_1 ,ASI_Start_DDL_2 ,ASI_Start_DDL_3 ,ASI_Start_DDL_4 ,ASI_String_Start_1 ,ASI_String_Start_2 ,ASI_String_Start_3 ,ASI_String_Start_4 ,ASI_End_Account_Number_1_1 ,ASI_End_Account_Number_1_2 ,ASI_End_Account_Number_1_3 ,ASI_End_Account_Number_1_4 ,ASI_End_Account_Number_1_5 ,ASI_End_Account_Number_1_6 ,ASI_End_Account_Number_1_7 ,ASI_End_Account_Number_1_8 ,ASI_End_Account_Number_1_9 ,ASI_End_Account_Number_1_10 ,ASI_End_Account_Number_2_1 ,ASI_End_Account_Number_2_2 ,ASI_End_Account_Number_2_3 ,ASI_End_Account_Number_2_4 ,ASI_End_Account_Number_2_5 ,ASI_End_Account_Number_2_6 ,ASI_End_Account_Number_2_7 ,ASI_End_Account_Number_2_8 ,ASI_End_Account_Number_2_9 ,ASI_End_Account_Number_2_10 ,ASI_End_Account_Number_3_1 ,ASI_End_Account_Number_3_2 ,ASI_End_Account_Number_3_3 ,ASI_End_Account_Number_3_4 ,ASI_End_Account_Number_3_5 ,ASI_End_Account_Number_3_6 ,ASI_End_Account_Number_3_7 ,ASI_End_Account_Number_3_8 ,ASI_End_Account_Number_3_9 ,ASI_End_Account_Number_3_10 ,ASI_End_Account_Number_4_1 ,ASI_End_Account_Number_4_2 ,ASI_End_Account_Number_4_3 ,ASI_End_Account_Number_4_4 ,ASI_End_Account_Number_4_5 ,ASI_End_Account_Number_4_6 ,ASI_End_Account_Number_4_7 ,ASI_End_Account_Number_4_8 ,ASI_End_Account_Number_4_9 ,ASI_End_Account_Number_4_10 ,ASI_End_Date_Token_DDL_1 ,ASI_End_Date_Token_DDL_2 ,ASI_End_Date_Token_DDL_3 ,ASI_End_Date_Token_DDL_4 ,ASI_End_Date_1 ,ASI_End_Date_2 ,ASI_End_Date_3 ,ASI_End_Date_4 ,ASI_End_DDL_1 ,ASI_End_DDL_2 ,ASI_End_DDL_3 ,ASI_End_DDL_4 ,ASI_String_End_1 ,ASI_String_End_2 ,ASI_String_End_3 ,ASI_String_End_4 ,ASI_Search_From_Str_1 ,ASI_Search_From_Str_2 ,ASI_Search_From_Str_3 ,ASI_Search_From_Str_4 ,ASI_Search_To_Str_1 ,ASI_Search_To_Str_2 ,ASI_Search_To_Str_3 ,ASI_Search_To_Str_4 ,ASI_Search_Logic_Type ,ASI_Max_Records ,ASI_Sort_Dict_ID ,ASI_Sort_Field ,ASI_Sort_Type ,ASI_Field_Comparison_1 ,ASI_Field_Comparison_2 ,ASI_Field_Comparison_3 ,ASI_Field_Comparison_4 ,ASI_Start_Comp_Field_ID_1 ,ASI_Start_Comp_Field_ID_2 ,ASI_Start_Comp_Field_ID_3 ,ASI_Start_Comp_Field_ID_4 ,ASI_Start_Comp_Field_Dic_1 ,ASI_Start_Comp_Field_Dic_2 ,ASI_Start_Comp_Field_Dic_3 ,ASI_Start_Comp_Field_Dic_4 ,ASI_End_Comp_Field_ID_1 ,ASI_End_Comp_Field_ID_2 ,ASI_End_Comp_Field_ID_3 ,ASI_End_Comp_Field_ID_4 ,ASI_End_Comp_Field_Dict_1 ,ASI_End_Comp_Field_Dict_2 ,ASI_End_Comp_Field_Dict_3 ,ASI_End_Comp_Field_Dict_4 FROM ASIEXP81 WHERE USERID = '<SourceUserID>'; GO
In the above script <SourceUserID> and <DestinationUserID> are placeholders for the actual user IDs.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
5 comments:
Actually I want to create a new user and transfer all the access and security to all companies. Is there any script availale for that?
Thank you
Thanks Mariano for this script.
Kind Regards,
Geoff James
Geoff,
Glad I could help!
MG.-
The user lost all her smartlists, so I restored an older DYNAMICS TO DYN-Restore all her lists are there. I should be able to copy from Database/table to the other Database and table. Can you suggest the correct syntax?
Thanks in advance.
Okay. Maybe I answered my own. None of the missing Smartlists are listed by ASI_Favorite_Dict_ID in the Live DYNAMICS database. So, if I use that to identify the ones to be copied, I think it would look like this:
INSERT INTO [DYNAMICS].[dbo].[ASIEXP81]
[ASI_Favorite_Dict_ID]
,[ASI_Favorite_Type]
,[ASI_Favorite_Save_Level]
,[CMPANYID]
,[USRCLASS]
,[USERID]
,[ASI_Favorite_Name]
----(reduced)-----
,[ASI_End_Comp_Field_Dict_1]
,[ASI_End_Comp_Field_Dict_2]
,[ASI_End_Comp_Field_Dict_3]
,[ASI_End_Comp_Field_Dict_4]
,[DEX_ROW_ID]
SELECT [ASI_Favorite_Dict_ID]
,[ASI_Favorite_Type]
,[ASI_Favorite_Save_Level]
,[CMPANYID]
,[USRCLASS]
,[USERID]
,[ASI_Favorite_Name]
----(reduced)-----
,[ASI_End_Comp_Field_Dict_1]
,[ASI_End_Comp_Field_Dict_2]
,[ASI_End_Comp_Field_Dict_3]
,[ASI_End_Comp_Field_Dict_4]
,[DEX_ROW_ID]
FROM [DYN-Restore].[dbo].[ASIEXP81] WHERE [ASI_Favorite_Dict_ID] = ‘XXX’
Post a Comment