Is It Possible To Create A UDF From A .NET 2.0 Assembly That Uses Dllimport To Call Unmanaged Code?
Jan 23, 2006
I have been reading up on everything I can find on this subject and I am not clear if this is allowed within the SQL Server 2005 CLR. My function calls work within a Windows form project, but don't run when invoked from a SQL function. I don't get any errors or warnings when creating the assembly and functions within SQL Server, but when running via a select statement, the spid just hangs and I have to stop & restart the service to kill the process. I have been investigating the security settings for this assembly, but I think I have that covered via the RunTime Security Policy settings in the .NET Framework 2.0 Configuration tool.
Any insights, knowledge, or thoughts would be greatly appreciated.
I understand there are two ways to run native/unmanaged code in SQL Server 2005 in a .NET stored procedure: 1. using COM objects; or 2. using imported DLL functions.
The thing I'd really like to be able to do, is to use a .NET assembly that either: 1. contains both managed and unmanaged code (i.e. C++); or 2. uses a second assembly that contains both managed and unmanaged code. SQL Server doesn't allow me to do either, and from what I've read it just isn't possible - however, I can't find this information direct from Microsoft. I understand the need for marking such assemblies as unsafe, but I just can't understand why mixed assemblies are forbidden, when COM and P/Invoke are allowed from within managed assemblies!
Could someone confirm this? (And maybe put me straight as to the 'why'?!)
With SQL Server 2005 I'm trying to define a CLR integration UDF that calls unmanaged C++ code contained in a .dll.
I have the following two signed assemblies:
1) 'wrapper' - this assembly is basically a wrapper class writen in C++ managed extensions code that calls a legacy library written in unmanaged C++
2) 'provider'- this assembly offers services some of which are contained in the 'wrapper' assembly. This class is written in C# and its assembly is marked as unsafe since it is calling unmanaged code
The problem is that when I run the assembly creation statement:
CREATE ASSEMBLY [Provider] FROM 'c:DevelopmentProvider.dll' WITH PERMISSION_SET = UNSAFE;
Produces the error:
Msg 6544, Level 16, State 1, Line 1 CREATE ASSEMBLY for assembly 'Provider' failed because assembly 'wrapper' is malformed or not a pure .NET assembly. Unverifiable PE Header/native stub.
I understand the nature of the error; wrapper.dll is unsafe unmanaged code and the documentation of CLR integration security states:
"executing from within an UNSAFE assembly can call unmanaged code"
I would think that the 'PERMISSION_SET = UNSAFE' would take care of this situation.
BTW, I can sucessfully call, from a console C# program, the services contained in wrapper.dll, the problem is that I can't get it to work within SQL Server 2005.
With SQL Server 2005 I'm trying to define a CLR integration UDF that calls unmanaged C++ code contained in a .dll.
I have the following two signed assemblies:
1) 'wrapper' - this assembly is basically a wrapper class writen in C++ managed extensions code that calls a legacy library written in unmanaged C++
2) 'provider'- this assembly offers services some of which are contained in the 'wrapper' assembly. This class is written in C# and its assembly is marked as unsafe since it is calling unmanaged code
The problem is that when I run the assembly creation statement:
CREATE ASSEMBLY [Provider]
FROM 'c:DevelopmentProvider.dll'
WITH PERMISSION_SET = UNSAFE;
Produces the error:
Msg 6544, Level 16, State 1, Line 1
CREATE ASSEMBLY for assembly 'Provider' failed because assembly 'wrapper' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.
I understand the nature of the error; wrapper.dll is unsafe unmanaged code and the documentation of CLR integration security states:
"executing from within an UNSAFE assembly can call unmanaged code"
I would think that the 'PERMISSION_SET = UNSAFE' would take care of this situation.
BTW, I can sucessfully call, from a console C# program, the services contained in wrapper.dll, the problem is that I can't get it to work within SQL Server 2005.
Hi, I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it. 1) I creat a class in Visual Basic.Net 2005, whose code is given below: Imports System Imports System.IO Imports Microsoft.VisualBasic Imports System.Diagnostics Public Class WLog Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String) Dim w As StreamWriter = File.AppendText(LogName) LogIt(newMessage, w) w.Close() End Sub Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter) wr.Write(ControlChars.CrLf & "Log Entry:") wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString()) wr.WriteLine(" :") wr.WriteLine(" :{0}", logMessage) wr.WriteLine("---------------------------") wr.Flush() End Sub Public Shared Sub LotToEventLog(ByVal errorMessage As String) Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog log.Source = "My Application" log.WriteEntry(errorMessage) End Sub End Class
2) Make & register its assembly, in SQL Server 2005. 3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile ( @LogName nvarchar(255), @NewMessage nvarchar(255) ) AS EXTERNAL NAME [asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile': System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied. System.UnauthorizedAccessException: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options) at System.IO.StreamWriter.CreateFile(String path, Boolean append) at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize) at System.IO.StreamWriter..ctor(String path, Boolean append) at System.IO.File.AppendText(String path) at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
We have written a test CRL stored procedure to test replacing one of our complex stored procedures but can€™t get it deployed to our SQL server that hosts a mirrored configuration of our production database (very locked down). It works fine on our development instances (not very locked down). It only references the default assemblies that were added when we created the project. All it does is use Context Connection=true to get data, loops though some records and returns the data using SQLContext. CLR is enabled on SQL server, the assembly is strongly signed, and we tried deploy using the binary string with the SAFE setting.
CREATE ASSEMBLY for assembly 'SQLCLRTest2' failed because assembly 'SQLCLRTest2' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [ : SQLCLRTest2.StoredProcedures::GetLift][mdToken=0x600001e] Type load failed. [token 0x02000008] Type load failed.
I have used a script component and went in the design script editor by clicking the "Design Script" button.From their using a object browser trying to load a dll file in references to be used in the ssis application.
I am trying to deploy a Database Project with Visual Studio 2005 and SQL Server 2005 Standard. I import €œSystem.IO€? and have therefore set the permission levels to EXTERNAL_ACCESS.
I am receiving the same error message that many folks have received.
CREATE ASSEMBLY for assembly 'Images' failed because assembly 'Images' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem. Images.
My CLR access is €œon€?
I have tried
1) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to [BuiltinAdministrators]. 2) From master run: GRANT EXTERNAL ACCESS ASSEMBLY to €œMy Windows Authentication ID€?. 3) Run ALTER DATABASE MYDATABASE SET TRUSTWORTHY ON 4) In Visual Studio .NET 2005 Set the permission levels to €˜external€™ 5) Tried BuiltinAdministrators and my SQL Server Windows Authenticated Login ID for the ASSEMBLY OWNER.
I can compile BUT NOT DEPLOY
Any help would be greatly appreciated. Regards Steve
I was trying to understand how VS.NET2005 was deploying .NET CLR assemblies to SQL2005 so I ran a trace and found some interesting results.
VS.NET creates some SQL that looks pretty interesting:
CREATE ASSEMBLY [AssemblyNameHere] FROM 0x4D5A90000300000004000000FFFF000......<continue binary data> WITH PERMISSION_SET = EXTERNAL_ACCESS
Boy howdy!
I have tried to reproduce this and create my own deployment application but I cant figure out how they create this binary stream. The info in BOL is not much help and I have not found any samples anywhere on how to create this stream in c#.
I tried to access a dot net assembly (.dll) file in ssis script component using following steps.
Create new Script Task in Data Flow Task Edit Design Script button-> Loads script Project in MS VSA Locate Object Browser Select Custom Component Set from the dropdown and hit browse button Browse and place the custom component dll (This Custom component dll has to be in GAC - Global assembly cache before browsing) Select the namespace from the Component list of the object browser and click on €œAdd to references to Selected project in the solution explorer€? button Write Imports <namespace> in the script code to invoke class methods from the .NET custom component The following steps worked properly with June CTP version of yukon.In september CTP version of Yukon in SSIS when i browse the dot net assembly with the same above steps i get a error stating " The file could not be browsed ".Can anybody help me in the same.
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG'
I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression.
Do you have a recommended implementation for this situation?
I have writted a dozen or so functions in the Code window of the Report Properties window. I have been copying them from report to report but now I'd like to put them into one assembly and link that to Report Server so they can be more easily maintained. I have ZERO experience with .net. I'm not sure what an assembly is or what it does.
OK. I created a few functions in a Public Class called SSRSFunctions. I built it and deployed it to my desktop. What do I do now? Can it be included as standard functions in the function list within SSRS? Do I add a reference to the Class at the solution level? At the report level? If all I do is add a reference at the report level, I may as well add the functions to each report's code window, which is what I have been doing. I'd at least like to add a single reference to my solution if I can't get it added to the function list.
Hi, I'm trying to develop a custom conflict resolver and I get the following message:
Error loading custom class "HQ.MemberHandler" from custom assembly "C:shareConflictHandler.dll", Error : "Could not load type 'HQ.MemberHandler' from assembly 'ConflictHandler, Version=1.0.0.0, Culture=neutral, PublicKeyToken=42ba9b913dccf3a9'."."
The resolver is register in the GAC. I checked with sp_enumcustomresolvers that it is register in SQL. The sync is working fine if I'm not using the resolver.
Here's the code of the resolver:
Imports System Imports System.Text Imports System.Data Imports System.Data.Common Imports Microsoft.SqlServer.Replication.BusinessLogicSupport Namespace HQ Public Class MemberHandler Inherits BusinessLogicModule ' Variables to hold server names. Private publisherName As String Private subscriberName As String ' Implement the Initialize method to get publication ' and subscription information. Public Overrides Sub Initialize( _ ByVal publisher As String, _ ByVal subscriber As String, _ ByVal distributor As String, _ ByVal publisherDB As String, _ ByVal subscriberDB As String, _ ByVal articleName As String _ ) ' Set the Publisher and Subscriber names. publisherName = publisher subscriberName = subscriber End Sub ' Declare what types of row changes, conflicts, or errors to handle. Public Overrides ReadOnly Property HandledChangeStates() As ChangeStates Get ' Handle Subscriber inserts, updates and deletes. Return (ChangeStates.SubscriberInserts Or _ ChangeStates.SubscriberUpdates Or ChangeStates.SubscriberDeletes) End Get End Property Public Overrides Function InsertHandler(ByVal insertSource As SourceIdentifier, _ ByVal insertedDataSet As DataSet, ByRef customDataSet As DataSet, _ ByRef historyLogLevel As Integer, ByRef historyLogMessage As String) _ As ActionOnDataChange If insertSource = SourceIdentifier.SourceIsSubscriber Then ' Build a line item in the audit message to log the Subscriber insert. Dim AuditMessage As StringBuilder = New StringBuilder() AuditMessage.Append(String.Format("A new member was entered at {0}. " + _ "The ID for the member is :", subscriberName)) AuditMessage.Append(insertedDataSet.Tables(0).Rows(0)("memb_seq").ToString()) AuditMessage.Append("Member Name :") AuditMessage.Append(insertedDataSet.Tables(0).Rows(0)("memb_pnom").ToString()) ' Set the reference parameter to write the line to the log file. historyLogMessage = AuditMessage.ToString() ' Set the history log level to the default verbose level. historyLogLevel = 1 ' Accept the inserted data in the Subscriber's data set and ' apply it to the Publisher. Return ActionOnDataChange.AcceptData Else Return MyBase.InsertHandler(insertSource, insertedDataSet, customDataSet, _ historyLogLevel, historyLogMessage) End If End Function Public Overrides Function UpdateHandler(ByVal updateSource As SourceIdentifier, _ ByVal updatedDataSet As DataSet, ByRef customDataSet As DataSet, _ ByRef historyLogLevel As Integer, ByRef historyLogMessage As String) _ As ActionOnDataChange If updateSource = SourceIdentifier.SourceIsPublisher Then ' Build a line item in the audit message to log the Subscriber update. Dim AuditMessage As StringBuilder = New StringBuilder() AuditMessage.Append(String.Format("An existing member was updated at {0}. " + _ "The ID for the member is ", subscriberName)) AuditMessage.Append(updatedDataSet.Tables(0).Rows(0)("memb_seq").ToString()) AuditMessage.Append("Member Name :") AuditMessage.Append(updatedDataSet.Tables(0).Rows(0)("memb_nom").ToString()) ' Set the reference parameter to write the line to the log file. historyLogMessage = AuditMessage.ToString() ' Set the history log level to the default verbose level. historyLogLevel = 1 ' Accept the updated data in the Subscriber's data set and apply it to the Publisher. Return ActionOnDataChange.AcceptData Else Return MyBase.UpdateHandler(updateSource, updatedDataSet, _ customDataSet, historyLogLevel, historyLogMessage) End If End Function Public Overrides Function DeleteHandler(ByVal deleteSource As SourceIdentifier, _ ByVal deletedDataSet As DataSet, ByRef historyLogLevel As Integer, _ ByRef historyLogMessage As String) As ActionOnDataDelete If deleteSource = SourceIdentifier.SourceIsSubscriber Then ' Build a line item in the audit message to log the Subscriber deletes. ' Note that the rowguid is the only information that is ' available in the dataset. Dim AuditMessage As StringBuilder = New StringBuilder() AuditMessage.Append(String.Format("An existing member was deleted at {0}. " + _ "The rowguid for the member is ", subscriberName)) AuditMessage.Append(deletedDataSet.Tables(0).Rows(0)("rowguid").ToString()) ' Set the reference parameter to write the line to the log file. historyLogMessage = AuditMessage.ToString() ' Set the history log level to the default verbose level. historyLogLevel = 1 ' Accept the delete and apply it to the Publisher. Return ActionOnDataDelete.AcceptDelete Else Return MyBase.DeleteHandler(deleteSource, deletedDataSet, _ historyLogLevel, historyLogMessage) End If End Function End Class End Namespace
Here's the SQL query to register the resolver:
DECLARE @publication AS sysname; DECLARE @article AS sysname; DECLARE @friendlyname AS sysname; DECLARE @assembly AS nvarchar(500); DECLARE @class AS sysname; SET @publication = N'memb'; SET @article = N'memb'; SET @friendlyname = N'TestConlictResolver'; SET @assembly = N'C:shareConflictHandler.dll'; SET @class = N'HQ.MemberHandler'; -- Register the business logic handler at the Distributor. EXEC sys.sp_registercustomresolver @article_resolver = @friendlyname, @resolver_clsid = NULL, @is_dotnet_assembly = N'true', @dotnet_assembly_name = @assembly, @dotnet_class_name = @class; -- Add an article that uses the business logic handler -- at the Publisher. EXEC sp_changemergearticle @publication = @publication, @article = @article, @property = N'article_resolver', @value = @friendlyname, @force_invalidate_snapshot = 0, @force_reinit_subscription = 0; GO
I'm trying to make a reusable header function. In it, there is the report name and a couple other bits of info. The problem is making RS use the real report name
If I return "Globals!ReportName" in an assembly function, then call that function, I get that exact text - but what I really want is the report name. I understand what it's doing, but how to I trick it:
Is there some way to make RS aware of the fact that this is meant to be seen as something it needs to evaluate rather than just displaying the actual text?
can someone point me in the right direction? I know how to call a stored procedure, but I can't seem to find code examples on how to call user defined functions.
I'm using SQL Server Management Studio Express and am unable to create an assembly. The following code:
create assembly HelloWorld from 'c:HelloWorld.dll'
with permission_set = safe
returns the following error:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'assembly'.
I noticed that the word "assembly" isn't in blue text and what's more, if I expand the programmability node on any database, there is no assembly node. Anybody know what I'm doing wrong here?
I have some problem to need you all help. My problem is i have a Excel Worksheet with VBA coding, in VBA code it have modules,function and sub procedures. after that, i change the VBA coding in add-in file and save as .xla to make the user friendly.
But now, i need to use DTS to automatic to execute the excel file that contain VBA code every week. This is because the VBA in excel cannot automation excute by every week. So, i need use DTS to make it automation. The problem is i don't know how to use the DTS function to call the addin excel to execute? Which the connection and task compenent should i use? Or how do i paste the VBA code in DTS?
DTS is very new for me, are any help can provide me the solution? thanks
I've got a project that requires J#. I know the J# assemblies aren't on the approved list, but I'm ok with that. I've done all the nasty things you're supposed to do to register it, and I've been succesful on my x86 machine. However, when I try the same thing on an x64 machine, it ends in failure. Here's the exact T-SQL I'm running:
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = 'vjscor') CREATE ASSEMBLY [vjscor] FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727vjscor.dll' WITH PERMISSION_SET = UNSAFE
IF NOT EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = 'vjslib') CREATE ASSEMBLY [vjslib] FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727vjslib.dll' WITH PERMISSION_SET = UNSAFE
As I said, this runs fine on SQL Server 2005 running on an x86 version of Windows.
When I run the same T-SQL on SQL Server 2005 on an x64 version of windows, I get this lovely error:
Assembly 'vjslib' references assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.
As you can see, vjscore has indeed been registered. I can verify it by running SELECT * FROM sys.assemblies asms WHERE asms.name = 'vjscor'. So, why does vjslib not like the vjscor that has been registered, which just so happens to be the vjscor that's in the same directory as it is? And which happens to be one of only two vjscor.dll files on my entire computer (the other being the .NET 1.1 version).
Anyway, I'm totally stumped on this problem. Anyone have any ideas?
I need to get some .dll's into MS SQL-Server 2005, that i need to get a own made .dll installed.
When i try to:
Code Block CREATE ASSEMBLY SystemWeb FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Web.dll' WITH PERMISSION_SET = UNSAFE;
i get the following message:
Code Block Warning: The Microsoft .Net frameworks assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment. Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment. Warning: The Microsoft .Net frameworks assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. Warning: The Microsoft .Net frameworks assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. Msg 701, Level 17, State 13, Line 1 There is insufficient system memory to run this query.
When i try to create one of the another assemblies ( system.enterpriseservices, system.runtime.remoting, system.design ) i get the same message.
I looked for this Error and found only this BUG-report: BUG#: 58267 (SQLBUG_70) Article ID: 274030 http://support.microsoft.com/kb/274030/en-us
But this does not solve my problem.
As far as i know we use MS SQL-Server 2005 without any Service-Packs.
Question:
is there any Table/View to find out the Versionnumber/Service-Pack
In the moment i am waiting for our admin to install SP2 for SQL-Server, hoping that this will fix the problem.
Warning: The Microsoft .Net frameworks assembly 'stdole, version=7.0.3300.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in SQL Server hosted environment.
Warning: Assembly "stdole" was built using version v1.0.3705 of the .NET Framework. SQL Server currently uses version v2.0.50727.
Msg 6215, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'Next' on type 'stdole.IEnumVARIANT' in safe assembly 'stdole' has invalid attribute 0x1003.
i really need urgently help with my deploying my assembly to my database!! can somebody tell me how can i get the authorization to deploy my assembly as permission_set = External_access or Unsafe? i allways get error messages when i try to deploy my assembly that way.
i tried it with strong key and i tried it with asymmetric key like this:
use master go
create asymmetric key WebServiceKey from executable file = '...16082007.dll'
CREATE LOGIN WebServiceLogin FROM ASYMMETRIC KEY WebServiceKey
GRANT EXTERNAL ACCESS ASSEMBLY TO WebServiceLogin GO
than i get the message:
The certificate, asymmetric key, or private key file does not exist or has invalid format.
using System; using Microsoft.SqlServer.Dts.Runtime; public class Execute { public static void Package(string dtsxPath) { try { Package pkg; Application app; DTSExecResult pkgResults; app = new Application(); pkg = app.LoadPackage(dtsxPath, null); pkgResults = pkg.Execute(); } catch (System.Exception ex) { throw ex; } } }
Then this c:Program FilesMicrosoft Visual Studio 8VC>csc /target:library C:SSISSSISE xecute.cs /reference:C:SSISSSISMicrosoft.SqlServer.ManagedDTS.dll
It creates Execute.dll
but when I run this is sql server
CREATE ASSEMBLY ssid from 'c:Program FilesMicrosoft Visual Studio 8VCExecute.dll'
WITH PERMISSION_SET = UNSAFE
I am getting Error
Warning: The SQL Server client assembly 'microsoft.sqlserver.manageddts, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The SQL Server client assembly 'microsoft.sqlserver.dtsruntimewrap, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10301, Level 16, State 1, Line 1
Assembly 'Execute' references assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
I don't see any place I am using System.window.forms
Any idea why? Thanks - Ashok
--------------------------------------------------------- I changed my location to to CSC C:WINNTMicrosoft.NETFrameworkv2.0.50727>
Now I get this error
Warning: The SQL Server client assembly 'microsoft.sqlserver.manageddts, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The SQL Server client assembly 'microsoft.sqlserver.dtsruntimewrap, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.windows.forms, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.drawing, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'accessibility, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Warning: The Microsoft .Net frameworks assembly 'system.runtime.serialization.formatters.soap, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10301, Level 16, State 1, Line 1
Assembly 'Execute1' references assembly 'microsoft.sqlserver.msxml6_interop, version=6.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
--------------------------------------------- I managed to found this DLL Microsoft.SQLServer.msxml6_interop.dll so I copied to same location Now It's looking for
microsoft.sqlserver.sqltdiagm - Which I am not able to find any place .......................
I'm trying to create an "unsafe assembly": USE master GO
CREATE ASYMMETRIC KEY StoredProcedures_dll_Key FROM EXECUTABLE FILE = 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll';
CREATE LOGIN StoredProcedures_dll_Login FROM ASYMMETRIC KEY StoredProcedures_dll_Key;
GRANT EXTERNAL ACCESS ASSEMBLY TO StoredProcedures_dll_Login; GO
USE gfx_sa GO
CREATE ASSEMBLY hunter_storedProcedures FROM 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll' WITH PERMISSION_SET = UNSAFE; GO
CREATE PROCEDURE [dbo].[hunter_storedProcedure1] ( @symbol_id as int ) AS EXTERNAL NAME hunter_storedProcedures.StoredProcedures.StoredProcedure1 GO
But on 'CREATE ASSEMBLY" clause I'm getting error: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
I work with February CTP of SqlServer 2008. I have an Assembly with several UDTs inside. Version of assembly is 1.0.* I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version). Then I try to update assembly in SqlServer. I use ALTER ASSEMBLY <name> FROM <path> WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA statement for this. Statement runs with error: Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1. I found the list of condition for ALTER ASSEMBLY in MSDN: ALTER ASSEMBLY statement cannot be used to change the following: · The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly. · The signatures of methods in the assembly that are called from other assemblies. · The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly. · The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly. · The FillRow method name attribute for CLR table-valued functions. · The Accumulate and Terminate method signature for user-defined aggregates. · System assemblies. · Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead. Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes: · Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed. · Adding new public methods. · Modifying private methods in any way.
But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions. What could be the reason for such behavior? P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
I have a microsoft report that uses a dll to calculate a value. If I hard code the value, my report shows it. My method in the dll is connecting to a database in order to calculate a result. However, in order to connect to the database, I am using Microsoft.Practices.EnterpriseLibrary.
The error in my report is: Could not load file or assembly 'Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
I have added a reference in the report to the EnterpriseLibrary but this has not made a difference.
Does anyone know where I can download a C# CLI Assembly template?
In VS 2005 there are no defaults to create a C# SQL Assembly like the sample HelloWorld. Rather than modifying the Hello World, I'd like to get an original template for creating a new project just like the templates to create a new Windows project.
Create assembly LibSql FROM 'c:SQLNETLIBSQLLIBSQL.dll' WITH PERMISSION_SET = External_Access
I got the following error:
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.
Configuration:
Server: 8 processors with 16 GB RAM
select @@version returns:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
