SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Invoking BCP from a VBScript
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sbrief58
Starting Member

2 Posts

Posted - 06/27/2012 :  11:15:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 06/27/2012 :  11:53:21  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.

Edited by - nigelrivett on 06/27/2012 11:54:28
Go to Top of Page

sbrief58
Starting Member

2 Posts

Posted - 06/27/2012 :  12:32:07  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000