Truncating logs with a PHP script

29 Jul 2005 2:59 AM

Chin

I am writing a little PHP script that grabs a list of DBs and generates
a radio button list of DBs as a user's choice to select which DB's
transaction log to truncate.

The problem is MS SQL does not appear to like double-quotes being passed
into the query through PHP:

PHP code:

//$post is the variable containing the DB name

$statement="backup log \"" . $post . "\" with truncate_only";

echo $statement . "<br>"    ;//Echoes SQL statement being queried

$connect = mssql_pconnect($server, $username, $password);

if($connect)
{
    $result=mssql_query($statement)    ;
    echo $result    ;
}

PHP output from web page:

backup log "c1234-1" with truncate_only

Warning: mssql_query(): message: Line 1: Incorrect syntax near
'c1234-1'. (severity 15) in C:\Inetpub\vhosts\httpdocs\truncate.php on
line 24

Warning: mssql_query(): Query failed in
C:\Inetpub\vhosts\httpdocs\truncate.php on line 24

NOTE: I had to enclose the DB name in double-quotes due to the dash(-)
in the DB name.

backup log "c1234-1" with truncate_only   <-- This statement executes
fine when ran directly in query analyzer.

Any tips, pointers or ideas on what I am missing would greatly help.
Thanks in advance.

Regards,

Chin

29 Jul 2005 3:06 AM

Stu

Apart from the fact that truncating backup logs is a REALLY BAD IDEA,
you may want to try wrapping the database name in brackets ([c1234-1])
instead of quotes.

Stu

29 Jul 2005 6:03 AM

Tibor Karaszi

I'd catch the SQL statement in Profiler to see what is really submitted to SQL Server.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Chin" <chc***@ntguruwannabe.com> wrote in message news:u1hJQm%23kFHA.3580@TK2MSFTNGP09.phx.gbl...
>I am writing a little PHP script that grabs a list of DBs and generates
> a radio button list of DBs as a user's choice to select which DB's
> transaction log to truncate.
>
> The problem is MS SQL does not appear to like double-quotes being passed
> into the query through PHP:
>
> PHP code:
>
> //$post is the variable containing the DB name
>
> $statement="backup log \"" . $post . "\" with truncate_only";
>
> echo $statement . "<br>" ;//Echoes SQL statement being queried
>
> $connect = mssql_pconnect($server, $username, $password);
>
> if($connect)
> {
> $result=mssql_query($statement) ;
> echo $result ;
> }
>
>
> PHP output from web page:
>
>
> backup log "c1234-1" with truncate_only
>
> Warning: mssql_query(): message: Line 1: Incorrect syntax near
> 'c1234-1'. (severity 15) in C:\Inetpub\vhosts\httpdocs\truncate.php on
> line 24
>
> Warning: mssql_query(): Query failed in
> C:\Inetpub\vhosts\httpdocs\truncate.php on line 24
>
>
>
> NOTE: I had to enclose the DB name in double-quotes due to the dash(-)
> in the DB name.
>
> backup log "c1234-1" with truncate_only <-- This statement executes
> fine when ran directly in query analyzer.
>
> Any tips, pointers or ideas on what I am missing would greatly help.
> Thanks in advance.
>
> Regards,
>
> Chin