Changing table column size

I'm new to mssql.

I need to change the size of a column of a table from char(255) to char(500). I used the line:

alter table table_name alter column column_name char(500)

When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.

What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?Really?

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(250))
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',250) UNION ALL
SELECT REPLICATE('x',25) UNION ALL
SELECT REPLICATE('x',2)
GO

SELECT LEN(Col1) FROM myTable99
GO

-- Will Fail
INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

ALTER TABLE myTable99 ALTER COLUMN Col1 char(500)
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

SELECT LEN(Col1) FROM myTable99
GO

DROP TABLE myTable99
GO|||xiphias, you're probably testing the number of characters inserted by doing SELECT col_name FROM table. At this point you see only 255 characters, right? Check your QA settings (Tools/Options/Results/Maximum characters per column)|||Haha. That was the problem. I changed the settings and now I can see all the characters.

Now I try to do queries with the database with PHP and the number of characters return from the query is 255 again. Is there some setting file that I have to change in PHP?|||Your's is an interface problem...has nothing to do with sql server...

What's PHP?|||Sorry, I didn't know what was wrong..

PHP is a programming language for dynamic web pages. I guess I'll have to ask that in a different forum.

Thanks for the help.|||True, it seems you need to control from PHP point of view also.sql

Changing SQL Account and Password

Hello, about 3 weeks ago I went about changing the service startup account and password for the 2 SQL Service Accounts (MSSQL & SQLAgent) using the Service applet instead of the Enterprise Manager. I made a mistake in that I forgot to ensure 2 rights (Replace a Process Level Token & Lock Pages in Memory) when I made the change but the services were still able to startup and they ran for about 1 week. I have since changed the service startup account the proper way by dropping the account and adding it again via Enterprise Manager. I checked and all the rights, etc. that Microsoft requires are there. Lately our SQL 2000 Cluster has been acting up during peak periods (the CPU goes to 100%) and our site stops responding. This did not happen that much prior to this change. My question is: is there anyway what I did initially could've caused damage to my SQL cluster? Any way to fix it? thanks for any help.i don't think cpu spiking is directly (or indirectly) related to changes you made.

Changing maximum memory option

If I change the Maximum Memory option (sp_configure or vai EM), do I need to restart the MSSQL service?
CliveNo restart of the service is required, anymore. At least in SQL 2000. If you do this via sp_configure, you will need to run reconfigure with override.|||Thank you for the quick reply.

Changing default location for FTDATA folder

When I restore a database it will place the catalogs in the default location
"C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA", how can I change this
default location so that when I restore a new database, the full text
catalogs are stored elsewhere ?
Changing the location of the catalog is only an option in SQL 2005.
Basically the full text catalog is in a different file group so you have an
option of exactly where you want to park it.
For SQL 200 I suggest you follow the instructions in
http://support.microsoft.com/default...b;en-us;240867
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GMG" <nospam@.nospam.com> wrote in message
news:evgizv87FHA.3048@.TK2MSFTNGP10.phx.gbl...
> When I restore a database it will place the catalogs in the default
> location
> "C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA", how can I change
> this
> default location so that when I restore a new database, the full text
> catalogs are stored elsewhere ?
>
|||I am aware of this document and have found it very complicated and with a
lot of registry updates which makes me uneasy. I have finally opted for
dropping the catalog and then recreating it in a different location,
followed by full population.
Despite this when the documentation for sp_help_fulltext_catalogs states:
"NULL indicates the default directory determined during installation", my
question is how do I change this default directory ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:equvR897FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Changing the location of the catalog is only an option in SQL 2005.
> Basically the full text catalog is in a different file group so you have
an
> option of exactly where you want to park it.
> For SQL 200 I suggest you follow the instructions in
> http://support.microsoft.com/default...b;en-us;240867
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "GMG" <nospam@.nospam.com> wrote in message
> news:evgizv87FHA.3048@.TK2MSFTNGP10.phx.gbl...
>
|||Try this key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\SQL2000\MSSQLServer
And set it using this entry FullTextDefaultPath
Note that my instance name is SQL2000, so it could be
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer for you
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GMG" <nospam@.nospam.com> wrote in message
news:O4vaSO%237FHA.3592@.TK2MSFTNGP12.phx.gbl...
>I am aware of this document and have found it very complicated and with a
> lot of registry updates which makes me uneasy. I have finally opted for
> dropping the catalog and then recreating it in a different location,
> followed by full population.
> Despite this when the documentation for sp_help_fulltext_catalogs states:
> "NULL indicates the default directory determined during installation", my
> question is how do I change this default directory ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:equvR897FHA.3224@.TK2MSFTNGP09.phx.gbl...
> an
>