Unable to build SP which have select query to Linked server
Hi,
I have SP which select openquery ti linked teradata server
SELECT * FROM OPENQUERY(TDPROD, 'LOCKING ROW FOR ACCESS SELECT * FROM EIS.SO_BILL_Fct_V a ')
We have a linked server TDPROD defined in Serverobject in SQL explorer. The SP build fine in SQL management studio but fails when we build via teamcity. I am getting below error.
Error: Synchronization of 'Scripts.state' and
'foothill-svr-37.sqlCI_a6771d88-1f50-433f-94ab-19620bbc9cdb' failed: The
operation could not be performed because OLE DB provider "MSDASQL" for linked
server "TDPROD" was unable to begin a distributed transaction. OLE DB provider
"MSDASQL" for linked server "TDPROD" returned message "[Teradata][ODBC Teradata
Driver] Invalid Attribute". Error executing the following SQL: create PROCEDURE
[dbo].[SP_Get_ChannelsAndAccountsFromTeraData] AS BEGIN TRUNCATE TABLE
Can you please suggest solution for this.
Thanks,
Vipul
I have SP which select openquery ti linked teradata server
SELECT * FROM OPENQUERY(TDPROD, 'LOCKING ROW FOR ACCESS SELECT * FROM EIS.SO_BILL_Fct_V a ')
We have a linked server TDPROD defined in Serverobject in SQL explorer. The SP build fine in SQL management studio but fails when we build via teamcity. I am getting below error.
Error: Synchronization of 'Scripts.state' and
'foothill-svr-37.sqlCI_a6771d88-1f50-433f-94ab-19620bbc9cdb' failed: The
operation could not be performed because OLE DB provider "MSDASQL" for linked
server "TDPROD" was unable to begin a distributed transaction. OLE DB provider
"MSDASQL" for linked server "TDPROD" returned message "[Teradata][ODBC Teradata
Driver] Invalid Attribute". Error executing the following SQL: create PROCEDURE
[dbo].[SP_Get_ChannelsAndAccountsFromTeraData] AS BEGIN TRUNCATE TABLE
Can you please suggest solution for this.
Thanks,
Vipul
Comments
Thanks for your post!
In order to build this object you will need to have the correct drivers and linked servers in place so that the dependencies are not broken when building- to do this you need to use a SQL Server instance as a temp server (instead of LocalDB) and it needs to be connected to your TDPROD server and have the ``[Teradata][ODBC Teradata Driver]`` installed.
If that is not possible then you will need to filter out that object during your build using /additionalCompareArgs eg /additionalCompareArgs="/filter:myFilterName.scpf /Options:DoNotOutputCommentHeader,ForceColumnOrder"
More info on that https://documentation.red-gate.com/disp ... s(optional)
You can set up the filter in Source Control and it will be in the repo folder and the resulting nuget package https://documentation.red-gate.com/disp ... ng+filters
Let me know if you have any questions or issues with that!
Warmly,
Technical Sales Engineer
Redgate Software