Is There A .net Assembly (api) That Lets Me Run Transact Sql From C#?
Apr 25, 2008I see examples of running osql programmatically, but was wondering if there is a better way?
View 4 Replies
ADVERTISEMENT
ALTER ASSEMBLY Error Msg 6509 An Error Occurred While Gathering Metadata From Assembly ‘&&<Assembly Name&&>’ With HRESULT 0x1.
Feb 22, 2008I 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.
Lets Start A Thread Just On The IDE
Oct 18, 2006When a query is executed, the tab is split horizontally with the bottom half containing two tabs. The first with the result set(s) and the second with the messages.
How do I hide the bottom half after I have read it and now want the entire column to view my TSQL? Is there some menu item or shortcut key that I can use to accomplish this?
In 2000, there is a configuration option to make these tabs appear at the top of the window. I haven't found this setting in Express.
Another nice feature that I appreciate is to be able to divide the window into independant horizontal or vertical windows (or tabs) each with different files. I have found that I can get there with a mouse click but I could use some guidence in restoring the original layout, expecially without closing the windows.
Thanks,
IanO
Transact SQL :: Getting Data From External Assembly Into Temp Table Inside Stored Procedure
Oct 8, 2015I have a Custom .net assembly which retrieves some data, basically just a set of data containing, ID, value,
I need my stored procedure to return this data as a table.
My question what/how is fastest way to do this, my assembly can return anything you want, a datatable, a hashtable, list of(class) etc..
Microsoft No Longer Lets Us Download SqlExpress. Why?
Jun 23, 2007Microsoft no longer lets us download SqlServer Express 2005. Does anybody know why? I thought it was free.
http://www.microsoft.com/downloads/details.aspx?FamilyID=220549b5-0b07-4448-8848-dcc397514b41&DisplayLang=en
In fact, none of the many options here work at all:
http://www.microsoft.com/downloads/browse.aspx?displaylang=en&productID=9F07F9CC-C308-4EBF-A4E9-2B8530AB1EA8
How come? Is there someplace else I can get it? I just don't understand why they do this stuff with no explanation. I'm trying to learn C# and I need a database.
Any help would be appreciated.
Dave
Arlington, Virginia
Why RS Lets User To Create BookmarkID And Actions By Using A Different Query That The Reportitem Is Using.
Jun 20, 2007It seems in the spec that Data Regions can only reference a single dataset, then why bookmarkID and bookmark action can reference a different dataset than the one that the Data Region is using?
Like in this case:
1) create a table with dataset1
2) create another table with dataset2
3) create a bookmarkID using =Last(somefield, "dataset1") in any table cell in the first table
4) create a bookmark action using =Last(somefield, "dataset1") in any table cell in the SECOND table.
This is valid report, and it can preview....
I know this example not practical at all, it is just used to illustrate my question.
Is this a scoping rule exception for any special reasons? or it's just a bug?
If it is a scoping rule exception, please explain, and a practical example would be great.
Msg 6573 Method, Property Or Field In Assembly Is Not Static. VB.Net Assembly In SQL Server Problem
Feb 29, 2008
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
CREATE FUNCTION dbo.Temperature(@FluidName char, @InpCode Char, @Units Char, @Prop1 varchar, @Prop2 varChar)
RETURNS VarChar
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
Call CalcProp(FluidName, InpCode, Units, Prop1, Prop2)
Temperature = ConvertUnits("-T", Units, T, 0)
End Function
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
Thanks
Buck
Failed To Load Expression Host Assembly. Details: StrongName Cannot Have An Empty String For The Assembly Name.
Jan 12, 2006I previously had an ASP.NET 1.1 site running on my IIS 6.0 server (not the default website) with Reporting Services running in a subdirectory of that website. I recently upgraded to ASP.NET 2.0 for my website and was greeted with an error when trying to view a report. The error was very non-descript, but when I checked the server logs, it recorded the details as "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process."
First of all, I could not figure out where and how to do this. Secondly, I decided to try to also change the Reporting Services folders to run ASP.NET 2.0 and when I did, I was greeted with the following message when attempting to view a report:
"Failed to load expression host assembly. Details: StrongName cannot have an empty string for the assembly name."
Please help.
Creating A System.Management Assembly In Order For My Own Assembly To Work?
Aug 2, 2006Hi
I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.
I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.
in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.
namespace inlineCLRsql{
public static class test{
public static void PrintMessage(){
inLineLib.Queue q = new inLineLib.Queue();
int i = q.queueId ;
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());
}
}
}
to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.
so what I did is (and this is where I need to know if I just ruined sql server or not):
1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.
2- CREATE ASSEMBLY SystemManagement
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Management.dll'
WITH PERMISSION_SET = UNSAFE
3- CREATE ASSEMBLY inLineLibMaster
FROM 'D:inLineServerinLineLibinDebuginLineLib.dll'
WITH PERMISSION_SET = unsafe
4- and finally
CREATE ASSEMBLY inLineLib
FROM 'D:inLineServerCLRSQLinlineCLRsqlinDebuginlineCLRsql.dll'
WITH PERMISSION_SET = SAFE
Everything works after those steps (which took some trial and error). I can create a sproc like:
CREATE PROC sp_test AS
EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage
and it returns the Queue ID
Is there anything unadvisable about the steps above?
Thanks for your help
M
Error Registering Assembly Using CREATE ASSEMBLY
May 1, 2008We 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.
Error: CREATE ASSEMBLY For Assembly
May 31, 2007I 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
CREATE ASSEMBLY Using Assembly Binary?!?!
Mar 8, 2006I 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#.
Anyone out there been able to get this to work?
-Ben
Assembly.Load Can't Load My Custom Assembly From The GAC.
Mar 7, 2007Hi there.
I have an assembly, call it A1, that I've deployed to a SQL Server 2005 database. I can use the managed stored procedures from A1 in SQL Server no problem.
In A1 there is a bit of code which uses the Assembly.Load() method, so load another assembly and use instances of class found in that external assembly. However, when I run the managed stored proc in A1 that uses Assembly.Load() I get the error:
Could not load file or assembly 'A1, Version=1.0.0.0, Culture=neutral,PublicKeyToken='????' or one of its dependencies. The system cannot find the file specified.
(note: for security I've changed some of the above line).
So I changed the Assembly.Load() to use
System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089
I re-built the project, re-deployed it and ran the code in SQL Server - it worked. I could create an instance of a System.Data.DataSet for example. So why can't I load my own custom assembly? My assembly does have a strong name and it's installed in the GAC. I wrote a console app to try and Assembly.Load() my custom assembly and that worked fine (it was also running on the same server as the SQL Server).
So it's defiantely the SQL Server that can't 'see' my customer assembly. What do I need to do this assembly so that SQL Server will allow me to Assembly.Load it, just as it can with System.Data?
Thanks
Jas.
How To Create A Assembly
Sep 13, 2006Hi,
I have one ClassLibrary three member functions.I have the dll for this...Can some one please tell me how to create a Assembly in .NET.
I need to create a simple private assembly with the dll that i have.
Thanks
How To Add Assembly In GAC To SQL 2005?
Jan 12, 2007I want use Microsoft.Office.Interop.Excel to access excel file my clr. But that assembly is installed in GAC. How could I register it in SQL 2005?
thanks!
How To Deploy An Assembly (CLR)?
Nov 2, 2006Good day to ALL,
I have already setup a db to my client.
My sp's are all created using CLR.
If my sp's are changed, how do I deploy them back to my client?
I usually send them a backup of the DB during the first few implementation.
But currently, their DB now contains live data, so I can't just let them restore the backup.
Is there another way?
Thanks and more power!
Arthur
Cannot Create Assembly
May 30, 2007I'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?
Thanks in advance.
Methods Within An Assembly
Apr 4, 2007Is there a way to retrieve the methods within an assembly that are attributed with SqlTrigger or SqlProcedure using T-SQL?
View 1 Replies View RelatedHow To Add A Signed CLR Assembly
Aug 30, 2012I'm trying to add an assembly to my SSDT project, and in my assembly, it requires EXTERNAL access, which also means that the assembly has to be signed, and an asymmetric key must be created from the assembly. Â I can't figure out how to get this to work from the SSDT project.
View 8 Replies View RelatedSQL 2005 && CLR Assembly
Feb 22, 2008 Hi.
One of my databases has an assembly that uses system.web. Therefore, in the deploy script, the developer has put in a create assembly for it like so
Create Assembly [System.Web]
FROM 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727System.Web.dll'
WITH PERMISSION_SET = UNSAFE
followed by the create assembly statements for the actual bespoke assemblies.
The deploy script all works fine, as do the assemblies it creates.
However, when SQL restarts, the calls to the assemblies no longer work, and the only way to get them to is to run the deploy script (which drops them if they exist and recreates them) again.
Not particularly familiar with 2005 & CLR. Is this normal behaviour (I would have expected that once deployed, they were there after re-boot). If it's not how things are supposed to work, what's wrong, and how do I fix it?
Thanks
Calling .net Assembly In DTS Package
Sep 26, 2006Hi everyone, I want to call the .net assembly(DLL) in a DTS package. Can anyone help me as to how to achieve this. I read numerous articles on internet, but couldn't find the one that can help me with this problem.Any help or directing me to an article will be greatly appreciated.Thanks.Vinki
View 9 Replies View RelatedSSIS - Need To Reference .NET Assembly Without GAC
Jan 25, 2007I have a .NET assembly that I need to reference and use within my SSIS script task.
The only way I can acheive this is to strongly name my assembly and put it in the GAC.
This is not possible as my assembly is already in production and uses other 3rd party assemblies that would also need to be registered in the GAC.
As a workaround, I have created a .Net console application that references my assembly, that call from a SSIS Process Task.
Does anyone know of another way I could use my .NET assembly within my SSIS package?
Any help appreciated.
Regards,
Paul.
Assembly For Stored Procedure
Feb 24, 2006I was playing around with the CLR in writing assemblies for the sql server 2005 stored procedure. I guess the example i found was for the beta version
This line is from the beta but no longer works. Any ideas what will fix this. There is no longer GetCommand property.
SqlCommand cmd = SqlContext.GetCommand();
Example
public partial class StoredProcedures{[Microsoft.SqlServer.Server.SqlProcedure] public static void StoredProcedure1()
{
// Put your code here
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText="select firstname + ' ' + lastname + as [name] from person.contact";
SqlDataReader rdr = cmd.ExecuteReader();
SqlPipe sp = SqlContext.GetPipe();
sp.Send(rdr);
}};
Error While Creating Assembly
Apr 12, 2007I am trying to create an assembly on a sql server 2005 machine but it gives me following error:
Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.
I tried using the alter authorization statement to change the owner.
It did not work.
I am able to create same assembly on another test database but can not create it on this database.
Is this because of orphan logins?
Thanks for the help.
Harshal.
Error When Trying To Create Assembly
Dec 27, 2006Hi,
I ran the following in the management studio:
CREATE ASSEMBLY MyCLRAssembly from 'c: empMyCLRAssembly.dll'
WITH PERMISSION_SET = SAFE
Go
For some reason I get the following error:
Msg 6517, Level 16, State 1, Line 1
Failed to create AppDomain 'AdventureWorks.dbo[ddl].22'.
I tried to google this error, but couldn't find it...
I would appreciate it if someone can tell me what I'm doing wrong.
Ohad.
Error While Creating Assembly
Apr 12, 2007I am trying to create an assembly on a sql server 2005 machine but it gives me following error:
Msg 33009, Level 16, State 2, Line 2
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.
I tried using the alter authorization statement to change the owner.
It did not work.
The same assembly is created on another test database but can not create it on this database.
Is this because of orphan logins?
Thanks for the help.
Harshal.
.NET Assembly For BULK INSERT
Sep 8, 2006Hi,Is there any .NET package/module/assembly for BCPing the data into SQLServer.The BULK INSERT utility works fine, but we are kind of worried onplacing the data.If there is any utility to build the data into a data structure and BCPin into the SQL Server, please let know. It would be of great use.Let know if I should post this elsewhereRegards,Thyagu
View 3 Replies View RelatedUsing Trigger To Notify An Assembly Of Changes.
Oct 13, 2006I have a requirement to create a sorted list of objects, stored withinan assembly, and I would like to use a After Insert/Update trigger tonotify that assembly that something has changed, rather than pollingthe database for changes all the time.My initial problem is that I need to create a static list so that itcan be dynamically looked at by other assemblies, but SQL Server won'tallow me to attach a dll which contains a static method.Has anyone done something like this? Are there any decent examplesavailable anywhere?any help will be appreciated.simo
View 4 Replies View RelatedCLR Assembly Redeploy Problem.
Oct 31, 2007I have created a C# library containing CLR stored procedures and user-define functions using Visual Studio 2005, and I have used VisualStudio to deploy the assembly to a SQL Server 2005 instancesuccessfully, of course these sps and udfs are used by other T-SQL spsand udfs. Now the assembly has a new version, and I want to use it toreplace the original one, but when I use Visual Studio 2005 to deploythe new assembly, the following error occurs:Error1Cannot drop the function 'TranslationStringLike', because itdoes not exist or you do not have permission.Cannot drop the function 'TranslateEngString', because it does notexist or you do not have permission.Cannot drop the function 'TranslateEngStringReverse', because it doesnot exist or you do not have permission.DROP ASSEMBLY failed because 'FirmBankCLR' is referenced by object'TranslationStringLike'.FirmBankCLRThat is the assembly is dependent by other objects, it can't bedropped until it is not dependent by other objects. How can I resolvethis problem using Visual Studio 2005 or something else?
View 3 Replies View RelatedCustom Assembly SecurityException
Feb 26, 2008
Hi,
I'm trying to do something rather weird, but I really need it. I'm trying to launch a SSIS package from a report
I built a custom assembly with one method that loads a package from file and then executes it. The assembly works fine.
I added the custom assembly to the report properties, instantiated the class in the assembly (the method is not static), called the method from the report (a textbox renders the string return value from the method).
I added the
Code Snippet[assembly: System.Security.AllowPartiallyTrustedCallers]
attribute to the AssemblyInfo.cs file
Created the strong name for the assembly.
Got the PublicKeyBlob with ILDASM, used it to add a new CodeGroup with the "StrongNameMembershipCondition" in the policy configuration files of ReportDesigner (RSPreviewPolicy.config), ReportServer (rssrvpolicy.config) and ReportManager (rsmgrpolicy.config).
Copied the dll in the bin folders of ReportServer and Visual Studio PrivateAssemblies.
I keep getting the awfull #Error message on the report when viewing it from ReportManager or ReportServer (in preview mode in VisualStudio all is well because security is not applied to user code).
I tried debugging:
- created a solution with two subprojects, one is the report, the other one is the assembly
- marked the report as the Startup Item
I get a SecurityException in debug and debuglocal modes when I try to instantiate a SSIS package.
Code SnippetPackage = new Package();
While digging in the exception call stack, it turns out I lack a permission for accessing unmanaged code (the source assembly of the exception is mscorlib). Also in the ouput window, while starting debugging i see a lot of SystemArgument exceptions in mscorlib.
I am perfectly aware that in order to be able to launch a SSIS package from code I referenced Microsoft.SqlServer.DtsRuntimeWrap.dll, so in desperation I added another CodeGroup in the aforementioned config files for this dll (StrongNameMembershipCondition) and copied the assembly in the deployment folders.
1: I would really like to know how to get the whole thing working. I find rather strange that even though I set the FullTrust
PermissionSet for my assembly (and the Microsoft.SqlServer.DtsRuntimeWrap assembly) I keep getting security exceptions.
While exploring the web for help I saw I need to assert all individual permissions for every restricted resource I'm accessing in the custom assembly, problem is I don't know exactly which ones I'm accessing and, being new to .NET security, I don't know how.
Surely I need to assert a file IO permission to load the package from file, a sqlclientpermission if I am accessing SqlServer in the package, and perhaps a unmanagedcode permission, given the the exception I described earlier (perhaps the Microsoft.SqlServer.DtsRuntimeWrap assembly is not managed code or is accessing some other unmanaged assembly).
Again, I wouldn't know how.
...and last
2: I tried modifying the assembly to only load a string from a file.. I had some problems with security but I got it working once, but I haven't figured out how exactly 'cause when fiddling around to understand things, I lost the working configuration. Really stupid, I know....
While testing this version of the assembly I followed an advice I found on some blog and restarted IIS and Reporting Services system service..
Is this always necessary after modifying the assembly source ? Do I need to restart every time both IIS AND Reporting Services
I would really appreciate every bit of help or advice anyone has to offer.
Thanks in advance.
Using Custom Assembly && Gacutil && Sn
Jan 22, 2008Hello experts,
I need some help please.
Yesterday I get the answer that I had to use an assembly if I would like to use a function as global. Right now I€™m very desperate because my VS didn€™t recognize my DLL.
I get the error: "Could not load file or assembly .....The system cannot find the file specified."
After red the threads I tried to use gacutil.exe to add my assembly to the cache.
This action fails with the error:€? Failure adding Assembly to cache. Attempt to install an assembly without a strong name.€?
After that I tried to build a strong name through the follow execute:
Sn €“Vr ClassLibrary.dll. This action fails too. I get the follow Error description: €œdll does not represent a strongly named assembly€?.
What made me wrong?
Is there an easier workaround?
Have someone a good Idea could help me?
Hope to hear you soon.
Best regards,
Alex
CLR In SQL2005 With Assembly Containing Dllimports
Jan 20, 2006I am trying to call some functions within a 3rd party dll that I have wrapped with .NET 2.0 managed code. The same calls work when I use them within a Windows exe, but don't work when called from a SQL UDF. Is this a limitation with SQL CLR or am I doing something wrong here?
The class declarations in the CLR are:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
Public Class ALC_Tech
<DllImport("PCMSRV32.DLL", EntryPoint:="PCMSOpenServer", ExactSpelling:=False, CharSet:=System.Runtime.InteropServices.CharSet.Ansi, SetLastError:=True)> _
Public Shared Function PCMSOpenServer(ByVal appInst As Int32, ByVal hWnd As Int32) As Int16
End Function
<DllImport("PCMSRV32.DLL", EntryPoint:="PCMSCloseServer", ExactSpelling:=False, CharSet:=System.Runtime.InteropServices.CharSet.Ansi, SetLastError:=True)> _
Public Shared Function PCMSCloseServer(ByVal serverID As Int16) As Int32
End Function
<DllImport("PCMSRV32.DLL", EntryPoint:="PCMSCalcDistance2", ExactSpelling:=False, CharSet:=System.Runtime.InteropServices.CharSet.Ansi, SetLastError:=True)> _
Public Shared Function PCMSCalcDistance2(ByVal serverID As Int16, ByVal orig As System.String, ByVal dest As System.String, ByVal routeType As Int32) As Int32
End Function
End Class
Public Class pcMiler
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function pcMiler_OpenServer(ByVal appInst As Int32, ByVal hWnd As Int32) As Int16
Dim ret As Int16
ret = ALC_Tech.PCMSOpenServer(appInst, hWnd)
Return ret
End Function
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function pcMiler_CloseServer(ByVal serverID As Int16) As Int32
Dim ret As Int32
ret = ALC_Tech.PCMSCloseServer(serverID)
Return ret
End Function
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function pcMiler_CalcDistance2(ByVal serverID As Int16, ByVal orig As System.String, ByVal dest As System.String, ByVal routeType As Int32) As Int32
Dim ret As Int32
ret = ALC_Tech.PCMSCalcDistance2(serverID, orig, dest, routeType)
Return ret
End Function
End Class
After creating the assembly on SQL Server, the T-SQL used to create the functions are:
CREATE FUNCTION dbo.pcMiler_OpenServer(@appInst int, @hWnd int)
RETURNS smallint
AS EXTERNAL NAME ALC_Tech.[pcMiler].[pcMiler_OpenServer]
GO
CREATE FUNCTION dbo.pcMiler_CalcDistance2
(@serverID smallint,
@orig nchar,
@dest nchar,
@routeType int)
RETURNS int
AS EXTERNAL NAME ALC_Tech.[pcMiler].[pcMiler_CalcDistance2]
GO
CREATE FUNCTION dbo.pcMiler_CloseServer
(@serverID smallint)
RETURNS int
AS EXTERNAL NAME ALC_Tech.[pcMiler].[pcMiler_CloseServer]
GO
The T-SQL used to test is:
SET NOCOUNT ON
GO
DECLARE @serverID smallint,
@orig nchar,
@dest nchar,
@routeType int,
@appInst int,
@hWnd int,
@retcd int
SELECT @serverID = NULL,
@orig = 'Boston, MA',
@dest = 'Hartford, CT',
@routeType = 1,
@appInst = 0,
@hWnd = 0
SELECT @serverID = dbo.pcMiler_OpenServer(0, 0)
SELECT @serverID
SELECT dbo.pcMiler_CalcDistance2(@serverID, @orig, @dest, @routeType)
SELECT @retcd = dbo.pcMiler_CloseServer(@serverID)
SELECT @retcd
When I run this test SQL, the dbo.pcMiler_OpenServer just hangs and the process can't be killed. The calls work when I call them from a Windows form, but hang when calling from a SQL UDF. I would appreciate any suggestions or feedback because at this point I am not sure if these calls are possible.
Barry
Could Not Load File Or Assembly
Jul 14, 2015I try backup database but this error message pop up: could not load file or assembly 'SqlManagerUi, Culture=neutral, PublicKeyToken= 89845dcd 8080cc91' oe one of its dependencies. Method signature has invalid calling convention. (Exception from HRESULT: 0x80131239)(mscorlib)
View 2 Replies View Related