question Re: Move data from locally installed SQL Server to Azure SQL in Nifi in Support Questions

Move data from locally installed SQL Server to Azure SQL in Nifi

Kiranq — Sun, 13 Aug 2023 15:42:33 GMT

Hi,
My task is to move data from my locally installed SQL server (16.0) to Azure SQL (12.0) hosted on my Azure subscription.
So far, I have used ExecuteSQL processor to run my select query from SQL Server and it returns me the rows successfully. I have created a Database Polling Connection Service which connect to my SQL Server.
After fetching the rows, I am using PutDatabaseRecord processor to insert the fetched rows into Azure SQL but its showing me error that "use statement is not supported to switch between databases". Note that the Database Polling Connection Service is different for PutDatabaseRecord where I have mentioned connection string of my Azure SQL.
Please if anyone can give feedback on this issue and suggest any workaround.

Re: Move data from locally installed SQL Server to Azure SQL in Nifi

Kiranq — Sun, 13 Aug 2023 14:20:40 GMT

Should I be using "use statement" in my sql select query?

Re: Move data from locally installed SQL Server to Azure SQL in Nifi

steven-matison — Mon, 14 Aug 2023 12:54:15 GMT

@Kiranq No, you do not have to use the USE statement. Some things to consider:

Make sure each DBCP is configured correctly with the right schema or namespace if possible in the confIguration or connection string.
You can also make sure the schema or namespace above your table is included in the processor configuration. For example in PutDatabaseRecord you will find Schema Name in the properties.
It could also be done in the sql statement without a specific use statement like select * from default.tablename; assuming your user has access to this default namespace with the tablname.

Re: Move data from locally installed SQL Server to Azure SQL in Nifi

Kiranq — Thu, 17 Aug 2023 11:49:55 GMT

Thanks. It was an issue with the connection string. It was pointing to another database while my processor was inserting into another database.