Linked server query issues?

Question

Post reply

Linked server query issues?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
February 17, 2014 at 10:41 pm

#285795

Hi,
Source - SQL server 2008 R2 64 bit (sp2 CU1)
Destination - SQL server 2008 32 bit (sp3)
I have created linked server and working fine but query execution throwing errors as below
Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI10' for linked server 'IP address' returned data that does not match expected data length for column '['IP address' ].[DBNAME].[dbo].[Viewname].columnname'. The (maximum) expected data length is 20, while the returned data length is 28.
how to fix it?
1. Receive an error message when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server
2. select * use a column list with a Convert(varchar(5), columnname)
3. Does patch level need to update at soruce or destination server side?
rgds
ananda

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

doing select * from will always get you in trouble, it is a bad practice, you should always use

select columnName from to avoid this issue

try this http://stackoverflow.com/questions/4215979/problem-with-select-in-mysql-through-odbc-from-microsoft-sql-server

hope it helps

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Patches should pretty much always be applied, but it sounds like you have some type of translation issue going on. Something in your code has told SQL Server to expect a 20 character field and you're sending 28. Without seeing the code, the structures and other things, I can't tell you specifically what the issue is, but that's the general problem.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

First Error resolved Just refersh that View at source server, then fetch 198768 rows from source database thru linked server..

But i am facing another memory issues while executing that linked server SQL statement

Server - 4 GB RAM, 32 bit OS, not enabled /3GB switch. this instance Total 6 linked server configured.. also database OLTP busy system..

Attached Error log file, How to fix it?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Buy more memory. My laptop has 32gb of ram. Running a SQL Server instance on 4gb in this day and age is crazy.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

Please post your error log, I have no intention of opening a compressed file at the office but am curious to see it

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience