asked on

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.

RobSampson

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 Diaz

ASKER

Thank you Rob, I will test it tomorrow.

Luis Diaz

ASKER

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?

RobSampson

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 Diaz

ASKER

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!

RobSampson

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 Diaz

ASKER

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

RobSampson

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

Rob.

Luis Diaz

ASKER

Hello Rob,

Sorry for that.

Please find attached the TEST.csv

RobSampson

Sorry, still can't see it....

Luis Diaz

ASKER

Here is the file, let me know if all is ok now.
TEST.csv

Luis Diaz

ASKER

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.

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

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.

RobSampson

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 Diaz

ASKER

Got it! Thank you again Rob!