VB Script: use sqlcmd in VBscript

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.
LVL 1
LD16Asked:
Who is Participating?
 
RobSampsonCommented:
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

0
 
RobSampsonCommented:
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

0
 
LD16Author Commented:
Thank you Rob, I will test it tomorrow.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
LD16Author 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?
0
 
RobSampsonCommented:
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.
0
 
LD16Author 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!
0
 
RobSampsonCommented:
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.
0
 
LD16Author 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

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

Rob.
0
 
LD16Author Commented:
Hello Rob,

Sorry for that.

Please find attached the TEST.csv
0
 
RobSampsonCommented:
Sorry, still can't see it....
0
 
LD16Author Commented:
Here is the file, let me know if all is ok now.
TEST.csv
0
 
LD16Author 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.
0
 
LD16Author 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.
0
 
RobSampsonCommented:
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.
0
 
LD16Author Commented:
Got it! Thank you again Rob!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.