Getting "The parameter is incorrect." when trying to insert a row using linked server hosted on Azure.

Question

Hi all,

We are running PeopleSoft Application (Peopletools - 8.55) running SQL server 2014.
Here we got a requirement where we need to insert some rows via the linked server on Azure Hosted database.
I have created the linked server on my Sql Server 2014 pointing to the Azure Database using "Microsoft OLE DB Provider for SQL Server".
When trying to insert the rows from SQL Server Managment Studio, it works fine.

Below is the query on my PeopleSoft Application....

%NoUpperCase
SET XACT_ABORT ON
INSERT INTO [AZURE_SIG_PROD].[Integration_Database].[ps].PS_DYS_NAME_RCD_DHANA
SELECT*
FROM PS_DYS_NAME_RCD

Where as, when i use the same query on the PeopleSoft application, i am getting the below said error

File: e:\pt85515b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]OLE DB provider "SQLNCLI11" for linked server "AZURE_SIG_PROD" returned message "The parameter is incorrect.".
[Microsoft][SQL Server Native Client 11.0][SQL Server]The OLE DB provider "SQLNCLI11" for
Failed SQL stmt: NOUPPERCASE SET XACT_ABORT ON INSERT INTO [AZURE_SIG_PROD].[Integration_Database].[ps].PS_DYS_NAME_RCD SELECT* FROM PS_DYS_NAME_RCD
Message Set Number: 200
Message Number: 0
Message Reason: File: e:\pt85515b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1723 Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]OLE DB provider "SQLNCLI11" for linked server "AZURE_SIG_PROD" returned message "The parameter is incorrect.".
[Microsoft][SQL Server Native Client 11.0][SQL Server]The OLE DB provider "SQLNCLI11" for
Failed SQL stmt: NOUPPERCASE SET XACT_ABORT ON INSERT INTO [AZURE_SIG_PROD].[Integration_Database].[ps].PS_DYS_NAME_RCD SELECT (200,0)

Trid creating Linked Server using "SQl Server Native Client 11.0" and the same error persists.

BTW, this issue doesnt happen when running the same sql on remote database via linked server hosted on SQL Server Platform.
This issue is happening only on the Database which is running on Azure Platform.

Need solutions from your expertise.

regards
Dhana

Answer 1

Hi @Dhanasekaran ,

> As listed on the stackoverflow, i think Azure doesnt support MSDTC (i.e. above insert Query). Am i correct?

Azure SQL databases uses Elastic Transactions. While on premises servers uses Microsoft Distributed Transaction Coordinator (MSDTC).

MSDTC is not supported on azure, and according to the elastic transactions documentation,

>Only transactions across databases in SQL DB are supported. Other X/Open XA resource providers and databases outside of SQL DB cannot participate in elastic database transactions. That means that elastic database transactions cannot stretch across on premises SQL Server and Azure SQL Database. For distributed transactions on premises, continue to use MSDTC.

•On premises SQL Server uses MSDTC, which is not available on Azure SQL Database.
•Azure SQL Database uses elastic transactions, which won't work with on premises SQL server.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

See:

https://stackoverflow.com/questions/54432254/azure-linked-sql-server-from-on-premise-sql-server-msdtc-exception

Answer 3

Not really. I would like to know, if the Challenge November conditions are granted and I still have access to MS-learn. That's where my guestaccount is from. :) ???

Answer 4

Ich weis es nicht, weil ich Gast bin durch die Challenge, aber weiterlernen will.