Query between 2 servers

We can query between two servers
SQL Server query between two servers.

as below
select IntroducerID as eh_IntroducerID, [name] as ec_InroducerName, userid as ec_userid from [192.168.NNN.XXX].[mydatabasename1].[dbo].[table1] as t1
inner join [192.168.NNN.XYZ.[mydatabasename2].[dbo].[table2] as t2
on t1.ColumnId=t2.Columnid
where t1.status=1 and t2.columnname in( 'djgfd','skdd')

VB and VBScript passing array to a function

Private Sub PassArray()
Dim myArray() 'Declare a Static Integer Array of 4 elements
Dim arrayIndex

arrayIndex = 0
ReDim Preserve myArray(arrayIndex)
myArray(0) = "one"

arrayIndex=arrayIndex+1
ReDim Preserve myArray(arrayIndex)
myArray(1) = "two"

arrayIndex=arrayIndex+1
ReDim Preserve myArray(arrayIndex)
myArray(2) = "three"

arrayIndex=arrayIndex+1
ReDim Preserve myArray(arrayIndex)
myArray(3) = "four"

Call AcceptArray(myArray) 'Call the procedure and pass the Array
End Sub

Private Sub AcceptArray(outArray())
dim iCount
For iCount=0 to ubound(outArray)
WScript.echo outArray(iCount)
Next
End Sub

CDONTS multiple attachments sendmail

CDONTS_Object.AttachFile(Physical_Path_to_File, English_Name_For_File)

Assuming your files already exist on the server:

1) Include the Server.MapPath() or specify the full file path.
It doesn't seem to like virtual paths
2) Set the BodyFormat and Mail Format to Zero(0)
3) Then repeat the Attachfile method for each file

<%
Dim objMail
Set objMail = Server.CreateObject("CDONTS.NewMail")
objMail.From = "test@myserver.com"
objMail.To = "youremail@account.net"
objMail.Subject = "Multiple File Attachments"
objMail.Body = "Intentionally Left Blank"
objMail.BodyFormat = 0
objMail.MailFormat = 0
objMail.AttachFile( Server.MapPath("images") & "\file1.jpg")
objMail.AttachFile( Server.MapPath("images") & "\file2.jpg")
objMail.AttachFile( Server.MapPath("images") & "\file3.jpg")
objMail.Send
Set objMail=Nothing
%>

VB, VBScript Method to cut long words in a text,

'function to add spaces between long string
Function FormatString(LongString, maxWordLen)
' Split the string at the space characters.
Dim words
words = Split(LongString, " ")

Dim iWordsCount,iCharCount, newString, stringSegment

'for each words
For iWordsCount = 0 To UBound(words)

'for lenght of the string
if len(words(iWordsCount))>maxWordLen then
for iCharCount = 1 to len(words(iWordsCount)) step maxWordLen
'get string segment
stringSegment=mid(words(iWordsCount),iCharCount,maxWordLen)
if len(newString) > 0 then
'add a space char for long string
newString = newstring & " " & replace(stringSegment," ","")
else
newString=replace(stringSegment," ","")
end if
next
Else
if len(newString) > 0 then
'add a space char between words
newString = newString & " " & replace(words(iWordsCount)," ","")
else
newString=replace(words(iWordsCount)," ","")
end if
End if
Next
FormatString = newString
End Function

Call webservice without adding webreference:

1. Create a class as with "DynamicWebService" and new method "CallWebService".
2. Create an object od this class and call method "CallWebService" with web service url and other paramaters.

CLASS:
Create a new class and replace the code for this:

<------------------------------

Imports System.CodeDom
Imports System.CodeDom.Compiler
Imports System.Security.Permissions
Imports System.Web.Services.Description
Imports System.Reflection

Public Class DynamicWebService

Public Function CallWebService(ByVal webServiceAsmxUrl As String, ByVal serviceName As String, ByVal methodName As String, ByVal args() As Object) As Object
Try

Dim client As System.Net.WebClient = New System.Net.WebClient()

'-Connect To the web service
Dim stream As System.IO.Stream = client.OpenRead(webServiceAsmxUrl + "?wsdl")

'Read the WSDL file describing a service.
Dim description As ServiceDescription = ServiceDescription.Read(stream)

'LOAD THE DOM'''''''''''''''''''''''''''
'--Initialize a service description importer.
Dim importer As ServiceDescriptionImporter = New ServiceDescriptionImporter()
importer.ProtocolName = "Soap12" ' Use SOAP 1.2.
importer.AddServiceDescription(description, Nothing, Nothing)

'--Generate a proxy client.
importer.Style = ServiceDescriptionImportStyle.Client

'--Generate properties to represent primitive values.
importer.CodeGenerationOptions = System.Xml.Serialization.CodeGenerationOptions.GenerateProperties

'Initialize a Code-DOM tree into which we will import the service.
Dim nmspace As CodeNamespace = New CodeNamespace()
Dim unit1 As CodeCompileUnit = New CodeCompileUnit()
unit1.Namespaces.Add(nmspace)

'Import the service into the Code-DOM tree.
'This creates proxy code that uses the service.
Dim warning As ServiceDescriptionImportWarnings = importer.Import(nmspace, unit1)
If warning = 0 Then

