July 23, 2008 at 5:16 am
I am writing a VBScript which in turn uses sqlcmd to execute the sql script file. I need your help in trapping errors on execution of sqlcmd.
What could be the possible ways to trap error messages and then display error messages?
sqlcmd -s testserver -d testdatabase -E -i "c:\temp\sqltest.sql"
Any suggestions or advices?
July 23, 2008 at 5:59 am
was doing research on internet, i found that i could use -b and -r tags in order to trap error messages, but i cant see anything in return from sqlcmd command on it use.
the way i am using sqlcmd in vbscript is
strcmp = "sqlcmd -s testserver -d testdatabase -r -b -i ""c:\tmp\query.sql"""
Set obj_Shell = CreateObject("WScript.Shell")
obj_Shell.Exec(strcmp)
i didnt get any error message on execution of above script.
i also found another script on internet which traps every message returned on execution of the vb script.
Set obj_Shell = CreateObject("WScript.Shell")
Set obj_WSH = obj_Shell.Exec("sqlcmd -s testserver -E -d testdatabase -i c:\tmp\query.sql")
Set obj_STDOUT = obj_WSH.StdOut
str_Output = obj_STDOUT.ReadAll
WScript.Echo "output:" & str_Output
but there is problem with above script, it return all messages like (2 rows effected) and warnings and error messages .
do you guys have any other way in mind to trap errors or warnings?
July 24, 2008 at 7:30 am
With your current solution, you could add "SET NOCOUNT ON" as the first line of your input file to suppress the "number of rows affected" messages. Also, "osql /?" will show you switches to suppress other output, such as "-h-1" to eliminate column headings and those underline rows from output.
December 21, 2014 at 11:19 pm
Hi Anam,
I know this is an old thread, but i was just recently busy with calling SQLCMD from vbscript.
Possibly this can be of some help for somebody else.
Facing the same problem as you have, i decided to lookup the methods for 'Shell.exec'
This is what i came up with.
The Code below is called from within a function (RunSqlCmdFile ) in the vbscript.
Set objShell = CreateObject("WScript.Shell")
comspec = objShell.ExpandEnvironmentStrings("%comspec%")
Set objExec = objShell.Exec(comspec & " /c " & strCommand)
If Not objExec.StdErr.AtEndOfStream Then
WriteErrorLines "Error Occured inside sqlcmd execution.", "", "SQLCMD",objExec.StdErr.ReadAll, intWarning, ""
Else
Do
WScript.StdOut.WriteLine(objExec.StdOut.ReadLine())
Loop While Not objExec.Stdout.atEndOfStream
'--WScript.StdOut.WriteLine(objExec.StdOut.ReadAll)
End If
RunSqlCmdFile = objExec.StdOut.ReadAll
Wkr,
Van Heghe Eddy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply