Protocol Error In Tds Stream Linked Server
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss protocol error in tds stream sql server the workings and policies of this site About Us Learn more about
Ole Db Provider For Linked Server Returned Message "communication Link Failure
Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions
Protocol Error In Tds Stream Ssis
Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn
Session Provider: Physical Connection Is Not Usable [xffffffff]
from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Communication link failure for some queries to linked server up vote 5 down vote favorite 1 I am seeing the following error in odbc sql server driver protocol error in tds stream SSMS (server has Windows Server 2008 and Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 installed) when running some queries against the linked server, in particular long running queries. Simple selects from tables across the linked server work fine. This is a new issue that was noticed when SPs that have worked for years started failing. I have run a Wireshark capture on the server, capturing for packets to port 1433 on the linked server host. At the tail of the capture, I see many (10) TCP Keep-Alives being issued (after a message regarding bad checksum) and then an RST packet. The RST packet is correlated with the error below being returned to the client. There are other database servers on our network, where the linked server is configured identically, that don't exhibit this issue. I have found some articles such as this and this. We are using the implicated Broadcom NICs. The Chimney Offload State setting is enabled on the server. We will try disabling. Other thoughts on troubleshooting would be much appreciated. OLE D
30, 20098 Share 0 0 Encountered this error while running a SSIS package through a SQL Agent Job on SQL Server 2008. The processing in the package is a pretty simple andthe purpose is to export protocol error in tds stream communication link failure datafrom few tables from one SQL Server to another. Both the source and target servers protocol error in tds stream sql server 2008 r2 are on SQL Server 2008 (10.0.2531) andData Flow Task(with OLEDB source and destination tasks) is used for data export. The package smux provider: physical connection is not usable [xffffffff] is created using Visual Studio 2008 and the job is running on the destination server. The job continuously failed with the following error and in each run the failure occured at a different table. Source: DFT_MyTable http://dba.stackexchange.com/questions/37184/communication-link-failure-for-some-queries-to-linked-server Source - MyTable Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream". An OLE DB record is available The issue is solved when the Network Protocol used to connect to the source SQL Server is changed fromTCP/IP https://blogs.msdn.microsoft.com/ramoji/2009/12/30/ssis-package-fails-with-protocol-error-in-tds-stream/ to NamedPipes. Basically, an aliasfor the source is created on the destination server using the below steps. 1. Go to Start-> All Programs-> Microsoft SQL Server 2008-> Configuration Tools -> SQL Server Configuration Manager 2. Expand the node SQL Native Client 10.0 Configuration 3. Right click on "Aliases" and then select "New Alias…" 4. Provide the IP address of the Source SQL server against the "Server" field and specify the Source server name against the "Alias Name" field. 5. Choose the protocol as "NamedPipes" and click OK Please drop me a note if you have come across this error and this blog has helped you in resolving the issue.
Comments (8) Cancel reply Name * Email * Website Jennie says: October 13, 2010 at 9:15 am That solved our problem, thanks! We are running a 32-bit application with SQL Server 2008 on Windows Server 2008 R2 (64 bit). Reply Anon says: October 5, 2011 at 5:54 am Not only did this not work, SSIS now refuses to end the job and stays yellow until I click the stop button. ***. Reply Anon says: October 5, 2011 at 5:55 am Not only did this not work, SSIS now refuses to end the job and stays yellow until I click the stop button. Arsehole.here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn http://stackoverflow.com/questions/430956/linked-server-error-using-msdtc more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Linked Server Error using MSDTC up vote 1 down vote protocol error favorite I am Showing An example of Stored Procedure For Data Transaction using "Linked Server" Between Two System Through Internet Alter Proc [dbo].[usp_Select_TransferingDatasFromServerCheckingforExample] @RserverName varchar(100), ----- Server Name @RUserid Varchar(100), ----- server user id @RPass Varchar(100), ----- Server Password @DbName varchar(100) ----- Server database As Set nocount on Set Xact_abort on Declare @user varchar(100) Declare @userID varchar(100) Declare @Db Varchar(100) Declare @Lserver varchar(100) Select @Lserver = @@servername Select protocol error in @userID = suser_name() select @User=user Exec('if exists(Select 1 From [Master].[' + @user + '].[sysservers] where srvname = ''' + @RserverName + ''') begin Exec sp_droplinkedsrvlogin ''' + @RserverName + ''',''' + @userID + ''' exec sp_dropserver ''' + @RserverName + ''' end ') set @RserverName='['+@RserverName+']' declare @ColumnList varchar(max) set @ColumnList = null select @ColumnList = case when @ColumnList is not null then @ColumnList + ',' + quotename(name) else quotename(name) end from syscolumns where id = object_id('bditm') order by colid set identity_insert Bditm on exec ('Insert Into Bditm ('+ @ColumnList +') Select * From '+ @RserverName + '.'+ @DbName + '.'+ @user + '.Bditm') set identity_insert Bditm off set @RserverName=replace(replace(@RserverName,'[',''),']','') Exec sp_droplinkedsrvlogin @RserverName,@userID Exec sp_dropserver @RserverName When i Running the Stored Procedure I Getting the different errors in different Time of Execution Some time Shows this Error "The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "ServerName" was unable to begin a distributed transaction." Another time Shows This Type of Error OLE DB provider "SQLNCLI" for linked server "70.38.11.49" returned message "Protocol error in TDS stream". OLE DB provider "SQLNCLI" for linked server "70.38.11.49" returned message "Communication link failure". Msg 65535, Level 16, State 1, Line 0 S