Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
D_PBI
Post Patron
Post Patron

Message=Keyword or statement option 'tzoffset' is not supported in this version of SQL Server.

Hi,
We use Dynamics 365 (D365) as our transactional system. For reporting, we were using Data Export Services (DES) that pushed D365 data to Azure SQL Server where I created SQL Views on top of the D365 data. Power BI reporting then extracted from those SQL Views.
All was working fine until Microsoft (MS) decided to deprecate DES and suggested Synapse as the substitute. We followed MS's suggestion. Whilst migrating all over what I found is a lot of standard Azure SQL functions weren't compatible with Synapse and so I had to handle those incompatibilities. In the end, all was working and has been for the past eighteen months since that migration exercise.

Overnight, the scheduled Power BI (PBI) reports are failing their data refreshes due to the below error:

D_PBI_0-1713347814647.png


When I open the same report from PBI Desktop and open the Advanced Editor to have Power Query re-evaluate against the source SQL Views, I get this error message:

D_PBI_1-1713347962613.png


Both messages are referring to the 'tzoffset' word/code that is used in the SQL Views to handle the UTC/local timezone datestamps. The line of code in the SQL View is:

D_PBI_2-1713348136850.png


The above line of code is something Synapse did allow eighteen months ago. Although the error messages refer to SQL, the SQL Views query Synapse data and so the technology not accepting the 'tzoffset' code will be Synapse. To test this, I queried Synapse directly from within Synapse inteface (ignoring any reference to our Dynamics 365 data or our use of SQL Views) and I received the below error. You can see I was referencing the Master db and simply calling the system time.

D_PBI_3-1713348374207.png

I haven't changed anything in my Power BI reporting, SQL Views, or Synapse. No code changes, no software updates in the last week or so. I do not know what has caused the 'tzoffset' to no longer be supported.

I have a strong suspicion that if I were to remove any reference to 'tzoffset' in my SQL Views then there could well be another error that arises. It's often the cause that you have to resolve the showing error only for the next error in line to show. Point being, will there be further code snippets that Synapse has overnight decided not to allow.

Can anyone advise what could be causing this as in why all of a sudden (with no changes to anything) has Synpase started to not allow 'tzoffset'?
Thanks.

1 ACCEPTED SOLUTION

I've been advised that Microsoft deployed a new version of code that caused the error. I've also been advised Microsoft have reverted the train and all should now work again - and it does.

View solution in original post

4 REPLIES 4
v-gchenna-msft
Community Support
Community Support

Hi @D_PBI ,

Apologize for the issue you are facing. The best course of action is to open a support ticket and have our support team take a closer look at it.

 

Please reach out to our support team so they can do a more thorough investigation on why this it is happening: Link 

 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

 

Hope this helps. Please let us know if you have any other queries.

Hi @D_PBI ,

We haven’t heard from you on the last response and was just checking back to see if you got a chance to create a support ticket.
After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thanks

I've been advised that Microsoft deployed a new version of code that caused the error. I've also been advised Microsoft have reverted the train and all should now work again - and it does.

Hi @D_PBI ,

It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

MayFabricCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.