Creating the CLR Stored Procedure : CLR « CLR « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. CLR
3. CLR

vbc /t:library /out:C:\ReadFiles.DLL /r:"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll" C:\ReadFiles.vb"

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO

Public Class ReadFiles
    Public Shared Sub Main(ByVal sFile As SqlString)
        Dim sReader As StreamReader = New StreamReader(sFile)
        Dim sLine As String
        Dim sPipe As SqlPipe = SqlContext.Pipe

        Do
            sLine = sReader.ReadLine()
            If Not sLine Is Nothing Then
                sPipe.Send(sLine)
            End If
        Loop Until sLine Is Nothing
        sReader.Close()
    End Sub
End Class
////////////////////

CREATE PROCEDURE dbo.usp_FileReader
(@FileName nvarchar(1024))
AS EXTERNAL NAME ReadFiles.ReadFiles.Main
GO

EXEC dbo.usp_FileReader
N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1'