Renaming a system (host) after MSDE is installed

Should this be an issue?
My system was working fine before I renamed my system (host), but since
I have renamed it, my application is working from a remote system. From
the local system (the machine where the MSDE is running) the app is
working fine.
Any ideas?
- manzoor
With MSDE 2000, if you rename the host then the name of the SQL Server will
follow the name of the host. Make sure your remote client is connection to
the new name.
Jim
"Manzoorul Hassan" <manzoorul.hassan@.gmail.com> wrote in message
news:1105393521.649255.134690@.c13g2000cwb.googlegr oups.com...
> Should this be an issue?
> My system was working fine before I renamed my system (host), but since
> I have renamed it, my application is working from a remote system. From
> the local system (the machine where the MSDE is running) the app is
> working fine.
> Any ideas?
> - manzoor
>
|||Yeap, it's working fine now. Even though I am not doing anything
different. I had expected teh new name to be required and was trying to
use it initially. Maybe it has something to do with using DHCP, but I'm
not sure.
Anyway, I realy do appreciate your reposnse.
- manzoor

removing spaces from a text

Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:

UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')

so, i tried this query and it does nothing.

UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')

does anybody have any ideas? Thanks!Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP|||...So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:
UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')The sample code you gave will do exactly what you wanted it to do, and would not have inserted spaces where the dashes were:select REPLACE('123-45-6789', '-','')So something else is going on. Either you have dirty characters in your data, or you ran a different statement than the one you posted.|||Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP
okay, i ran your code and i'm getting this in the results: 0x20
Is that a space?|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.
thanks for your post! your solution worked perfectly except i had to use this:

update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,8,4)
ELSE SSN
end

i had to insert an 8 b/c the last 4 digits of a ssn start at 8. but thanks for the help though! you solved my problem perfectly!

Removing embedded SQL from database application

I inherited an existing database application about 6 months ago. I've
finished converting it to use SQL Server 2000 as the backend (MS Access
2002 frontend w/ Userforms+VBA code pointing to the backend).

In its current state the application makes use of a ton of embedded SQL
statements and I'm thinking about the best way to clean this up. It
seems like the best way would probably to encapsulate each of the SQL
statements into its own stored proc and then have the MS Access
application call the sp. However, this would result in a very large
number of stored procedures.

Anyone have any links or suggestions regarding best practices on
removing embedded SQL scattered throughout a DB app? Thanks.Beowulf (beowulf_is_not_here@.hotmail.com) writes:
> I inherited an existing database application about 6 months ago. I've
> finished converting it to use SQL Server 2000 as the backend (MS Access
> 2002 frontend w/ Userforms+VBA code pointing to the backend).
> In its current state the application makes use of a ton of embedded SQL
> statements and I'm thinking about the best way to clean this up. It
> seems like the best way would probably to encapsulate each of the SQL
> statements into its own stored proc and then have the MS Access
> application call the sp. However, this would result in a very large
> number of stored procedures.
> Anyone have any links or suggestions regarding best practices on
> removing embedded SQL scattered throughout a DB app? Thanks.

Certainly sounds like a daunting task. An alternative is review all
embedded SQL and make sure that no SQL statements interpolate values,
but all queries are parameterised. Furthermore, make sure that tables
are prefixed with dbo.

As for why, read these two sections:
http://www.sommarskog.se/dynamic_sql.html#SQL_injection
http://www.sommarskog.se/dynamic_sql.html#queryplans

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx