Herbee
bot software developmentTSQL: user defined function split
CREATE FUNCTION [dbo].[udfSplit](@text nvarchar(max), @delimiter char(1) = ‘ ‘)
RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(max) )
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text) BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index – 1))
SET @text = RIGHT(@text, (LEN(@text) – @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) – @index))
END
RETURN
END
parameters:
@text: text to split
@delimiter: character
returns:
table with columns position und value
SELECT * FROM [OLReweAbf].[dbo].[udfSplit] (‘test’,'t’)
| position | value |
| 1 | es |
example:
Update LBFulfillmentKNORueckmeldungAuftrag
SET PaketGew = (select sum(cast([value] as bigint)) from [dbo].[udfSplit](PaketGew,’:'))
WHERE VersandartID LIKE ‘LKW%’ AND PaketGew LIKE ‘%:%’ AND TrackingNr is NULL
new: Herbee´s design t-shirts shop
“Altstadtcafe – speaker ‘n wings on leaves”
white girlie tee classic . we carry t-shirts in all colors and sizes. Stunning at Herbee Shirts.
new ISP
I changed ISP: installed KabelDeutschland today and the result is not so bad ^^
hmmm … the upload could be better :/
web Instant Messenger for Second Life
i found it today: web|SIM – Instant Messenger for Second Life
it´s from a german guy: SL Name Keane Koga
“You can register at a terminal inworld and get a free premium access for 31 days.
(freebie tarif from 1L$ per month)
With the registration you´ll get the web|SIM-HUD.
With this HUD you are able to send IMs from SL to his website.
The SL password is not needed for web|SIM !”
get imported namespaces of a schema and their schema location
''' <summary>
''' get all imported namespaces of a schema and their schema location
''' </summary>
''' <param name="oSchema">schmema</param>
''' <returns>Dictionary(imported namespace, schema location) </returns>
''' <remarks></remarks>
Function RecurseExternalSchemas(ByVal oSchema As XmlSchema) As Dictionary(Of String, String)
Dim oResult As New Dictionary(Of String, String)
For Each oExternal As XmlSchemaExternal In oSchema.Includes
If oExternal.GetType() Is GetType(XmlSchemaImport) Then
Dim import As XmlSchemaImport = CType(oExternal, XmlSchemaImport)
If Not oExternal.SchemaLocation = Nothing Then
oResult.Add(import.Namespace, oExternal.SchemaLocation)
Else
oResult.Add(import.Namespace, String.Empty)
End If
End If
If Not oExternal.Schema Is Nothing Then
RecurseExternalSchemas(oExternal.Schema)
End If
Next
RecurseExternalSchemas = oResult
End Function
..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..
how to check if a column in a datareader exists
”’ <summary>
”’ checks if a column in a datareader exists
”’ </summary>
”’ <param name=”reader”>SqlDataReader</param>
”’ <param name=”sColumnName”>columnname</param>
”’ <returns>true, column exists else false</returns>
”’ <remarks></remarks>
Public Function ColumnExists(ByVal reader As SqlDataReader, ByVal sColumnName As String) As Boolean
reader.GetSchemaTable().DefaultView.RowFilter = “ColumnName= ‘” + sColumnName + “‘”
Return (reader.GetSchemaTable().DefaultView.Count > 0)
End Function
..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..
a recursive walk through XPathNavigator
Sub RecursiveWalkThroughXpath(ByVal oNavigator As XPathNavigator)
Select Case oNavigator.NodeType
Case XPathNodeType.Element
If oNavigator.Prefix = String.Empty Then
Console.WriteLine(“<{0}>”, oNavigator.LocalName)
Else
Console.Write(“<{0}:{1}>”, oNavigator.Prefix, oNavigator.LocalName)
Console.WriteLine(vbTab + oNavigator.NamespaceURI)
End If
Case XPathNodeType.Text
Console.WriteLine(vbTab + oNavigator.Value)
End Select
If oNavigator.MoveToFirstChild() Then
Do
RecursiveWalkThroughXpath(oNavigator)
Loop While (oNavigator.MoveToNext())
oNavigator.MoveToParent()
If (oNavigator.NodeType = XPathNodeType.Element) Then
Console.WriteLine(“</{0}>”, oNavigator.Name)
End If
Else
If oNavigator.NodeType = XPathNodeType.Element Then
Console.WriteLine(“</{0}>”, oNavigator.Name)
End If
End If
End Sub
..:: Whereever you go, stay in touch. Download toolbar now! It´s free, private and secure. ::..
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. ::..
Stumble It!