'--Generate the proxy code
Dim provider1 As CodeDomProvider = CodeDomProvider.CreateProvider("VB")

'--Compile the assembly proxy with the // appropriate references
Dim assemblyReferences() As String
assemblyReferences = New String() {"System.dll", "System.Web.Services.dll", "System.Web.dll", "System.Xml.dll", "System.Data.dll"}

Dim parms As CompilerParameters = New CompilerParameters(assemblyReferences)
Dim results As CompilerResults = provider1.CompileAssemblyFromDom(parms, unit1)

'-Check For Errors
If results.Errors.Count > 0 Then
Dim oops As CompilerError
For Each oops In results.Errors
System.Diagnostics.Debug.WriteLine("========Compiler error============")
System.Diagnostics.Debug.WriteLine(oops.ErrorText)
Next
Throw New System.Exception("Compile Error Occured calling webservice.")
End If

'--Finally, Invoke the web service method
Dim wsvcClass As Object = results.CompiledAssembly.CreateInstance(serviceName)
Dim mi As MethodInfo = wsvcClass.GetType().GetMethod(methodName)
Return mi.Invoke(wsvcClass, args)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
End Class
----------------------------------------------->

CALLING:

Call this web service in a form or method etc... as below..

--------------------------
Dim WebserviceUrl As String = "http://www.abc.com/lgl/test/webservice/v1_00/security.asmx"

'specify service name
Dim serviceName As String = "SecurityAndSessionManagement"

'specify method name to be called
Dim methodName As String = "Session_Start"

'Paraments passed to the method
Dim arArguments(1) As String
arArguments(0) = "abc"
arArguments(1) = "xxxx"

Dim objCallWS As New DynamicWebService
sSessionID = objCallWS.CallWebService(WebserviceUrl, serviceName, methodName, arArguments)
MsgBox("new SessionID: " & sSessionID)
---------------------

good article for XSL: The Complete Guide

http://www.sagehill.net/docbookxsl/

Build custon XML from database using XmlTextWriter

Simple way to out put as xml is using
dim ds as dataset
ds.WriteXml("C:\test\HCSReports\createdXML\employee.xml")

if you need custom xml output then use the below function

Imports System.Xml
Imports System.Xml.XPath
Imports System.Xml.Xsl

  Public Function CreateCustomXML(ByVal objDataSet As DataSet) As String

        Dim intI As Integer
        Dim strPath As String

        'create an instance of the XmlTextWriter object
        Dim objWriter As XmlTextWriter = Nothing

        Try

            ' location to the XML file to write
            strPath = strFilepath & Now.Second & ".xml"

            objWriter = New XmlTextWriter(strPath, System.Text.Encoding.Default)

            ' start writing the XML document
            objWriter.WriteStartDocument()

            ' write a comment in our XML file
            objWriter.WriteComment("PanelManagerMonthlyReport")

            ' starting with the root element i.e. "movies"
            objWriter.WriteStartElement("PMMonthlyReport")

            'Set data to XML tags
            If objDataSet.Tables(0).Rows.Count > 0 Then
                For intI = 0 To objDataSet.Tables(0).Rows.Count - 1
                    objWriter.WriteStartElement("Period")    ' output the "Employee" element
                    objWriter.WriteElementString("Name", objDataSet.Tables(0).Rows(intI).Item("Name").ToString)
                    objWriter.WriteElementString("Prospects", objDataSet.Tables(0).Rows(intI).Item("Prospects").ToString)
                    objWriter.WriteElementString("Instructions", objDataSet.Tables(0).Rows(intI).Item("Instructions"))
                    objWriter.WriteElementString("Cancellations", objDataSet.Tables(0).Rows(intI).Item("Cancellations"))
                    objWriter.WriteElementString("Completions", objDataSet.Tables(0).Rows(intI).Item("Completions"))
                    objWriter.WriteElementString("Quotes", objDataSet.Tables(0).Rows(intI).Item("Quotes"))
                    objWriter.WriteElementString("IntroducerUserRegistrations", objDataSet.Tables(0).Rows(intI).Item("IntroducerUserRegistrations"))
                    objWriter.WriteElementString("IntroducerUserTrained", objDataSet.Tables(0).Rows(intI).Item("IntroducerUserTrained"))
                    objWriter.WriteElementString("SolicitorAppsIssued", objDataSet.Tables(0).Rows(intI).Item("SolicitorAppsIssued"))
                    objWriter.WriteElementString("SolicitorContractsReturned", objDataSet.Tables(0).Rows(intI).Item("SolicitorContractsReturned"))
                    objWriter.WriteElementString("Index", objDataSet.Tables(0).Rows(intI).Item("Index"))
                    objWriter.WriteEndElement()                 ' close "Employee" element
                Next
            End If

            ' end the "Menu" element
            objWriter.WriteEndElement()

            ' flush and write XML data to the file
            objWriter.Flush()

            Return strPath
        Catch ex As Exception
            Return False
        Finally
            ' clear up memory
            objWriter.Close()
            objWriter = Nothing
            objDataSet = Nothing
        End Try
    End Function