?
Solved

Linked Server to Sybase ASE 15

Posted on 2008-10-01
9
Medium Priority
?
460 Views
Last Modified: 2012-05-05
I am creating a Linked Server using ASE 15 to Sybase.  The field I am selecting is varchar(10).  When we run the following query, it always returns len 10 when we only 5 char in the result.

SELECT cparty_category, len(cparty_category)      
FROM OPENQUERY (SybaseLink,
      'SELECT c.cparty_category
      FROM    table ')

Do you know if there is any setting that will prevent the driver returning with embedded blank?
0
Comment
Question by:trieuro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22628552
These documents may be a bit dated, but should still apply:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=311875&SiteID=17
http://www.sybase.com/content/1029118/1029118.doc

Oh wait, I see now that you have the connection and you are asking question on the query.  I always get these backwards, but I believe LEN() will count the characters in a string/column including trailing spaces but DATALENGTH() will not.

Check and make sure that you have trimmed text, but using Len(LTrim(RTrim(cparty_category))) or try the DataLength(cparty_category) function.

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22628609
Nope, I am dyslexic.  LEN is the one I use all the time, so I should have known I switched them around in my previous post.  LEN will strip out trailing spaces.  DataLength shows you the number of bytes required to store the value so it includes the space taken up by the whitespace.

The problem is probably in leading spaces as LEN will not account for that.

Just wrap with LTrim as discussed.
DECLARE @cparty_category nvarchar(10)
SET @cparty_category = '    Johnny'
 
SELECT LEN(@cparty_category), Len(LTrim(RTrim(@cparty_category))) 

Open in new window

0
 

Author Comment

by:trieuro
ID: 22649811
I left out one detail.  When I install Sybase ASE 15.02 driver on my local machines.   It works find without the trailing spaces.  On production machine (64 bits) required 64 bits ASE 15.02, it added trailing spaces and the Len is always 10.  Then, we added convert, the trailing space is removed.  This is the result we were looking.  Is there a setting, on ASE 15 (64bits) or SQL to remove the trailing spaces without adding convert?

SELECT cparty_category, len(cparty_category)      
FROM OPENQUERY (SybaseLink,
      'SELECT convert (char, c.cparty_category)
      FROM    table ')
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:trieuro
ID: 22649821
The local machine is 32 bit ASE 15.02.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22651198
Did you try with the LTrim(RTrim()) as I suggested?
0
 

Author Comment

by:trieuro
ID: 22657921
Yes, we did but the trim function does not trim the spaces.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22658103
Could they possibly be other characters that are just not being represented correctly so appear as spaces?  Could even be newlines.  If you can look at the original record in Sybase, see if there are puntuation or other marks in that data that are not coming through.

For newlines you can REPLACE(str, CHAR(13)+CHAR(10), '') OR REPLACE(str, CHAR(10)+CHAR(13), '') but usually shows up the former from what I have seen.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question