Solved

Insertion of blank caracs in each rows.

Posted on 1998-11-19
5
206 Views
Last Modified: 2010-03-19
Hi,

I DO am a novice user of SQLServer 6.5 and 7.0 beta3
and I have a problem in my DB that is the following:

MSSQL automatically inserts white spaces at the end of each
new slot in any inserted row so that the size of each string in the row is maximal.

For instance, if I create a table with a column of type
VARCHAR(5) and I insert a row containing the string "toto", the table will contain a row with the string
equaling "toto ".

The strange thing is that it does not happend systematically!
If I drop the table and fill it several times, I do not
get the same problem again, but new rows inserted
afterwards are still affected...

This should not be coming from my stored procedures as long
as I haven't got any pb on another DB...

Is there a problem with my SQLServer configuration?
0
Comment
Question by:grosbill
5 Comments
 
LVL 2

Expert Comment

by:formula
ID: 1091904
I do not get the same result as you.  I tried the exact same example and although it looks like white space in the varchar(5)
field (when doing select *), in actuality the 5th char is null.
I tested this by trying this select:

select substring(field1,5,1) from test1

This select returns a null in column 5 of the field which is correct.
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1091905
This will be the case if you use CHAR , not  VARCHAR. What do you use to view the table?
0
 
LVL 1

Expert Comment

by:csalves
ID: 1091906
Hi,
Are you sure that your problem is not in INSERT?
if it is use trim$() function to insert your data.

bye,
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1091907
SQL Server does not "Pad" varchar columns to fill them all out.  YOu need to look outside of the server for the problem if this is the result you are getting, such as at the program that loads the rows, or the program you are using to display the rows.  You can verify this yorself by using an insert query within SQL Enterprise manager.  It will not pad the the columns.  Something else is doing this to your app, if it is really happening.
0
 

Author Comment

by:grosbill
ID: 1091908
OK, I was all wrong and my question was wrong too because I discovered that I had
some CHAR(...) left in some stored procedures instead of VARCHAR(...).

So lots of thanks to you all (and especially spiridonov) for your help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

862 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

22 Experts available now in Live!

Get 1:1 Help Now