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 removing replication

Running SQL Server 2000. I removed the subscription and publication from a
database but the database is still showing that it is being replicated. I
have checked the tables in the distribution database and that database is not
listed. Anyone lese ver have this problem or now of a solution.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200610/1
The sp_dboption procedure will give you this information. Alternatively you
can use:
select name, databasepropertyex (name,'IsMergePublished') from
master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from
master..sysdatabases
When you use sp_dboption you pass the database name as the parameter.
The output will look something like this:
The following options are set:
published
select into/bulkcopy
merge publish
trunc. log on chkpt.
auto create statistics
auto update statistics
If they are listed, you use sp_dboption to reverse the flag.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
"masmith via droptable.com" <u27472@.uwe> wrote in message
news:6834f742682f8@.uwe...
> Running SQL Server 2000. I removed the subscription and publication from a
> database but the database is still showing that it is being replicated. I
> have checked the tables in the distribution database and that database is
> not
> listed. Anyone lese ver have this problem or now of a solution.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200610/1
>
|||Thank you worked wonderful
Paul Ibison wrote:[vbcol=seagreen]
>The sp_dboption procedure will give you this information. Alternatively you
>can use:
>select name, databasepropertyex (name,'IsMergePublished') from
>master..sysdatabases
>select name, databasepropertyex (name,'IsPublished') from
>master..sysdatabases
>When you use sp_dboption you pass the database name as the parameter.
>The output will look something like this:
>The following options are set:
>--
>published
>select into/bulkcopy
>merge publish
>trunc. log on chkpt.
>auto create statistics
>auto update statistics
>If they are listed, you use sp_dboption to reverse the flag.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200610/1

Problem removing old back up files....

I am having problems with removing the update files from a folder. When
a do a new backup, I want it to overwrite the old backup files with the
new one. Apparently the code I am using is wrong. Any suggestions?Hi,
Would you be able to post your code so that people can see if they can
see anything with it or if it's fine?
If you are using BACKUP statements then you have to add "INIT" to the
"WITH" section. This will overwrite whatever backup device (e.g. the
file name) that you specify in the statement. For example:
BACKUP DATABASE Test_DB
TO DISK = 'C:\Test_DB_Backup.bak'
WITH INIT
WIll overwrite the previous contents of Test_DB_Backup.bak. Note that
there are some caveats with using INIT (namely that it won't overwrite
in certain situations) but these are described in SQL Server Books
Online in the topic about the BACKUP statement.
Hope that helps a bit|||EXECUTE master.dbo.xp_sqlmaint N'-PlanID
88FF9022-F638-4939-99FF-2FD57140382C -Rpt "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG\<Backup Sever Name> Backup4.txt" -DelTxtRpt 2WEEKS
-WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "\\<Backup Server
Name>\Backup Do Not Touch\<SQL Server Name>" -DelBkUps 1DAYS
-CrBkSubDir -BkExt "BAK"'
There is the one we are using.|||OK, I see...There is an Database Maintenance Plan in place that will
take a backup of the desired database and delete backups older than one
day.
I don't believe that the sqlmaint utility (the utility that will be
called by xp_sqlmaint) has a switch that orders backups to be
overwritten. If you want to take backups but overwrite them each day
then you'll probably need to define your own SQL Server Agent job that
executes the following T-SQL as per whatever schedule you desire:
BACKUP DATABASE <DB_Name>
TO DISK = '<Path Of Backup File (includes the file name)>'
WITH INIT -- Force an overwrite most of the time (some caveats as
described in Books Online)
BTW, I think that generally it is a good idea to keep a few backup
files just in case something goes wrong with the backup you are
currently taking there is something to fall back on.
Hope that helps a bit

Problem removing group header

