Office Interop Assemblies And SSIS
Apr 5, 2007
Hi guys,
I'm trying to write an SSIS script that will create an excel file and append data to it.
I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.
However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.
If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.
Am I missing anything obvious?
Thanks for your help in advance
Mike
View 8 Replies
ADVERTISEMENT
Feb 14, 2008
HiHo,
I have a problem and hopefully some can help me. I googled already al lot but I haven't found the answer.
I would like to build a Source-Script Component with Acces to MS outlook (I use the Book "SSIS" from Kirk Haselden as a Guideline).
I downloaded and installed "Microsoft.Office.Interop.Outlook" .
I can find it the GAC.
I also installed VSTO 2005 and even run the VSTO 2005 checker, which shows that everything is ok.
But when I try to add the reference to my project it isn't there.
While adding a reference I only see a ".net" tab.
From what I found so far there should be "com" tab, where I should find "MS.Office.Interop.Outlook".
Can some help me to make the reference ??
Best Regards
Chris
View 5 Replies
View Related
Mar 21, 2007
What is the proper method of using the Excel.Interop assemblies with SSIS? I am using SQL Server 2005 Integration Services (VS 2005) against the .NET Framework v2.0.50727 with the Microsoft.Office.Interop.Excel.dll assembly.
I find that the referenced instance of Excel in my SSIS package is not always released from memory as expected. I'm not sure that I have a problem per se, but I am interested in knowing the Microsoft reccommended method of using the Office Interop assemblies with SSIS.
For this thread, "working" code is defined as observing the appearance and disappearance of the Excel.exe image in the Task Manager as it is created and destroyed in the SSIS package.
The following Sript Task code (between stars) works as expected in that the image of Excel can be seen created and removed from the Task Manager list of processes:
************************************************
Imports Microsoft.Office.Interop.Excel
Public Sub Main()
Dim mobjExcelApp As Microsoft.Office.Interop.Excel.Application
Dim mobjWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim objSheet as Worksheet
Dim strExcelFilename as string = "C:TempMyExcelfile.xls"
Dim strSheetname_ValidSamples as String = "Sheet1"
mobjExcelApp = New Microsoft.Office.Interop.Excel.Application
mobjExcelApp.Visible = False
mobjWorkbook = OpenWorkbook(strExcelFilename)
objSheet = CType(mobjWorkbook.Worksheets(strSheetname_ValidSamples), Worksheet)
strWell = CType(objSheet.Range("a1").Offset(intRow, 0).Value, String)
objSheet = Nothing
mobjWorkbook.close
mobjWorkbook = Nothing
mobjExcelApp.Quit
mobjExcelApp = nothing
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
End Sub
***********************
On occasion, when the line of code that sets the variable strWell (in BLUE above) is followed with the following line of code:
strSampleName = CType(objSheet.Range("a1").Offset(intRow, 1).Value, String)
the code will work fine with no errors, however, the Excel image is not removed from the task list. I have found that the instance will disappear upon waiting (several minutes or more).
Thanks,
Rob
View 5 Replies
View Related
Apr 28, 2008
I applied the following info and created an SSIS package that allows COM control of Excel:
unable to add reference to Microsoft Excel in VSA
The package runs locally, and can be executed successfully after it is migrated to the server, but both of these processes use local memory space (I think). When I try to run the package via a job, it fails with the following error:
-------------------------------------------------------------------------------------------
Date 4/28/2008 10:34:04 AM
Log Job History (J_MSR_UHC_PC)
Step ID 1
Server SFA30229SQL003
Job Name J_MSR_UHC_PC
Step Name S_MSR_UHC_PC
Duration 00:00:05
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: ALEREsql_admin. ...on 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:34:04 AM Error: 2008-04-28 10:34:06.73 Code: 0xC001405F Source: Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information. End Error Progress: 2008-04-28 10:34:09.34 Source: Data Flow Task Validating: 0% complete End Progress Error: 2008-04-28 10:34:09.45 Code: 0xC0202009 Source: msr_uhc_pc Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-04-28 10:34:09.45 Code: 0xC020801C Source: Data Flow Task... The package execution fa... The step failed.
----------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.dll has been added to the assembly directory.
Any ideas would be appreciated.
TruckeeBill
View 4 Replies
View Related
Apr 16, 2008
Hi,
I have one SSIS package which is written in Visual studio business intelligence tool. For that SSIS packages i have scheduled a job from SQL server management studio 2005. I mean i have scheduled a job in SQL server agent.
This job which i have scheduled contains 6 SSIS packages and the other 5 SSIS packages executes successfully but this only fails giving sone com.Interop exception.
But it is failing giving some com.interop exception. Not sure what type of error is this?
It give following type error:
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:00:00 PM Error: 2008-03-27 23:00:00.81 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:00 PM Elapsed: 0.579 seconds. The package execution failed. The step failed.
I get the same error when i try to execute the package from Visual studio Business Intelligence tool.
Can you please help me out as to what is this "System.Runtime.InteropServices.COMException" exception occuring when scheduling or executing the job.
Thanks,
Ashok
View 1 Replies
View Related
May 18, 2007
As painful as it is proving out to be, I am trying to create a single package that is vendor neutral between SQL Server and Oracle.
At first, I thought that as long as I was going OLEDB in and out, and I ensured that my SQL syntax was generic enough, I'd be OK.
Well, not exactly. I am finding there is more to this. Specifically, metadata gets generated differently based on the source/targets.
For example, on an OLE DB Source task, datatypes will vary (i.e. NUMBER in Oracle vs Int in SQL). The job here is to pick the common denominator for the SSIS specific type. Time consuming, but doable, I think.
Another issue is on an OLE DB Desitnation. If you choose Table for Data Access Mode, this value gets written to the dtsx XML file. So, even when both RDBMS have the same schema (which is an obvious prereq) if choosing SQL Server "dbo.DimTable" will get written to the file and for Oracle "SCHEMA.DIMTABLE" will get written.
So, I am am wondering, what is the best way to address this?
My inital thought was using a dtsConfig file (which I am already using extensively) and set the target table names accordingly. This approach would have the added benefit of allowing for post-deployment configuration changes should a table name change, for example.
This section of the dtsx file shows the values of interest:
Code Snippet
<component id="138" name="OLE_DST_DimTable" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property'>http://www.microsoft.com/sql/support;4">http://www.microsoft.com/sql/support;4">
<properties>
<property id="139" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="140" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">"ORASCHEMA"."DIMTABLE"</property>
<!-- More Properies -->
</component>
Ideally, I'd like configuration time control over the name attribute of the component element (highlighted) so that I can set the value of the property element with the OpenRowset attribute type (also highlighted). This way, presumambly as long as datatypes were generic enough, the mapping would just work.
But, in walking through the dtsConfig tree, I don't see these elements or attributes exposed.
I would sincerely appreciate any suggestions on how to accomplish this.
Rick
View 6 Replies
View Related
May 27, 2006
Hi!
Today we moved from 32bit to 64bit computing environment. 64bit Window server 2003 and SQL 2005 are now our playing ground. And 32 bit Office 2003 is also installed. Excel in Office program plays important roll in our mission.
One of our routine task is that
step 1. get Excel data files from other websites and
step 2. extract specific data from them and
step 3. converting them into some type of data that can be compatible with SQL
Our problem occured in step 3. To convert data, we realised taht 64 bit excel driver, which we believe is not yet available, is required.
My questions are:
1. Is 64 bit excel driver (of office 2003 or office 2007) available now?
2. Is there any way OR IS IT POSSIBLE to use 32 bit excel driver with 64bit SQL 2005?
If possible, please enlighten me.
I would appreciate it very much if anybody answer my questions.
Sincerely
View 8 Replies
View Related
Dec 4, 2006
Using SSIS, every month/week I have to generate serveral excel files. I need to find a way to automatically format these excel files (Bold, Headings in Different color etc.). Is there a way to integrate MS Office excel task in SSIS?
Thanks
Shafiq
View 5 Replies
View Related
Oct 4, 2007
Help! My network support staff just upgraded my MS Office products from Office 2003 to Office 2007, and now I no longer get the "F5 = Run" option in Microsoft Visual Studio to run any of my SSIS packages that ran just fine yesterday, before the upgrade.
The "Debug" option does not occur in the menu choices across the top of the screen. (They are File, Edit, View, Project, Build, Data, Format, SSIS, Tools, Test, Window, Community, and Help -- no Debug.)
I store my packages in the File System. I can run many of them just fine, even today, with the Execute Package Utility. My problem occurs when I try to run them in Visual Studio.
Even if I make a simple, new package that moves a table from Access 2003 to SQL Server 2005, I see no Debug menu selection option when I am finished with the new package.
I figure it must be something simple -- I just don't know where to find it in the Visual Studio menu.
Most of the packages I am trying to run do not access Office files: they access dBase 5 files (database); they run processes on SQL Server 2005 servers. I realize that I may have to make some adjustments to connections to Access databases, should such databases be upgraded to Office 2007.
Thanks in advance.
Dan
View 9 Replies
View Related
Feb 28, 2006
I am developing a Script Task for my SSIS package to transfer a file via FTP over SSH (SFTP). I have a nice set of Assemblies/DLLs (purchased from IP*Works) that gives me the added functionality of SSH.
To even get Visual Studio for Applications to see it in the reference list, I copied the DLL (nsoftware.IPWorksSSH.Sftp.dll) to the C:WINDOWSMicrosoft.NETFrameworkv2.0.50727 directory, added it in the references for the script, and added the Imports nsoftware.IPWorksSSH.Sftp line in the script.
As described in: http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx
Yet, when I try to run the SSIS package I get a DTS Script Task Runtime Error:
Could not load file or assembly 'nsoftware.IPWorksSSH.Sftp, Version=7.1.2203.0, Culture=neutral, PublicKeyToken=cdc168f89cffe9cf' or one of its dependencies. The system cannot find the file specified.
I tried to move the DLL to the bin directory in the SSIS Package, but that did not help.
Any Ideas?
Thank you for your time,
-Tim
View 10 Replies
View Related
Sep 4, 2007
I have several versions of one SSIS Dataflow component. I need to bind some of them( definite version) to my SSIS package. How can I do that?
Thanks in advance.
View 5 Replies
View Related
Sep 19, 2007
Been given a vbs project that uses Interop.DTS object to dynamically create a DTS package for data transfer.
Will this code work on a SQL 2005 server?
View 3 Replies
View Related
Dec 4, 2006
I am trying to load a
project under VS2005 under Windows Vista that references an ADOX
wrapper that is looking for version 2.8.0.0. The reference fails
because the library is not registered.
I thought maybe
installing MDAC 2.8 would solve my problems, but when I run the
installer for that, it briefly puts up a dialog that it is extracting
files, then it disappears.
Anyone have any ideas?
--Bruce
View 3 Replies
View Related
Nov 30, 2006
I am trying to load a project under VS2005 under Windows Vista that references an ADOX wrapper that is looking for version 2.8.0.0. The reference fails because the library is not registered.
I thought maybe installing MDAC 2.8 would solve my problems, but when I run the installer for that, it briefly puts up a dialog that it is extracting files, then it disappears.
Any ideas?
--Bruce
View 10 Replies
View Related
Mar 28, 2006
I am using a com component in my asp.net programme and it was working fine for many days . now I am getting following error .
Source Error:
Line 196: <add assembly="System.EnterpriseServices, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 197: <add assembly="System.Web.Mobile, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 198: <add assembly="*"/>Line 199: </assemblies>Line 200: </compilation>
Source File: c:windowsmicrosoft.netframeworkv1.1.4322Configmachine.config Line: 198
Assembly Load Trace: The following information can be helpful to determine why the assembly 'Interop.ADODB' could not be loaded.
=== Pre-bind state information ===LOG: DisplayName = Interop.ADODB (Partial)LOG: Appbase = file:///e:/inetpub/wwwroot/SAPTRainingLOG: Initial PrivatePath = binCalling assembly : (Unknown).=== LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODBLOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///e:/inetpub/wwwroot/SAPTRaining/bin/Interop.ADODB.DLL.LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODB, Version=2.6.0.0, Culture=neutral, PublicKeyToken=null
Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300
How this can be solved ? Please help
View 1 Replies
View Related
Aug 2, 2006
this thread is actually to help another guy out in a seperate thread lol...I am not a COM expert but I do have a basic understanding of it from a dev perspective. I am trying to get COM interop via VS2005 to work from a sql2005 sp_OACreate call. below is the code. I can replace my custom object with say a sqldmo reference and IT WORKS! So I was thinking maybe security issues, but even when I run sql under an admin account it does not work. and i am connecting via sa for the code.
Also, I have tested the COM object via windows script host/.vbs file: dim oOjbect set oObject = CreateObject(myCOMObject.Math) msgbox oObject.Add(1,1). And the object appears to be listed correctly in the registry under HKEY_Classes
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class Math
Public Function Add(ByVal iFirstNum As Integer, ByVal iSecondNum As Integer)
Return (iFirstNum + iSecondNum)
End Function
End Class
------------------------------------------------------------------------------------------
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'myCOMObject.Math', @object OUT,5
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
View 6 Replies
View Related
Oct 31, 2007
Hi, a silly question here.
I wrote an assembly and attached to a trigger in one of my tables. It works as I expected, but then I decided to modify the code, say adding some new triggers that I will use with other tables. Then I realize that in order to update my new version of the assembly in SQL first I need to remove the trigger in the table, then remove the assembly, add the new version and finally run a script to create the trigger again in the table.
How could I avoid going through all this process everytime I want to update my assembly?
Thank you!
View 1 Replies
View Related
Oct 12, 2006
This is related to one of my previous posts.
I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.
I am using the Novell ldap library.
I want to do this with SSL so my code referneces the Mono security library as well.
However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.
Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)
Any ideas?
Thanks,
BIG
View 22 Replies
View Related
Jan 19, 2007
i found one tutorial on self signing assemblies for use in sql server, but it appears to have errors. does anyone know of a better tutorial on this topic? the site im currently looking at is here: http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
View 1 Replies
View Related
Jan 6, 2008
I want to use a custom assembly in my report expression.
I have created a class library and built it. Then what do I do next.
Can yu be a little more explicit since I am a novice.
Thanks
Sai
View 3 Replies
View Related
May 16, 2005
I have a question: how is it planned to replicate changes made to the assemblies and to the CLR functions or UDT? Will Visual Studio deploy change of the Assembly as ALTER not DROP/CREATE?
View 1 Replies
View Related
Jun 15, 2007
Good morning,
How does SQL Server 2005 handle the replication of assemblies? I get the following when trying to replicate an assembly:
Timestamp: 6/15/2007 9:41:33 AM
Message: HandlingInstanceID: 05eaed53-b6be-42da-8c72-0f91fcbfa5b6
An exception of type 'Microsoft.SqlServer.Replication.ComErrorException' occurred and was caught.
-------------------------------------------------------------------------------------------------
06/15/2007 09:41:33
Type : Microsoft.SqlServer.Replication.ComErrorException, Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Message : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber.
Source : Merge Replication Provider
Help link :
ErrorMessage : Unspecified error
InterfaceId : 00000000-0000-0000-0000-000000000000
HelpFile :
HelpContext : 0
Description : The schema script 'StratisDSonetd85d6e89_1061.sch' could not be propagated to the subscriber.
ComHResult : -2147467259
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite : Void Run()
Stack Trace : at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run()
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Synchronize()
at SQLClientSync.SQLClientSync.Replicate()
Additional Info:
MachineName : REPCLIENT
TimeStamp : 6/15/2007 1:41:33 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=1.1.1.1, Culture=neutral, PublicKeyToken=d2ff10320a77ec18
AppDomainName : sqlclientsync.exe
ThreadIdentity :
WindowsIdentity : REPCLIENTAdministrator
I appears that the initial replication of the assembly was fine, when re-running the sync (no changes to the assembly), this error is generated.
Thanks...
View 3 Replies
View Related
Mar 20, 2008
How to add assemblies to Sql server 2005 to do some user defined function......with some example code..........
View 1 Replies
View Related
Oct 10, 2007
Hi all,
I need to dynamically create a ADO.NET connection manger
so, my question is if it is safe to create it with the string that is generated (ConnectionManagerType) when creating the connection manager manually on my PC.
I understood that this string is the full qualified name of the assembly, so will it be different when I create it on another P.C or it is fixed on all P.Cs ?
Thanks ahead
Eran
View 3 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
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 Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 11 Replies
View Related
Jun 1, 2006
Hi,
It's possible to register an assembly in SqlServer 2005 using the
CREATE ASSEMBLY
syntax.
How can I get a list of assemblies that have been registered?
Thanks
Herbjörn
View 1 Replies
View Related
Apr 23, 2006
Where is it documented what the list of approved assemblies are for SQL 2005. Also, is there a way via T-SQL, or other means to get it programatically?
View 4 Replies
View Related
Mar 23, 2008
I have two questions.
How can I run my C++ assembly in safe mode?
I'm building a SQL function from C++ and compile my assembly with /clrafe. After that I create the assembly with SAFE permission sets and create the function, both in SQL. But the server don't accept load or execute the function because of permission error. It says:
Code Snippet
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. The server may be running out of resources, or the assembly may not be trusted with PERMISSIONSET = EXTERNALACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
Does the terminology coince with the technology, or is it diffrent "safety" conditions we are speaking about? One for the SQL permission set, and another safety for CLR type-safe assemblies?
Anyway, after playing around a bit, and following the tricks from this thread. I managed to load and execute the function, but only in UNSAFE permission (unrestricted mode?)
---8<----
My second question is can I use unrestricted mode for assembly compiled with /clr (mixed CLR)?
I'm asking, because I have some C source code that I want to reuse, and for this I can't be type-safe and therefore need to compile only with /clr (mixed CLR).
Should I go back to the plain C API in SQL 2000 to implement such function in SQL 2005, and ignore all new things in c++/CLR/.NET?
If you have any points to C++ sample code, then please forward this to me.
View 4 Replies
View Related
Apr 12, 2007
I have followed the steps outlined in the knowledge base article http://support.microsoft.com/kb/913668 for effecting Xml Serialization within the SQL CLR. That is, I have
1. Prebuilt the serialization assembly X.Serializers for the types in assembly X via the SGEN tool and,
2. Registered both assemblies with SQL Server via the create assembly directive
Yet, when I attempt to create an XmlSerializer on the basis of one of the types defined in X, SQL CLR ignores the pre-built serialization assembly and attempts to dynamically create/load the assembly. Since dynamic loading is disallowed, this fails with the expected exception:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. ---> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(Com
...
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
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 Cypress.Services.Client..
What do I need to do the force the runtime to load the pre-built serialization assembly instead of dynamically trying to create one?
Thank You,
Chris.
View 1 Replies
View Related
Jul 11, 2007
Where are CLR Assemblies stored on SQL Server when compiled from a SQL Server Project?
Are they stored in a specific folder or stored inside the database?
Does deleting the Assembly under Programmability/Assemblies remove the Assembly from the server?
Thanks.
View 6 Replies
View Related
Mar 2, 2004
Hi everyone,
I am testing reporting services custom assembly feature, but have problems.
I have referenced my dll, put my class name and instantiated it...
when i try and use a method within my instantiated class, it says "file or assembly name was not found".
Any ideas?
View 2 Replies
View Related
Mar 29, 2006
Did this as a exercise and am fed up with my fruitless searching.Went and create an CLR assembly and all with min hassel. Now that Ihave that dll out there, I am tring to find some system table or somedisplay in management studio that shows me where the file is located onthe hard drive and cannot locate it.Can someone please point me in the correct location.Thanks.
View 1 Replies
View Related
Aug 18, 2006
I am trying to load multiple strongly named assemblies into the same database which are signed with the same .snk file (signed in Visual Studio). I use the following code to create an asymmetric key and login as Books Online recommends:
CREATE ASYMMETRIC KEY SQLCLRKey FROM FILE = 'D:dbainAssembly.dll'
CREATE LOGIN CLRAssembler FROM ASYMMETRIC KEY SQLCLRKey
GRANT UNSAFE ASSEMBLY TO CLRAssembler
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRAssembler
REVOKE CONNECT SQL FROM CLRAssembler
Do I need to create a new login and asymmetric key for each assembly I load? If so, do I need to sign each with a different key because its giving me an error message when I try to create 2 separate asymmetric keys/logins from 2 different assemblies which have been signed with the same .snk file.
The only way I've gotten everything to load properly is to create a separate key for each assembly and sign each, then create separate logins and asymmetric keys in the database.
Is this the only way to do this? Or am I missing something?
View 5 Replies
View Related
