0

As the title says, what are the full requirements to get a SQL transaction running with SQL Server? The transaction will eventually get more complex, but so far the debugging process has left me with stripping out all the logic (and nearly tearing my hair out).

Some precursory info that may help.

  • Attempting to run this locally from DBeaver.
  • Definitely connected to the correct DB, the code for the transaction works fine on its own.
  • Executing code as a script Opt + X (so \n does not deliminate statements).

the current code is as follows:

BEGIN TRANSACTION Test_Transaction

    SELECT COUNT(*) FROM schema.table;

COMMIT TRANSACTION

It really is that simple at the moment, the error I get out is:

SQL Error [42601]: ERROR: syntax error at or near "SELECT"
  Position: 21

It's suggesting a syntax error at the SELECT but it really doesn't matter what I put in the transaction, it just fails at the first input after the line BEGIN TRANSACTION Test_Transaction, that is why I'm thinking I've missed a requirement of SQL Transactions to get them running.

Joe
  • 678
  • 9
  • 24
  • 2
    See what happens if you add a `;` at the end of the BEGIN TRANSACTION row, – jarlh Jul 27 '20 at 14:16
  • 3
    That error doesn't look like a SQL Server error. SQL Server's error codes don't go that high: [Database engine errors](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15#errors-31000-to-41400) – Thom A Jul 27 '20 at 14:17
  • 1
    Is there any reason you are giving your transaction a name? It's not a requirement, unless you intend to create a savepoint. You can simply try CREATE TRAN by itself. Other than that, there doesn't appear to be anything syntactically wrong with your statement. Have you considered that it might be the IDE you are using? – Jason Jul 27 '20 at 14:35
  • It's a good point @Jason, the previous code did require a savepoint and it's a leftover from that. Turns out it was an IDE issue, though, thanks. – Joe Jul 27 '20 at 14:48

1 Answers1

0

Super spot from @Larnu in the comments! The error code shown is not high enough for SQL Server, so, on checking my connection settings, I realised my DB Connection in DBeaver was using Postgres as its driver. Changing this got things running.

Joe
  • 678
  • 9
  • 24