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.

msbuild for Code Analysis and Build/Rebuild Log generation

msbuild.exe commandline can be useful to enable Code Analysis and log the output of visual studio solutions or project files.

Rule set for the code analysis will be same as what developer has set with his proj file/ solution.

eg:

"C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe" "\SolutionFile.sln" /P:RunCodeAnalysisOnce=True  /t:rebuild     /verbosity:quiet /logger:FileLogger,Microsoft.Build.Engine;logfile="\DebuggedOutPut.log" 

The Log file will give detail information about the CA errors with details and details on Errors and other warnings from Solution file.

Passing parameter to command line option of setup.exe - using VB Script

1.       Installation of setup.exe in silent mode by passing arguments.

How to Run setup.exe in silent>

            strEXE = here & "\setup.exe"

commandline=""& strEXE &" /s “

retVal=wsh.Run(commandline, 1, true )

                How to pass parameters in silent mode installation

                        here = fso.GetParentFolderName(Wscript.ScriptFullName)

wsh.CurrentDirectory = here

INSTALLDIR="\"& chr(34) &"C:\TEST Folder\My Dest\"& chr(34)

REPLACETHIS="\"& chr(34) &"APPNAME_2013_V3\"& chr(34)

REPLACE2="\"& chr(34) &"APPNAME\"& chr(34)

REPLACEDESCR="\"& chr(34) &"Information\"& chr(34)

LogFile ="C:\Test\My Logs\MyTest EXE.log"

strParams = "INSTALLDIR=" & INSTALLDIR & _

                " REPLACETHIS=" & REPLACETHIS &  _

                " REPLACE2=" & REPLACE2 & _

                " REPLACEDESCR=" &REPLACEDESCR& _

                ""

strEXE = here & "\setup.exe"

commandline=""& strEXE &" /s /v""/qb /L*v \"""& LogFile &"\"" " & strParams & "" & chr(34)

retVal=wsh.Run(commandline, 1, true )

msgbox(retVal)

Get Character from ASCII value in Batch

cmd /c exit 34
SET strChar= %=exitcodeAscii%
:: reuse strChar
echo %strChar%

List & Drop all Procedures and Tables in a Database

I) List all Stored procedures in a database:

Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

OR

Select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%'

Please note that I have added the 'NOT LIKE' part to get rid of stored procedures created during database installation.

II) Delete all [user created] Stored procedures in a database:

Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

This would list down all the user defined Stored procedure as 'Drop Procedure procedureName' in the result pane. Just copy-paste it into the query window and execute it once.


Drop all tables:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"