Avatar of lankapala
lankapala
 asked on

sql server issue

Hi, I have done all the for above attached issue.
see below sql scripts for used for the issue. I have restrated the server the server already after scripts.

SELECT 'TEST' AS Test EXECUTE AS USER = 'dbo'
EXEC sp_changedbowner 'sa'
ALTER DATABASE [Company DB] SET TRUSTWORTHY ON
ALTER AUTHORIZATION ON DATABASE::[Company DB] TO sa;

USE [Company DB];
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO

But still getting same error message (See attached)
image006.jpg
SQLMicrosoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
lankapala

8/22/2022 - Mon
Vitor Montalvão

Should be:
RECONFIGURE WITH OVERRIDE
lankapala

ASKER
so you think
USE [Company DB];
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO
Vitor Montalvão

I'm not sure what you're trying to do (you didn't provide the code line that raised the error) but the RECONFIGURE command needs the WITH OVERRIDE to get the changes applied immediately.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
lankapala

ASKER
yes , try this also not working same issue
USE [Company DB];
EXEC sp_configure 'clr enabled' ,1
GO

RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'clr enabled'   -- make sure it took
GO
lankapala

ASKER
Error is i have attached
Vitor Montalvão

Error is i have attached
Yes, I saw it but that's the error message. We need to know the line of code that raised the error so we can try to fix it.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lankapala

ASKER
this not coming of from any code,this coming when user trying to enter new records.
for example

INSERT INTO test1 (name) Values('1111')
Vitor Montalvão

That's weird because there's no CLR call in your example. I'm expecting a stored procedure call instead of a simple INSERT command.
lankapala

ASKER
Hi, This is Assembly

CREATE ASSEMBLY [TimeZone]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103009FAA0E500000000000000000E00002210B010800001C000000060000000000009E3B000000200000004000000000400000200000000200000400000000000000040000000000000000800000000200005B350000030040850000100000100000000010000010000000000000100000000000000000000000443B00005700000000400000A803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A41B000000200000001C000000020000000000000000000000000000200000602E72737263000000A80300000040000000040000001E0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000002200000000000000000000000000004000004200000000000000000000000000000000803B000000000000480000000200050088270000BC1300000900000000000000000000000000000050200000800000000000000000000000000000000000000000000000000000000000000000000000AB854990567EC148F2E1C71E01811DFE3F161B7409C70DD760B4A64DA2CD8E21321194A04022BCB13F3E3FF5A4DB3048DD7320AAE083D61BB386B5A26B21DD740615692A64CB14B708C6D9A73529B7C65F5D7AC428F1A6CDDE1D1C78EE20C877C5DC9A2DE962703B12AE2F508EFAB257262B9DEC8DF9C08A2E7FBFF91E03F861820F00280F00000A0F01281000000A0F02281000000A2803000006731100000A2A1E02281200000A2A2602030428070000062A001330070025010000010000110F007B030000042C300F007B030000040F007B040000040F007B060000040F007B070000040F007B080000040F007B09000004731300000A2A0F007B0600000417327B0F007B060000041A307112000F01281400000A0F007B04000004170F007B070000040F007B0800000416281300000A12000F007B0600000417596C230000000000001C405A281500000A0A170B2B261200281600000A680F007B050000042E19120023000000000000F03F281500000A0A0717580B071C32D6062A12000F01281400000A0F007B040000040F01281400000A0F007B04000004281700000A0F007B070000040F007B0800000416281300000A170C2B261200281600000A680F007B050000042E19120023000000000000F0BF281500000A0A0817580C081C32D6062A3E02031717731800000A28040000062A0000001B3003008C01000002000011720100007002281900000A0A140B7E1A00000A066F1B00000A0C082D11727300007002281C00000A731D00000A7A0872BB0000706F1E00000A0BDE0A082C06086F1F00000ADC07740200001B0D1204FE150500000212040916282000000A7D0B0000041204091E282000000A7D0D00000412047C0E000004091F0C282100000A7D0300000412047C0E000004091F0E282100000A7D0400000412047C0E000004091F10282100000A7D0500000412047C0E000004091F12282100000A7D0600000412047C0E000004091F14282100000A7D0700000412047C0E000004091F16282100000A7D0800000412047C0E000004091F18282100000A7D0900000412047C0F000004091F1C282100000A7D0300000412047C0F000004091F1E282100000A7D0400000412047C0F000004091F20282100000A7D0500000412047C0F000004091F22282100000A7D0600000412047C0F000004091F24282100000A7D0700000412047C0F000004091F26282100000A7D0800000412047C0F000004091F28282100000A7D0900000411042A0110000002001A00223C000A000000001B3003009900000003000011032C0C042C090304282200000A2C02022A140A7E02000004036F2300000A0B076F2400000A2C09076F2400000A0A2B1203730A0000060A7E02000004066F2500000ADE0A072C06076F1F00000ADC140C7E02000004046F2300000A0D096F2400000A2C09096F2400000A0C2B1204730A0000060C7E02000004086F2500000ADE0A092C06096F1F00000ADC0806026F2600000A6F160000062A000000011C000002001F002544000A0000000002005C002581000A000000007A72C3000070230000000000001C40282700000A732800000A80020000042A1E02281200000A2A00133007008A0000000400001102282A00000A02037D100000040328060000060A282B00000A0C1202281400000A0B12007C0F0000047B0600000416314312007C0E0000047B060000041631340212007B0F00000407280500000612007B0E0000040728050000061612007B0D0000046516732C00000A732D00000A7D11000004021612007B0B0000046516732C00000A7D120000042A2E72DF000070731D00000A7A1E027B110000042A0000133003002C0000000500001102036F2E00000A0A062D08027B120000042B16027C12000004027B110000046F2F00000A283000000A0B072A1E027B100000042A2602031628100000062A00001330040060000000060000110F01283100000A173302162A0228140000062D02162A020F01281400000A6F3200000A0A03066F3300000A283400000A2C2C03066F3500000A0B1201042D09166A733600000A2B0B066F2F00000A283700000A283800000A283900000A2A162A220203282600000A2A2E7237010070731D00000A7A000000133003004F000000060000110F01283100000A173302162A0228140000062D02172A020F01281400000A6F3200000A0A03066F3300000A283900000A2D1B03066F3300000A0B1201066F2F00000A283800000A283400000A2A172A36027B1100000414FE0116FE012A0000001330030054000000060000110F01283100000A173302162A0228140000062D02162A020F01281400000A6F3200000A0A03066F3500000A283900000A2C2003066F3500000A0B1201066F2F00000A283700000A283800000A283400000A2A162A133003003B000000070000110F01283A00000A18733B00000A0B1201027B12000004283800000A0A02061728100000062C131200027B110000046F2F00000A283800000A0A062A1E027B100000042A062A1E027B130000042A2202037D130000042A000042534A4201000100000000000C00000076322E302E35303732370000000005006C0000006C060000237E0000D80600006007000023537472696E677300000000380E0000CC010000235553000410000010000000234755494400000014100000A803000023426C6F620000000000000002000001571FA2090902000000FA253300160000010000002300000006000000130000001A0000001A000000010000003B000000010000000C000000070000000100000004000000050000000400000001000000030000000200000000000A00010000000000060085007E0006008C007E00060096007E000A00B40039000E00E500D0000E00F100D00006002C017E000A006601390006001E020902060038027E000600FB03E90306001204E90306002F04E90306004E04E90306008804690406009C04E9030600B504E9030600CE04E9030600E904E90306000205E90306001B05E9030600580538050600780538050E00B105960506009D017E000600FB057E0006001906090606002206090606004D067E00060060067E00060074067E000A009D0639000600C70669040600DD0669040600FA067E0000000000010000000000010001000100100017000000050001000100010010002C0039000500010003000A0110004E000000090003000A000A0110005900000009000B000A0000001000680039000D0010000A0056801101280033007501BA0006009201C30006009701C30006009D01C3000600A701C3000600AB01C3000600B001C3000600B701C3000600BE01C3000600CB01C6000600D001C6000600DD01C6000600EA01C9000600F701C90026000402280001002B02CD0001004102D100010014030301D020000000009600FB0019000100F1200000000086180B0124000400F920000000009600FB009A00040004210000000091003501A300070035220000000096004401AC00090048220000000093005501B4000B00F0230000000091007B019A000C00D3240000000086180B0124000F00B424000000009118B7067D020F00DC240000000086180B01D5000F00722500000000C6084E02DA0010007E2500000000C6005F02DE001000882500000000C6007202E4001100C02500000000C6087F02DA001200C82500000000C6009002EB001200D4250000000081009002F1001300402600000000C600A502F8001500492600000000C600B502F80016005826000000008100C102EB001700B326000000008108D202FF001800C426000000008100ED02EB00180024270000000086000203F80019006B2700000000C6011F03DA001A00732700000000C601260324001A00752700000000E609300306011A007D2700000000E6093A030A011A00000001007B03000002008203000003008D03000001007B03000002008203000003008D03000001009603000002009C0300000100A30300000200B00300000100B503000001007B03000002008203000003008D0300000100C40300000100B00300000100C90300000100C90300000100C90300000200CE0300000100C90300000100C90300000100C90300000100C90300000100C90300000100E3030600060059000B01D50061000B01D50069000B01D50071000B01D50079000B011B0181000B01D50089000B01D50091000B01D50099000B01D500A1000B01D500A9000B01D500B1000B012001B9000B012400C1000B0124002900C605CC013100C605DA0029000B01D10109000B01240039000B01D7013900D005E1013900D905E5013900E105EB013900EF05F00139000B01FD01D10002060402D9002E060A02E1003B060E02D10046061402E9000B01D500E10057061A02F1006C062400F90081062202F90089062902D10091063B021C00AA0649022400C6055D021C00B30662021900A502F8005100BE0681021C000B01870209010B018E0219000B0124003900E806950251000B01FD0149000B019A0219009002EB004900F006AC025100B306B10239000707BE0219005F02DE0049001007CC0139001A07C40249003007CC0151000B01CC0251003807D1023900B306D80239004907C40239005507E60239000B01EA020E0004002B0020007300C7012E002B0007032E00130007032E001B0007032E00230007032E000B00F9022E0033000D032E003B0018032E00430030032E005B0071032E0063007F032E006B008803F60130026802A402B802DF02F20206000100000044030F01000051030F0100005E03130100007503170102000B00030002000E00050002001400070002001900090001001A00090013001F02410255020480000007000100000000000100000025019600000002000000000000000000000001007500000000000700010000000000000000000A009F00000000000200000000000000000000000100C40000000000040003000500030000000000003C4D6F64756C653E0054696D655A6F6E652E646C6C0055736572446566696E656446756E6374696F6E730054696D655A6F6E6542617365004D6F6E676F6F73652E436F72652E436F6D6D6F6E0053595354454D54494D450054494D455F5A4F4E455F494E464F004672656554696D655A6F6E65006D73636F726C69620053797374656D004F626A6563740056616C7565547970650054696D655A6F6E65004D4753686172656453716C436C72556E7361666500494361636865644F626A65637460310053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C4461746554696D650053716C537472696E670054696D655A6F6E65436F6E76657274002E63746F720054696D655A6F6E6552656769737472795375624B65794E616D65004461746554696D650043616C63756C6174656444617932004461796C69676874426F756E646172790047657454696D655A6F6E65496E666F32004D616E61676564436163686560320043616368650054696D655A6F6E65436F6E766572745F4361636865640059656172004D6F6E7468004461794F665765656B0044617900486F7572004D696E757465005365636F6E64004D696C6C697365636F6E64730042696173005374616E6461726442696173004461796C6967687442696173005374616E6461726444617465004461796C6967687444617465004E616D650053797374656D2E476C6F62616C697A6174696F6E004461796C6967687454696D65004461796C69676874496E666F0054696D655370616E005554435374644F6666736574006765745F4461796C696768744E616D65004765744461796C696768744368616E676573004765745574634F6666736574006765745F5374616E646172644E616D650049734461796C69676874536176696E6754696D6500546F556E6976657273616C54696D6500546F4C6F63616C54696D650049734C6567616C4C6F63616C44617465006765745F4461796C69676874536176696E67734170706C696573004973416D626967756F75734C6F63616C446174650046726F6D556E6976657273616C54696D650063616368655374617465004765744B6579004F6E44697363617264006765745F5374617465007365745F5374617465004461796C696768744E616D65005374616E646172644E616D65004461796C69676874536176696E67734170706C69657300537461746500696E446174650066726F6D545A4E616D6500746F545A4E616D6500747A44617900696E54696D6500626F756E64617279496E666F00796561720074696D655F7A6F6E655F6E616D65006E616D650074696D65006578636C756465416D676967756F75735370616E0076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C7954726164656D61726B4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C7565006765745F596561720041646444617973006765745F4461794F665765656B0044617973496E4D6F6E746800537472696E6700436F6E636174004D6963726F736F66742E57696E33320052656769737472790052656769737472794B6579004C6F63616C4D616368696E65004F70656E5375624B657900466F726D617400457863657074696F6E0047657456616C75650049446973706F7361626C6500446973706F736500426974436F6E76657274657200546F496E74333200546F496E743136006F705F457175616C697479004943616368654C6F636B6031006765745F4974656D00416464002E6363746F720046726F6D44617973005374727563744C61796F7574417474726962757465004C61796F75744B696E64006765745F4E6F77006765745F44656C7461004461746554696D654B696E64006765745F4B696E64006765745F5374617274006F705F477265617465725468616E4F72457175616C006765745F456E64006F705F556E6172794E65676174696F6E006F705F4C6573735468616E006765745F5469636B730000007153006F006600740077006100720065005C004D006900630072006F0073006F00660074005C00570069006E0064006F007700730020004E0054005C00430075007200720065006E007400560065007200730069006F006E005C00540069006D00650020005A006F006E00650073005C000047540069006D00650020007A006F006E00650020007B0030007D0020006E006F007400200066006F0075006E006400200069006E00200072006500670069007300740072007900000754005A004900001B540069006D0065005A006F006E00650043006100630068006500005754006800650020006D006500740068006F00640020006F00720020006F007000650072006100740069006F006E0020006900730020006E006F007400200069006D0070006C0065006D0065006E007400650064002E0000809354006800650020006D006500740068006F006400200054006F004C006F00630061006C00540069006D00650028002900200069007300200064006500700072006500630061007400650064002E00200020005500730065002000460072006F006D0055006E006900760065007200730061006C00540069006D00650028002900200069006E00730074006500610064002E0000C6BEC6B76764024092EB2D5DB1BEC6010008B77A5C561934E0890887D3698AFE01EF3705151211010E0A000311151115111911190320000102060E6E53006F006600740077006100720065005C004D006900630072006F0073006F00660074005C00570069006E0064006F007700730020004E0054005C00430075007200720065006E007400560065007200730069006F006E005C00540069006D00650020005A006F006E0065007300080003111D111D0E0E080002111D1110111D070002111D11100805000111140E0806151221020E1218020606020608030611100306122503061129042001010E0320000E0520011225080620011129111D05200102111D06200202111D02062001111D111D0320000202061C0320001C042001011C0328000E032800020328001C0420010102042001010880A00024000004800000940000000602000000240000525341310004000001000100793C4B8149CA133219371E5CF3147D896A61899F980E760A1502F5DA9F33461E19D197F1BF9008453252D6CE8226D70F86222AE864637824D988AD64DE8D9E3CF1475494480A5D2C6624B2522189B1E444BFB81E53E494947B32E8B513076C4DC0B0520C207F5687AD40ADE349E408D5FA0452C8C42F7DDA161C670B4DFEBFAD0401000000042000111D05200101111D0920060108080808080803200008052001111D0D0420001165050002080808060703111D0808062003010808080500020E0E0E0306127105200112710E0500020E0E1C0420011C0E021D05060002081D0508060002061D05080A07050E1C12711D051114050002020E0E07151221020E12180B2001151280810113011300071512808101121804200013000520010113011407041218151280810112181218151280810112180300000105000111290D062002010E112906200101118089040000111D09200301111D111D1129070703111408111D04200011290620011129112905070202112905200011808D07000202111D111D042001010A06000111291129062001111D11290607021225111D0320000A072002010A11808D060702111D111D0D01000854696D655A6F6E6500000501000000000A010005496E666F720000170100124D6F6E676F6F7365204672616D65776F726B00004001003B436F7079726967687420C2A9203230313120496E666F722E20416C6C207269676874732072657365727665642E207777772E696E666F722E636F6D00000D010008372E312E302E323400000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301006C3B000000000000000000008E3B0000002000000000000000000000000000000000000000000000803B00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C03000000000000000000004C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100010007001800000001000700180000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC020000010053007400720069006E006700460069006C00650049006E0066006F0000008802000001003000300030003000300034006200300000002C000600010043006F006D00700061006E0079004E0061006D0065000000000049006E0066006F00720000003C0009000100460069006C0065004400650073006300720069007000740069006F006E0000000000540069006D0065005A006F006E00650000000000340009000100460069006C006500560065007200730069006F006E000000000037002E0031002E0030002E0032003400000000003C000D00010049006E007400650072006E0061006C004E0061006D0065000000540069006D0065005A006F006E0065002E0064006C006C00000000009C003B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020003200300031003100200049006E0066006F0072002E00200041006C006C0020007200690067006800740073002000720065007300650072007600650064002E0020007700770077002E0069006E0066006F0072002E0063006F006D000000000044000D0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000540069006D0065005A006F006E0065002E0064006C006C0000000000480013000100500072006F0064007500630074004E0061006D006500000000004D006F006E0067006F006F007300650020004600720061006D00650077006F0072006B0000000000380009000100500072006F006400750063007400560065007200730069006F006E00000037002E0031002E0030002E00320034000000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000037002E0031002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000A03B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO


AND Function
CREATE FUNCTION [dbo].[TimeZoneConvert](@inDate [datetime], @fromTZName [nvarchar](4000), @toTZName [nvarchar](4000))
RETURNS [datetime] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TimeZone].[UserDefinedFunctions].[TimeZoneConvert]
GO
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vitor Montalvão

So the error occurs when you call the function and not during the insert?
lankapala

ASKER
yes
Vitor Montalvão

You're creating an assembly from an assembly bits. I'm not sure how that behaves when you're migrating it.
I would expect a reference to a .dll file so you can also migrate the file.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lankapala

ASKER
how to do that. this is function coming from ERP (syteline) system, i didn't build this
Vitor Montalvão

I think that's why you're getting those bits. Try to contact the vendor to know which .dll file is needed or see if this article helps you (you'll need Visual Studio to run the presented code).
ASKER CERTIFIED SOLUTION
lankapala

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
lankapala

ASKER
thnx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy