April 12, 2011 at 8:51 am
Hello,
I come across a logical question while working with CLR. I have a dll file, based on this dll file i have created an assembly using 'create assembly' funcion. I have 4 clr functions based upon this assmbly. Now i am in a situation where i need to take a back up of this DB and restore it on a server at different geographic location.
So the question is, do i need the dll file to send it through, in order to help clr function works fine?
My understanding says, as soon as we create an assembly we save the dll info into the binary format within the sql server. Once we have this binary we really dont need dll files any more.
Please advice.
Thanks
April 12, 2011 at 12:26 pm
As long as you are not using the assembly from any other .net application(s) on that server you do not need to worry about the dll file.
The probability of survival is inversely proportional to the angle of arrival.
April 20, 2011 at 2:28 pm
I have had the same question for some time and just did a test.
Created a SQL-CLR assembly from a dll file, then moved the dll to another location.
Restarted the instance, and reran the T-SQL code that executes the assembly. Code ran fine.
Ran "dbcc freesystemcache('all')" and reran the T-SQL. Code ran with no problems.
So you don't need the dll after assembly is registered on the SQL instance.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 20, 2011 at 2:50 pm
Technically you do not need the DLL at all, even in the first place. When you build and deploy a SQL CLR project from Visual Studio the assembly is serialized as a binary string and it is compiled into the server using T-SQL, i.e. the physical DLL is not used to create a CLR object in SQL Server. This makes CLR objects portable through T-SQL script. You can also see this through SSMS if you right click on an Assembly and Aggregate and choose to Script it. The CLR code is stored in the database as metadata just as a stored procedure definition would be.
Here is an example T-SQL script that creates an assembly and a SQL CLR UDA from a class in the assembly:
CREATE ASSEMBLY [group_concat]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103007C45A74D0000000000000000E00002210B010800002A000000060000000000004E48000000200000006000000000400000200000000200000400000000000000040000000000000000A000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000F447000057000000006000003803000000000000000000000000000000000000008000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000005428000000200000002A000000020000000000000000000000000000200000602E72737263000000380300000060000000040000002C0000000000000000000000000000400000402E72656C6F6300000C000000008000000002000000300000000000000000000000000000400000420000000000000000000000000000000030480000000000004800000002000500D83300001C14000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003202731100000A7D010000042A0000001330040047000000010000110F01281200000A2D3D0F01281300000A0A027B01000004066F1400000A2C1A027B01000004250B06250C07086F1500000A17586F1600000A2A027B0100000406176F1700000A2A001B30040071000000020000110F017B010000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B01000004076F1400000A2C1D027B01000004250D072513040911046F1500000A17586F1600000A2B0D027B0100000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3004009C00000003000011027B010000046F1D00000A163E84000000731E00000A0A027B010000046F1800000A13042B3A1204281900000A0B1201281A00000A26160C2B1C061201281A00000A7201000070281F00000A6F2000000A260817580C081201282100000A32DA1204281B00000A2DBDDE0E1204FE160200001B6F1C00000ADC066F2200000A0D0916096F2300000A17596F2400000A732500000A2A14282600000A2A0110000002002400476B000E00000000133003003900000004000011036F2700000A0A0206732800000A7D01000004160B2B1B027B01000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF2A0000001B3002005B0000000500001103027B010000046F1D00000A6F2A00000A027B010000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC2A000110000002001D002F4C000E00000000133002004D00000006000011027B030000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D030000042A4E02731100000A7D0200000402147D030000042A000000133004004F000000010000110F01281200000A2D3E0F01281300000A0A027B02000004066F1400000A2C1B027B02000004250B06250C07086F1500000A17586F1600000A2B0D027B0200000406176F1700000A020428070000062A001B30040071000000020000110F017B020000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B02000004076F1400000A2C1D027B02000004250D072513040911046F1500000A17586F1600000A2B0D027B0200000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3003004201000007000011027B020000043930010000027B020000046F1D00000A163E1F010000731E00000A0A027B02000004733000000A0C027B03000004720D000070282E00000A2D7F086F3100000A17590D2B52027B0200000409280100002B13091209281A00000A13041613052B190611047201000070281F00000A6F2000000A261105175813051105027B0200000409280100002B130A120A282100000A32CE0917590D09162FAA066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A086F3400000A130B2B3B120B283500000A13061206281A00000A13071613082B190611077201000070281F00000A6F2000000A2611081758130811081206282100000A32DC120B283600000A2DBCDE0E120BFE160500001B6F1C00000ADC066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A14282600000A2A0000011000000200C700480F010E00000000133003004500000004000011036F2700000A0A0206732800000A7D02000004160B2B1B027B02000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF02036F2900000A7D030000042A0000001B300200670000000500001103027B020000046F1D00000A6F2A00000A027B020000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC03027B030000046F2B00000A2A000110000002001D002F4C000E000000003202733700000A7D040000042A720F01281200000A2D12027B040000040F01281300000A6F3800000A2A5E027B04000004027B040000046F3900000A6F3A00000A2AC2027B040000046F3B00000A16311B7201000070027B040000046F3900000A283C00000A732500000A2A14282600000A2A00133003003300000004000011036F2700000A0A0206733D00000A7D04000004160B2B15027B04000004036F2900000A6F3800000A0717580B0706175931E52A001B300200480000000800001103027B040000046F3B00000A6F2A00000A027B040000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC2A0110000002001D001C39000E0000000072027B060000042D13020F01FE16040000016F2200000A7D060000042A4E02733700000A7D0500000402147D060000042A8E0F01281200000A2D12027B050000040F01281300000A6F3800000A020428140000062A5E027B05000004027B050000046F3900000A6F3A00000A2AC6027B050000046F3B00000A16311C027B06000004027B050000046F3900000A283C00000A732500000A2A14282600000A2A00133003003F00000004000011036F2700000A0A0206733D00000A7D05000004160B2B15027B05000004036F2900000A6F3800000A0717580B0706175931E502036F2900000A7D060000042A001B300200540000000800001103027B050000046F3B00000A6F2A00000A027B050000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC03027B060000046F2B00000A2A0110000002001D001C39000E0000000072027B080000042D13020F01FE16040000016F2200000A7D080000042A000000133002004D00000006000011027B090000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D090000042A6A02733700000A7D0700000402147D0800000402147D090000042AAA0F01281200000A2D12027B070000040F01281300000A6F3800000A0204281B0000060205281C0000062A62027B070000040F017B070000046F3900000A6F3A00000A2A13300300AB00000009000011027B070000046F3B00000A163E93000000027B070000046F4100000A027B09000004720D000070282E00000A2D5A731E00000A0A027B070000046F3B00000A17590B2B2406027B07000004076F4200000A6F2000000A2606027B080000046F2000000A260717590B07162FD806066F4300000A1759176F4400000A26066F2200000A732500000A2A027B08000004027B070000046F3900000A283C00000A732500000A2A14282600000A2A00133003004B00000004000011036F2700000A0A0206733D00000A7D07000004160B2B15027B07000004036F2900000A6F3800000A0717580B0706175931E502036F2900000A7D0800000402036F2900000A7D090000042A001B300200600000000800001103027B070000046F3B00000A6F2A00000A027B070000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC03027B080000046F2B00000A03027B090000046F2B00000A2A0110000002001D001C39000E00000000133002004D00000006000011027B0B0000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D0B0000042A4E02733700000A7D0A00000402147D0B0000042A8E0F01281200000A2D12027B0A0000040F01281300000A6F3800000A020428230000062A62027B0A0000040F017B0A0000046F3900000A6F3A00000A2A000013300300A900000009000011027B0A0000046F3B00000A163E91000000027B0A0000046F4100000A027B0B000004720D000070282E00000A2D59731E00000A0A027B0A0000046F3B00000A17590B2B2306027B0A000004076F4200000A6F2000000A260672010000706F2000000A260717590B07162FD906066F4300000A1759176F4400000A26066F2200000A732500000A2A7201000070027B0A0000046F3900000A283C00000A732500000A2A14282600000A2A000000133003003F00000004000011036F2700000A0A0206733D00000A7D0A000004160B2B15027B0A000004036F2900000A6F3800000A0717580B0706175931E502036F2900000A7D0B0000042A001B300200540000000800001103027B0A0000046F3B00000A6F2A00000A027B0A0000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC03027B0B0000046F2B00000A2A0110000002001D001C39000E0000000072027B0D0000042D13020F01FE16040000016F2200000A7D0D0000042A000000133002004D00000006000011027B0E0000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D0E0000042A6A02731100000A7D0C00000402147D0D00000402147D0E0000042A1330040056000000010000110F01281200000A2D3E0F01281300000A0A027B0C000004066F1400000A2C1B027B0C000004250B06250C07086F1500000A17586F1600000A2B0D027B0C00000406176F1700000A0204282A0000060205282B0000062A00001B30040071000000020000110F017B0C0000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B0C000004076F1400000A2C1D027B0C000004250D072513040911046F1500000A17586F1600000A2B0D027B0C00000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3003004701000007000011027B0C0000043935010000027B0C0000046F1D00000A163E24010000731E00000A0A027B0C000004733000000A0C027B0E000004720D000070282E00000A3A80000000086F3100000A17590D2B53027B0C00000409280100002B13091209281A00000A13041613052B1A061104027B0D000004281F00000A6F2000000A261105175813051105027B0C00000409280100002B130A120A282100000A32CD0917590D09162FA9066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A086F3400000A130B2B3C120B283500000A13061206281A00000A13071613082B1A061107027B0D000004281F00000A6F2000000A2611081758130811081206282100000A32DB120B283600000A2DBBDE0E120BFE160500001B6F1C00000ADC066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A14282600000A2A00011000000200CB004914010E00000000133003005100000004000011036F2700000A0A0206732800000A7D0C000004160B2B1B027B0C000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF02036F2900000A7D0D00000402036F2900000A7D0E0000042A0000001B300200730000000500001103027B0C0000046F1D00000A6F2A00000A027B0C0000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC03027B0D0000046F2B00000A03027B0E0000046F2B00000A2A000110000002001D002F4C000E0000000072027B100000042D13020F01FE16040000016F2200000A7D100000042A4E02731100000A7D0F00000402147D100000042A000000133004004F000000010000110F01281200000A2D3E0F01281300000A0A027B0F000004066F1400000A2C1B027B0F000004250B06250C07086F1500000A17586F1600000A2B0D027B0F00000406176F1700000A020428320000062A001B30040071000000020000110F017B0F0000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B0F000004076F1400000A2C1D027B0F000004250D072513040911046F1500000A17586F1600000A2B0D027B0F00000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3004009D00000003000011027B0F0000046F1D00000A163E85000000731E00000A0A027B0F0000046F1800000A13042B3B1204281900000A0B1201281A00000A26160C2B1D061201281A00000A027B10000004281F00000A6F2000000A260817580C081201282100000A32D91204281B00000A2DBCDE0E1204FE160200001B6F1C00000ADC066F2200000A0D0916096F2300000A17596F2400000A732500000A2A14282600000A2A0000000110000002002400486C000E00000000133003004500000004000011036F2700000A0A0206732800000A7D0F000004160B2B1B027B0F000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF02036F2900000A7D100000042A0000001B300200670000000500001103027B0F0000046F1D00000A6F2A00000A027B0F0000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC03027B100000046F2B00000A2A000110000002001D002F4C000E0000000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000400A0000237E0000AC0A0000E005000023537472696E6773000000008C1000006800000023555300F4100000100000002347554944000000041100001803000023426C6F6200000000000000020000015717A2090908000000FA25330016000001000000260000000900000010000000380000003800000008000000440000002A000000090000000600000008000000080000000700000001000000040000000100000000000A000100000000000600F300EC000A00240109010600500135010A007E0169010600AD01A3010600BF01A3010600EB0135010A001A02090106005402420206006B0242020600880242020600A70242020600C00242020600D90242020600F402420206000F03420206004703280306005B0342020600940374030600B40374030600D203EC000A00E80309010A00090409010600100428030600260428030F00680400000600810435010600AD04EC000600D704CB040600E504EC000600FA04EC0006005505EC000E005F053501060072053501120098058C050600A30535018700680400001F00680400000000000001000000000001000100092110001B00330005000100010009211000400033000500020007000921100033003300050004000E00092110005F0033000500050014000921100072003300050007001B00092110008C00330005000A00230009211000A000330005000C002A0009211000C500330005000F00320001005D010A0001005D010A000100D201330001005D01490001005D0149000100F201330001005D0149000100F20133000100D201330001005D0149000100D201330001005D010A000100F20133000100D201330001005D010A000100F201330050200000000086006401120001006020000000008600880116000100B42000000000860093011C0002004421000000008600990122000300FC2100000000E601BA0127000400442200000000E601CC012D000500BC22000000008108D9011600060015230000000086006401120007002C23000000008600880136000700882300000000860093013E0009001824000000008600990122000A00782500000000E601BA0127000B00CC2500000000E601CC012D000C005026000000008600640112000D005D26000000008600880116000D007A26000000008600930150000E009226000000008600990122000F00C42600000000E601BA0127001000042700000000E601CC012D0011006827000000008108FC011600120085270000000086006401120013009927000000008600880136001300BD27000000008600930156001500D527000000008600990122001600082800000000E601BA0127001700542800000000E601CC012D001800C428000000008108FC0116001900E428000000008108D90116001A003D29000000008600640112001B00582900000000860088015C001B008329000000008600930166001E009C29000000008600990122001F00542A00000000E601BA0127002000AC2A00000000E601CC012D002100282B000000008108D90116002200812B000000008600640112002300952B000000008600880136002300B92B00000000860093016C002500D42B0000000086009901220026008C2C00000000E601BA0127002700D82C00000000E601CC012D002800482D000000008108FC0116002900682D000000008108D90116002A00C12D000000008600640112002B00DC2D00000000860088015C002B00402E000000008600930172002E00D02E000000008600990122002F00343000000000E601BA0127003000943000000000E601CC012D0031002431000000008108FC011600320041310000000086006401120033005831000000008600880136003300B4310000000086009301780035004432000000008600990122003600003300000000E601BA0127003700543300000000E601CC012D003800000001001402000001003202000000000000000001003802000001003A02000001003C0200000100140200000200E401000001003202000000000000000001003802000001003A02000001001402000001003202000000000000000001003802000001003A02000001003C02000001001402000002000A02000001003202000000000000000001003802000001003A02000001003C02000001003C02000001001402000002000A0200000300E401000001003202000000000000000001003802000001003A02000001003C0200000100140200000200E401000001003202000000000000000001003802000001003A02000001003C02000001003C02000001001402000002000A0200000300E401000001003202000000000000000001003802000001003A02000001003C02000001001402000002000A02000001003202000000000000000001003802000001003A02020009000300090004000900050009000600090007000900080009000900090041002C02120049002C02CA0051002C02CA0059002C02CA0061002C02CA0069002C02CA0071002C02CA0079002C02CA0081002C02CA0089002C02CF0091002C02CA0099002C02D400A1002C021200A9002C021200B1002C02D900C1002C0252010C002C021200210031045F0121003C0463010C00460467010C0052046D010C005B0474010C00640474010C00730487011400900499011C009C04AB011400A4045F01E100B90412000C00C104C701E9002C021200F100EC04CB01E900F304D1011C003C04D701F90001056301F1000A05C701F1001505DC0121002C02CA0021001F05E20129002B05C7010C002C02D4002900350563013100CC01D4003100CC01CA00F10040056301F10045056301F1004D050F0201012C02CA0024002C0221022400C104C7011901B1052E02F100BB05DC012400730445022C00900499012C00A4045F0134002C021200340064048A023400C20590023400CA0596023400C104C701F100D305A10234002C02D40034007304A8023C009004AB013C00A4045F013400D805120034005204C302E9000A05C701E900BB05C90224000B007E002E005300E8022E006B00F7022E002B00E8022E006300EE022E003B00E8022E001B00E8022E003300D6022E002300E8022E004300E8022E001300D60243007B00DF0063007B00DF0064000B00910083007B00DF00A3007B00DF00C3007B00DF00E3007B00DF00E4000B007E0003017B00DF0004010B00A40023017B00DF0044010B009100A4010B007E00E4010B00910064020B007E0084020B00B700C4020B00910064030B007E0084030B00B700A4030B00A400E4030B00910064040B007E0084040B00A400C4040B00910064050B007E0084050B00B700A4050B00A400E4050B00910064060B007E0084060B00B700C4060B0091007C01B001E801FB01000215025902B902D0020300010005000200060003000700050008000600090008000000E401440000000A02440000000A0244000000E40144000000E401440000000A0244000000E401440000000A02440001000700030001001400050001001B00070001001C000900010023000B0001002A000D0001002B000F0001003200110058019201A401190251028402B202048000000100000019100655000000000000330000000200000000000000000000000100E300000000000200000000000000000000000100FD00000000000200000000000000000000000100EC0000000000030005000000000000000000010080050000000065003C020000003C4D6F64756C653E0067726F75705F636F6E6361742E646C6C0067726F75705F636F6E6361745F64696374696F6E6172790067726F75705F636F6E6361740067726F75705F636F6E6361745F64696374696F6E6172795F736F727465640067726F75705F636F6E6361745F64656C696D0067726F75705F636F6E6361745F64656C696D5F736F727465640067726F75705F636F6E6361745F736F727465640067726F75705F636F6E6361745F64696374696F6E6172795F64656C696D5F736F727465640067726F75705F636F6E6361745F64696374696F6E6172795F64656C696D006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E436F6C6C656374696F6E732E47656E657269630044696374696F6E61727960320076616C75657300496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E61727957726974657200577269746500736F72744279007365745F536F7274427900536F72744279004C69737460310064656C696D69746572007365745F44656C696D697465720044656C696D697465720056616C75650053716C4661636574417474726962757465002E63746F720047726F7570007200770076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E64006765745F49734E756C6C006765745F56616C756500436F6E7461696E734B6579006765745F4974656D007365745F4974656D0041646400456E756D657261746F7200476574456E756D657261746F72004B657956616C7565506169726032006765745F43757272656E74006765745F4B6579004D6F76654E6578740049446973706F7361626C6500446973706F7365006765745F436F756E740053797374656D2E5465787400537472696E674275696C64657200537472696E6700436F6E63617400417070656E64004F626A65637400546F537472696E67006765745F4C656E67746800537562737472696E67006F705F496D706C696369740052656164496E7433320052656164537472696E67005472696D00546F4C6F77657200436F6D7061726500457863657074696F6E00536F7274656444696374696F6E6172796032004944696374696F6E61727960320053797374656D2E436F72650053797374656D2E4C696E7100456E756D657261626C650049456E756D657261626C65603100456C656D656E7441740052656D6F766500546F41727261790041646452616E6765004A6F696E00536F72740000000000032C00000761007300630000096400650073006300004D49006E00760061006C0069006400200053006F0072007400420079002000760061006C00750065003A002000750073006500200041005300430020006F007200200044004500530043002E00000000004268D5D1111F1345919D8453F7F6CC350008B77A5C561934E089070615120D020E0803200001052001011111052001011108042000111105200101121505200101121902060E072002011111111105200101110C0428001111060615121D010E0520010111100520010111140920030111111111111105200101111805200101111C05200101112005200101112412010001005408074D617853697A65A00F000012010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A650400000012010001005408074D617853697A6501000000042001010E0420010102042001010805200101115D72010002000000050054080B4D61784279746553697A65FFFFFFFF5402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720154020D49734E756C6C4966456D707479010520010111650615120D020E08032000020320000E0520010213000620011301130007200201130013010A07030E15120D020E080E0A2000151169021300130106151169020E080A200015116D02130013010615116D020E08042000130016070515116D020E080E151169020E0815120D020E080E032000080500020E0E0E05200112750E04200013010520020E080805000111110E120705127515116D020E08080E151169020E0804070208080E070215116D020E08151169020E08050002080E0E0307010E0715128085020E080C2001011512808902130013010D1001021E0015128091011E0008080A0115116D020E080B20001511809502130013010715118095020E082A070C12750E15128085020E08080E0815116D020E080E0815116D020E0815116D020E0815118095020E080515121D010E0520010113000520001D13000A200101151280910113000600020E0E1D0E092000151180990113000615118099010E0907020E15118099010E052001130008062002127508080507021275081101000C67726F75705F636F6E63617400000501000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100001C48000000000000000000003E480000002000000000000000000000000000000000000000000000304800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000E00200000000000000000000E00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000655191000000100065519103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00440020000010053007400720069006E006700460069006C00650049006E0066006F0000001C020000010030003000300030003000340062003000000044000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000670072006F00750070005F0063006F006E006300610074000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100320031002E00320031003700360036000000000044001100010049006E007400650072006E0061006C004E0061006D0065000000670072006F00750070005F0063006F006E006300610074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000670072006F00750070005F0063006F006E006300610074002E0064006C006C00000000003C000D000100500072006F0064007500630074004E0061006D00650000000000670072006F00750070005F0063006F006E006300610074000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100320031002E00320031003700360036000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100320031002E0032003100370036003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000503800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE ;
GO
CREATE AGGREGATE [dbo].[group_concat_dictionary](@Value [nvarchar](4000))
RETURNS[nvarchar](MAX)
EXTERNAL NAME [group_concat].[group_concat.group_concat_dictionary]
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 2:58 pm
opc.three (4/20/2011)
Technically you do not need the DLL at all, even in the first place. When you build and deploy a SQL CLR project from Visual Studio the assembly is serialized as a binary string and it is compiled into the server using T-SQL, i.e. the physical DLL is not used to create a CLR object in SQL Server. This makes CLR objects portable through T-SQL script. You can also see this through SSMS if you right click on an Assembly and Aggregate and choose to Script it. The CLR code is stored in the database as metadata just as a stored procedure definition would be.Here is an example T-SQL script that creates an assembly and a SQL CLR UDA from a class in the assembly:
CREATE ASSEMBLY [group_concat]AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103007C45A74D0000000000000000E00002210B010800002A000000060000000000004E48000000200000006000000000400000200000000200000400000000000000040000000000000000A000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000F447000057000000006000003803000000000000000000000000000000000000008000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000005428000000200000002A000000020000000000000000000000000000200000602E72737263000000380300000060000000040000002C0000000000000000000000000000400000402E72656C6F6300000C000000008000000002000000300000000000000000000000000000400000420000000000000000000000000000000030480000000000004800000002000500D83300001C14000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003202731100000A7D010000042A0000001330040047000000010000110F01281200000A2D3D0F01281300000A0A027B01000004066F1400000A2C1A027B01000004250B06250C07086F1500000A17586F1600000A2A027B0100000406176F1700000A2A001B30040071000000020000110F017B010000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B01000004076F1400000A2C1D027B01000004250D072513040911046F1500000A17586F1600000A2B0D027B0100000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3004009C00000003000011027B010000046F1D00000A163E84000000731E00000A0A027B010000046F1800000A13042B3A1204281900000A0B1201281A00000A26160C2B1C061201281A00000A7201000070281F00000A6F2000000A260817580C081201282100000A32DA1204281B00000A2DBDDE0E1204FE160200001B6F1C00000ADC066F2200000A0D0916096F2300000A17596F2400000A732500000A2A14282600000A2A0110000002002400476B000E00000000133003003900000004000011036F2700000A0A0206732800000A7D01000004160B2B1B027B01000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF2A0000001B3002005B0000000500001103027B010000046F1D00000A6F2A00000A027B010000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC2A000110000002001D002F4C000E00000000133002004D00000006000011027B030000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D030000042A4E02731100000A7D0200000402147D030000042A000000133004004F000000010000110F01281200000A2D3E0F01281300000A0A027B02000004066F1400000A2C1B027B02000004250B06250C07086F1500000A17586F1600000A2B0D027B0200000406176F1700000A020428070000062A001B30040071000000020000110F017B020000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B02000004076F1400000A2C1D027B02000004250D072513040911046F1500000A17586F1600000A2B0D027B0200000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3003004201000007000011027B020000043930010000027B020000046F1D00000A163E1F010000731E00000A0A027B02000004733000000A0C027B03000004720D000070282E00000A2D7F086F3100000A17590D2B52027B0200000409280100002B13091209281A00000A13041613052B190611047201000070281F00000A6F2000000A261105175813051105027B0200000409280100002B130A120A282100000A32CE0917590D09162FAA066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A086F3400000A130B2B3B120B283500000A13061206281A00000A13071613082B190611077201000070281F00000A6F2000000A2611081758130811081206282100000A32DC120B283600000A2DBCDE0E120BFE160500001B6F1C00000ADC066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A14282600000A2A0000011000000200C700480F010E00000000133003004500000004000011036F2700000A0A0206732800000A7D02000004160B2B1B027B02000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF02036F2900000A7D030000042A0000001B300200670000000500001103027B020000046F1D00000A6F2A00000A027B020000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC03027B030000046F2B00000A2A000110000002001D002F4C000E000000003202733700000A7D040000042A720F01281200000A2D12027B040000040F01281300000A6F3800000A2A5E027B04000004027B040000046F3900000A6F3A00000A2AC2027B040000046F3B00000A16311B7201000070027B040000046F3900000A283C00000A732500000A2A14282600000A2A00133003003300000004000011036F2700000A0A0206733D00000A7D04000004160B2B15027B04000004036F2900000A6F3800000A0717580B0706175931E52A001B300200480000000800001103027B040000046F3B00000A6F2A00000A027B040000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC2A0110000002001D001C39000E0000000072027B060000042D13020F01FE16040000016F2200000A7D060000042A4E02733700000A7D0500000402147D060000042A8E0F01281200000A2D12027B050000040F01281300000A6F3800000A020428140000062A5E027B05000004027B050000046F3900000A6F3A00000A2AC6027B050000046F3B00000A16311C027B06000004027B050000046F3900000A283C00000A732500000A2A14282600000A2A00133003003F00000004000011036F2700000A0A0206733D00000A7D05000004160B2B15027B05000004036F2900000A6F3800000A0717580B0706175931E502036F2900000A7D060000042A001B300200540000000800001103027B050000046F3B00000A6F2A00000A027B050000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC03027B060000046F2B00000A2A0110000002001D001C39000E0000000072027B080000042D13020F01FE16040000016F2200000A7D080000042A000000133002004D00000006000011027B090000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D090000042A6A02733700000A7D0700000402147D0800000402147D090000042AAA0F01281200000A2D12027B070000040F01281300000A6F3800000A0204281B0000060205281C0000062A62027B070000040F017B070000046F3900000A6F3A00000A2A13300300AB00000009000011027B070000046F3B00000A163E93000000027B070000046F4100000A027B09000004720D000070282E00000A2D5A731E00000A0A027B070000046F3B00000A17590B2B2406027B07000004076F4200000A6F2000000A2606027B080000046F2000000A260717590B07162FD806066F4300000A1759176F4400000A26066F2200000A732500000A2A027B08000004027B070000046F3900000A283C00000A732500000A2A14282600000A2A00133003004B00000004000011036F2700000A0A0206733D00000A7D07000004160B2B15027B07000004036F2900000A6F3800000A0717580B0706175931E502036F2900000A7D0800000402036F2900000A7D090000042A001B300200600000000800001103027B070000046F3B00000A6F2A00000A027B070000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC03027B080000046F2B00000A03027B090000046F2B00000A2A0110000002001D001C39000E00000000133002004D00000006000011027B0B0000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D0B0000042A4E02733700000A7D0A00000402147D0B0000042A8E0F01281200000A2D12027B0A0000040F01281300000A6F3800000A020428230000062A62027B0A0000040F017B0A0000046F3900000A6F3A00000A2A000013300300A900000009000011027B0A0000046F3B00000A163E91000000027B0A0000046F4100000A027B0B000004720D000070282E00000A2D59731E00000A0A027B0A0000046F3B00000A17590B2B2306027B0A000004076F4200000A6F2000000A260672010000706F2000000A260717590B07162FD906066F4300000A1759176F4400000A26066F2200000A732500000A2A7201000070027B0A0000046F3900000A283C00000A732500000A2A14282600000A2A000000133003003F00000004000011036F2700000A0A0206733D00000A7D0A000004160B2B15027B0A000004036F2900000A6F3800000A0717580B0706175931E502036F2900000A7D0B0000042A001B300200540000000800001103027B0A0000046F3B00000A6F2A00000A027B0A0000046F3E00000A0B2B0F1201283F00000A0A03066F2B00000A1201284000000A2DE8DE0E1201FE160700001B6F1C00000ADC03027B0B0000046F2B00000A2A0110000002001D001C39000E0000000072027B0D0000042D13020F01FE16040000016F2200000A7D0D0000042A000000133002004D00000006000011027B0E0000042D440F01FE16040000016F2200000A6F2C00000A6F2D00000A0A067205000070282E00000A2C1806720D000070282E00000A2C0B7217000070732F00000A7A02067D0E0000042A6A02731100000A7D0C00000402147D0D00000402147D0E0000042A1330040056000000010000110F01281200000A2D3E0F01281300000A0A027B0C000004066F1400000A2C1B027B0C000004250B06250C07086F1500000A17586F1600000A2B0D027B0C00000406176F1700000A0204282A0000060205282B0000062A00001B30040071000000020000110F017B0C0000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B0C000004076F1400000A2C1D027B0C000004250D072513040911046F1500000A17586F1600000A2B0D027B0C00000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3003004701000007000011027B0C0000043935010000027B0C0000046F1D00000A163E24010000731E00000A0A027B0C000004733000000A0C027B0E000004720D000070282E00000A3A80000000086F3100000A17590D2B53027B0C00000409280100002B13091209281A00000A13041613052B1A061104027B0D000004281F00000A6F2000000A261105175813051105027B0C00000409280100002B130A120A282100000A32CD0917590D09162FA9066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A086F3400000A130B2B3C120B283500000A13061206281A00000A13071613082B1A061107027B0D000004281F00000A6F2000000A2611081758130811081206282100000A32DB120B283600000A2DBBDE0E120BFE160500001B6F1C00000ADC066F2200000A0B07076F2300000A1759176F3300000A0B07732500000A2A14282600000A2A00011000000200CB004914010E00000000133003005100000004000011036F2700000A0A0206732800000A7D0C000004160B2B1B027B0C000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF02036F2900000A7D0D00000402036F2900000A7D0E0000042A0000001B300200730000000500001103027B0C0000046F1D00000A6F2A00000A027B0C0000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC03027B0D0000046F2B00000A03027B0E0000046F2B00000A2A000110000002001D002F4C000E0000000072027B100000042D13020F01FE16040000016F2200000A7D100000042A4E02731100000A7D0F00000402147D100000042A000000133004004F000000010000110F01281200000A2D3E0F01281300000A0A027B0F000004066F1400000A2C1B027B0F000004250B06250C07086F1500000A17586F1600000A2B0D027B0F00000406176F1700000A020428320000062A001B30040071000000020000110F017B0F0000046F1800000A0C2B481202281900000A0A1200281A00000A0B027B0F000004076F1400000A2C1D027B0F000004250D072513040911046F1500000A17586F1600000A2B0D027B0F00000407176F1700000A1202281B00000A2DAFDE0E1202FE160200001B6F1C00000ADC2A0000000110000002000D005562000E000000001B3004009D00000003000011027B0F0000046F1D00000A163E85000000731E00000A0A027B0F0000046F1800000A13042B3B1204281900000A0B1201281A00000A26160C2B1D061201281A00000A027B10000004281F00000A6F2000000A260817580C081201282100000A32D91204281B00000A2DBCDE0E1204FE160200001B6F1C00000ADC066F2200000A0D0916096F2300000A17596F2400000A732500000A2A14282600000A2A0000000110000002002400486C000E00000000133003004500000004000011036F2700000A0A0206732800000A7D0F000004160B2B1B027B0F000004036F2900000A036F2700000A6F1700000A0717580B0706175931DF02036F2900000A7D100000042A0000001B300200670000000500001103027B0F0000046F1D00000A6F2A00000A027B0F0000046F1800000A0B2B221201281900000A0A031200281A00000A6F2B00000A031200282100000A6F2A00000A1201281B00000A2DD5DE0E1201FE160200001B6F1C00000ADC03027B100000046F2B00000A2A000110000002001D002F4C000E0000000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000400A0000237E0000AC0A0000E005000023537472696E6773000000008C1000006800000023555300F4100000100000002347554944000000041100001803000023426C6F6200000000000000020000015717A2090908000000FA25330016000001000000260000000900000010000000380000003800000008000000440000002A000000090000000600000008000000080000000700000001000000040000000100000000000A000100000000000600F300EC000A00240109010600500135010A007E0169010600AD01A3010600BF01A3010600EB0135010A001A02090106005402420206006B0242020600880242020600A70242020600C00242020600D90242020600F402420206000F03420206004703280306005B0342020600940374030600B40374030600D203EC000A00E80309010A00090409010600100428030600260428030F00680400000600810435010600AD04EC000600D704CB040600E504EC000600FA04EC0006005505EC000E005F053501060072053501120098058C050600A30535018700680400001F00680400000000000001000000000001000100092110001B00330005000100010009211000400033000500020007000921100033003300050004000E00092110005F0033000500050014000921100072003300050007001B00092110008C00330005000A00230009211000A000330005000C002A0009211000C500330005000F00320001005D010A0001005D010A000100D201330001005D01490001005D0149000100F201330001005D0149000100F20133000100D201330001005D0149000100D201330001005D010A000100F20133000100D201330001005D010A000100F201330050200000000086006401120001006020000000008600880116000100B42000000000860093011C0002004421000000008600990122000300FC2100000000E601BA0127000400442200000000E601CC012D000500BC22000000008108D9011600060015230000000086006401120007002C23000000008600880136000700882300000000860093013E0009001824000000008600990122000A00782500000000E601BA0127000B00CC2500000000E601CC012D000C005026000000008600640112000D005D26000000008600880116000D007A26000000008600930150000E009226000000008600990122000F00C42600000000E601BA0127001000042700000000E601CC012D0011006827000000008108FC011600120085270000000086006401120013009927000000008600880136001300BD27000000008600930156001500D527000000008600990122001600082800000000E601BA0127001700542800000000E601CC012D001800C428000000008108FC0116001900E428000000008108D90116001A003D29000000008600640112001B00582900000000860088015C001B008329000000008600930166001E009C29000000008600990122001F00542A00000000E601BA0127002000AC2A00000000E601CC012D002100282B000000008108D90116002200812B000000008600640112002300952B000000008600880136002300B92B00000000860093016C002500D42B0000000086009901220026008C2C00000000E601BA0127002700D82C00000000E601CC012D002800482D000000008108FC0116002900682D000000008108D90116002A00C12D000000008600640112002B00DC2D00000000860088015C002B00402E000000008600930172002E00D02E000000008600990122002F00343000000000E601BA0127003000943000000000E601CC012D0031002431000000008108FC011600320041310000000086006401120033005831000000008600880136003300B4310000000086009301780035004432000000008600990122003600003300000000E601BA0127003700543300000000E601CC012D003800000001001402000001003202000000000000000001003802000001003A02000001003C0200000100140200000200E401000001003202000000000000000001003802000001003A02000001001402000001003202000000000000000001003802000001003A02000001003C02000001001402000002000A02000001003202000000000000000001003802000001003A02000001003C02000001003C02000001001402000002000A0200000300E401000001003202000000000000000001003802000001003A02000001003C0200000100140200000200E401000001003202000000000000000001003802000001003A02000001003C02000001003C02000001001402000002000A0200000300E401000001003202000000000000000001003802000001003A02000001003C02000001001402000002000A02000001003202000000000000000001003802000001003A02020009000300090004000900050009000600090007000900080009000900090041002C02120049002C02CA0051002C02CA0059002C02CA0061002C02CA0069002C02CA0071002C02CA0079002C02CA0081002C02CA0089002C02CF0091002C02CA0099002C02D400A1002C021200A9002C021200B1002C02D900C1002C0252010C002C021200210031045F0121003C0463010C00460467010C0052046D010C005B0474010C00640474010C00730487011400900499011C009C04AB011400A4045F01E100B90412000C00C104C701E9002C021200F100EC04CB01E900F304D1011C003C04D701F90001056301F1000A05C701F1001505DC0121002C02CA0021001F05E20129002B05C7010C002C02D4002900350563013100CC01D4003100CC01CA00F10040056301F10045056301F1004D050F0201012C02CA0024002C0221022400C104C7011901B1052E02F100BB05DC012400730445022C00900499012C00A4045F0134002C021200340064048A023400C20590023400CA0596023400C104C701F100D305A10234002C02D40034007304A8023C009004AB013C00A4045F013400D805120034005204C302E9000A05C701E900BB05C90224000B007E002E005300E8022E006B00F7022E002B00E8022E006300EE022E003B00E8022E001B00E8022E003300D6022E002300E8022E004300E8022E001300D60243007B00DF0063007B00DF0064000B00910083007B00DF00A3007B00DF00C3007B00DF00E3007B00DF00E4000B007E0003017B00DF0004010B00A40023017B00DF0044010B009100A4010B007E00E4010B00910064020B007E0084020B00B700C4020B00910064030B007E0084030B00B700A4030B00A400E4030B00910064040B007E0084040B00A400C4040B00910064050B007E0084050B00B700A4050B00A400E4050B00910064060B007E0084060B00B700C4060B0091007C01B001E801FB01000215025902B902D0020300010005000200060003000700050008000600090008000000E401440000000A02440000000A0244000000E40144000000E401440000000A0244000000E401440000000A02440001000700030001001400050001001B00070001001C000900010023000B0001002A000D0001002B000F0001003200110058019201A401190251028402B202048000000100000019100655000000000000330000000200000000000000000000000100E300000000000200000000000000000000000100FD00000000000200000000000000000000000100EC0000000000030005000000000000000000010080050000000065003C020000003C4D6F64756C653E0067726F75705F636F6E6361742E646C6C0067726F75705F636F6E6361745F64696374696F6E6172790067726F75705F636F6E6361740067726F75705F636F6E6361745F64696374696F6E6172795F736F727465640067726F75705F636F6E6361745F64656C696D0067726F75705F636F6E6361745F64656C696D5F736F727465640067726F75705F636F6E6361745F736F727465640067726F75705F636F6E6361745F64696374696F6E6172795F64656C696D5F736F727465640067726F75705F636F6E6361745F64696374696F6E6172795F64656C696D006D73636F726C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E61727953657269616C697A650053797374656D2E436F6C6C656374696F6E732E47656E657269630044696374696F6E61727960320076616C75657300496E69740053797374656D2E446174612E53716C54797065730053716C537472696E6700416363756D756C617465004D65726765005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E61727957726974657200577269746500736F72744279007365745F536F7274427900536F72744279004C69737460310064656C696D69746572007365745F44656C696D697465720044656C696D697465720056616C75650053716C4661636574417474726962757465002E63746F720047726F7570007200770076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053657269616C697A61626C654174747269627574650053716C55736572446566696E656441676772656761746541747472696275746500466F726D6174005374727563744C61796F7574417474726962757465004C61796F75744B696E64006765745F49734E756C6C006765745F56616C756500436F6E7461696E734B6579006765745F4974656D007365745F4974656D0041646400456E756D657261746F7200476574456E756D657261746F72004B657956616C7565506169726032006765745F43757272656E74006765745F4B6579004D6F76654E6578740049446973706F7361626C6500446973706F7365006765745F436F756E740053797374656D2E5465787400537472696E674275696C64657200537472696E6700436F6E63617400417070656E64004F626A65637400546F537472696E67006765745F4C656E67746800537562737472696E67006F705F496D706C696369740052656164496E7433320052656164537472696E67005472696D00546F4C6F77657200436F6D7061726500457863657074696F6E00536F7274656444696374696F6E6172796032004944696374696F6E61727960320053797374656D2E436F72650053797374656D2E4C696E7100456E756D657261626C650049456E756D657261626C65603100456C656D656E7441740052656D6F766500546F41727261790041646452616E6765004A6F696E00536F72740000000000032C00000761007300630000096400650073006300004D49006E00760061006C0069006400200053006F0072007400420079002000760061006C00750065003A002000750073006500200041005300430020006F007200200044004500530043002E00000000004268D5D1111F1345919D8453F7F6CC350008B77A5C561934E089070615120D020E0803200001052001011111052001011108042000111105200101121505200101121902060E072002011111111105200101110C0428001111060615121D010E0520010111100520010111140920030111111111111105200101111805200101111C05200101112005200101112412010001005408074D617853697A65A00F000012010001005408074D617853697A65FFFFFFFF12010001005408074D617853697A650400000012010001005408074D617853697A6501000000042001010E0420010102042001010805200101115D72010002000000050054080B4D61784279746553697A65FFFFFFFF5402124973496E76617269616E74546F4E756C6C73015402174973496E76617269616E74546F4475706C696361746573005402124973496E76617269616E74546F4F726465720154020D49734E756C6C4966456D707479010520010111650615120D020E08032000020320000E0520010213000620011301130007200201130013010A07030E15120D020E080E0A2000151169021300130106151169020E080A200015116D02130013010615116D020E08042000130016070515116D020E080E151169020E0815120D020E080E032000080500020E0E0E05200112750E04200013010520020E080805000111110E120705127515116D020E08080E151169020E0804070208080E070215116D020E08151169020E08050002080E0E0307010E0715128085020E080C2001011512808902130013010D1001021E0015128091011E0008080A0115116D020E080B20001511809502130013010715118095020E082A070C12750E15128085020E08080E0815116D020E080E0815116D020E0815116D020E0815118095020E080515121D010E0520010113000520001D13000A200101151280910113000600020E0E1D0E092000151180990113000615118099010E0907020E15118099010E052001130008062002127508080507021275081101000C67726F75705F636F6E63617400000501000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100001C48000000000000000000003E480000002000000000000000000000000000000000000000000000304800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000E00200000000000000000000E00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000655191000000100065519103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00440020000010053007400720069006E006700460069006C00650049006E0066006F0000001C020000010030003000300030003000340062003000000044000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000670072006F00750070005F0063006F006E006300610074000000000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003100320031002E00320031003700360036000000000044001100010049006E007400650072006E0061006C004E0061006D0065000000670072006F00750070005F0063006F006E006300610074002E0064006C006C00000000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000670072006F00750070005F0063006F006E006300610074002E0064006C006C00000000003C000D000100500072006F0064007500630074004E0061006D00650000000000670072006F00750070005F0063006F006E006300610074000000000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003100320031002E00320031003700360036000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003100320031002E0032003100370036003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000503800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE ;
GO
CREATE AGGREGATE [dbo].[group_concat_dictionary](@Value [nvarchar](4000))
RETURNS[nvarchar](MAX)
EXTERNAL NAME [group_concat].[group_concat.group_concat_dictionary]
GO
Thank you, that's good to know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 20, 2011 at 2:59 pm
Excellent point. I had noticed that but for some reason never took it to the next level. It makes sense since every other database object can be [re]created in the form of a script.
The probability of survival is inversely proportional to the angle of arrival.
April 20, 2011 at 5:02 pm
Are there any scenarios where having the dll file present is required?
I have had reports from our dev/QA teams that after a certain database is restored from production to the test environment, this extra step needs to be taken before assembly becomes operational again:
…
ALTER ASSEMBLY assemblyName1
FROM @CLRFilePath
WITH PERMISSION_SET = UNSAFE;
…
Is there another way to resolve this without referencing the file path of the dll file?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 20, 2011 at 5:11 pm
That's a good question...I do not have much experience using with UNSAFE assemblies. I am wondering whether SQL Server treats them different somehow.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2011 at 6:36 pm
See if this article is relevant to your situation:
http://support.microsoft.com/kb/918040
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 22, 2011 at 12:36 pm
I believe what may be causing that is that when a database is restored it can end up with a new owner... if the login that restored it is different than the database owner of the original database.
This will make any assembly unavailable until the db owner is changed back. (sp_changedbowner) or the assenbly is re-created under the new owner. You may also have to alter the database and make it trustworthy depending upon other things you are doing in the database or with you application.
The probability of survival is inversely proportional to the angle of arrival.
April 22, 2011 at 9:27 pm
sturner, you and I are on the same track. The article in my previous post deals with the issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply