Creating DB from scripts: assembly

Showing posts with label assembly. Show all posts

Thursday, March 29, 2012

Creating an Assembly that calls a COM object

Hi,
I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
which in turn calls some COM stuff. I get an error message in SQL Server 2005
related to COM interoperability (does not exist in SQL catalog). I can give
you the exact error message if needed. Any ideas on how this can be handled?
Appreciate any help. Thanks.Can anyone help me with my question? Thanks.
"KMP" wrote:
> Hi,
> I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
> which in turn calls some COM stuff. I get an error message in SQL Server 2005
> related to COM interoperability (does not exist in SQL catalog). I can give
> you the exact error message if needed. Any ideas on how this can be handled?
> Appreciate any help. Thanks.|||Here is the error message (below) that I get when I try to create an
assembly in SQL Server2005 pointing to Test.dll (just an example name).
Test.dll (which is C# dll) refers to some COM objects inside it. I thinking
that is what is causing the Assembly to fail. I hope this info is good
enough. Please let me know if I need to pass on any more info. Thanks.
=====================================================
Assembly "interop.testlib,
version=0.0.0.0,culture=neutral,publickeytoken=null" was not found in SQL
catalog
====================================================="Kent Tegels" wrote:
> Hello KMP,
> Providing the error and code in question is always a good idea. Did you catalog
> the assembly as unsafe?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I was wondering if anyone could help me with this issue. Thanks in
anticipation...
"Kent Tegels" wrote:
> Hello KMP,
> > Here is the error message (below) that I get when I try to create an
> > assembly in SQL Server2005 pointing to Test.dll (just an example
> > name). Test.dll (which is C# dll) refers to some COM objects inside
> > it. I thinking that is what is causing the Assembly to fail. I hope
> > this info is good enough. Please let me know if I need to pass on any
> > more info. Thanks.
> Its been ages since I need to do something like this from even regular .NET.
> My first thought is that you probably need to run TLBIMP and get a PIA into
> the directory with your DLL so that it also catalogs it. You may also have
> to manually deploy it.
> I'm pushing this over to the CLR newsgroup and we'll see if Niels chimes
> in with additional thoughts.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||=?Utf-8?B?S01Q?= <KMP@.discussions.microsoft.com> wrote in news:88F5ED5D-
B63B-436D-AB35-B5B90AA66C75@.microsoft.com:
> I was wondering if anyone could help me with this issue. Thanks in
> anticipation...
>
Sorry, I didn't see this post until now. Can you please re-post the issue
you have.
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************|||I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
which in turn calls some COM stuff. I get an error message in SQL Server 2005
related to COM interoperability (does not exist in SQL catalog). I can give
you the exact error message if needed. Thanks.
"KMP" wrote:
> Hi,
> I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
> which in turn calls some COM stuff. I get an error message in SQL Server 2005
> related to COM interoperability (does not exist in SQL catalog). I can give
> you the exact error message if needed. Any ideas on how this can be handled?
> Appreciate any help. Thanks.|||What is "m.p.ss.clr"?
"Kent Tegels" wrote:
> Hello KMP,
> > I am trying to create an Assembly in SQL Server 2005 that calls a C#
> > DLL which in turn calls some COM stuff. I get an error message in SQL
> > Server 2005 related to COM interoperability (does not exist in SQL
> > catalog). I can give you the exact error message if needed. Thanks.
> Reposting to m.p.ss.clr, please continue thread there.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Probably a newsgroup on this server named microsoft.public.sqlserver.clr.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:B98ADDDA-970A-42F1-92A9-831A2DBD51B9@.microsoft.com...
> What is "m.p.ss.clr"?
> "Kent Tegels" wrote:
>> Hello KMP,
>> > I am trying to create an Assembly in SQL Server 2005 that calls a C#
>> > DLL which in turn calls some COM stuff. I get an error message in SQL
>> > Server 2005 related to COM interoperability (does not exist in SQL
>> > catalog). I can give you the exact error message if needed. Thanks.
>> Reposting to m.p.ss.clr, please continue thread there.
>> Thank you,
>> Kent Tegels
>> DevelopMentor
>> http://staff.develop.com/ktegels/
>>|||I haven't found this newsgroup. Can you please send me a link (if
available)? Thanks.
"Tibor Karaszi" wrote:
> Probably a newsgroup on this server named microsoft.public.sqlserver.clr.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:B98ADDDA-970A-42F1-92A9-831A2DBD51B9@.microsoft.com...
> >
> > What is "m.p.ss.clr"?
> >
> > "Kent Tegels" wrote:
> >
> >> Hello KMP,
> >>
> >> > I am trying to create an Assembly in SQL Server 2005 that calls a C#
> >> > DLL which in turn calls some COM stuff. I get an error message in SQL
> >> > Server 2005 related to COM interoperability (does not exist in SQL
> >> > catalog). I can give you the exact error message if needed. Thanks.
> >>
> >> Reposting to m.p.ss.clr, please continue thread there.
> >>
> >> Thank you,
> >> Kent Tegels
> >> DevelopMentor
> >> http://staff.develop.com/ktegels/
> >>
> >>
> >>
>|||Seems MS haven't refreshed the web site for this yes
(http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx). But if you refresh your
newsreader, the group should show up.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:29900A69-0704-40D1-AFC1-05644386F690@.microsoft.com...
> I haven't found this newsgroup. Can you please send me a link (if
> available)? Thanks.
> "Tibor Karaszi" wrote:
>> Probably a newsgroup on this server named microsoft.public.sqlserver.clr.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:B98ADDDA-970A-42F1-92A9-831A2DBD51B9@.microsoft.com...
>> >
>> > What is "m.p.ss.clr"?
>> >
>> > "Kent Tegels" wrote:
>> >
>> >> Hello KMP,
>> >>
>> >> > I am trying to create an Assembly in SQL Server 2005 that calls a C#
>> >> > DLL which in turn calls some COM stuff. I get an error message in SQL
>> >> > Server 2005 related to COM interoperability (does not exist in SQL
>> >> > catalog). I can give you the exact error message if needed. Thanks.
>> >>
>> >> Reposting to m.p.ss.clr, please continue thread there.
>> >>
>> >> Thank you,
>> >> Kent Tegels
>> >> DevelopMentor
>> >> http://staff.develop.com/ktegels/
>> >>
>> >>
>> >>
>>

