CLR: run a SQL script from a .sql file

Public Function RunSQLScript(ByVal Filename As String, ByRef Errortext As String) As Boolean
Dim file As New FileInfo(Filename)
Dim script As String = String.Empty
Dim command As SqlCommand = Nothing
Try
script = file.OpenText().ReadToEnd()
Catch ex As Exception
Errortext = ex.Message
RunSQLScript = False
Exit Function
End Try
script = script.Replace(“GO”, “”)
‘Optional to Replace Comments with empty string
script = Regex.Replace(script, “([/*][*]).*([*][/])”, “”)
‘Optional to Replace Chain of spaces with one Space
script = Regex.Replace(script, “\\s{2,}”, ” “)
Try
command = DBCnn.CreateCommand
command.CommandType = CommandType.Text
command.CommandText = script
command.ExecuteNonQuery()
RunSQLScript = True
Catch ex As SqlException
Errortext = ex.Message
RunSQLScript = False
End Try
DBCnnClose()
End Function

Public Function RunSQLScript(ByVal Filename As String, ByRef Errortext As String) As Boolean
Dim file As New FileInfo(Filename)        Dim script As String = String.Empty        Dim command As SqlCommand = Nothing
Try
script = file.OpenText().ReadToEnd()
Catch ex As Exception
Errortext = ex.Message            RunSQLScript = False            Exit Function
End Try
script = script.Replace(“GO”, “”)        ‘Optional to Replace Comments with empty string        script = Regex.Replace(script, “([/*][*]).*([*][/])”, “”)        ‘Optional to Replace Chain of spaces with one Space        script = Regex.Replace(script, “\\s{2,}”, ” “)
Try
command = DBCnn.CreateCommand            command.CommandType = CommandType.Text            command.CommandText = script            command.ExecuteNonQuery()            RunSQLScript = True
Catch ex As SqlException
Errortext = ex.Message            RunSQLScript = False
End Try
DBCnnClose()
End Function

CLR: arithmetic overflow while insert into a table containing a datetime column

problem: you get an arithmetic overflow error while you want in your CLR stored procedure to insert into a table containing a datetime column
reason: datetime-format on sql serveris set up differently than you use it
workaround:  you can use SET DATEFORMAT before you call you CLR stored procedures.

example:

USE Database
GO

DECLARE    @iReturnvalue int
–set datetime format you want
SET DATEFORMAT ymd;
GO

— call CLR stored procedure
EXEC   [dbo].[sp] @iMandant = 1, @iReturnvalue = @iReturnvalue OUTPUT

CLR stored procedure to create a external schema (xsd) or a xml file including schema from a SQL Server table

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub spLBgenerateInstallscript( _
ByVal sTablename As String, _
<Out()> ByRef iReturnvalue As SqlInt32)

Dim oCommand As SqlCommand
Dim oDom As New Xml.XmlDocument
Dim oDom2 As New Xml.XmlDocument
Dim oReader As Xml.XmlReader
Dim sXML As String = String.Empty
Dim oSB As New StringBuilder
Dim sOutputfilename As String

Using oConnection As New SqlConnection(“context connection=true”)
Try

oConnection.Open()
oCommand = oConnection.CreateCommand
oCommand.CommandText = “SELECT * FROM ” & sTablename & ” FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS, ROOT (‘” & sTablename & “‘)”
oCommand.CommandType = CommandType.Text
oReader = oCommand.ExecuteXmlReader

oReader.Read()
Do While oReader.ReadState <> Xml.ReadState.EndOfFile
sXML &= oReader.ReadOuterXml()
Loop
oReader.Close()
oDom.LoadXml(sXML)

Dim schema As Xml.XmlNode = oDom.ChildNodes(0).FirstChild

Dim mynode As Xml.XmlNode = oDom2.ImportNode(schema, True)
oDom2.AppendChild(mynode)

‘write schema only
sOutputfilename = “C:\” & sTablename & “_” & String.Format(“{0:” & “yyyyMMddHHmmss” & “}”, Now)
oDom2.Save(sOutputfilename & “.xsd”)

‘write xml with inline schema
oDom.Save(sOutputfilename & “.xml”)

iReturnvalue = 0

Catch SQLex As Exception

#If DEBUG Then
EmitDebugMessage(“spLBgenerateInstallscript exeption SQLex: ” & SQLex.Message)
#End If
iReturnvalue = 1

Finally

If oConnection.State <> ConnectionState.Closed Then
oConnection.Close()
End If

End Try
End Using

End Sub

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..

sample XmlReader Validation Handler

”’ <summary>
”’ XmlReader Validation Handler
”’ </summary>
”’ <param name=”sender”></param>
”’ <param name=”e”></param>
”’ <remarks>not used, not ready</remarks>
Sub oXmlReaderSettingsValidationEventHandler(ByVal sender As Object, ByVal e As ValidationEventArgs)
If e.Severity = XmlSeverityType.Warning Then
Console.Write(“WARNING: “)
Console.WriteLine(e.Message)
ElseIf e.Severity = XmlSeverityType.Error Then
Console.Write(“ERROR: “)
Console.WriteLine(e.Message)
End If
End Sub

..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..