Solved

Linked Server to Sybase ASE 15

Posted on 2008-10-01
9
436 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
  • 4
  • 3
9 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:trieuro
Comment Utility
The local machine is 32 bit ASE 15.02.
0
 
LVL 59

Expert Comment

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

Author Comment

by:trieuro
Comment Utility
Yes, we did but the trim function does not trim the spaces.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now