July 28, 2009 at 8:33 am
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
(
@JobID int,
@LogID INT OUTPUT
)
AS
SET NOCOUNT ON
INSERT INTO [BackupLog] (
[JobID],
[BeginDate]
) VALUES (
/* JobID - int */ @JobID,
/* BeginDate - datetime */ GETDATE()
)
SELECT @LogID = SCOPE_IDENTITY()
RETURN
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 (
@JobLogID int,
@notes VARCHAR(500),
@Result tinyint)
AS
SET NOCOUNT ON
UPDATE [BackupLog]
SET [EndDate] = GETDATE(),
[Notes] = @notes,
ResultType = @Result
WHERE [ID] = @JobLogID
RETURN
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.
-Luke.
July 29, 2009 at 7:40 am
As an Update, I've been continuing to revisit this issue. I've tried various statements in OSQL that should throw an error, things like not surrounding my text variables with single quotes. This is definitely something that should return an error state. When I log into osql from the command line osql -S MyServer -d Mydb -E then enter my command, hit enter add a go and hit enter again I see the command in profiler, then I see the error message from SQL.
If I execute the same exact query but use -Q to execute and exit. I get no error message4 and the command never shows up in my profiler trace. I can't understand the difference here.
And help would be greatly appreciated.
Thanks,
-Luke.
July 29, 2009 at 7:59 am
Try moving the -Q to before the query you are executing. I had the same behavior when I ran this with the -Q on the end.
osql.exe -S MyServer -d MyDB -E -Q "EXECUTE dbo.updateJobLogRow 16 ,'6 File(s)
copied', 0"
July 29, 2009 at 8:04 am
yup this has got to be one of those big head-desk-head-desk moments...
When I was initially troubleshooting this, I found an incorrect parameter was being passed. Before that however I'd be switching the order of the switches passed to the osql command. Apparently I never switched them back. Once I did it started working again.
Thanks so much for the second set of eyes.
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply