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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.