How to alter a 'text' column to 'ntext' ?

Hi all,
how to do that with SQL7 or 2000 by Transact-SQL ?
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<

Lilian.
Hi,
You cannot alter the text column directly.
2 Approaches to do this
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL ?
> If I read the SQL2000 doc on 'Alter Table' :
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>
|||Thanks )
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN
> is
>

How to alter a 'text' column to 'ntext' ?

Hi all,
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :[vbcol=seagreen]
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<

Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
>
>
> Lilian.
>|||Thanks )
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
COLUMN[vbcol=seagreen]
> is
>sql

How to alter a 'text' column to 'ntext' ?

Hi all,
how to do that with SQL7 or 2000 by Transact-SQL '
If I read the SQL2000 doc on 'Alter Table' :
>>>>>>>>>>>>..
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.
The altered column cannot be:
a.. A column with a text, image, ntext, or timestamp data type.
<<<<<<<<<<<<<<<<<<<<<<<
:(
Lilian.Hi,
You cannot alter the text column directly.
2 Approaches to do this
--
I.
1.Create an ntext column,
2.update the text column's data to the ntext column,
3.drop the text column (Alter table drop column <column_name>
4.rename the ntext column to the prior text column's name (
5.sp_rename 'table_name.column_name',old_col_name,'column')
II.
1. You have export the data out (BCP OUT/DTS)
2. Take the script of table and dependant objects
3. Drop the table
4. In create tabl script change TEXT datatype to NTEXT
5. Craete the table.
6. BCP IN/ DTS to Import data
7. Create the dependant objects
You can also try the below, which requires less downtime
--
Thanks
Hari
MCDBA
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> how to do that with SQL7 or 2000 by Transact-SQL '
> If I read the SQL2000 doc on 'Alter Table' :
> >>>>>>>>>>>>..
> ALTER COLUMN
> Specifies that the given column is to be changed or altered. ALTER COLUMN
is
> not allowed if the compatibility level is 65 or earlier. For more
> information, see sp_dbcmptlevel.
> The altered column cannot be:
> a.. A column with a text, image, ntext, or timestamp data type.
> <<<<<<<<<<<<<<<<<<<<<<<
> :(
>
> Lilian.
>|||Thanks :))
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23HoJXlGVEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You cannot alter the text column directly.
> 2 Approaches to do this
> --
> I.
> 1.Create an ntext column,
> 2.update the text column's data to the ntext column,
> 3.drop the text column (Alter table drop column <column_name>
> 4.rename the ntext column to the prior text column's name (
> 5.sp_rename 'table_name.column_name',old_col_name,'column')
>
> II.
> 1. You have export the data out (BCP OUT/DTS)
> 2. Take the script of table and dependant objects
> 3. Drop the table
> 4. In create tabl script change TEXT datatype to NTEXT
> 5. Craete the table.
> 6. BCP IN/ DTS to Import data
> 7. Create the dependant objects
>
> You can also try the below, which requires less downtime
> --
> Thanks
> Hari
> MCDBA
> "Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
> news:u3iKxbGVEHA.4092@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > how to do that with SQL7 or 2000 by Transact-SQL '
> >
> > If I read the SQL2000 doc on 'Alter Table' :
> > >>>>>>>>>>>>..
> > ALTER COLUMN
> >
> > Specifies that the given column is to be changed or altered. ALTER
COLUMN
> is
> > not allowed if the compatibility level is 65 or earlier. For more
> > information, see sp_dbcmptlevel.
> >
> > The altered column cannot be:
> >
> > a.. A column with a text, image, ntext, or timestamp data type.
> > <<<<<<<<<<<<<<<<<<<<<<<
> > :(
> >
> >
> > Lilian.
> >
> >
>

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to N
ULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

how to allow multi-rec.set results ?

hi
until today, with sql2000 i get recordsets connecting with
"Provider=sqloledb;Data Source=myServer;Initial
Catalog=myDB;Integrated Security=SSPI"
but now, having migrated to sql2005
i use
"Provider=SQLNCLI;Server=myServer;
Database=myDB;UID=xx;PWD=yy"
i get the ERROR "Current provider does not support
multiple recordsets from a single execution"
wich provider should i use '
thanks
atte,
HernnCheck the following links for more information on MARS:
http://blogs.msdn.com/dataaccess/ar.../02/446894.aspx
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
-Sue
On Mon, 17 Jul 2006 12:04:24 -0300, "bajopalabra"
<bajopalabra@.hotmail.com> wrote:

>hi
>until today, with sql2000 i get recordsets connecting with
> "Provider=sqloledb;Data Source=myServer;Initial
> Catalog=myDB;Integrated Security=SSPI"
>but now, having migrated to sql2005
>i use
> "Provider=SQLNCLI;Server=myServer;
> Database=myDB;UID=xx;PWD=yy"
>i get the ERROR "Current provider does not support
>multiple recordsets from a single execution"
>wich provider should i use '
>thanks

How to alert via email when Agent records errors/warning in log?

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
Hi
Instead of using alerts you could have steps that send emails, to avoid mapi
you can use SMTP see http://www.sqldev.net/xp/xpsmtp.htm. The sending of the
email can be determined by the jobs workflow if necessary.
If you are looking for a product to monitor your server then you may want to
check out MOM.
John
"ssciarrino" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?

How to alert via email when Agent records errors/warning in log?

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error lo
g
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?Hi
Instead of using alerts you could have steps that send emails, to avoid mapi
you can use SMTP see http://www.sqldev.net/xp/xpsmtp.htm. The sending of the
email can be determined by the jobs workflow if necessary.
If you are looking for a product to monitor your server then you may want to
check out MOM.
John
"ssciarrino" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors fo
r
> Mail Profile not available. It wasn't until we looked at the agent error
log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email
or
> alert as a pro-active stance?

How to alert via email when Agent records errors/warning in log?

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?Hi
Instead of using alerts you could have steps that send emails, to avoid mapi
you can use SMTP see http://www.sqldev.net/xp/xpsmtp.htm. The sending of the
email can be determined by the jobs workflow if necessary.
If you are looking for a product to monitor your server then you may want to
check out MOM.
John
"ssciarrino" wrote:
> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?sql

How to add a calcualted field in Matrix?

Hello.. I would like to know how this can be done in RS (SQL2000)...

In the dataset I have... sales, company, and year. What I want to display is such...

2005 2006 Growth

companyA $1,000,000 $500,000 100%

companyB $50,000 $100,000 -50%

How do I add a column in a Matrix to calculate the growth based on the sales? Thanks!

-Joseph

you would have to add growth to your dataset as a calculation in of the years. So for MDX you would add a calculated member to the time dimension and calculate growth in that.