Issues with OSQL

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • 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"

  • 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.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

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