Given below is a way to access the file contents of a database server using the latest feature of SQL Server 2005 called CLR integration.
The theory and source code can be accessed from http://blogs.msdn.com/sqlclr/archive/20 ... 15034.aspx.
The compilation is quite staraight forward and all you need to do is create a dll file from the C# code by creating a Class Library.
You may face some problems giving the assembly external_access.
The error maybe something like
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'TVF' failed because assembly 'TVF' 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.
In my case I was executing the statements as an administrator hence the database owner had the EXTERNAL ACCESS ASSEMBLY permission so all I needed to do was alter the database to make it trustworthy by executing the command
alter database {database name} set trustworthy on
You may also be required to set the database compatibility level to the highest value using :-
sp_dbcmptlevel {database name} , 90
Note :- Make sure that you set the Enable CLR Integration option in the Surface Area Configuration to true.
Many a times there are situations when you want to split a string in a field based on a seperator like a comma(",") or maybe a semicolon(";"). Here's an SQL function which would allow you to do just that.
CREATE FUNCTION [dbo].[Split_with] (@sep varchar(max), @s varchar(max))
RETURNS @mytab table (rowid int,keys varchar(max))
AS
[more]BEGIN
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CAST(CHARINDEX(@sep, @s) AS INT)
UNION ALL
SELECT pn + 1, stop + 1, CAST(CHARINDEX(@sep, @s, stop + 1) AS INT)
FROM Pieces
WHERE stop > 0
)
INSERT @mytab
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
OPTION (MAXRECURSION 10000)
RETURN
END
To make the function work you need to call it like:-
SELECT * FROM Split_With(',',(SELECT {field} FROM {table} Term where {filter a single row} ))
Ofcourse the function will work on a single field at a time. All you need to do a recurse through the entire table using a cursor or a while loop.
Hope this helps whoever needs it.
All JAR files contain something called a manifest file which holds information Java wants to know. One piece of information a manifest file may contain is the name of a class that will be run if the JAR file is executed.
The first thing you must do is create a text file that lists the "main" class - the class that has the main method you want executed when the JAR is executed. Let's say that Three from the above example has the main method I want executed. I create a text file called "mainClass.txt" with the following text:
Main-Class: Three
IMPORTANT: the text file only needs the one line of text for this purpose. However, the file must end with a blank line or this will not work, ie the file has two lines in it - the second one is empty. Note too the class is called "Three" and not "Three.java" (the file containing the source code) or "Three.class" (the file containing the byte codes). If your class file is in a package hierarchy, you must use the fully qualified name of the class (eg "myPackage.MyClass").
I then run the jar utility with this command line:
jar cmf mainClass.txt example.jar *.class
With this line, I told jar to create a JAR file (option c) with modifications to the manifest file (option m) as specified within mainClass.txt, naming the JAR file (option f) as example.jar and including everything that matches the pattern *Class
Source:- http://neptune.netcomp.monash.edu.au/Ja ... to/jar.htm