May 1, 2006 at 11:29 am
Hi,
I have written a VB Script that runs a BCP job and then deletes that information from the table. But I am having a problem, that is, even before BCP finishes the job, deletion job is getting kicked-off (as I am only waiting for few seconds). Although I could increase the wait time, it's not a fool-proof solution.
Is there a way to get any kind of notification from BCP that the job is complete, so that I can start the delete job?
R
May 1, 2006 at 2:22 pm
Here is a part of the solution. I said part of the solution because, when the stored procedure is run in Query Analyzer, an output file is created. But when run in a VB script, nothing happens.
Private Sub archiveNew(pFileName)
Dim query
query = "bcp ""SELECT * FROM dbo.TableA """
''' ========================================================================
''' If there is no default file name, then create a filename.
''' ========================================================================
If (Len(LTrim(Rtrim(pFileName)))>0) Then
query = query & " queryout " & pFileName & " -c -T"
Else
query = query & " queryout TableA.dat -c -T"
End If
Dim DBConnection
Set DBConnection = wscript.CreateObject("ADODB.connection")
DBConnection.Open gConnectionString
Dim lWrite
Set lWrite = WScript.CreateObject("ADODB.command")
With lWrite
.ActiveConnection = DBConnection
.CommandType = 4
.CommandText = "dbo.jobExecuteBCP"
.CommandTimeout = 10000
.Parameters.Refresh
End With
With lWrite
.Parameters.Item("@pBCPJob").Value = query
.Execute
End With
End Sub
CREATE PROCEDURE dbo.jobExecuteBCP
@pBCPJob VARCHAR(2000) = NULL
AS
SET NOCOUNT ON
-- Variable declaration
DECLARE @lErr INT,
@lMsg NVARCHAR(100)
DECLARE @lId NVARCHAR(15),
@lProcName VARCHAR(30)
DECLARE @lCnt INT
-- Initialize error handle-related constants
SET @lId = 'Job'
SET @lProcName = 'jobExecuteBCP'
SET @pBCPJob = LTRIM(RTRIM(@pBCPJob))
EXEC master..xp_cmdshell @pBCPJob
SET @lErr = @@ERROR
IF @lErr 0
BEGIN
SET @lMsg = 'BCP of dbo.Events table failed'
GOTO OnError
END
RETURN @@ERROR
OnError:
-- Store error and raise error
-- EXECUTE sp_LogError @lErr, @lMsg, @lId, @lProcName, @lProcDB
RAISERROR(@lMsg, 18,1)
RETURN @lErr
GO
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply