Monday, October 24, 2011

Copying Smartlists favorites from one user to another

I get this question asked very often and sometimes have to dig up my notes on the subject, so I figured I would just post the SQL script that I use for this.

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:

Peace Warrior said...

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

Geoff James said...

Thanks Mariano for this script.

Kind Regards,

Geoff James

Mariano Gomez said...

Geoff,

Glad I could help!

MG.-

Big E said...

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.

Big E said...

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’