Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server query issues? Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 10:41 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:15 AM
Points: 1,015, Visits: 2,843
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
Post #1542368
Posted Monday, February 17, 2014 10:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 482, Visits: 842
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
Post #1542373
Posted Tuesday, February 18, 2014 4:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 14,840, Visits: 27,315
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542458
Posted Tuesday, February 18, 2014 5:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:15 AM
Points: 1,015, Visits: 2,843

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?



  Post Attachments 
error log file.rar (2 views, 4.72 KB)
Post #1542495
Posted Tuesday, February 18, 2014 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:42 PM
Points: 14,840, Visits: 27,315
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542561
Posted Tuesday, February 18, 2014 7:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 21, 2014 7:46 PM
Points: 3,738, Visits: 7,081
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; They'll drag you down to their level and beat you with experience"
Post #1542570
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse