On Fri, Sep 17, 2004 at 03:25:41PM -0400, Jeff Urlwin <[EMAIL PROTECTED]> wrote:
> >
> >
> > What happens, is that the backup proceeds
> > normally until just before it finishes. Then, the backup
> > file (which I have watched grow during execution) is removed
> > and the command finishes with no error indicated. I've
> > tracked it down to a problem with SQLExecDirect, since I can
> > get it to work by using a placeholder for the database name,
> > which forces the normal prepare/execute rather than
> > SQLExecDirect.
>
> I'm a little curious now. I notice that SQLExecuteDirect is returning -1, showing an
> error. That's strange because I would have thought I would have attempted to parse
> the
> error message, but it doesn't seem to be going there.
I think the message is just a server status message. In my real script,
I have an odbc_error_handler setup to capture them. When I bypass
SQLExecDirect, I get two messages back just basically telling me how
many pages were dumped in the backup (one message for the data, one for
the transaction log). So, although it is returning an "error" message, it's
not really an error...
> I have a few thoughts, after quick inspection:
> #1, use prepare and execute without needing to bind parameters and you'll get
> the
> correct behaviour. (Ok, this falls under the category of "Doctor it hurts when I
> move my
> arm up ... So don't move your arm up :)
Ah. I thought you had to do a fetch with the regular prepare/execute so I
did the placeholder bit instead.
> #2 It seems that the backup command returns a message and SQLExecute is still
> trying to get row counts, etc, even though there were errors. That's why the "good"
> log
> looks different. The execute in either case returns SQL_ERROR, I'm guessing to flag
> that
> there are messages coming back, but none are coming back via SQLError. I'm
> surprised at
> that. It seems to be a difference between using SQLExecDirect vs that SQLExecute,
> as you
> saw.
Yeah. Both MS-SQL and Sybase will send status messages that are technically
"error" messages, but don't indicate a real error. This is especially the
case for backups and the dbcc command. In DBD::Sybase, you can get the
severity level assigned to an error message to determine this, but ODBC
doesn't support it.
> What surprises me is that they behave differently on the server between the two
> (i.e. one
> deletes the file the other doesn't). From reading the SQL*Server docs,
> SQLExecDirect uses
> sp_executesql, which may behave differently, but works fine calling sp_executesql
> when
> using prepare/execute.
I'm thinking that for whatever reason the SQL Server thinks that the command
was aborted, so it removes the backup file. This is also supported by the
fact that the backup is not recorded in the SQL Server log when the backup
file gets deleted. Maybe if the status messages are not retrieved before
the connection is closed the SQL Server thinks the session aborted?
I think maybe I oversimplified by test case. I think I should've put in an
error handler to catch the status messages like I do in the real backup
script. That might highlight the problem. I'll try to do that on Monday...
> I think it's a bug, but I don't believe it's in my control at this point.
The odd thing is that it worked with DBD::ODBC 0.43, but according to the
changelog, the SQLExecDirect optimization for "do" was added in 0.35.
Unfortunately, I don't have any systems setup with 0.43 anymore to test
on, though :-(
--
David Good [EMAIL PROTECTED]
This space intentionally left blank.
