Moving Users to a New Site

Overview

There are a few reasons we need to move users from one site to another. It may be that a site is split in two, a group of users move or a group of users were assigned to the wrong site. If a new site needs to be created, this must currently be done by Allscripts. Put a ticket in for them to create a new site with whatever name you need. Next you need to update the users' default site and auto-print defaults. If there less than a several users, do this manually. If there are many users, follow the directions below.

Resolution

First, gather the user names of the people that need to be moved to another site. Next, pull the IDs of the two sites - "old" and "new". Now, open up the attached script and replace the list of usernames with your list and update the new and old site ids. Run the script. If there are any errors, rollback your changes. If you're satisfied with the updates, commit the changes. The rollback and commit commands are commented out at the bottom of the script file.

SQL Script

/*****************************************************************************************
	Galen Healthcare Solutions, Inc

	Description: Update users' preferences to have their default site be something new,
			and udpate their AutoPrint Defaults

		In this case we're moving from Site 1 to Site 2


	History:
11Oct2006	mhd	created
*****************************************************************************************/

-- SELECT * FROM Site_DE WHERE EntryName LIKE '%Site 2%'

begin tran

DECLARE @OldSite varchar(8)
	,@NewSite varchar(8)
	,@ListOfUserNames varchar(4000)

SELECT @ListOfUserNames = 'username1, username2, username3, '

SELECT @OldSite = '2' -- Site 1
	,@NewSite = '5' -- Site 2

DECLARE @Users table (UserName varchar(50))

INSERT INTO @Users
SELECT replace(replace(str, char(13), ''), char(10), '') FROM dbo.fnGetChrListToTable(@ListOfUserNames, ',')

-- SELECT * FROM @Users

/*** Fix default site ***/
UPDATE User_Preference
SET TextString = @NewSite
FROM User_Preference up
INNER JOIN IDX_User iu ON iu.ID = up.IDXUserID
INNER JOIN @Users u ON u.UserName = iu.AUserName
WHERE up.Section = 'Site'
AND up.TextString = @OldSite

/*** Update Auto-print defaults ***/
UPDATE User_Print_Default
SET SiteDE = @NewSite
FROM User_Print_Default upd
INNER JOIN IDX_User iu ON upd.UserID = iu.ID
INNER JOIN @Users u ON u.UserName = iu.AUserName
WHERE upd.SiteDE = @OldSite

rollback
-- commit