Problem removing trailing spaces

Hi,
I am very new to SQL and made a dumb mistake when setting up my
database. I have some small tables I used for drop-down-lists in a web
page I designed. I used a datatype of CHAR. Well, I should have used
VARCHAR as it is causing me problems now.
I have redesigned the site so the drop-down-lists are not pulled from
the SQL table. The problem is that every record created with one of
these dropdown values has trailing spaces. In other words, if the field
was CHAR with length 10 when we store Canada, it is really
Canada<space><space><space><space>.
I have found one way to fix this:
UPDATE mytable
SET country='Canada' WHERE country='Canada'
This has the effect of removing the spaces. I guess I could also use:
UPDATE mytable
SET country='Canada' WHERE country LIKE '%Canada%'
This is WAY too tedious to do for every value of every column I have
this problem with. Is there a way to iterate through the database a
remove ALL trailing spaces?
I appreciate the help!So you have changed the datatype in the table and want to get rid of the tra
iling spaces in the
varchar column?
UPDATE tblname
SET col = RTRIM(col)
Pls test above first, just in case...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>|||I read about RTRIM but didn't realize it could be used that way. I will
test it. Thanks!
Tibor Karaszi wrote:
> So you have changed the datatype in the table and want to get rid of the t
railing spaces in the
> varchar column?
> UPDATE tblname
> SET col = RTRIM(col)
> Pls test above first, just in case...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> <mattmerc@.bellsouth.net> wrote in message
> news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...|||First, in Enterprise Manager you need to redefine the Char(10) column as
VarChar(30), becuase Char columns cannot be trimmed. The rtrim() function
will returns a string with trailing spaces trimmed. You will probably want
to trim every country in the table, so there is no need to specify a where
clause.
UPDATE mytable SET country = rtrim(country)
http://sqlcourse.com/create.html
http://sqlcourse.com/update.html
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>|||You can also use the RTRIM() function, but this still require you to update
every column. Although it might be easier to drop, recreate the tables
using varchar, and reload them.
--Brian
(Please reply to the newsgroups only.)
<mattmerc@.bellsouth.net> wrote in message
news:1124996454.969917.40520@.o13g2000cwo.googlegroups.com...
> Hi,
> I am very new to SQL and made a dumb mistake when setting up my
> database. I have some small tables I used for drop-down-lists in a web
> page I designed. I used a datatype of CHAR. Well, I should have used
> VARCHAR as it is causing me problems now.
> I have redesigned the site so the drop-down-lists are not pulled from
> the SQL table. The problem is that every record created with one of
> these dropdown values has trailing spaces. In other words, if the field
> was CHAR with length 10 when we store Canada, it is really
> Canada<space><space><space><space>.
> I have found one way to fix this:
> UPDATE mytable
> SET country='Canada' WHERE country='Canada'
> This has the effect of removing the spaces. I guess I could also use:
> UPDATE mytable
> SET country='Canada' WHERE country LIKE '%Canada%'
> This is WAY too tedious to do for every value of every column I have
> this problem with. Is there a way to iterate through the database a
> remove ALL trailing spaces?
> I appreciate the help!
>

Problem of realization replication through WEB

Hello

There is a following mistake at replication through WEB

A security error occurred

I try to go through IE which user has specified in the master of creation of the subscriber, in a diagnostic mode

https://Servak/Replication/replisapi.dll?diag

And all works, all certificates fulfil correctly who did not collide{face} in what business? WHERE TO DIG?

The name of the server on the certificate and the one you are using in the URL do not match. Hence the error.

It could also be that the the certificate is issued to machine.domain and you are using only machine in the URL. Please use the domain name and try again.

Match up the names on both and retry. Let me know if you continue to see the error.

|||

Certification all has adjusted as it is told BOL

The circuit has turned out such

http://replication2006.narod.ru/index.html

All the same deduces this mistake A security error occurred

The certificate signed the center of certification which has requested with IIS, has established the server certificate both on a server and on the client, and even having given{allowed} unlimited rights to participants of the circuit, it is impossible. Help! In what there can be a mistake? Where to dig?

|||

Here the text of a mistake from JOB

I enter this URL the address in IE all Ок!!

Help!

Selected row details:

Log Job History (REPL-GisisTerminalClient_RED-ReplTerm-SHEIN-GisisTerminalClient_RED- 0)

Step ID 1
Server SHEIN
Job Name REPL-GisisTerminalClient_RED-ReplTerm-SHEIN-GisisTerminalClient_RED- 0
Step Name Run agent.
Duration 00:00:31

Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
-XSTEPID 1
-XSUBSYSTEM Merge
-XSERVER SHEIN

-XCMDLINE 0
-XCancelEventHandle 00000634
2006-11-14 15:14:12.578 Connecting to Subscriber 'SHEIN'
2006-11-14 15:14:12.906 Connecting to Subscriber 'SHEIN'
2006-11-14 15:14:13.062 The upload message to be sent to Publisher 'REPL' is being generated
2006-11-14 15:14:13.078 The merge process is using Exchange ID '7AA8372B-F9E6-4F43-99AB-7C49614D2528' for this web synchronization session.

2006-11-14 15:14:28.500 A security error occurred

2006-11-14 15:14:28.546 Category:NULL
Source: Merge Process
Number: -2147209329

Message: A security error occurred

2006-11-14 15:14:28.546 Category:NULL
Source: Merge Process(Web Sync Client)
Number: -2147010889
Message: The Merge Agent could not connect to the URL 'https://repl/ReplWeb/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and tha

Web server is reachable.

|||

Sorry, I dont understand from your reply whether the name of the server on the certificate matches the one on InternetURL in the merge agent.

I see in the job that you have: 'https://repl/ReplWeb/replisapi.dll'

Is the certificate issued to machine "repl"?

Is there domain name also on the certificate? If so, please try using that in the job parameter.

Have you installed the certificate on the client?

Security error is always a result of mismatch or the client does not trust the certificate.

|||

YES, I do{make} inquiry about receptions of the certificate from a computer repl, on a computer repl it is established IISб I sign the made inquiry in the center of certification and I transfer{pass} on repl signed by the center of certification the certificate and the root certificate cent Ра of certification, I install in IE the root certificate, and the certificate signed IIS I fix{establish} for adjustment{option} of encoding SSL, On the client I transfer{pass} the root certificate of the center of certification and I install it{him} with help IE

IE - tools - internet options - content - certificates - trusted root Certification Authorities - import

For check on the client I collect{type}:

<https://shein/ReplGAI/replisapi.dll?diag>

Any problems does not arise at once the window where it is necessary to enter Login and Password is deduced, I receive page:

http://replication2006.narod.ru/TestReplisapi.html

|||Are you saying the problem is now fixed or still have the issue?|||

No. The problem is!

Load a file, I there on steps have described and have drawn a problem. Thanks big that help me!!!

http://www.replication2006.narod.ru/Problem.rar