Friday, 16 November 2007

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')

Thursday, 15 November 2007

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"

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

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

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)
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 = ""
objMail.To = ""
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")
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
if len(newString) > 0 then
'add a space char for long string
newString = newstring & " " & replace(stringSegment," ","")
newString=replace(stringSegment," ","")
end if
if len(newString) > 0 then
'add a space char between words
newString = newString & " " & replace(words(iWordsCount)," ","")
newString=replace(words(iWordsCount)," ","")
end if
End if
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.

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

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()

'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============")
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)
Return Nothing
End If
Catch ex As Exception
Throw ex
End Try
End Function
End Class


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

Dim WebserviceUrl As String = ""

'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)