Solved

Linked Server to Sybase ASE 15

Posted on 2008-10-01
9
437 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
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 59

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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 59

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

Zoho SalesIQ

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why does this keep coming up NULL? 2 44
Parsing the XML data to SQL Server 4 59
display data in text field from data base for updating 6 50
Sql query 107 27
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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

21 Experts available now in Live!

Get 1:1 Help Now