Creating SQL Server Linked Server using VBS

Here is a sample script for creating Linked Server using Vb script.

createLinkedSrvQuery "MACNAME\INSTANCENAME", "LINKEDSERVERNAME", "MAC_To_EstablishConnection\INSTNAME", "SERVICE_ACC_UNAME", "rmtUserLS", "rmtPwdLS"

Function createLinkedSrvQuery(strInstance, strLnkdSrv, dataSource, locLogin, rmtUser, rmtPwd)
    On Error Resume Next
    database = "master"
    strConnParams = "Provider=SQLOLEDB; Data Source=" & strInstance & "; Integrated Security=SSPI"

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open strConnParams

    strQuery = "IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'" & strLnkdSrv & "') EXEC master.dbo.sp_dropserver @server=N'" & strLnkdSrv & "', @droplogins='droplogins'"
    strQuery = strQuery & " BEGIN "
    strQuery = strQuery & " EXEC master.dbo.sp_addlinkedserver @server = N'" & strLnkdSrv & "', @srvproduct=N'SQLOLEDB', @provider=N'SQLNCLI', @datasrc=N'" & dataSource & "', @catalog=N'master' "
    strQuery = strQuery & "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'" & strLnkdSrv & "',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL "
    strQuery = strQuery & "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'" & strLnkdSrv & "',@useself=N'False',@locallogin=N'" & locLogin & "',@rmtuser=N'" & rmtUser & "',@rmtpassword='" & rmtPwd & "' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'collation compatible', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'data access', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'dist', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'pub', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'rpc', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'rpc out', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'sub', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'connect timeout', @optvalue=N'0' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'collation name', @optvalue=null "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'lazy schema validation', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'query timeout', @optvalue=N'0' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'use remote collation', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'remote proc transaction promotion', @optvalue=N'true' "
    strQuery = strQuery & "END"



    objConn.Execute strQuery
 

End Function

Public IP using VBS

Dim o
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\windows\PublicIP.txt",True)
Set o = CreateObject("MSXML2.XMLHTTP")
o.open "GET", "http://ifconfig.me/ip", False
o.send
objFile.WriteLine "Public IP: " & o.responseText

You can even refer http://checkip.dyndns.com/
for getting Public IP

VBS to check the existence of a Key in Registry

We can use simple vbscript for checking the existence of a Key in Registry.
Here is the sample code:


IsKeyExists("KeyName")

Function IsKeyExists(KeyName)

  Const HKCR = &H80000000 'HKEY_CLASSES_ROOT
  Const HKCU = &H80000001 'HKEY_CURRENT_USER
  Const HKLM = &H80000002 'HKEY_LOCAL_MACHINE
  Const HKUS = &H80000003 'HKEY_USERS
  Const HKCC = &H80000005 'HKEY_CURRENT_CONFIG

  Dim oReg: Set oReg = GetObject("winmgmts:!root/default:StdRegProv")
  KeyPath = "SOFTWARE\Microsoft\Windows\" & KeyName
  If oReg.EnumKey(HKLM, KeyPath, arrSubKeys) = 0 Then
    IsProductInstalled = True
  Else
KeyPath = "SOFTWARE\WoW6432Node\Microsoft\Windows\CurrentVersion\Uninstall\" & GUID
If oReg.EnumKey(HKLM, KeyPath, arrSubKeys) = 0 Then
MsgBox("Key Exists")
Else
MsgBox("Key is not exists")
End If
  End If
End Function

Debug vbscript using Visual studio

From command prompt we can call vbs to debug the code.

Follow the steps.

1. Open cmd.exe and call vbs in cscript.exe

2. Select Visual Studio 2010 option and click on Yes.

3. Your vbs will open in Visual studio editor, and you can use Visual studio Dubugging options for VBS also.

eg: using F11, F10, applying breakpoints etc.