SQL help: 'create assembly' on SQL Server 2000

[D.P.]

Firstly, I have more or less zero knowledge of SQl but I have some SQL code that needs to be installed on a clients DB. We don't have access to the clients server to directly test things and it is paramount that nothing bad happens to their DB.


The SQL code is a DB trigger that is called "after insert", e.g. when an event is entered in to the DB the trigger is called. The trigger passes the data to an external windows DLL library, which in turn sends the data to our remote server through UDP.


The SQL works fine in SQL Server 2005 but the "create assembly" procedures do not seem to be supported on SQL Server 2000.

I need a simple work around to accomplish this task. It is perfectly feasible to replace the DLL with some other means of establishing a UDP connection to our server.


I am very lost since I really don't have a clue about SQL. Any advice much appreciated. So far the advice seems to be update to >= SQL 2005, but that is simply not feasible since it is our clients DB and government agencies don't like changing their software (we still access computers running NT4, win95, and earlier....).

 

[D.P.]

Seems I may have a solution but since I really have no clue about SQL/COM/.NET/C# i don't really know.

Seems I can try to replace the .DLL with a COM object and then use some alternative SQL commands:
http://elankathir.wordpress.com/2008/06/17/call-the-dll-or-the-com-object-from-your-sql-server-2000/

http://www32.brinkster.com/srisamp/sqlArticles/article_31.htm


Since the DLL uses some .NET features I need to do something like the following:
http://www.codeproject.com/Articles/3511/Exposing-NET-Components-to-COM



Anyone have any comments on the above solution?

 

[Mr^B]

Personally that sounds ghastly.

I'd write a windows service which watches a table. The trigger which would have called the .dll instead writes a row into that table, the windows service picks up this change and uses the .dll to create the UDP connection (and marks the table entry as processed so it doesn't get picked up again).

Seriously, the more logic and piping and whatnot you put into SQL Server the more problems you'll have. Use it for what it's good at ie. storing and retrieving data.

 

[D.P.]

We aren't allowed to touch/modify the database in anyway what so ever. There are a lot of events being written to this database so we only want to listen to incoming events and forwards them on to our server.


The original approach of using the trigger to call some procedures in an external .dll works really well with clients running SQL server =>2005. TBH, you are probably 100% as I do not really understand the systems architecture or any database related systems at all so I am not really in the position to propose and develop a completely new solution.

One of the things preventing running a windows service is we typically do not have permission to access or run anything else on these DB systems, so that is why we had a .DLL which passed the data to a separate server through UDP. The whole setup is a little convoluted and there is a quite a long chain of systems forwarding the data onto various intermediate servers before we really get where we want it.

In time if I get comfortable developing SQL/windows services etc. then I could think of streamlining a lot of the data acquisition we do.


Anyway, with regards to my previously mentioned solution I think I have the .NET library compiling to be compatible as a COM object. I may have problems with some of the SQL variable types but I think I can work around that.

My problem now is I am finding SQL and SQL server incredibly complex, and hard to grasp, it took hours just to get MS SQL server 2000 installed.

 

[Mr^B]

You don't have to modify the DB, you already have an "on insert" trigger, you can roll your own insert trigger, but entirely outside of SQL Server, all you needs is a windows box to host the service and a read-only connection to it. That's why I suggested it, you don't need to touch SQL at all.