Unable to build SP which have select query to Linked server

coolvipzcoolvipz Posts: 2
edited August 1, 2016 5:26PM in SQL CI 2
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

Comments

  • Hey Vipul,

    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,
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
Sign In or Register to comment.