Thursday, March 22, 2012

Creating a System.Management assembly in order for my own Assembly to work?

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:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

3- CREATE ASSEMBLY inLineLibMaster

FROM 'D:\inLine\Server\inLineLib\bin\Debug\inLineLib.dll'

WITH PERMISSION_SET = unsafe

4- and finally

CREATE ASSEMBLY inLineLib

FROM 'D:\inLine\Server\CLRSQL\inlineCLRsql\bin\Debug\inlineCLRsql.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

System.Management is not supported by CLR integration in SQL Server 2005. What this means is that it has not been tested for reliability and security to work perfectly under a hosted environment. Some (or all) parts of this assembly might work just fine and some (or none) might not work reliably under conditions like memory pressure, stack overflow, stress etc. You need to test your scenario well (for reliablilty and security) and ensure that it works for you. The list of supported .NET framework assemblies is following:

CustomMarshalers

Microsoft.VisualBasic

Microsoft.VisualC

mscorlib

System

System.Configuration

System.Data

System.Data.OracleClient

System.Data.SqlXml

System.Deployment

System.Security

System.Transactions

System.Web.Services

System.Xml

Thursday, March 29, 2012

Creating an Assembly that calls a COM object

Creating an Assembly that calls a COM object

Creating an Assembly that calls a COM object

Thursday, March 22, 2012

Creating a System.Management assembly in order for my own Assembly to work?

Creating DB from scripts

Blog Archive

About Me