Linked Server problem?

I have done a search of the posts and have not found anything that applies to our problem. I hope somebody can help. Here it goes:

We have several scheduled packages that retrieve data from several tables in a linked server and insert it into mirror copies of those same tables in the 'home' server. These packages have run for months without any problems at all.

A couple of weeks ago we started getting several different issues. Some packages would fail in the middle of processing. Some would run 'successfully' but would only have pulled half of the expected data, sometimes 10%.

About two days ago most of the packages were failing, but not all. One of the packages that access the linked server has run every time with no problems. Another package that gets data from an Access database runs with no problems.

To test, I decided to run queries in SQL Server Management Studio manually. Running the same query from three different databases in the 'home' server I have received very inconsistent results. Sometimes the query runs successfully. Most of the time it gives me different error messages. This is a sample of the error messages. Please keep in mind that this is the same query run in different databases in the 'home' server accessing tables in the linked server. I ran it in different databases because someone in our IT department suggested that our database where we usually run the packages was possibly corrupt. Each message is from a different run.

[Populate stg_di [190]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Received an invalid column length from the bcp client for colid 44.".

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Communication link failure".

[Populate stg_di [190]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Received an invalid column length from the bcp client for colid 31.".

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Protocol error in TDS stream".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "<server address>".

Running just the SQL query 4 times:

Msg 7339, Level 16, State 1, Line 1

OLE DB provider 'SQLNCLI' for linked server '<server address>' returned invalid data for column '<linked server path>.CRM_SALE_CREDIT_TOTAL'.

2^nd time, at :04:13 mins and 20,582 rows

Msg 7339, Level 16, State 1, Line 1

OLE DB provider 'SQLNCLI' for linked server <server address>' returned invalid data for column '<linked server path>.CRM_SALE_CREDIT_CLOSING_MGR'.

3rd time, at :00:42 seconds and 3,501 rows

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Protocol error in TDS stream".

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Protocol error in TDS stream".

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Protocol error in TDS stream".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "<server address>".

4th time, at :00:08 and 601 rows

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Communication link failure".

OLE DB provider "SQLNCLI" for linked server "<server address>" returned message "Protocol error in TDS stream".

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "<server address>".

Running it 4 times gives us 4 different errors. The first two times it complains about two different fields. Remember that the source and target tables have identical structures except that the target tables have a 'ProcessedDate' field.

Any help would be greatly appreciated.

Ricardo

If it just happened all of a sudden and no changes were made to the server and linked servers, then perhaps you need to start looking into the network infrastructure - bad router perhaps? Just a thought.|||

Phil, thanks for the response. We're having a meeting with IT. The most likely outcome will be that we will re-install SQL Server 2005. I heard that the server had a memory and motherboard issue last week. We'll see.

Thanks

|||

Ricardo_ES wrote:

Phil, thanks for the response. We're having a meeting with IT. The most likely outcome will be that we will re-install SQL Server 2005. I heard that the server had a memory and motherboard issue last week. We'll see.

Thanks

Please post back here your results. I've seen some strange connection related errors in my sysdtslog90 table as well, and can't explain them at all. Would be curious to know if we have similar issues.|||

Phil, sorry it took so long to reply. It turned out that there was a problem with the server. They didn't give me details and are still trying to figure out what the problem was.

Their solution was to switch us over to a new server and everything is fine now.

You know the errors I was getting. The IT department was getting errors like the backup software saying that there was an integrity problem with one of the databases when the database was fine. The last straw for them was that one night they couldn't finish copying some simple files from that server to some other server. That's when they decided that the server was the problem.

This probably won't help you, but I appreciate your time and concern.

Ricardo

|||Great. Thanks for reporting back.|||

I'm running into a similar error and have found very little info regarding Msg 7339.

In this case, my source is a table on a linked SQL 2000 database. The offending column is a REAL, so it was probably upsized from Access. The destination is an identical table on a new SQL 2005 machine. The process is a simple data copy using an INSERT into <localtable> select from <linkedserver>.db.owner.table.

I can run the select portion of the query (using Query Analyzer) directly against the remote server and I get the entire resultset w/o a hiccup. When I run it "through" the local machine using the linked server syntax, I get that

OLE DB provider 'SQLNCLI' for linked server <servername> returned invalid data for column <columnname>

When I compare the two resultsets, I can see which record it stopped on/before to determine the offending value. That value shows as 2.101948E-44 in query analyzer. The SQL Books definition of a REAL datatype is:

Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).

So my first assumption is that this is some sorta bug in SQL server 2000 allowing an out-of-range value to be stored. If I cast the value as a float, I get 2.10194769648723E-44 , which I assume is just bogus data.

My solution was to create a view on the remote server where I do just that, convert the real to a float... works fine. When it dumps the data into the local table, I get what I would expect... that particular value is stored as zero.

-Chris

ps - sorry about the small fonts above. This editor is acting weird!

sql