We help IT Professionals succeed at work.

sql server issue

112 Views
Last Modified: 2017-04-11
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
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Should be:
RECONFIGURE WITH OVERRIDE

Author

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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

Author

Commented:
Error is i have attached
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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ãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
yes
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
how to do that. this is function coming from ERP (syteline) system, i didn't build this
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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).
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
thnx

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions