We help IT Professionals succeed at work.

VB Script: use sqlcmd in VBscript

Luis Diaz
Luis Diaz asked
on
Hello experts,

I have the following sqlcmd code:

@echo off

sqlcmd -S server -d db -U user -P password -i "file1.sql" -o "TEST.csv" -s";" -w 700 -W

findstr /B /V /C:"-------------------;-----------;-------;-------" TEST.csv > test_match_combined.csv

del TEST.csv
Pause

Open in new window


I would like to introduce this code into VBscript with the following requirements:
The following commands need to be defined at the beginning of the code as variables:
sqlcmdrun, "-S", "-d", "-U", "P", "-i", "-o"
If DB is not accessible log ouput: "Error, Unable to connect to the db, please check db variables".

Thank you for your help.
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, see whether this works.  I haven't included the FindStr functionality yet.

Regards,

Rob.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
strCurrentFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")

strSqlCmdExe = "C:\Program Files\Microsoft SQL Server\90\Tools\Bin\sqlcmd.exe"
strServer = "Server"
strDB = "db"
strUser = "user"
strPass = "password"
strInput = strCurrentFolder & "file1.sql"
strOutput = "TEST.csv"

If objFSO.FileExists(strSqlCmdExe) = True Then
	If objFSO.FileExists(strInput) = True Then
		strSqlCmdExe = objFSO.GetFile(strSqlCmdExe).ShortPath
		strInput = objFSO.GetFile(strInput).ShortPath
		strCommand = strSqlCmdExe & " -S " & strServer & " -d " & strDB & " -U " & strUser & " -P " & strPass & " -i " & strInput & " -d " & strDB & " -s"";"" -w 700 -W"
		intReturn = objShell.Run(strCommand, 1, True)
		If intReturn = 0 Then
			MsgBox "Command completed successfully"
		Else
			MsgBox "Command did not complete successfully. Return code " & intReturn
		End If
	Else
		MsgBox "Unable to find " & strInput
	End If
Else
	MsgBox "Unable to find " & strSqlCmdExe
End If

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you Rob, I will test it tomorrow.
Luis DiazIT consultant

Author

Commented:
Hello Rob,

I tried your code and I am able to launch it without any error. However the csv file is not exported so I was wondering if there is a char(34) to add in strInput variable.

Additionnally I don't understand why we call SQCMD.EXE, I use the following code wich use a SQLCMD variable without calling the exe:


	
Option Explicit
Const ForReading = 1, ForWriting = 2, ForAppending = 8 
	dim oSQLs , oSQLDB , sResult, oFSO , oQres , sServer, oShell
	
	dim sMA_name , sUser,sPwd, sDBname , logFile , sOutputFolder

	dim  sqlRun , i , Outputfile , sTmp, sDataLoc, exists
	
	sServer="mySQL"
	sUser = "myuser"
	sPwd = "mypwd"
	sDBname = "mydb"
	
	logFile = "C:\ee\test1.txt"
	
	sOutputFolder = "c:\ee\"   'note the trailing backslash
  
  sMA_name = sOutputFolder  & sDBname & "_now_date("  & year(now) & "_" & month(now) & "_" & day(now) & "_" & hour(now) & "_" & minute(now) & "_" & second(now) & ").bak"
	

	wscript.echo "<" & sMA_name & ">"
	
	set oShell = Createobject("wscript.shell")
	set oFSO = CreateObject("Scripting.FileSystemObject")	


'------ Phase 0 : ==== setup logging
	
	dim f , oFS 

	if oFSO.FileExists(logFile) then
    		set f = oFSO.GetFile(logFile)  
    		set oFS = f.OpenAsTextStream(ForAppending,0)
  	else
    		set oFS = oFSO.CreateTextFile(logFile, True)
	end if
	oFS.writeline "Start : " & now()
	
'------- Phase 1 : ==== checks
	
exists = oFSO.FolderExists(sOutputFolder )

if not exists then 
    oFS.WriteLine "Error  Outputfolder not found : " & sOutputFolder
    wscript.echo "<error5"
		wscript.quit
end if

if not IsValidFile(sDBname) then
    oFS.WriteLine "Error  DBName has invalid characters : " & sDBname
    wscript.echo "<error6"
		wscript.quit
end if
	
'Phase 4 : ======== detach the DB ============
	'on error resume next
	'on error goto 0	
	
	'C:\Program Files\Microsoft SQL Server\110\Tools\Binn
	Outputfile = "BACKUP DATABASE " & sDBname & " TO DISK='" & sMA_name & "' WITH FORMAT"
	
	SQLrun = "sqlCmd -U " & sUser & " -P " & sPwd & " -Q " & chr(34) & Outputfile & chr(34)
	wscript.echo "<" & sqlRun & ">"

	sResult = oShell.run (SQLRun, 2, True)
	
	if sResult=0 then
		oFS.WriteLine "4.1 bakup ok"
		wscript.echo "<ok"
	 else
		oFS.WriteLine "4.2 Error bakup"
		wscript.echo "<error"
	end if
	
	if Err.number > 0 then
		oFS.WriteLine "Error@4.1 : " & err.number &  Err.description
		oFS.WriteLine SQLrun
		wscript.quit
	end if
	
	
	Err.Clear
	on error resume next
	


Public Function IsValidFile(sFileName ) 
'-------------------------------------------------------------------------
' Procedure : IsValidFile
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (www.jkp-ads.com)
' Created   : 31-10-2009
' Purpose   : Returns TRUE if filename contains no invalid characters
'-------------------------------------------------------------------------
    dim sInvalidChars 
    sInvalidChars = "<>?[]:|*!"
    Dim lCt 
    If (Len(sFileName) > 0) Then
        For lCt = 1 To Len(sInvalidChars)
            If InStr(sFileName, Mid(sInvalidChars, lCt, 1)) > 0 Then
                IsValidFile = False
                Exit Function
            End If
        Next
        IsValidFile = True
    End If
End Function

Open in new window



Can we apply the same process of the code above?

Thank you so much for your feedback?
Most Valuable Expert 2012
Top Expert 2014

Commented:
Can you try again with this code?  I had two -d parameters, forgot the -o, so that's why you never got the CSV file.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
strCurrentFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")

strSqlCmdExe = "C:\Program Files\Microsoft SQL Server\90\Tools\Bin\sqlcmd.exe"
strServer = "Server"
strDB = "db"
strUser = "user"
strPass = "password"
strInput = strCurrentFolder & "file1.sql"
strOutput = strCurrentFolder & "TEST.csv"

