Solved

Linked Server to Sybase ASE 15

Posted on 2008-10-01
9
449 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 250 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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