H
I am very new in SQL2000. My question is , is it possible to change the sort collation in SQL2000 after installation and how. Currently my server is set to case sensitive. how do I change it to case insensitive?
Also is it possible to do this on my select command instead? For instance, my server is case sensitive, but what i want to do is when I do a select command, I would temporarily remove the case sensitivity so it can get the desired results. This setting is only temporary and it goes back to the original setting once the session is diconnected. Is there a way to do this
Thank youCollation in SQL 2000 is now a per database setting. Before you had to
reinstall SQL to change the collation, and you could not load databases that
had a different sort setting.
In SQL 2000, you can set the collation on a server, database, table and
obviously transact-SQL level setting. To change the sort order for the
server, you have to detach all databases, uninstall and reinstall SQL in the
desired sort order, then convert the existing databases into new ones to
change the sort.
To change the sort of a database do ALTER DATABASE <db_name> COLLATE <new
collation order>
To alter a table, you can change the characters columns with ALTER TABLE
ALTER TABLE MyTable ALTER COLUMN ColumnName
[varchar|char](size) COLLATE <new collation order> [NOT
NULL|NULL]
Read more about it by downloading SQL books online from
http://www.microsoft.com/sql and looking up 'change collation'. Good luck.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||The collation on the sql server 2000 is database-specific. That means you
can create your application database with any collation you want.
To do that use, for exaple,
CREATE DATABASE dbname
COLLATE SQL_Latin1_General_CP1_CI_AI
If you want your database with one collation, having, for example, case
insensitive, and you want that one field of one table to be case sensitive,
you can do that by using:
CREATE TABLE mytable (
ID int primary key identity(1,1),
NAME nvarchar(256) COLLATE SQL_Latin1_General_CP1_CS_AI
But, if you want to rebuild your master database (that defines your system
tables collation), you can use the rebuild master using a utility.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rebldmst/rebldmst_24aa.asp.
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||Thanks andy and francisco for the info!!
Showing posts with label installation. Show all posts
Showing posts with label installation. Show all posts
Tuesday, March 27, 2012
changing the SORT Collations
Hi
I am very new in SQL2000. My question is , is it possible to change the sor
t collation in SQL2000 after installation and how. Currently my server is s
et to case sensitive. how do I change it to case insensitive?
Also is it possible to do this on my select command instead? For instance,
my server is case sensitive, but what i want to do is when I do a select com
mand, I would temporarily remove the case sensitivity so it can get the desi
red results. This setting
is only temporary and it goes back to the original setting once the session
is diconnected. Is there a way to do this?
Thank youCollation in SQL 2000 is now a per database setting. Before you had to
reinstall SQL to change the collation, and you could not load databases that
had a different sort setting.
In SQL 2000, you can set the collation on a server, database, table and
obviously transact-SQL level setting. To change the sort order for the
server, you have to detach all databases, uninstall and reinstall SQL in the
desired sort order, then convert the existing databases into new ones to
change the sort.
To change the sort of a database do ALTER DATABASE <db_name> COLLATE <new
collation order>
To alter a table, you can change the characters columns with ALTER TABLE
ALTER TABLE MyTable ALTER COLUMN ColumnName
[varchar|char](size) COLLATE <new collation order> [NOT
NULL|NULL]
Read more about it by downloading SQL books online from
http://www.microsoft.com/sql and looking up 'change collation'. Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||The collation on the sql server 2000 is database-specific. That means you
can create your application database with any collation you want.
To do that use, for exaple,
CREATE DATABASE dbname
COLLATE SQL_Latin1_General_CP1_CI_AI
If you want your database with one collation, having, for example, case
insensitive, and you want that one field of one table to be case sensitive,
you can do that by using:
CREATE TABLE mytable (
ID int primary key identity(1,1),
NAME nvarchar(256) COLLATE SQL_Latin1_General_CP1_CS_AI
But, if you want to rebuild your master database (that defines your system
tables collation), you can use the rebuild master using a utility.
See
http://msdn.microsoft.com/library/d... />
_24aa.asp.
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||Thanks andy and francisco for the info!!
I am very new in SQL2000. My question is , is it possible to change the sor
t collation in SQL2000 after installation and how. Currently my server is s
et to case sensitive. how do I change it to case insensitive?
Also is it possible to do this on my select command instead? For instance,
my server is case sensitive, but what i want to do is when I do a select com
mand, I would temporarily remove the case sensitivity so it can get the desi
red results. This setting
is only temporary and it goes back to the original setting once the session
is diconnected. Is there a way to do this?
Thank youCollation in SQL 2000 is now a per database setting. Before you had to
reinstall SQL to change the collation, and you could not load databases that
had a different sort setting.
In SQL 2000, you can set the collation on a server, database, table and
obviously transact-SQL level setting. To change the sort order for the
server, you have to detach all databases, uninstall and reinstall SQL in the
desired sort order, then convert the existing databases into new ones to
change the sort.
To change the sort of a database do ALTER DATABASE <db_name> COLLATE <new
collation order>
To alter a table, you can change the characters columns with ALTER TABLE
ALTER TABLE MyTable ALTER COLUMN ColumnName
[varchar|char](size) COLLATE <new collation order> [NOT
NULL|NULL]
Read more about it by downloading SQL books online from
http://www.microsoft.com/sql and looking up 'change collation'. Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||The collation on the sql server 2000 is database-specific. That means you
can create your application database with any collation you want.
To do that use, for exaple,
CREATE DATABASE dbname
COLLATE SQL_Latin1_General_CP1_CI_AI
If you want your database with one collation, having, for example, case
insensitive, and you want that one field of one table to be case sensitive,
you can do that by using:
CREATE TABLE mytable (
ID int primary key identity(1,1),
NAME nvarchar(256) COLLATE SQL_Latin1_General_CP1_CS_AI
But, if you want to rebuild your master database (that defines your system
tables collation), you can use the rebuild master using a utility.
See
http://msdn.microsoft.com/library/d... />
_24aa.asp.
"DaSaint" <anonymous@.discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@.microsoft.com...
> Hi
> I am very new in SQL2000. My question is , is it possible to change the
sort collation in SQL2000 after installation and how. Currently my server
is set to case sensitive. how do I change it to case insensitive?
> Also is it possible to do this on my select command instead? For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results. This setting is only temporary and it goes back to
the original setting once the session is diconnected. Is there a way to do
this?
> Thank you|||Thanks andy and francisco for the info!!
Sunday, March 11, 2012
Changing Server
Hi guys
We are closing down our old test server. This server used to run RS. Is
there any documentation on how to move the RS installation to another server
so that Reports, Schedules etc. is intact and ready for use on the new
server?
Thanks.
Regards
JonasJonas,
You may find my post useful
http://prologika.com/blog/archive/2004/08/20/161.aspx
Let me know if you find discrepancies.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:O9WLxgkjEHA.1136@.tk2msftngp13.phx.gbl...
> Hi guys
> We are closing down our old test server. This server used to run RS. Is
> there any documentation on how to move the RS installation to another
server
> so that Reports, Schedules etc. is intact and ready for use on the new
> server?
> Thanks.
> Regards
> Jonas
>|||Hi Teo
My current version of various sw does not support web farms therefore I had
to:
- Remove the key in the new server: rskeymgmt -r[InstallationID]
(InstallationId is in the RSReportServer.Config file).
- Swap the installation id in the actual RSReportServer.Config file with
the old one.
- Run rskeymgmt -a -f -p
Regards
Jonas
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:uYUkuAojEHA.3972@.tk2msftngp13.phx.gbl...
> Jonas,
> You may find my post useful
> http://prologika.com/blog/archive/2004/08/20/161.aspx
> Let me know if you find discrepancies.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:O9WLxgkjEHA.1136@.tk2msftngp13.phx.gbl...
> > Hi guys
> >
> > We are closing down our old test server. This server used to run RS. Is
> > there any documentation on how to move the RS installation to another
> server
> > so that Reports, Schedules etc. is intact and ready for use on the new
> > server?
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>
We are closing down our old test server. This server used to run RS. Is
there any documentation on how to move the RS installation to another server
so that Reports, Schedules etc. is intact and ready for use on the new
server?
Thanks.
Regards
JonasJonas,
You may find my post useful
http://prologika.com/blog/archive/2004/08/20/161.aspx
Let me know if you find discrepancies.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:O9WLxgkjEHA.1136@.tk2msftngp13.phx.gbl...
> Hi guys
> We are closing down our old test server. This server used to run RS. Is
> there any documentation on how to move the RS installation to another
server
> so that Reports, Schedules etc. is intact and ready for use on the new
> server?
> Thanks.
> Regards
> Jonas
>|||Hi Teo
My current version of various sw does not support web farms therefore I had
to:
- Remove the key in the new server: rskeymgmt -r[InstallationID]
(InstallationId is in the RSReportServer.Config file).
- Swap the installation id in the actual RSReportServer.Config file with
the old one.
- Run rskeymgmt -a -f -p
Regards
Jonas
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:uYUkuAojEHA.3972@.tk2msftngp13.phx.gbl...
> Jonas,
> You may find my post useful
> http://prologika.com/blog/archive/2004/08/20/161.aspx
> Let me know if you find discrepancies.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:O9WLxgkjEHA.1136@.tk2msftngp13.phx.gbl...
> > Hi guys
> >
> > We are closing down our old test server. This server used to run RS. Is
> > there any documentation on how to move the RS installation to another
> server
> > so that Reports, Schedules etc. is intact and ready for use on the new
> > server?
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>
Sunday, February 19, 2012
Changing From Default Installation
Hello,
Our security center just scanned my database server and has asked that I change the database server from the default instance name and change the default port used. When installed MS SQL Server 2000 I used the default installation settings. I have installed SP4 and this is all running on a fully patched WIN 2003 Server machine. Is this possible without un-installing and then re-installing?
Thanks for any and all help.
Ben
Renaming the instance is not possible. For installing a named instance, you will have to run the installer of SQL Server and install a named instance from scratch (and patch the instance afterwards with the appropiate service packs) The port can be changed using the SQL Server Configuration Manager:Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration manager > SQL Server Network configuration > Protocols (Select the instance to configure) > TCP/IP / IPAdresses > Condigure the port for the appropiate IP Addresses
restart the service...done.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the help!
Thursday, February 16, 2012
Changing folder for databases to reside in?
Can I change the folder that the databases reside in for my installation of SQL Server? I would like to have them in a folder on another drive, but I can't seem to find where the path is configured.
Any help is appreciated.I don't think you can because all SQL Server files are in Microsoft SQL Server folder under programs in the C drive but I am not a networking person so you may get a better answer. I am assuming you could tell setup to put the folder in a different drive during installation but I could be wrong. Hope this helps.|||When restoring the Database you can select the folders to use in the options tab.
I am 95% sure you can not change the folders after restoring.
Mathias|||Ok, thanks for the information!|||In Enterprise Manager, open the property dialogue for your Sql Server Instance. Click the "Database Settings" TAB. You'll see "New database default location". Use this to set a new locations for DataBases and Logfiles. Next time you create a DB, it will be created in this Directory.
I believe it's possible to backup and recreate all your database to the new location.
NOT FOR THE FAINT OF HEART
You will need to change the "startup parameters" to point to the new location of the Master DB, ErrorLog and MastLog files after recreating them in the new location. This is done at your own risk. I am not even sure if it is possible to move the MASTER etc.
NOT FOR THE FAINT OF HEART
Any help is appreciated.I don't think you can because all SQL Server files are in Microsoft SQL Server folder under programs in the C drive but I am not a networking person so you may get a better answer. I am assuming you could tell setup to put the folder in a different drive during installation but I could be wrong. Hope this helps.|||When restoring the Database you can select the folders to use in the options tab.
I am 95% sure you can not change the folders after restoring.
Mathias|||Ok, thanks for the information!|||In Enterprise Manager, open the property dialogue for your Sql Server Instance. Click the "Database Settings" TAB. You'll see "New database default location". Use this to set a new locations for DataBases and Logfiles. Next time you create a DB, it will be created in this Directory.
I believe it's possible to backup and recreate all your database to the new location.
NOT FOR THE FAINT OF HEART
You will need to change the "startup parameters" to point to the new location of the Master DB, ErrorLog and MastLog files after recreating them in the new location. This is done at your own risk. I am not even sure if it is possible to move the MASTER etc.
NOT FOR THE FAINT OF HEART
Subscribe to:
Posts (Atom)