If objFSO.FileExists(strSqlCmdExe) = True Then
	If objFSO.FileExists(strInput) = True Then
		strSqlCmdExe = objFSO.GetFile(strSqlCmdExe).ShortPath
		strInput = objFSO.GetFile(strInput).ShortPath
		strCommand = strSqlCmdExe & " -S " & strServer & " -d " & strDB & " -U " & strUser & " -P " & strPass & " -i " & strInput & " -o """ & strOutput & """ -s"";"" -w 700 -W"
		intReturn = objShell.Run(strCommand, 1, True)
		If intReturn = 0 Then
			MsgBox "Command completed successfully"
		Else
			MsgBox "Command did not complete successfully. Return code " & intReturn
		End If
	Else
		MsgBox "Unable to find " & strInput
	End If
Else
	MsgBox "Unable to find " & strSqlCmdExe
End If

Open in new window


The SQLCmd.exe executable must always be called with these parameters.  I have just put the path to sqlcmd.exe in a variable.  It should still work the same.

Rob.
Luis DiazIT consultant

Author

Commented:
Thank you very much it works!

Concerning the SQLCMD.exe it means that if a user want to launch the script and it doesn't have SQL it would not work?
Is it possible to add a loop in order to remove the extra dash "------------------  -----------  -------" placed in the first line of the TEST.csv. Removing the second of the file will be perfect.

Thank you in advance for your help!
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'm sorry, I must have missed this, although I thought I posted a question.....must have not happened.

Can you show me a sample of the output file, and what it is you would like removed?

Regards,

Rob.
Luis DiazIT consultant

Author

Commented:
Hello Rob,

Thank you for your feedback.
Here is a sample of the file generated by a select with the current.
The thing that I want to do is to remove the line 2 which have extra dash.
Everytime when I made a select I have those dash in line 2.

I supose that with a openfile for writting this should be ok.

Thank you in advance for your feedback.

This is your last code which works perfectly

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
strCurrentFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")

strSqlCmdExe = "C:\Program Files\Microsoft SQL Server\90\Tools\Bin\sqlcmd.exe"
strServer = "Server"
strDB = "db"
strUser = "user"
strPass = "password"
strInput = strCurrentFolder & "file1.sql"
strOutput = strCurrentFolder & "TEST.csv"

If objFSO.FileExists(strSqlCmdExe) = True Then
	If objFSO.FileExists(strInput) = True Then
		strSqlCmdExe = objFSO.GetFile(strSqlCmdExe).ShortPath
		strInput = objFSO.GetFile(strInput).ShortPath
		strCommand = strSqlCmdExe & " -S " & strServer & " -d " & strDB & " -U " & strUser & " -P " & strPass & " -i " & strInput & " -o """ & strOutput & """ -s"";"" -w 700 -W"
		intReturn = objShell.Run(strCommand, 1, True)
		If intReturn = 0 Then
			MsgBox "Command completed successfully"
		Else
			MsgBox "Command did not complete successfully. Return code " & intReturn
		End If
	Else
		MsgBox "Unable to find " & strInput
	End If
Else
	MsgBox "Unable to find " & strSqlCmdExe
End If

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, sorry, I can't see the sample output file.  Can you post it again?

Rob.
Luis DiazIT consultant

Author

Commented:
Hello Rob,

Sorry for that.

Please find attached the TEST.csv
Most Valuable Expert 2012
Top Expert 2014

Commented:
Sorry, still can't see it....
Luis DiazIT consultant

Author

Commented:
Here is the file, let me know if all is ok now.
TEST.csv
Luis DiazIT consultant

Author

Commented:
If you are unable to access the file here is the content of the csv file.

owning_organization;Cardinality;POT ID;STT ID
-------------------;-----------;-------;-------
S.tetff;Jan-00;EMEAS 12g14;

Line 2 needs to be removed.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Hi, sorry for my delay.  See how this goes.

Regards,

Rob.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
strCurrentFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")

strSqlCmdExe = "C:\Program Files\Microsoft SQL Server\90\Tools\Bin\sqlcmd.exe"
strServer = "Server"
strDB = "db"
strUser = "user"
strPass = "password"
strInput = strCurrentFolder & "file1.sql"
strOutput = strCurrentFolder & "TEST.csv"

If objFSO.FileExists(strSqlCmdExe) = True Then
	If objFSO.FileExists(strInput) = True Then
		strSqlCmdExe = objFSO.GetFile(strSqlCmdExe).ShortPath
		strInput = objFSO.GetFile(strInput).ShortPath
		strCommand = strSqlCmdExe & " -S " & strServer & " -d " & strDB & " -U " & strUser & " -P " & strPass & " -i " & strInput & " -o """ & strOutput & """ -s"";"" -w 700 -W"
		intReturn = objShell.Run(strCommand, 1, True)
		If intReturn = 0 Then
			MsgBox "Command completed successfully"
		Else
			MsgBox "Command did not complete successfully. Return code " & intReturn
		End If
	Else
		MsgBox "Unable to find " & strInput
	End If
Else
	MsgBox "Unable to find " & strSqlCmdExe
End If
 
Set objFile = objFSO.OpenTextFile(strOutput, 1, False)
strContents = ""
While Not objFile.AtEndOfStream
	strLine = objFile.ReadLine
	If Trim(Replace(Replace(strLine, "-", ""), ";", "")) <> "" Then
		If strContents = "" Then
			strContents = strLine
		Else
			strContents = strContents & vbCrLf & strLine
		End If
	End If
Wend
objFile.Close
Set objFile = objFSO.CreateTextFile(strOutput, True)
objFile.Write strContents
objFile.Close

Open in new window

Luis DiazIT consultant

Author

Commented:
Hello Rob,
It works perfect! Thank you again for your help.

I am trying to understanding the loop:

'Every line which contains "-" or ";" is replaced by "" and if this is different than "" do nothing
If Trim(Replace(Replace(strLine, "-", ""), ";", "")) <> "" Then
'???
            If strContents = "" Then
                  strContents = strLine
            Else
'????
                  strContents = strContents & vbCrLf & strLine
            End If


Could you please just add the comment so I understand what has been done? Thank you in advance for your help.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, here is that section commented as to how I was thinking about it when I wrote it:
	' Check to see whether a line contains ONLY - and ; characters, by replacing those characters with nothing, and
	' checking if the resulting string is equal to ""
	If Trim(Replace(Replace(strLine, "-", ""), ";", "")) <> "" Then
		' If the line contains more than just - and ; characters, add it to the output
		If strContents = "" Then
			strContents = strLine
		Else
			strContents = strContents & vbCrLf & strLine
		End If
	End If

Open in new window


I hope that helps.

Regards,

Rob.
Luis DiazIT consultant

Author

Commented:
Got it! Thank you again Rob!