Using SS2000, RS2000, VS2003
I have a report that displays group totals and grand totals but no detail
lines. There is nothing in the group header. I made the height of the group
header as small as possible but there is still too much room between the rows
when the report is run.
I tried removing the group header and that works as far as the space between
rows. However, after removing the group header and running the report, I
noticed that nothing in the report footer was showing up.
Any ideas? Thanks,
--
Dan D.Have you tried setting Group Header's visibility to False?
"Dan D." wrote:
> Using SS2000, RS2000, VS2003
> I have a report that displays group totals and grand totals but no detail
> lines. There is nothing in the group header. I made the height of the group
> header as small as possible but there is still too much room between the rows
> when the report is run.
> I tried removing the group header and that works as far as the space between
> rows. However, after removing the group header and running the report, I
> noticed that nothing in the report footer was showing up.
> Any ideas? Thanks,
> --
> Dan D.|||I tried that and it made the group footer data disappear. Apparently, the
visibility is for the group as a whole and there is no way to make just the
header hidden.
Thanks,
--
Dan D.
"isaak" wrote:
> Have you tried setting Group Header's visibility to False?
> "Dan D." wrote:
> > Using SS2000, RS2000, VS2003
> > I have a report that displays group totals and grand totals but no detail
> > lines. There is nothing in the group header. I made the height of the group
> > header as small as possible but there is still too much room between the rows
> > when the report is run.
> >
> > I tried removing the group header and that works as far as the space between
> > rows. However, after removing the group header and running the report, I
> > noticed that nothing in the report footer was showing up.
> >
> > Any ideas? Thanks,
> > --
> > Dan D.|||Of course you can do this but not from the Edit Group >> Visibility tab. As
you discovered, changing the visibility here affects the whole group.
Make sure the properties window is visible in report designer (menu: View >>
Properties Window) and then select the group header row you want to hide. Now
find the Visibility property in the properties window, expand it and set
Hidden = True.
"Dan D." wrote:
> I tried that and it made the group footer data disappear. Apparently, the
> visibility is for the group as a whole and there is no way to make just the
> header hidden.
> Thanks,
> --
> Dan D.
>
> "isaak" wrote:
> > Have you tried setting Group Header's visibility to False?
> >
> > "Dan D." wrote:
> >
> > > Using SS2000, RS2000, VS2003
> > > I have a report that displays group totals and grand totals but no detail
> > > lines. There is nothing in the group header. I made the height of the group
> > > header as small as possible but there is still too much room between the rows
> > > when the report is run.
> > >
> > > I tried removing the group header and that works as far as the space between
> > > rows. However, after removing the group header and running the report, I
> > > noticed that nothing in the report footer was showing up.
> > >
> > > Any ideas? Thanks,
> > > --
> > > Dan D.|||That did it Jim. Thanks.
--
Dan D.
"Jim" wrote:
> Of course you can do this but not from the Edit Group >> Visibility tab. As
> you discovered, changing the visibility here affects the whole group.
> Make sure the properties window is visible in report designer (menu: View >>
> Properties Window) and then select the group header row you want to hide. Now
> find the Visibility property in the properties window, expand it and set
> Hidden = True.
> "Dan D." wrote:
> > I tried that and it made the group footer data disappear. Apparently, the
> > visibility is for the group as a whole and there is no way to make just the
> > header hidden.
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "isaak" wrote:
> >
> > > Have you tried setting Group Header's visibility to False?
> > >
> > > "Dan D." wrote:
> > >
> > > > Using SS2000, RS2000, VS2003
> > > > I have a report that displays group totals and grand totals but no detail
> > > > lines. There is nothing in the group header. I made the height of the group
> > > > header as small as possible but there is still too much room between the rows
> > > > when the report is run.
> > > >
> > > > I tried removing the group header and that works as far as the space between
> > > > rows. However, after removing the group header and running the report, I
> > > > noticed that nothing in the report footer was showing up.
> > > >
> > > > Any ideas? Thanks,
> > > > --
> > > > Dan D.|||Well, this is weird. I just noticed that when I change the visibility on the
group header to false, the report header and footer both disappear, like
they've also been changed to false. I don't see a visibility setting for the
report header and footer under properties, though. Do you have any idea what
happened? If I change the visibility of the group header back to true, the
report header and footer both appear again.
--
Dan D.
"Jim" wrote:
> Of course you can do this but not from the Edit Group >> Visibility tab. As
> you discovered, changing the visibility here affects the whole group.
> Make sure the properties window is visible in report designer (menu: View >>
> Properties Window) and then select the group header row you want to hide. Now
> find the Visibility property in the properties window, expand it and set
> Hidden = True.
> "Dan D." wrote:
> > I tried that and it made the group footer data disappear. Apparently, the
> > visibility is for the group as a whole and there is no way to make just the
> > header hidden.
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "isaak" wrote:
> >
> > > Have you tried setting Group Header's visibility to False?
> > >
> > > "Dan D." wrote:
> > >
> > > > Using SS2000, RS2000, VS2003
> > > > I have a report that displays group totals and grand totals but no detail
> > > > lines. There is nothing in the group header. I made the height of the group
> > > > header as small as possible but there is still too much room between the rows
> > > > when the report is run.
> > > >
> > > > I tried removing the group header and that works as far as the space between
> > > > rows. However, after removing the group header and running the report, I
> > > > noticed that nothing in the report footer was showing up.
> > > >
> > > > Any ideas? Thanks,
> > > > --
> > > > Dan D.sql