SQL Server 2K Database Name Problem

[David C. Barber]

This updates a previous post.

We have updated our SQL Server 2000 to new hardware by reinstalling SQL
Server 2K to a new, dual processor server. We performed a full SQL backup
of the entire server, and did a full restore onto the new hardware, which
has the same server name and IP address. One difference has bitten us,
however.

When I perform an sp_who standard procedure call in VB6, the dbname field
comes back with our database names padded with spaces out to 100 characters
total length. Database names in the left panel of Enterprise Manager also
show this padding. This seems only to apply to databases we restored in
their entirety, and not either newly created ones, or standard SQL Server
ones (e.g. master, model, msdb, Northwind, pubs tempdb).

Would really like to know how this could have happened since it has affected
some applications.

[Dan Guzman]

You still have an open thread on this subject ("Restore Messed Up Table
Names"). Erland Sommarskog asked you a couple of questions and is still
waiting for a response.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David C. Barber" <da***@NOSPAMdbarber.comwrote in message
news:MZ******************************@comcast.com. ..

This updates a previous post.

We have updated our SQL Server 2000 to new hardware by reinstalling SQL
Server 2K to a new, dual processor server. We performed a full SQL backup
of the entire server, and did a full restore onto the new hardware, which
has the same server name and IP address. One difference has bitten us,
however.

When I perform an sp_who standard procedure call in VB6, the dbname field
comes back with our database names padded with spaces out to 100
characters
total length. Database names in the left panel of Enterprise Manager also
show this padding. This seems only to apply to databases we restored in
their entirety, and not either newly created ones, or standard SQL Server
ones (e.g. master, model, msdb, Northwind, pubs tempdb).

Would really like to know how this could have happened since it has
affected
some applications.

[Erland Sommarskog]

David C. Barber (da***@NOSPAMdbarber.com) writes:

When I perform an sp_who standard procedure call in VB6, the dbname
field comes back with our database names padded with spaces out to 100
characters total length. Database names in the left panel of Enterprise
Manager also show this padding. This seems only to apply to databases
we restored in their entirety, and not either newly created ones, or
standard SQL Server ones (e.g. master, model, msdb, Northwind, pubs
tempdb).

Would really like to know how this could have happened since it has
affected some applications.

You have now posted three times about this issue, and I still don't
know how you performed the server migration.

From your second post, I got the impression that you used Windows backup
to move the entire disk contents. But reading the above it seems to
me that you installed SQL Server on the new hardware and then
restored/attached the user databases on the new hardware.

To bring this to any progress, let me present a guess. Someone wrote
composed a script to reattach the database files, probably by running a
query of some sort. Maybe he looped over sp_helpdb and for some
reason he took the name of database from the name column in the
in the first row of the second result set - this the logical name of
the datafile, and then he constructed:

"sp_attach_db '" + name + "', '" + datafilename + "', '" + logfilename

This would result in database name to be padded as that name is
always reported as 128 by sp_helpdb.

There are a number of possible variations on this theme, but all
boils down to a user error.

Of course, this is just a wild guess, but that is sort of the rule in
these newsgroups: the less you share about your problem, the wilder
the guesses.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx