Solved

Linked Server to Sybase ASE 15

Posted on 2008-10-01
9
438 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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 …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

813 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

13 Experts available now in Live!

Get 1:1 Help Now