I'm in the process of trying to do some better auditing of a backup process for my organization. It's a very low tech xcopy command in a batch script that copies some files from point a to point b. To better let us know how it's been doing, including alerting when it fails I'm rewriting the batch script to write to a database which will keep track of the backuplogs and allow us an easy way to check on these jobs etc.
The first thing that I do in the script is execute a stored procedure which writes to the BackupLog Table
CREATE TABLE [BackupLog] (
[ID] [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
[JobID] [int] NOT NULL ,
[BeginDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[ResultType] [int] NULL ,
[Notes] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
All the procedure does is add a row with GetDATE() And return the ID so I can refer back to it later when the file copy finishes...
CREATE PROCEDURE dbo.insJobLog
@LogID INT OUTPUT
SET NOCOUNT ON
INSERT INTO [BackupLog] (
) VALUES (
/* JobID - int */ @JobID,
/* BeginDate - datetime */ GETDATE()
SELECT @LogID = SCOPE_IDENTITY()
This all works... The issue I'm having is running a second procedure to update this row one the xcopy process is finished that annotates the end date, number of files copied (or error message) and the Resulting errorlevel. When I attempt to execute the following procedure
CREATE PROCEDURE dbo.updateJobLogRow (
SET NOCOUNT ON
SET [EndDate] = GETDATE(),
[Notes] = @notes,
ResultType = @Result
WHERE [ID] = @JobLogID
with the following osql cmd from my batch file nothing appears to happen...
osql.exe -S MyServer -d MyDB -E "EXECUTE dbo.updateJobLogRow @jobID = %JobID% , @Result = %Result% ,@notes = '%Notes%' " -Q
IF I echo it and then copy and paste the command onto the commandline nothing appears to happen. BTW, it looks like this when echoed...
osql.exe -S MyServer -d MyDB -E "EXECUTE dbo.updateJobLogRow 16 ,'6 File(s) copied', 0 " -Q
When I paste the command text into QA and execute it I have no problems.
Since I'm using the -E switch I should be connection with my domain credentials, which on that db have dbo rights. These are the same credentials I'm connecting with when using QA.
Using Profiler I can see the first sp run correctly including the audit logout event dropping the connection with no chance of locks right? Then the following is logged I see the Audit Login Event, then SQL BatchStarting setting quoted_identifier off, then another SQL:BatchStarting which is blank and finally an Audit Logout event. It's almost like there is a syntax error somewhere and the second procedure just isn't being executed. I've tried to redirect STDErr to a logfile and various other ways to get OSQL to tell me what is wrong, but I'm having no luck.
I've been beating myself up over this all morning so any ideas on things to check would be greatly appreciated. Other things to watch for in Profiler? Ways to get syntax error messages from OSQL? Anything else I've totally looked past?
Thanks in advance.
To help us help you read this[/url]For better help with performance problems please read this[/url]