SQL Server T-SQL block does not run

[SimonSellick]

Hi,

I've moved this to here from the SQL Server forum.

I'm submitting a complex series of updates as a T-SQL block from a PHP script. The return does not show an error but it also does not return a row as expected and has not updated the database. If I submit a simpler block it works as expected. If I catch the complex block text and paste it into QA it runs correctly and returns the expected row.

One difference between the simple and complex blocks is that I have a begin transaction / commit / rollback in the complex one. If I remove it, the database gets updated but the call still returns nothing (and I need the rollback facility).

Is there a preferred way to submit an executable T-SQL block, or am I correct in using the mssql_query function?

Any pointers would be appreciated.


Simon

 

[SimonSellick]

For info, in case anyone else encounters a similar problem - it turned out that the script was running as different users in QA and from PHP; I had omitted a couple of GRANTs for the PHP user and fixing these sorted the updates out. The PHP call to mssql_query() still didn't really seem to send back a consistent 'worked/didn't', but I think that I've got it:
- if the return is a boolean and false, it failed (as per the manual);
- if it's boolean and true but mssql_get_last_message() returns non-null, the script ran but encountered a problem;
- if it's boolean and true and there's no message, it ran and did not return a recordset;
- if it's not boolean, it's a recordset resource (or several) which might contain rows.

HTH