A number of questions have been posted in the past regarding the
problems encountered when renaming a computer that is running SQL or
MSDE. I too have had these problems but recent experience prompts me
to suspect that it is permissible to rename a computer that is running
XP Pro with MSDE2000 without any such problems.
Does anyone know of, or can anyone direct me to official Microsoft
information which details the restrictions on renaming a host computer
that is running SQL2000 in its various forms (Enterprise, Standard,
Personal Ed, MSDE)
Thanks
In my experience (and those of my customers) when you rename a computer and
reboot, SQL Server will reconfigure itself to the new machine name just
fine. Of course any scripts, DTS packages, or any other processes and
procedures that reference the old server name will need to be brought up to
date.
Jim
"David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
news:78d72c83.0404122023.225e5433@.posting.google.c om...
> A number of questions have been posted in the past regarding the
> problems encountered when renaming a computer that is running SQL or
> MSDE. I too have had these problems but recent experience prompts me
> to suspect that it is permissible to rename a computer that is running
> XP Pro with MSDE2000 without any such problems.
> Does anyone know of, or can anyone direct me to official Microsoft
> information which details the restrictions on renaming a host computer
> that is running SQL2000 in its various forms (Enterprise, Standard,
> Personal Ed, MSDE)
> Thanks
|||Thanks for the response Jim, but earlier versions of MSDE and SQL
often fail to work if the computer is renamed although that does not
seem to be the case recently and I have heard a rumor the MSDE2003
will not have this problem. I would like to know the offical Microsoft
position on the, but it is hard to come by.
Thanks again.
"J Young" <thorium48@.hotmail.com> wrote in message news:<O1wV5RRIEHA.3664@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> In my experience (and those of my customers) when you rename a computer and
> reboot, SQL Server will reconfigure itself to the new machine name just
> fine. Of course any scripts, DTS packages, or any other processes and
> procedures that reference the old server name will need to be brought up to
> date.
> Jim
> "David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
> news:78d72c83.0404122023.225e5433@.posting.google.c om...
|||I've only seen problems with SQL Server 7 (ie and MSDE v1).
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
news:78d72c83.0404132223.739b363e@.posting.google.c om...
> Thanks for the response Jim, but earlier versions of MSDE and SQL
> often fail to work if the computer is renamed although that does not
> seem to be the case recently and I have heard a rumor the MSDE2003
> will not have this problem. I would like to know the offical Microsoft
> position on the, but it is hard to come by.
> Thanks again.
> "J Young" <thorium48@.hotmail.com> wrote in message
news:<O1wV5RRIEHA.3664@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
|||Greg is correct. This problem is only present in SQL 7.0 and MSDE 1. In
these version of SQL Server there is no concept of an instance name and the
server name that you would address is always the name of the machine (except
in the case of a SQL7 cluster, which is another topic altogether).
Jim
"David Vanstone" <dave.vanstone@.fujitsu.com.au> wrote in message
news:78d72c83.0404132223.739b363e@.posting.google.c om...
> Thanks for the response Jim, but earlier versions of MSDE and SQL
> often fail to work if the computer is renamed although that does not
> seem to be the case recently and I have heard a rumor the MSDE2003
> will not have this problem. I would like to know the offical Microsoft
> position on the, but it is hard to come by.
> Thanks again.
> "J Young" <thorium48@.hotmail.com> wrote in message
news:<O1wV5RRIEHA.3664@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
to[vbcol=seagreen]
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Friday, March 30, 2012
Tuesday, March 20, 2012
Rename A Field Using SQL Statement?
Is there a way that i can rename some fields in a table using SQL from my
.NET program? The customer send sthem in as BMT Number and I need
BMT_Number, etc.
Thanks
dbWhat about looking in the BOL, there is an example for tenaming
columns:
Renaming a column
The following example renames the TerritoryID column in the
SalesTerritory table to TerrID.
Copy Code
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
.NET program? The customer send sthem in as BMT Number and I need
BMT_Number, etc.
Thanks
dbWhat about looking in the BOL, there is an example for tenaming
columns:
Renaming a column
The following example renames the TerritoryID column in the
SalesTerritory table to TerrID.
Copy Code
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
Monday, March 12, 2012
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!
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!
Monday, February 20, 2012
Removing articles from Merge replication
I do have a merge replication and a number of anonymous subscribers
In the publisher, I have a number of articles (tables) that are published
When the publisher was defined, I could tick in the tables I wanted to
publish.
Now I want to remove 2-3 of these tables. Apparently, when I go into the
publishers property window, I can't untick the tables, so how do I remove
these tables?
Anders
You have to drop the subscriptions to be able to do this in SQL 2000. In SQL
2005 you can drop them, but the subscriptions will be re-initialized.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Anders Balslev" <Anders Balslev@.discussions.microsoft.com> wrote in message
news:9832A46E-D419-496E-B65E-36397009C7FC@.microsoft.com...
>I do have a merge replication and a number of anonymous subscribers
> In the publisher, I have a number of articles (tables) that are published
> When the publisher was defined, I could tick in the tables I wanted to
> publish.
> Now I want to remove 2-3 of these tables. Apparently, when I go into the
> publishers property window, I can't untick the tables, so how do I remove
> these tables?
> Anders
|||Hi Hilary
- Sorry to say, but it still don't work.
I have now - on the subscriber sides - dropped the subscriptions, so I know
for sure there are no subscribers.
Then I go to the publisher, rightclick on the publication, go to the
"articles" section. The tables I want to remove are marked active in the
checkbox, but i'm still unable to unmark these tables.
PS! don't forget that the subscribers are anonymous
Wha can I do
Anders
"Hilary Cotter" wrote:
> You have to drop the subscriptions to be able to do this in SQL 2000. In SQL
> 2005 you can drop them, but the subscriptions will be re-initialized.
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Anders Balslev" <Anders Balslev@.discussions.microsoft.com> wrote in message
> news:9832A46E-D419-496E-B65E-36397009C7FC@.microsoft.com...
>
>
|||On Nov 14, 1:30 am, Anders Balslev
<AndersBals...@.discussions.microsoft.com> wrote:
> Hi Hilary
> - Sorry to say, but it still don't work.
> I have now - on the subscriber sides - dropped the subscriptions, so I know
> for sure there are no subscribers.
> Then I go to the publisher, rightclick on the publication, go to the
> "articles" section. The tables I want to remove are marked active in the
> checkbox, but i'm still unable to unmark these tables.
> PS! don't forget that the subscribers are anonymous
> Wha can I do
> Anders
>
> "Hilary Cotter" wrote:
>
>
>
> - Show quoted text -
have you tried the stored procedure to drop an article? I don't use
the gui much anymore...
http://technet.microsoft.com/en-us/library/ms179926.aspx
sp_dropmergearticle [ @.publication= ] 'publication'
, [ @.article= ] 'article'
[ , [ @.ignore_distributor= ] ignore_distributor
[ , [ @.reserved= ] reserved
[ , [ @.force_invalidate_snapshot= ] force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ] force_reinit_subscription ]
[ , [ @.ignore_merge_metadata = ] ignore_merge_metadata ]
In the publisher, I have a number of articles (tables) that are published
When the publisher was defined, I could tick in the tables I wanted to
publish.
Now I want to remove 2-3 of these tables. Apparently, when I go into the
publishers property window, I can't untick the tables, so how do I remove
these tables?
Anders
You have to drop the subscriptions to be able to do this in SQL 2000. In SQL
2005 you can drop them, but the subscriptions will be re-initialized.
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Anders Balslev" <Anders Balslev@.discussions.microsoft.com> wrote in message
news:9832A46E-D419-496E-B65E-36397009C7FC@.microsoft.com...
>I do have a merge replication and a number of anonymous subscribers
> In the publisher, I have a number of articles (tables) that are published
> When the publisher was defined, I could tick in the tables I wanted to
> publish.
> Now I want to remove 2-3 of these tables. Apparently, when I go into the
> publishers property window, I can't untick the tables, so how do I remove
> these tables?
> Anders
|||Hi Hilary
- Sorry to say, but it still don't work.
I have now - on the subscriber sides - dropped the subscriptions, so I know
for sure there are no subscribers.
Then I go to the publisher, rightclick on the publication, go to the
"articles" section. The tables I want to remove are marked active in the
checkbox, but i'm still unable to unmark these tables.
PS! don't forget that the subscribers are anonymous
Wha can I do
Anders
"Hilary Cotter" wrote:
> You have to drop the subscriptions to be able to do this in SQL 2000. In SQL
> 2005 you can drop them, but the subscriptions will be re-initialized.
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Anders Balslev" <Anders Balslev@.discussions.microsoft.com> wrote in message
> news:9832A46E-D419-496E-B65E-36397009C7FC@.microsoft.com...
>
>
|||On Nov 14, 1:30 am, Anders Balslev
<AndersBals...@.discussions.microsoft.com> wrote:
> Hi Hilary
> - Sorry to say, but it still don't work.
> I have now - on the subscriber sides - dropped the subscriptions, so I know
> for sure there are no subscribers.
> Then I go to the publisher, rightclick on the publication, go to the
> "articles" section. The tables I want to remove are marked active in the
> checkbox, but i'm still unable to unmark these tables.
> PS! don't forget that the subscribers are anonymous
> Wha can I do
> Anders
>
> "Hilary Cotter" wrote:
>
>
>
> - Show quoted text -
have you tried the stored procedure to drop an article? I don't use
the gui much anymore...
http://technet.microsoft.com/en-us/library/ms179926.aspx
sp_dropmergearticle [ @.publication= ] 'publication'
, [ @.article= ] 'article'
[ , [ @.ignore_distributor= ] ignore_distributor
[ , [ @.reserved= ] reserved
[ , [ @.force_invalidate_snapshot= ] force_invalidate_snapshot ]
[ , [ @.force_reinit_subscription = ] force_reinit_subscription ]
[ , [ @.ignore_merge_metadata = ] ignore_merge_metadata ]
Subscribe to:
Posts (Atom)