Stored Proc outputs differ between SQL Server and Azure SQL DB

Question

We have an om-prem data warehouse and are looking to migrate to Azure cloud. We've provisioned the Azure SQL serverless database and done a lift and shift on the stored procs. We are copying source data from SQL server database to Azure SQL and executing the procs as part of an ADF pipeline. What I have noticed is that there is a difference between the output/transformed data of that proc between Azure and On-prem. I have verified the source data and the stored proc script and there isn't amy discrepancy/missing scripts. This happens on 1 out of 6 procs that we've migrated but the other procs are fairly simple compared to the proc in question. I am wondering if anyone has ever faced this issue?

P.S. I've tried to match the collation between databases and this does not impact the output.

Answer 1

Hello,

It seems like you are experiencing a difference in the output of a stored procedure between your on-premises SQL Server and Azure SQL Database. This issue is occurring for one specific stored procedure, while the other procedures are working fine.

To troubleshoot this issue, there are a few steps you can take:

Double-check the data that is being used as input for the stored procedure. Ensure that the data in both the on-premises SQL Server and Azure SQL Database is the same and there are no discrepancies.

Compare the stored procedure script in both the on-premises SQL Server and Azure SQL Database. Make sure that there are no missing scripts or differences in the logic that could cause the difference in output.

Compare the database settings between the on-premises SQL Server and Azure SQL Database. Look for any differences in collation, compatibility level, or other settings that could affect the behavior of the stored procedure.

Create a test environment where you can execute the stored procedure in both the on-premises SQL Server and Azure SQL Database separately. Compare the output of the stored procedure in both environments to identify any differences.

Analyze the execution plans of the stored procedure in both the on-premises SQL Server and Azure SQL Database. Look for any differences in the query plans that could explain the difference in output.

If you have already tried matching the collation between databases and it did not impact the output, it's important to investigate other potential causes. If none of the above steps help in identifying the issue, it may be helpful to provide more specific details about the stored procedure and the differences in output you are observing.

Hope this helps

If it does please mark the answer as accepted.

Regards