Moving attributes between environments

There are many times when I need to move attributes from one environment to another. Below is what I use, it prints out one big script that you can copy and paste and run.

set nocount on;

declare @SQL nvarchar(max) = N'';

declare @ATTRIBUTES table(ID uniqueidentifier primary key, NAME nvarchar(100), ATTRIBUTERECORDTYPEID uniqueidentifier, DATATYPECODE tinyint, CODETABLECATALOGID uniqueidentifier, ONLYALLOWONEPERRECORD bit, CONSTITUENTSEARCHLISTCATALOGID uniqueidentifier, PROCESSED bit default 0);

insert into @ATTRIBUTES(ID, NAME, ATTRIBUTERECORDTYPEID, DATATYPECODE, CODETABLECATALOGID, ONLYALLOWONEPERRECORD, CONSTITUENTSEARCHLISTCATALOGID)
select ID, NAME, ATTRIBUTERECORDTYPEID, DATATYPECODE, CODETABLECATALOGID, ONLYALLOWONEPERRECORD, CONSTITUENTSEARCHLISTCATALOGID from ATTRIBUTECATEGORY;
print 'begin try';
print ' begin transaction';
print '';
print ' declare @CHANGEAGENTID uniqueidentifier;';
print '';
print ' exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;';
print '';

while exists(select 1 from @ATTRIBUTES where PROCESSED = 0)
begin
set @SQL = '';

declare @ID uniqueidentifier,
@NAME nvarchar(100),
@ATTRIBUTERECORDTYPEID nvarchar(36),
@DATATYPECODE tinyint,
@CODETABLECATALOGID nvarchar(36),
@ONLYALLOWONEPERRECORD bit,
@CONSTITUENTSEARCHLISTCATALOGID nvarchar(36);

select
top 1
@ID = ID,
@NAME = replace(NAME, '''', ''''''),
@ATTRIBUTERECORDTYPEID = coalesce(cast(ATTRIBUTERECORDTYPEID as nvarchar(36)), 'null'),
@DATATYPECODE = coalesce(DATATYPECODE, 'null'),
@CODETABLECATALOGID = coalesce(cast(CODETABLECATALOGID as nvarchar(36)), 'null'),
@ONLYALLOWONEPERRECORD = coalesce(ONLYALLOWONEPERRECORD, 'null'),
@CONSTITUENTSEARCHLISTCATALOGID = coalesce(cast(CONSTITUENTSEARCHLISTCATALOGID as nvarchar(36)), 'null')
from
@ATTRIBUTES
where
PROCESSED = 0;

set @SQL += ' --adding attribute: ''' + @NAME + '''';
set @SQL += char(10);
set @SQL += ' if not exists (select null from dbo.ATTRIBUTECATEGORY where ID = ''' + cast(@ID as nvarchar(36)) + ''')';
set @SQL += char(10);
set @SQL += ' begin';
set @SQL += char(10);
set @SQL += ' print ''adding the ' + @NAME + ' attribute''';
set @SQL += char(10);
set @SQL += char(10);
set @SQL += ' exec USP_DATAFORMTEMPLATE_ADD_ATTRIBUTECATEGORY ';
set @SQL += char(10);
set @SQL += ' @ID = ''' + cast(@ID as nvarchar(36)) + ''',';
set @SQL += char(10);
set @SQL += ' @NAME = ''' + @NAME + ''',';
set @SQL += char(10);
set @SQL += ' @ATTRIBUTERECORDTYPEID = ''' + cast(@ATTRIBUTERECORDTYPEID as nvarchar(36)) + ''',';
set @SQL += char(10);
set @SQL += ' @DATATYPECODE = ' + cast(@DATATYPECODE as nvarchar(2)) + ',';
set @SQL += char(10);
set @SQL += ' @CODETABLECATALOGID = ''' + @CODETABLECATALOGID + ''',';
set @SQL += char(10);
set @SQL += ' @ONLYALLOWONEPERRECORD = ' + cast(@ONLYALLOWONEPERRECORD as nvarchar(1)) + ',';
set @SQL += char(10);
set @SQL += ' @CONSTITUENTSEARCHLISTCATALOGID = ''' + @CONSTITUENTSEARCHLISTCATALOGID + ''',';
set @SQL += char(10);
set @SQL += ' @CHANGEAGENTID = @CHANGEAGENTID;';
set @SQL += char(10);
set @SQL += ' end';
set @SQL += char(10);
set @SQL += ' else';
set @SQL += char(10);
set @SQL += ' begin';
set @SQL += char(10);
set @SQL += ' print ''' + @NAME + ' already exists''';
set @SQL += char(10);
set @SQL += ' end';
set @SQL += char(10);

set @SQL = replace(@SQL, '''null''', 'null');

update
@ATTRIBUTES
set
PROCESSED = 1
where
ID = @ID;

print @SQL;
end

print ' commit transaction;';
print 'end try';
print 'begin catch';
print ' rollback tran;';
print ' exec USP_RAISE_ERROR;';
print 'end catch';

Posted in CRM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s