Invoking BCP from a VBScript - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Invoking BCP from a VBScript

Author

Topic

sbrief58
Starting Member

2 Posts

Posted - 2012-06-27 : 11:15:11

Hi

I am trying to invoke a BCP from a VBScript. The script is not throwing an error, but I don't get any output. Could you please look at what I did and let me know what the problem is? I am new to VBScripting so any help would be VERY appreciated. My goal is to double click the .vbs file and have the bcp run with out any manual intervention. I have provided the script below ..

Thank you
Shari

'--- FORMAT THE BATCH NUMBER FOR THE CUFF FILE NAMES
sYEAR = DatePart("yyyy",now)
sMONTH = DatePart("m",now)
sDAY = DatePart("d",now)
sHOUR = DatePart("h",now)
sMINUTE= DatePart("n",now)
sSECOND= DatePart("s",now)

IF LEN(TRIM(sYEAR)) < 4 THEN SMONTH = 20 & DatePart("yyyy",now) END IF
IF LEN(sMONTH) < 2 THEN sMONTH = 0 & DatePart("m",now) END IF
IF LEN(sDAY) < 2 THEN sDAY = 0 & DatePart("d",now) END IF
IF LEN(sHOUR) < 2 THEN sHOUR = 0 & DatePart("h",now) END IF
IF LEN(sMINUTE) < 2 THEN sMINUTE= 0 & DatePart("n",now) END IF
IF LEN(sSECOND) < 2 THEN sSECOND= 0 & DatePart("s",now) END IF

sBATCH = sYEAR & sMONTH & sDAY & sHOUR & sMINUTE & sSECOND

'--- CREATE FILE NAMES FOR CUFF
CUFF_FILENAME_TAG = ".TXT.READY"
CUFF_WP_FILENAME = "APP_WRK_" & sBATCH & CUFF_FILENAME_TAG
CUFF_PH_FILENAME = "APP_HDR_" & sBATCH & CUFF_FILENAME_TAG
CUFF_FH_FILENAME = "APP_HE_" & sBATCH & CUFF_FILENAME_TAG
CUFF_FT_FILENAME = "APP_TL_" & sBATCH & CUFF_FILENAME_TAG

'--- LET'S BCP OUT SOME CUFF PERF HEADER DATA ;)

CUFF_OUTFILE= "J:\" & CUFF_PH_FILENAME
sSERVER = "SQLeDev3"
sUSERID = "pmfuser"
sPASSWORD = "pmfuser"

SET objSHELL = WScript.CreateObject("WSCRIPT.SHELL")
objShell.run "cmd /c DPMFIW.dbo.tblGS_CUFFPERFHDR out " & CUFF_OUTFILE & "/S" & sSERVER & "/U" & sUSERID & "/P" & sPASSWORD & "/f" & """G:\Survey dev\Radio Applications\GS\SXM_Census\CuffBcp\CUFF_PH.fmt"""
Set objShell = Nothing

if Err.Number <> 0 then
WScript.Echo "Error: " & Err.Number
WScript.Echo "Source: " & Err.Source
WScript.Echo "Description: " & Err.Description
Err.Clear
End If

WScript.Quit

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-27 : 11:53:21

I don't see where it is running bcp.
Should cmd be bcp in the command string?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sbrief58
Starting Member

2 Posts

Posted - 2012-06-27 : 12:32:07

NigelRivett,

Thank you for the speedy reply. MY BAD! OOPS! I left out the bcp by mistake and added back in. I then tested the script by double clicking it. The command screen came up and prompted me for a password, I entered the password and nothing happened. No error message, no file, nothing ...

Please replace the incorrect bcp code with the code below ...

Again thank you ...

objShell.run "cmd /c bcp DPMFIW.dbo.tblGS_CUFFPERFHDR out " & CUFF_OUTFILE & "/S" & sSERVER & "/U" & sUSERID & "/P" & sPASSWORD & "/f" & """G:\Survey dev\Radio Applications\GS\SXM_Census\CuffBcp\CUFF_PH.fmt"""

Thanks,
Shari

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources