BCP & VBScript

Question

Post reply

BCP & VBScript

npinto

Old Hand

Points: 327
More actions
August 31, 2004 at 5:10 pm

#102181

Folks,
I'm using a bcp command within a vbscript file that's run by the windows scheduler on a nightly basis. Frustratingly the bcp command within the vbscript gives an error message:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
when it's run through the scheduler. When I logon to the server and run the vbscript file interactively using the SAME account that runs the scheduled job, it works perfectly.
I've pasted the problematic part of the vbscript here:
OScommand = "%comspec% /c " & Chr(34) & BCPPath & "BCP" & Chr(34) & " " & DatabaseName & "..ScannedFilesTemp in " & UploadFileName & " /S" & SQLServerName & " /T /c /o" & LogFolder & "ScannedDocsBCPOut.txt"
WshShell.Run OScommand,0,True
When executed the OScommand variable shows this value:
%comspec% /c "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\BCP" FinInterface..ScannedFilesTemp in C:\BCSApplLogs\ScannedDocs.txt /SSQL04 /T /c /oC:\ApplLogs\ScannedDocsBCPOut.txt
Pleeeeeeease HELP!
TIA.
Neville

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 1

As per the error you have stated, I doubt the data file is missing. Pls cross check whether the file exists.

Thanks,

Ganesh

npinto Old Hand Points: 327 More actions · Answer 2

Phill,

1. It's just a matter of organisational consistency rather than any other reason for using the windows scheduler.

2. Point taken. I'll give it a shot.

3. I logon to the server directly to the console using a tool called 'remoteadmin'.

Thanks.

Neville

philcart SSC-Forever Points: 47795 More actions · Answer 3

1) Ugh ... I hate those ...

3) Try using WScript.Shell to just run the command without the %comspec%

--------------------
Colt 45 - the original point and click interface

Glen McLeod Mr or Mrs. 500 Points: 560 More actions · Answer 4

Ive had this problem in the past, and it's a matter of security. When you run the script at night, there's no user security context (NT, not SQL Server) for the system to work with. When you run it when you're logged on, it uses your security context and is accepted at the OS level. I wound up compiling it in VB and running it through the scheduler. Or, as already suggested, try using the SQL scheduler.

Glen

npinto Old Hand Points: 327 More actions · Answer 5

Phill,

Thanks to your suggestion I finally managed to get it to work using the bulk insert command. This is what I used...

OScommand = "%comspec% /c osql /E /S " & SQLServerName & " /d " & DatabaseName & " /o " & LogFolder & "ScannedDocsBIOut.txt /Q " & chr(34) & "bulk insert " & DatabaseName & "..ScannedFilesTemp from '" & UploadFileName & "'" & chr(34)

Neville