Error during getting next value for sequence in ADF

Question

Hello,

I'm developing ETL to move data. I copy data from on-premises source DB into stage DB in Azure SQL Server and want to insert them into a target table in the another DB in the same Azure SQL Server.

The target table has PK_ID int field, values for it have to be taken in a sequence. The sequence is not a default value in the table (it's legacy thing). So I use the query as the source

But when I try to see data preview I get the following error:

Error:
at Source 'query': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, derived tables or return statements.

Interesting thing, I tried to create the same source for testing in a separate dataflow, and it worked, I could see data returned by the query. But when I implemented that in a main dataflow I got the error.

So question is how to get values of sequence without complex workarounds? Why can't I get the values in that query?

Thanks,
Max

Answer 1

Hi @Maksym Volkov ,

Welcome to Microsoft Q&A Platform. Thank you for posting query here.

The NEXT VALUE FOR function is a non-deterministic function, and is only allowed in contexts where the number of generated sequence values is well defined. There are a lot of limitations and restrictions with the use of the NEXT VALUE FOR. The NEXT VALUE FOR is not allowed to be used in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, derived tables or return statements.

Solution/Work Around:
You can the query using lookup activity and then pass value to dataflow. Your data flow should be parameterized.

OR

To overcome the limitation of the use of NEXT VALUE FOR, you can create a view that returns NEXT VALUE for sequence object. Unfortunately, one of the other limitations of the use of NEXT VALUE FOR is that it is also not allowed to be used in a view. To overcome this limitation, the OPENROWSET rowset function can be used. The OPENROWSET is an alternative to accessing tables in a linked server.

CREATE VIEW [SchemaName].[SequenceName]  
AS  
SELECT *  
FROM OPENROWSET('SQLNCLI', 'Server=<serverName>;Database=<DBName>;UID=<UserID>;Pwd=<Password>',   
                'SELECT NEXT VALUE FOR [schemaName].[SequenceName] AS [SequenceID]')  
GO  

Hope this will help. Please let us know if it works or any further queries. Thank you.

Answer 2

Hi , It will work if you use store procedure instead of direct query

1. Create Storedprocedure
2. Access sequence number in SP
3. Call SP in Data flow .