Jump to content


Photo
- - - - -

ms sql server and spaces in columns


7 replies to this topic

#1 lastenia_rojas

lastenia_rojas

    Member

  • Members
  • PipPip
  • 19 posts

Posted 06 July 2006 - 09:40 PM

Hi,

In Oracle, when ProIV populates a varchar2 col, spaces to the right of the value are trimmed. In sql server this doesn't seem to be the case for varchar cols. I was wondering if there is a config parameter in ProIV or sql server so that ProIV's behaviour on sql server matches Oracle's. Thanks.

Lastenia.

#2 Wim Soutendijk

Wim Soutendijk

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 211 posts
  • Gender:Male
  • Location:Netherlands

Posted 07 July 2006 - 06:34 AM

It depends on how you create your tables in SQL Server
If you use a SET ANSI_PADDING OFF
before the CREATE TABLE command, the extra spaces send by pro-iv will be trimmed by the database

Edited by Wim Soutendijk, 07 July 2006 - 06:35 AM.


#3 lastenia_rojas

lastenia_rojas

    Member

  • Members
  • PipPip
  • 19 posts

Posted 07 July 2006 - 06:17 PM

Thanks. Will try that.

#4 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 19 July 2006 - 07:31 PM

In their SQL Server 2000 docs Microsoft warns that ANSI_PADDING should always be set to on. I don't quite know why but they even put a red exclamation on it.

#5 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 24 July 2006 - 01:48 PM

Is anyone running with the padding turned off?

We have been testing with sql padding off, and have not experienced any problems.

We had originally turned it off, because in mssql query analyzer, we found we had to include the trailing spaces in selects to find data.
Eg: select * from CUST where LASTNAME = 'CASH '
instead of select * from CUST where LASTNAME = 'CASH'.

#6 jcduym

jcduym

    Member

  • Members
  • PipPip
  • 35 posts

Posted 25 July 2006 - 09:19 AM

Hi,

Our site has been running for years on a SQL-sever database with ANSI-padding off. Not noticed any problems


Jan

#7 Lewis Mccabe

Lewis Mccabe

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 455 posts
  • Gender:Male
  • Location:Sarasota, Florida

Posted 25 July 2006 - 02:50 PM

I checked on expert exchange for issues related to ANSI_PADDING. The issues all appear to be related to importing/ exporting from/to various database versions. There was no mention of not using it because there were problems with it.

#8 Mike Schoen

Mike Schoen

    Expert

  • Members
  • PipPipPipPip
  • 198 posts
  • Gender:Male
  • Location:Guelph, Canada

Posted 25 July 2006 - 06:24 PM

Thats exactly what I wanted to hear, thanks for your replies.

Mike



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users