Hi,
I have searched high and low for an answer to this question. Please
tell me that it is a simple process...!
I have created a website in English using SQL 2000 as the database.
Now I have just launched the same website in French using exactly the
same schema (and same server) as the english website. Both use
full-text search. BUT...I notice that the noise word file being used
for both is English so, naturally, I need to change my French language
site to French. How can I do this?
I have already tried changing the database and field collation in the
French database and re-creating and re-populating the full-text index
but the English noise word file is still being used. Please, somebody
help!!
Darren,
You can switch to another language via dropping the existing FT Catalog and
re-creating it via the FT Indexing Wizard and when you pick the column (Text)
to be FT Indexed, click on the drop down box marked "Language for Word
Breaker" and select your language and then run a full population.
(Thanks to John for telling that trick!-)
Regards, Gerald.
"Darren Jensen" wrote:
> Hi,
> I have searched high and low for an answer to this question. Please
> tell me that it is a simple process...!
> I have created a website in English using SQL 2000 as the database.
> Now I have just launched the same website in French using exactly the
> same schema (and same server) as the english website. Both use
> full-text search. BUT...I notice that the noise word file being used
> for both is English so, naturally, I need to change my French language
> site to French. How can I do this?
> I have already tried changing the database and field collation in the
> French database and re-creating and re-populating the full-text index
> but the English noise word file is still being used. Please, somebody
> help!!
>
|||Darren,
Garald is correct. Just some additional info for future reference... You can
find out which noise.* is linked to what language via the Schema.txt file
under \FTDATA\SQLServer\Config, for example for noise.FRA is the noise word
file for French:
<stoplist
language="French_French"
file="noise.fra"
primarylanguage=12
sublanguage=1>
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com...
> Darren,
> You can switch to another language via dropping the existing FT Catalog
and
> re-creating it via the FT Indexing Wizard and when you pick the column
(Text)[vbcol=seagreen]
> to be FT Indexed, click on the drop down box marked "Language for Word
> Breaker" and select your language and then run a full population.
> (Thanks to John for telling that trick!-)
> Regards, Gerald.
> "Darren Jensen" wrote:
|||Thanks, that worked a treat and very easy to do too!
Gerald Baeck <GeraldBaeck@.discussions.microsoft.com> wrote in message news:<209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com>...
> Darren,
> You can switch to another language via dropping the existing FT Catalog and
> re-creating it via the FT Indexing Wizard and when you pick the column (Text)
> to be FT Indexed, click on the drop down box marked "Language for Word
> Breaker" and select your language and then run a full population.
> (Thanks to John for telling that trick!-)
> Regards, Gerald.
|||I have one follow up question on the subject of fulltext indexing with
French and that is how can I get the search to ignore accents? For
example if I search 'hopital' I would like the results to show all
places where it finds 'hpital' or 'hopital'. Is this possible?
Thanks.
jensendarren@.hotmail.com (Darren Jensen) wrote in message news:<c2c1a066.0411171919.75ce4862@.posting.google. com>...[vbcol=seagreen]
> Thanks, that worked a treat and very easy to do too!
> Gerald Baeck <GeraldBaeck@.discussions.microsoft.com> wrote in message news:<209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com>...
|||Darren,
Unfortunately, the issue of accent sensitivity vs. accent insensitivity FTS
is more difficult to answer. This is a well known bug that has been around
for a long time (since SQL Server 7.0) and is truly only going to be fixed
in SQL Server 2005 <sigh>. As there are only workarounds for this bug in SQL
Server 2000 that require the use of duplicate data where you store the
non-accented search words, such as 'hopital'. You would then FT Index and FT
Search this column, while returning the accented search data (such as
'hpital') from another column back to the user. You will need to develop
accent removal procedures as well as use triggers (insert & update) to
maintain the currency of the two columns...
Regards,
John
"Darren Jensen" <jensendarren@.hotmail.com> wrote in message
news:c2c1a066.0411222026.3278d8fe@.posting.google.c om...
> I have one follow up question on the subject of fulltext indexing with
> French and that is how can I get the search to ignore accents? For
> example if I search 'hopital' I would like the results to show all
> places where it finds 'hpital' or 'hopital'. Is this possible?
> Thanks.
> jensendarren@.hotmail.com (Darren Jensen) wrote in message
news:<c2c1a066.0411171919.75ce4862@.posting.google. com>...[vbcol=seagreen]
news:<209E46D0-5BEE-4531-8145-A9B8DEDFCF50@.microsoft.com>...[vbcol=seagreen]
Catalog and[vbcol=seagreen]
(Text)[vbcol=seagreen]
|||Darren,
my workaround for this problem is to replace all special chars before
filling the index. I know it could take a while, but till Yukon is released
its the only way i think.
UPDATE [table] SET [field] = REPLACE([field], '', 'o')
Regards, Gerald.
"Darren Jensen" <jensendarren@.hotmail.com> schrieb im Newsbeitrag
news:c2c1a066.0411222026.3278d8fe@.posting.google.c om...[vbcol=seagreen]
>I have one follow up question on the subject of fulltext indexing with
> French and that is how can I get the search to ignore accents? For
> example if I search 'hopital' I would like the results to show all
> places where it finds 'hpital' or 'hopital'. Is this possible?
> Thanks.
> jensendarren@.hotmail.com (Darren Jensen) wrote in message
> news:<c2c1a066.0411171919.75ce4862@.posting.google. com>...
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Changing SQL Server Service Password
I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via SQLMonster.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via SQLMonster.com" wrote:
>> Bruce
>> The main reason that I've come across for changing the service account
>> password only via SQLEM is to maintain connections with any Full Text
>> Indexes that you may be using.
>> Changing the password in any other way is guaranteed to require an FTI
>> catalog rebuild (or worse)
>> As for the frequency of password change, I would question why you feel
>> the
>> need to change these on the service accounts. IMHO these accounts should
>> never see the light of day outside of their designated purpose (ie don't
>> log in using these accounts), so should be relatively secure in the long
>> term.
>> Also, if you change these, you are likely to open a whole can of worms
>> regarding access to other resources (file shares, other SQL servers,
>> clustering, replication etc)
>> Yes, it does requrie a restart of services.
>> I have, however, implemented 'monthly' password changes on the sa
>> account,
>> as this is far more visible. It's relatively straightforward to automate
>> using a DTS package.
>> Doing this also discourages developers from hard coding apps to use the
>> sa
>> account ;-))
>> Hope this helps
>> Andy H
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via SQLMonster.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via SQLMonster.com" wrote:
>> Bruce
>> The main reason that I've come across for changing the service account
>> password only via SQLEM is to maintain connections with any Full Text
>> Indexes that you may be using.
>> Changing the password in any other way is guaranteed to require an FTI
>> catalog rebuild (or worse)
>> As for the frequency of password change, I would question why you feel
>> the
>> need to change these on the service accounts. IMHO these accounts should
>> never see the light of day outside of their designated purpose (ie don't
>> log in using these accounts), so should be relatively secure in the long
>> term.
>> Also, if you change these, you are likely to open a whole can of worms
>> regarding access to other resources (file shares, other SQL servers,
>> clustering, replication etc)
>> Yes, it does requrie a restart of services.
>> I have, however, implemented 'monthly' password changes on the sa
>> account,
>> as this is far more visible. It's relatively straightforward to automate
>> using a DTS package.
>> Doing this also discourages developers from hard coding apps to use the
>> sa
>> account ;-))
>> Hope this helps
>> Andy H
Changing SQL Server Service Password
I need to change the password off the SQL Server service on about 70 servers
,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
>sql
,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
BruceBruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
>sql
Changing SQL Server Service Password
I need to change the password off the SQL Server service on about 70 servers,
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
Bruce
Bruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H
|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>
|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
and I'm trying to automate this process. After changing the Service account
password SQL books online states that I should change it for the service
using enterprise manager since it does some other stuff in the
background(including restart the service), but that makes it pretty much
impossible to automate.
I'd like to be able to just create a script to change the password the
service uses. Would this doing this be ok, or is there something enterprise
manager does that is neccessary?
After changing the service, do I have to restart it? In the past I always
have just to ensure that the new password wasn't 'fat-fingered', but if this
is an automated process I don't have that concern. What does concern me is
that some kind of authentication token may expire and the SQL server will go
down because it still has the old password cached. Is that the case?
Does anyone know of a 3rd party tool that could handle this kind of
scenario? Also, what is considered best practice for the frequency of
changing service account passwords? We're thinking somewhere between less
than never and 1 month.
Thanks,
Bruce
Bruce
The main reason that I've come across for changing the service account
password only via SQLEM is to maintain connections with any Full Text
Indexes that you may be using.
Changing the password in any other way is guaranteed to require an FTI
catalog rebuild (or worse)
As for the frequency of password change, I would question why you feel the
need to change these on the service accounts. IMHO these accounts should
never see the light of day outside of their designated purpose (ie don't
log in using these accounts), so should be relatively secure in the long
term.
Also, if you change these, you are likely to open a whole can of worms
regarding access to other resources (file shares, other SQL servers,
clustering, replication etc)
Yes, it does requrie a restart of services.
I have, however, implemented 'monthly' password changes on the sa account,
as this is far more visible. It's relatively straightforward to automate
using a DTS package.
Doing this also discourages developers from hard coding apps to use the sa
account ;-))
Hope this helps
Andy H
|||We don't use FTS so that's not a concern.
The service account passwords are used regularly when we setup new servers,
upgrade hardware, etc. So while they don't see much use they do get used
occasionally.
It sounds like somewhere between 1 year and Six months is the frequency they
should be changed.
Thanks,
Bruce
"Andy Hughes via droptable.com" wrote:
> Bruce
> The main reason that I've come across for changing the service account
> password only via SQLEM is to maintain connections with any Full Text
> Indexes that you may be using.
> Changing the password in any other way is guaranteed to require an FTI
> catalog rebuild (or worse)
> As for the frequency of password change, I would question why you feel the
> need to change these on the service accounts. IMHO these accounts should
> never see the light of day outside of their designated purpose (ie don't
> log in using these accounts), so should be relatively secure in the long
> term.
> Also, if you change these, you are likely to open a whole can of worms
> regarding access to other resources (file shares, other SQL servers,
> clustering, replication etc)
> Yes, it does requrie a restart of services.
> I have, however, implemented 'monthly' password changes on the sa account,
> as this is far more visible. It's relatively straightforward to automate
> using a DTS package.
> Doing this also discourages developers from hard coding apps to use the sa
> account ;-))
> Hope this helps
> Andy H
>
|||Hi
I am busy with an engineering project to change the service passwords.
With 300 Servers at one location, all using the same Service Account for SQL
Server and Agent is not that easy, and yes, there a clusters involved to
that adds a bit of adventure to the whole thing due to the way passwords
need to be changed on a cluster.
Once I have a good solution, I will post it here.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bruce Nation" <Bruce Nation@.discussions.microsoft.com> wrote in message
news:B4EA5677-FCBF-47AD-8DB9-E99BAD66AC84@.microsoft.com...[vbcol=seagreen]
> We don't use FTS so that's not a concern.
> The service account passwords are used regularly when we setup new
> servers,
> upgrade hardware, etc. So while they don't see much use they do get used
> occasionally.
> It sounds like somewhere between 1 year and Six months is the frequency
> they
> should be changed.
> Thanks,
> Bruce
>
> "Andy Hughes via droptable.com" wrote:
Wednesday, March 7, 2012
Changing physical SQL Server
Hello all,
We are in the process of upgrading our SQL physical server (with SS2k). In
the process we will change the OS form NT4 to W2K. What is the best way to
copy all my databases and SQL logins, roles, jobs, alerts, etc. from my
actual (old) SQL Server to my new one?
Thanks for your time.
YannickTransfer logins: see msft support article KB 246133
Transfer databases: backup and restore the Dbs on the new server. This
should copy the permissions and roles. (I recommend ftp'ing the backup
files from one server to another. I learned this the hard way.)
Transfer jobs & alerts: I don't know an easy way. I used EM to
generate sql scripts for each one and then ran them on the new server.|||Thanks for that Louis.
Yannick
"louis" <louisducnguyen@.gmail.com> wrote in message
news:1105044803.604211.75490@.z14g2000cwz.googlegro ups.com...
> Transfer logins: see msft support article KB 246133
> Transfer databases: backup and restore the Dbs on the new server. This
> should copy the permissions and roles. (I recommend ftp'ing the backup
> files from one server to another. I learned this the hard way.)
> Transfer jobs & alerts: I don't know an easy way. I used EM to
> generate sql scripts for each one and then ran them on the new server.
Sunday, February 19, 2012
Changing global variable based on day of the week
I'm new to SSIS so please be gentle...
I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?
Thanks in advance!
Is that the only file on the site? If so it is easier to just download it using a wildcard *.zip. Then use a for each loop to process each file in the folder which will pick up the filename for you.
Even if it's not it's probably better to check all files on the site and process those which you haven't processed before.
What happens on bank holidays.
|||Thanks for replying. Unfortunately it is not the only file (they keep them for archival purposes so there are hundreds). Is there an easy way to track which files I've processed (flag/switch) or are you suggesting a directory comparison or storing file names in a table somehow (not sure how to compare?)?
If there is no file, it just emails a failure notice and the process would be run manually as it is now (not the most efficient way but we can get to bells and whistles later).
You can disregard this question. I actually found the solution in a posting from "killerless" on 8/30/2006. He/she actually did pretty much the exact expression I needed to calculate the day. I love forums! :-)
|||I would use a ForEachLoop conatiner with an expression in FileSpec property. The FileSpec property overrides the 'files' entry in the conatiner GUI. The expression should have the logic to get the right file name based on the system or execution date. I think sothing like this should work:
Datepart("dw", @.[User::CurrentDate] ) == 2 ?
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -3, @.[User::CurrentDate])),2) + ".zip"
:
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -1, @.[User::CurrentDate])),2) + ".zip"
It says someting like: if its friday; then substarct 3 days otherwise substract 1.
Put all the logic inside of the container. Let me know if you want the sample package.
|||It looks like my answer got too late
|||Sorry! I do appreciate the effort!
Changing FQDN on SQL Server 2K
Can anybody shed some light on what I might run into when the FQDN is changed
on SQL Server 2K? We are currently in the process of migrating several
domains into a single .NET domain and there are several SQL Server 2K systems
that will be changed. Researching this I have been seeing a lot of
discrepancies, thus I thought I'd run the question through this newsgroup.
Thanks!Thanks for all the assistance, it was greatly appreciated!
"Bryan.S.Walker" wrote:
> Can anybody shed some light on what I might run into when the FQDN is changed
> on SQL Server 2K? We are currently in the process of migrating several
> domains into a single .NET domain and there are several SQL Server 2K systems
> that will be changed. Researching this I have been seeing a lot of
> discrepancies, thus I thought I'd run the question through this newsgroup.
> Thanks!
>
on SQL Server 2K? We are currently in the process of migrating several
domains into a single .NET domain and there are several SQL Server 2K systems
that will be changed. Researching this I have been seeing a lot of
discrepancies, thus I thought I'd run the question through this newsgroup.
Thanks!Thanks for all the assistance, it was greatly appreciated!
"Bryan.S.Walker" wrote:
> Can anybody shed some light on what I might run into when the FQDN is changed
> on SQL Server 2K? We are currently in the process of migrating several
> domains into a single .NET domain and there are several SQL Server 2K systems
> that will be changed. Researching this I have been seeing a lot of
> discrepancies, thus I thought I'd run the question through this newsgroup.
> Thanks!
>
Changing FQDN on SQL Server 2K
Can anybody shed some light on what I might run into when the FQDN is changed
on SQL Server 2K? We are currently in the process of migrating several
domains into a single .NET domain and there are several SQL Server 2K systems
that will be changed. Researching this I have been seeing a lot of
discrepancies, thus I thought I'd run the question through this newsgroup.
Thanks!
Thanks for all the assistance, it was greatly appreciated!
"Bryan.S.Walker" wrote:
> Can anybody shed some light on what I might run into when the FQDN is changed
> on SQL Server 2K? We are currently in the process of migrating several
> domains into a single .NET domain and there are several SQL Server 2K systems
> that will be changed. Researching this I have been seeing a lot of
> discrepancies, thus I thought I'd run the question through this newsgroup.
> Thanks!
>
on SQL Server 2K? We are currently in the process of migrating several
domains into a single .NET domain and there are several SQL Server 2K systems
that will be changed. Researching this I have been seeing a lot of
discrepancies, thus I thought I'd run the question through this newsgroup.
Thanks!
Thanks for all the assistance, it was greatly appreciated!
"Bryan.S.Walker" wrote:
> Can anybody shed some light on what I might run into when the FQDN is changed
> on SQL Server 2K? We are currently in the process of migrating several
> domains into a single .NET domain and there are several SQL Server 2K systems
> that will be changed. Researching this I have been seeing a lot of
> discrepancies, thus I thought I'd run the question through this newsgroup.
> Thanks!
>
Changing FQDN on SQL Server 2K
Can anybody shed some light on what I might run into when the FQDN is change
d
on SQL Server 2K? We are currently in the process of migrating several
domains into a single .NET domain and there are several SQL Server 2K system
s
that will be changed. Researching this I have been seeing a lot of
discrepancies, thus I thought I'd run the question through this newsgroup.
Thanks!Thanks for all the assistance, it was greatly appreciated!
"Bryan.S.Walker" wrote:
> Can anybody shed some light on what I might run into when the FQDN is chan
ged
> on SQL Server 2K? We are currently in the process of migrating several
> domains into a single .NET domain and there are several SQL Server 2K syst
ems
> that will be changed. Researching this I have been seeing a lot of
> discrepancies, thus I thought I'd run the question through this newsgroup.
> Thanks!
>
d
on SQL Server 2K? We are currently in the process of migrating several
domains into a single .NET domain and there are several SQL Server 2K system
s
that will be changed. Researching this I have been seeing a lot of
discrepancies, thus I thought I'd run the question through this newsgroup.
Thanks!Thanks for all the assistance, it was greatly appreciated!
"Bryan.S.Walker" wrote:
> Can anybody shed some light on what I might run into when the FQDN is chan
ged
> on SQL Server 2K? We are currently in the process of migrating several
> domains into a single .NET domain and there are several SQL Server 2K syst
ems
> that will be changed. Researching this I have been seeing a lot of
> discrepancies, thus I thought I'd run the question through this newsgroup.
> Thanks!
>
Friday, February 10, 2012
Changing database collation
I need to copy a database from one server to another. In the process of
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?
Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/library/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>
|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/library/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>
|||Use a scripting tool like Redgate SQL Compare to generate scripts for your
existing database objects. You could do it using the free 14 day trial then
show your boss how much time(money) it saved and get him or her to buy it.
Create your new database with the collation set as you want it and then
build your tables, stored procedures views etc. from your script. You can
now run SSIS to transfer data from one database to another - although in
this particular case, if it is a one off, not a regular job, T-SQL bulk
insert may be quicker and easier.
Nigel Ainscoe
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?
Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/library/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>
|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/library/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>
|||Use a scripting tool like Redgate SQL Compare to generate scripts for your
existing database objects. You could do it using the free 14 day trial then
show your boss how much time(money) it saved and get him or her to buy it.
Create your new database with the collation set as you want it and then
build your tables, stored procedures views etc. from your script. You can
now run SSIS to transfer data from one database to another - although in
this particular case, if it is a one off, not a regular job, T-SQL bulk
insert may be quicker and easier.
Nigel Ainscoe
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>
Changing database collation
I need to copy a database from one server to another. In the process of
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/library/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||Hi Loren
There is no method to change the collation to an existing SQL Server
database. Using ALTER DATABASE, you can only chage the default collation,
used to create new object, but existing objects maintain the previous
collation.
http://technet.microsoft.com/en-us/library/ms175835.aspx
Try to create a new database, using the desired collation, and create the
schema using the desired collation again. Then, you can populate tables from
your source database using SSIS.
Regards,
GuilleSQL
http://www.guillesql.es
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/library/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>>I need to copy a database from one server to another. In the process of
>>copying the database I would like to change the collation from
>>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
>> I have tried using the SSIS Export Wizard but it had difficulty in
>> transfering views and would not copy any stored procedures. I then tried
>> Restoring a backup of the source database onto the destingation database.
>> I tried using the "alter database statement" to change the collation but
>> this failed due to dependencies on the database collation.
>> Is there a good method for copying a database and changing the collation?
>|||Use a scripting tool like Redgate SQL Compare to generate scripts for your
existing database objects. You could do it using the free 14 day trial then
show your boss how much time(money) it saved and get him or her to buy it.
Create your new database with the collation set as you want it and then
build your tables, stored procedures views etc. from your script. You can
now run SSIS to transfer data from one database to another - although in
this particular case, if it is a one off, not a regular job, T-SQL bulk
insert may be quicker and easier.
Nigel Ainscoe
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/library/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||Hi Loren
There is no method to change the collation to an existing SQL Server
database. Using ALTER DATABASE, you can only chage the default collation,
used to create new object, but existing objects maintain the previous
collation.
http://technet.microsoft.com/en-us/library/ms175835.aspx
Try to create a new database, using the desired collation, and create the
schema using the desired collation again. Then, you can populate tables from
your source database using SSIS.
Regards,
GuilleSQL
http://www.guillesql.es
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/library/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>>I need to copy a database from one server to another. In the process of
>>copying the database I would like to change the collation from
>>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
>> I have tried using the SSIS Export Wizard but it had difficulty in
>> transfering views and would not copy any stored procedures. I then tried
>> Restoring a backup of the source database onto the destingation database.
>> I tried using the "alter database statement" to change the collation but
>> this failed due to dependencies on the database collation.
>> Is there a good method for copying a database and changing the collation?
>|||Use a scripting tool like Redgate SQL Compare to generate scripts for your
existing database objects. You could do it using the free 14 day trial then
show your boss how much time(money) it saved and get him or her to buy it.
Create your new database with the collation set as you want it and then
build your tables, stored procedures views etc. from your script. You can
now run SSIS to transfer data from one database to another - although in
this particular case, if it is a one off, not a regular job, T-SQL bulk
insert may be quicker and easier.
Nigel Ainscoe
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>
Changing database collation
I need to copy a database from one server to another. In the process of
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/...y/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||Hi Loren
There is no method to change the collation to an existing SQL Server
database. Using ALTER DATABASE, you can only chage the default collation,
used to create new object, but existing objects maintain the previous
collation.
http://technet.microsoft.com/en-us/...y/ms175835.aspx
Try to create a new database, using the desired collation, and create the
schema using the desired collation again. Then, you can populate tables from
your source database using SSIS.
Regards,
GuilleSQL
http://www.guillesql.es
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/...y/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>
copying the database I would like to change the collation from
Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
I have tried using the SSIS Export Wizard but it had difficulty in
transfering views and would not copy any stored procedures. I then tried
Restoring a backup of the source database onto the destingation database. I
tried using the "alter database statement" to change the collation but this
failed due to dependencies on the database collation.
Is there a good method for copying a database and changing the collation?Loren,
I have never personally done this so perhaps there is room for some
conjecture...
I would start with a logical method.
1. In SSIS I would use the Transfer database wizard which gives me source
and destination.
2. The next step of the SSIS package would be an execute SQL task and I
would Execute the Alter database command with the collate option.
Collate
http://technet.microsoft.com/en-us/...y/ms184391.aspx
Alter Database
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
Hope that helps...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||Hi Loren
There is no method to change the collation to an existing SQL Server
database. Using ALTER DATABASE, you can only chage the default collation,
used to create new object, but existing objects maintain the previous
collation.
http://technet.microsoft.com/en-us/...y/ms175835.aspx
Try to create a new database, using the desired collation, and create the
schema using the desired collation again. Then, you can populate tables from
your source database using SSIS.
Regards,
GuilleSQL
http://www.guillesql.es
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>I need to copy a database from one server to another. In the process of
>copying the database I would like to change the collation from
>Latin1_General_Bin to SQL_Latin1_General_CP1_CI_AS.
> I have tried using the SSIS Export Wizard but it had difficulty in
> transfering views and would not copy any stored procedures. I then tried
> Restoring a backup of the source database onto the destingation database.
> I tried using the "alter database statement" to change the collation but
> this failed due to dependencies on the database collation.
> Is there a good method for copying a database and changing the collation?
>|||I see what you are saying...
"You can change the collation of any new objects that are created in a user
database by using the COLLATE clause of the ALTER DATABASE statement. This
statement does not change the collation of the columns in any existing
user-defined tables. These can be changed by using the COLLATE clause of
ALTER TABLE. "
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
<GuilleSQL> wrote in message news:eohI5$mTIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Hi Loren
> There is no method to change the collation to an existing SQL Server
> database. Using ALTER DATABASE, you can only chage the default collation,
> used to create new object, but existing objects maintain the previous
> collation.
> http://technet.microsoft.com/en-us/...y/ms175835.aspx
> Try to create a new database, using the desired collation, and create the
> schema using the desired collation again. Then, you can populate tables
> from your source database using SSIS.
> Regards,
> GuilleSQL
> http://www.guillesql.es
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23OXTd8kTIHA.4360@.TK2MSFTNGP06.phx.gbl...
>
Subscribe to:
Posts (Atom)