Good Morning. This is more of a SQL Server/.NET issue but since my assembly utilizes the Cognos SDK, I figured I'd give Cognoise a shot. I guess I'm looking to see if anyone has had experience with the SDK and CLR stored procedures in SQL Sever 2008. The problem is defined as follows:
I am currently experiencing an issue with a CLR Stored procedure calling an assembly created in Visual Basic (VS 2008). At the highest level, the assembly executes a report contained in Cognos8 and moves the output to a specificed directory on the network. I have no issue when calling the method from a simple test EXE, but when I try to execute via the CLR stored procedure, I am getting the following:
Msg 6522, Level 16, State 1, Procedure ReportRunner, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "ReportRunner":
System.IO.FileNotFoundException: Could not load file or assembly 'cognosdotnetassembly_2_0, Version=10.1.4707.501, Culture=neutral, PublicKeyToken=d6e6d7d808b7e5b7' or one of its dependencies. The system cannot find the file specified.
System.IO.FileNotFoundException:
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.LoadWithPartialNameInternal(String partialName, Evidence securityEvidence, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadWithPartialName(String partialName, Evidence securityEvidence)
at System.Xml.Serialization.TempAssembly.LoadGeneratedAssembly(Type type, String defaultNamespace, XmlSerializerImplementation& contract)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at cognosdotnet_2_0.reportService1..ctor()
at ReportRunnerv3.ReportRunner.ExecuteReport(Int32 inPLAN_ID, Int32 inContract_Sfx, String inRptDate_DT, String inPlanType, String inInvstmentOnlyInd, String inMOMInd, String inGPSIInd, String inPBTInd, String inPICAInd, String inClientAccomInd, String inInstSelectInd, String inRptType, Int32& outRC)
Notes
•cognosdotnetassembly_2_0 is located in the same directory as the "ReportRunner" assembly
•cognosdotnetassembly_2_0 was cataloged using CREATE ASSEMBLY with permission set = unsafe
•cognosdotnetassembly_2_0 is also installed in the GAC
The following is the message provided by fuslogvw.exe:
*** Assembly Binder Log Entry (8/11/2011 @ 5:57:39 AM) ***
The operation failed.
Bind result: hr = 0x80070002. The system cannot find the file specified.
Assembly manager loaded from: c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe
--- A detailed error log follows.
=== Pre-bind state information ===
LOG: User = NT AUTHORITY\NETWORK SERVICE
LOG: DisplayName = cognosdotnetassembly_2_0, Version=10.1.4707.501, Culture=neutral, PublicKeyToken=d6e6d7d808b7e5b7
(Fully-specified)
LOG: Appbase = file:///c:/Program Files/Microsoft SQL Server/MSSQL10_50.SQLEXPRESS/MSSQL/Binn/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = NULL
Calling assembly : System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.
===
LOG: This bind starts in default load context.
LOG: No application configuration file found.
LOG: Using machine configuration file from c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: cognosdotnetassembly_2_0, Version=10.1.4707.501, Culture=neutral, PublicKeyToken=d6e6d7d808b7e5b7
LOG: Fusion is hosted. Check host about this assembly.
LOG: Assembly is not in CLR Loaded list. Asking host assembly store.
LOG: Try host assembly store with assembly cognosdotnetassembly_2_0, version=10.1.4707.501, culture=neutral, publickeytoken=d6e6d7d808b7e5b7, processorarchitecture=x86.
LOG: Try host assembly store with assembly cognosdotnetassembly_2_0, version=10.1.4707.501, culture=neutral, publickeytoken=d6e6d7d808b7e5b7, processorarchitecture=msil.
LOG: Try host assembly store with assembly cognosdotnetassembly_2_0, version=10.1.4707.501, culture=neutral, publickeytoken=d6e6d7d808b7e5b7.
WRN: Host assembly store does not contain this assembly.
ERR: Unrecoverable error occurred during pre-download check (hr = 0x80070002).
Can someone provide insight as to why SQL Server is unable to find cognosdotnetassembly_2_0?
If you need more information, please let me know.
Thanks for your assistance.
--Chris