Monthly Archives: February 2021

How to fix Azure Error Message: “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Cannot find the object because it does not exist or you do not have permissions.”

uncategorised

There can be a number of reasons for this error but below steps through a potential scenario and fix for the error as referenced in the post title.

(TL;DR it’s probably down to permissions. Code example below.)

If you are running a Data Factory pipeline that calls a Store Procedure the process may fail throwing the error “The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Cannot find the object because it does not exist or you do not have permissions.”

The first part of that error is boilerplate stuff i.e. the query cannot do what it has to do so it is rolling back.

The second part of the error is what needs to be addressed. Either the object does not exist or the credentials the process is using does not have the required permission to do what the query has specified.

If you can confirm the object exits i.e. the table, then it can only be a matter of credential permissions.

The question becomes what are you trying to do with the object?

SELECT, DELETE, TRUNCATE all require that the credentials i.e. the user, has the permissions to carry out those statements.

If your query needs to TRUNCATE a table and the credentials used by Data Factory do not have permission to perform a TRUNCATE on a table then Azure will throw the aforementioned error.

In order to resolve that problem specifically, i.e. for a user to TRUNCATE a table, the user needs ALTER permissions. Below is an example of how to apply ALTER permissions on the object TestTable in the schema dbo to the user TestUser. Just update the schema, object and user to fit your needs.

GRANT ALTER ON OBJECT::dbo.TestTable TO TestUser;
GO

The above conforms to applying the principle of least privilege as the ability to alter objects only applies to that object specifically. In closing if the object exists look at what the query does to object and ensure the user has the required permissions to do it.

How to fix an audio echo problem with bluetooth headphones on Windows 10

Audio, Hardware, IT, OS, Windows Administration, , , , , , , , ,

I recently got shipped a new bluetooth headset for work. The headset in question was the MPOW HC5 headset. They are very comfortable and have good audio quality but when they first arrived there was a problem. The audio had a persistent echo.

The echo was isolated to the headset as there was no echo when the audio was coming through the laptop speakers.

The echo was also isolated to Windows 10 as there was no echo when the headset was plugged into an Android device.

At first I thought the problem was caused by the Windows 10 audio enhancements settings. These settings include an option for echo. However in this instance this was not the cause of the problem.

To rule out Windows 10 audio enhancements as the cause of the problem do the following:

Control Panel > Hardware and Sound > Sound > Left click on Speakers (or another output device of your choosing) > Properties > Enhancements > Check “Disable all sound effects”.

In this particular case the echo problem for my headset was that playback was happening twice for the same device albeit slightly out of sync thus creating an echo. The problem was identifiable as when I traversed to the option path below I could see that the headset was registered with the OS twice under two slightly different names.

Options path:

Control Panel > Hardware and Sound > Sound > Playback

I tested playback for both device names, by right clicking the device and clicking test, and found that one of the devices had a audio glitch. When this instance of the device was disabled the headphones worked without any echo.

If you found this post helpful please like/share/subscribe.

An icon depicting a calendar and clock

How to format SQL Server datetime as dd/mm/yyyy hh:mm:ss

Coding, Data, Database Administrator, Development, How To, sql, SQL Server, T-SQL, , , , , , , , , ,

If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.

The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.

Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-us')

If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.