Error Begin Transcation Into SQL Server Compact Edition

Question

I want to begin a transaction in SQL Server CE 4.0 using the following code and also rollback that transaction.

Same query works fine in SQL Server 2008 but not in SQL Server CE 4.0, so please help me to solve this:

BEGIN TRAN d

SELECT COUNT(*) 
FROM Customers 
WHERE DOB = CONVERT(datetime, '16/01/2003',103)

UPDATE Customers 
SET dob = NULL
WHERE DOB = CONVERT(datetime, '16/01/2003', 103)

SELECT COUNT(*) 
FROM Customers 
WHERE DOB = CONVERT(datetime, '16/01/2003', 103)

ROLLBACK tran d;
--COMMIT tran d;

But I'm getting this error

Error Code: 80040E14
There was an error parsing the query.[ Token line number = 1,Token line offset = 1,Token in error = begin ]
Minor Err.: 25501
Source : SQL Server Compact ADO.NET Data Provider
Num.Par. : 1
Num. Par. : 1
Err. Par. : begin

What's wrong? Any help will be appreciated!

Please suggest how to use begin and rollback transaction in SQL Server CE 4.0.

I have also check the SQL Server CE rollback tran and SQL Server CE begin tran. but use of that method in c# so I don't understand how to use that in a SQL Server CE query.

Thank you.

and also my question is not a duplicate just because of I did not write code in C# and I use SQL query to do this task.

Thank you

Answer 1

After lots of surfing on google and etc.. I get the my answer that in SQL Server CE you cannot declare variables.

If you want to begin, rollback or commit a transaction in SQL Server CE and you write a SQL query, then than not possible in SQL Server CE.

But you can do this using another way using below msdn tutorial:-

• If you want use Begin transaction then use this method Begin transaction

• If you want use Rollback transaction then use this method Rollback Transaction

• If you want use Commit transaction then use this method Commit Transaction

Thank you.