How to get return code after BTEQ excuted SQL in VBScript

Tools & Utilities
Fan

How to get return code after BTEQ excuted SQL in VBScript

Hi,
I want to write a .vbs to let BTEQ excut SQL files. But I don't know how to get return code after BTEQ excuted SQL. Anyone can help me?
Here is the sample code.

'Run BTEQ
Set WshShell = CreateObject("WScript.Shell")
WshShell.run("bteq")
Wscript.sleep 3000
'Logon
WshShell.SendKeys ".Logon testUser{ENTER}"
Wscript.sleep 1000
WshShell.SendKeys "testUser{ENTER}"
'Excute SQL
Wscript.sleep 1000
WshShell.SendKeys ".run file=""C:\test.sql""{ENTER}"
'Get return code
'???

Regards
5 REPLIES
Teradata Employee

Re: How to get return code after BTEQ excuted SQL in VBScript

You may want to ask this question in a Microsoft (WSH) forum; it really has nothing to do with Teradata or BTEQ. I suspect you will need to use Exec method rather than Run.
Fan

Re: How to get return code after BTEQ excuted SQL in VBScript

Hi Fred ,
Thank you for your advise. It seems Run can excute BTEQ but cannot get any return while exec can get stdout, but it can not excute BTEQ....

Does anyone know how to excute BTEQ use exec function?
Enthusiast

Re: How to get return code after BTEQ excuted SQL in VBScript

I am not a VB guy, but I suspect that you would also want to add a quit statement at the end of all this.

WshShell.SendKeys ".quit {ENTER}"

bteq won't terminate in interactive mode without something like that.... and unless any program terminates, you ain't getting any return code.
Enthusiast

Re: How to get return code after BTEQ excuted SQL in VBScript

Hi,
Think, that exec should work, see: http://www.microsoft.com/technet/scriptcenter/guide/sas_wsh_pkoy.mspx?mfr=true

You can also use bteq in batch mode and redirect stdout to file for further processing: "bteq bteq_out.txt"
where bteq_wrap.txt contains just a wrapper to open and close bteq session and calls your script saved in "fresh_file.sql":

.SET ERROROUT STDOUT
.LOGON tdpid/userid, password

.RUN FILE="fresh_file.sql"

.LOGOFF
.EXIT

It could be easier for debugging, but may it is not so useful in your case :/

Petr
Enthusiast

Re: How to get return code after BTEQ excuted SQL in VBScript

uff, it's difficult to upload <, pls. replace LT resp. GT by corresponding symbols for "Less Then" and "Great Then"

There should be in the quotation marks 'bteq "LT"bteq_wrap.txt "GT"bteq_out.txt'