Change in legend of chart based on values?

I am developing a chart with type as column and subtype as stacked. The values (different columns from my dataset) are shown as series in the chart. If I have an entire column with no values, nothing in shown in the graph but the column name comes in the legend. I do not want to show the particular column in legend if all the values in that column are 0 or null. Is it possible through an expression or any other way?

Please let me know.

Thanks in advance !!!

If you use a dynamic series grouping in the chart and you want to get rid of a particular series grouping instance, you could use a filter expression on the series grouping, e.g.
Filter expression: =Sum(Fields!Name.Value, "DynamicSeriesGroupingName")
Filter operator: >
Filter value: =0

Again, the filter approach will only work in the case of dynamic series groupings.

-- Robert

Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

Change Graph type at run time

Hi
Can the user not change the graph type at run time the same way you can
change for example background colour. I want to have a parameter for the user
so he can choose line, bar or pie for the same graph.
Thanks
FrancoisSetting chart types dynamically is not supported in the current release.
However, you could approximate the desired behavior by using multiple charts
(of various types) and use an expression to dynamically hide all charts but
the one you want displayed.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Francois" <Francois@.discussions.microsoft.com> wrote in message
news:7CDB8D7C-2444-4C9B-916C-59A2A8946FB2@.microsoft.com...
> Hi
> Can the user not change the graph type at run time the same way you can
> change for example background colour. I want to have a parameter for the
user
> so he can choose line, bar or pie for the same graph.
> Thanks
> Francois

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!
It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change Data type from Nvarchar to datetime

i have a sql db in production and i need to change the data type of one of m
y
fields. Can this be done? I have made several attempts but have failed.Probably because you have data in there that is not a valid datetime. This
is a common symptom for using a catch-all datatype like nvarchar for storing
inappropriate data, whether it be to ease the development/validation of end
user interfaces or to seamlessly integrate legacy data.
"Failed" could mean many things to me. Are you just saying ALTER TABLE
tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to a
conversion issue, then you may want to consider trying this:
-- how many are bad:
SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
-- a few samples:
SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
You might be really hosed, or you my just have to tweak the data before
altering the table, by converting to a valid datetime format. It's tough to
get any more specific without valid DDL and sample data (see
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>i have a sql db in production and i need to change the data type of one of
>my
> fields. Can this be done? I have made several attempts but have failed.|||Thank you for your suggestion. However, I do not have any data in this
particular field. This is the error I get...
'Contacts' table
- Warning: Data might be lost converting column 'MaritalStatus' from
'nvarchar(20)'.
The MaritalStatus field does not have any data. I would like to rename it
to PromoCodeEmail and change the data type to datetime.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Probably because you have data in there that is not a valid datetime. Thi
s
> is a common symptom for using a catch-all datatype like nvarchar for stori
ng
> inappropriate data, whether it be to ease the development/validation of en
d
> user interfaces or to seamlessly integrate legacy data.
> "Failed" could mean many things to me. Are you just saying ALTER TABLE
> tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to
a
> conversion issue, then you may want to consider trying this:
> -- how many are bad:
> SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
> -- a few samples:
> SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
> You might be really hosed, or you my just have to tweak the data before
> altering the table, by converting to a valid datetime format. It's tough
to
> get any more specific without valid DDL and sample data (see
> http://www.aspfaq.com/5006
>
>
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>
>|||Joe,
First, thats a warning, not an error. The warning is generated because
changing data types can result in loss of data, but that doesn't necessarily
mean they will.
Second, if you have a column that is not used, drop the column and add a new
column. There is no need to rename it and change the data type.
You are best off using a script for these changes rather than using the gui.
Scripts can be run against your development and test databases and debugged
before runnign them against production. Scripts also insure that the
changes made in all databases are the same, without having to worry about
user error from clicking on the wrong field in enterprise manager.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:82D1F9C5-9AF9-4D3E-AC46-886F0E64CB4B@.microsoft.com...
> Thank you for your suggestion. However, I do not have any data in this
> particular field. This is the error I get...
> 'Contacts' table
> - Warning: Data might be lost converting column 'MaritalStatus' from
> 'nvarchar(20)'.
> The MaritalStatus field does not have any data. I would like to rename it
> to PromoCodeEmail and change the data type to datetime.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
This
storing
end
to a
tough to
of
failed.|||I have never written a script before. Is there some literature that will
assist me in doing you suggested?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Probably because you have data in there that is not a valid datetime. Thi
s
> is a common symptom for using a catch-all datatype like nvarchar for stori
ng
> inappropriate data, whether it be to ease the development/validation of en
d
> user interfaces or to seamlessly integrate legacy data.
> "Failed" could mean many things to me. Are you just saying ALTER TABLE
> tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to
a
> conversion issue, then you may want to consider trying this:
> -- how many are bad:
> SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
> -- a few samples:
> SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
> You might be really hosed, or you my just have to tweak the data before
> altering the table, by converting to a valid datetime format. It's tough
to
> get any more specific without valid DDL and sample data (see
> http://www.aspfaq.com/5006
>
>
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>
>|||What part of my suggestion are you having difficulty with?
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:356065F7-EB6A-4304-9448-ECF8B0FB40F7@.microsoft.com...
>I have never written a script before. Is there some literature that will
> assist me in doing you suggested?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

Change data type during INSERT INTO ?

I'm doing a data conversion project, moving data from one SQL app to
another.
I'm using INSERT INTO with Select and have the syntax correct. But when
executing the script I get:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Is it possible to change the data type during the INSERT INTO statement?

ThanksThe destination table should be compatible with the data type of select
table

Madhivanan|||"rdraider" <rdraider@.sbcglobal.net> wrote in message
news:eRu_d.11608$C47.6368@.newssvr14.news.prodigy.c om...
> I'm doing a data conversion project, moving data from one SQL app to
> another.
> I'm using INSERT INTO with Select and have the syntax correct. But when
> executing the script I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to float.
> Is it possible to change the data type during the INSERT INTO statement?
> Thanks

As the insert into has to guess about the datatypes of the table to create,
the safest way is to specifically create the table prior to the select into.

You can use cast or convert to gain a little more control over the way the
table is created.
but since you didn't post and example I can't help you more than this.|||On Fri, 18 Mar 2005 06:48:42 GMT, rdraider wrote:

>I'm doing a data conversion project, moving data from one SQL app to
>another.
>I'm using INSERT INTO with Select and have the syntax correct. But when
>executing the script I get:
>Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to float.
>Is it possible to change the data type during the INSERT INTO statement?

Hi rdraider,

If you use
INSERT INTO tablename (col1, col2, ..., colN)
SELECT expr1, expr2, ..., exprN
FROM ...
WHERE ...

Then the result of each expression will implicitly be converted to the
datatype of the corresponding column before it's stored in the table.

Your message indicates that one of your expressions is of datetype
varchar, but the corresponding column is of datatype float, and the
implicit conversion failed. E.g. because the varchar value to be
converted happened to be 'xxhasiu'.

If you use INSERT without column list, or SELECT *, then the first thing
should be to add a column list - changes to the table structure might
change the number and order of columns in the INSERT or in the SELECT *,
inducing a mismatch; explicitly naming the columns ensures that this
won't happen.

If that doesn't fix it, then find out which column(s) in the destination
table are of datatype float; run the SELECT (without the INSERT INTO) to
see the results and inspect the output to find the offending value. If
the output has too many rows for visual inspection, you might try adding
AND ISNUMERIC(exprN) = 0
This will return only the rows where exprN can't be converted to int,
float, money or decimal.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
Paul
Paul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>
|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT@.String = 'Hello'
SELECTDATALENGTH(@.String)
SELECTDATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)

Change data type

Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
Wait till "off hours"/ drop Subscription/ drop article/ change data type on both tables/ add article/ add subscription. If theres no good time to do it, you can re-snapshot the data as well.
"David" <davbarquero@.hotmail.com> wrote in message news:OvRppwFnEHA.3196@.TK2MSFTNGP10.phx.gbl...
Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
|||David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje news:eOJxZYJnEHA.3428@.TK2MSFTNGP11.phx.gbl...
David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Change Data Type

I received a db2 data file that I converted to MS-Access. From Access I
ran the upsize wizard to put the tabel in SQL. It put the table in ok
but all the data types are nvarchar. I have a couple of the fields that
are cureny and some that are numeric.

I need to change the data types from nvarchar to numeric type fields. I
am new to SQL so I do not know all the commands. How do I change the
data type?

Michael Charney

*** Sent via Developersdex http://www.developersdex.com ***ALTER TABLE table_name ALTER COLUMN col_name VARCHAR(10) ;

--
David Portas
SQL Server MVP
--

"Nothing" <me@.you.com> wrote in message
news:1Je1f.13$vU5.1288@.news.uswest.net...
>I received a db2 data file that I converted to MS-Access. From Access I
> ran the upsize wizard to put the tabel in SQL. It put the table in ok
> but all the data types are nvarchar. I have a couple of the fields that
> are cureny and some that are numeric.
> I need to change the data types from nvarchar to numeric type fields. I
> am new to SQL so I do not know all the commands. How do I change the
> data type?
> Michael Charney
> *** Sent via Developersdex http://www.developersdex.com ***

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requirements. If you have long string lengths you need to remember that nvarchar will double your storage
e.g
DECLARE @.string VARCHAR(10
SELECT @.String = 'Hello
SELECT DATALENGTH(@.String
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10))
Regard
Juli
http://www.sqlporn.co.uk :o)

Change Data Type

Hi,
I am not sure if I am posting to the correct group, but I am wondering if
there are any dangers in changing datatypes on a column.
More specifically, I want to change some varchars to nvarchars and texts to
ntexts. I already have the script written for the columns I need to change.
I have seen no issues in doing this on my test database, but I was wondering
if there are any inherent dangers in doing this.
Are there any articles that can be helpful for this subject?
Thank you,
PaulPaul,
The issues with varchar and nvarchar are fairly straight-forward, however
ALTER TABLE cannot change texts and ntexts. I understand that you must drop
any indexes on a varchar that you are changing to nvarchar before doing the
alter. One of the other main restrictions is that the data must all fit
into the new target. The BOL explains most of this in the ALTER TABLE
topic.
You might also find the following article from SQL Server magazine helpful.
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsqlmag04/html/InsideALTERTABLE.asp
Russell Fields
"Paul" <paul@.gotheta.com> wrote in message
news:uJjE5d6MEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am not sure if I am posting to the correct group, but I am wondering if
> there are any dangers in changing datatypes on a column.
> More specifically, I want to change some varchars to nvarchars and texts
to
> ntexts. I already have the script written for the columns I need to
change.
> I have seen no issues in doing this on my test database, but I was
wondering
> if there are any inherent dangers in doing this.
> Are there any articles that can be helpful for this subject?
> Thank you,
> Paul
>|||With the varchar to nvarchar make sure you're aware of the storage requireme
nts. If you have long string lengths you need to remember that nvarchar will
double your storage:
e.g.
DECLARE @.string VARCHAR(10)
SELECT @.String = 'Hello'
SELECT DATALENGTH(@.String)
SELECT DATALENGTH(CAST(@.String AS NVARCHAR(10)))
Regards
Julie
http://www.sqlporn.co.uk :o)

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change t
he
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?
Hi
"DBA72" wrote:

> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

change collation of a type of text column

Hi!
I have to change a complate database collation. After I had changed the
database collation I go through the tables and its columns and make a DDL
command on collated columns :
"ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
It work on varchar and char columns but I receive an error message in case
of text columns :
"Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'Ic_DbFields' because it is 'text'."
I use OLEDB.
Enterprise Manager can change the collation on text columns.
What can I do?
I nedd to use SQL-DMO?
thanks for any help :
ImreCheck if you have a full-text index using that column. if so, then you have
to remove it before changing the collation.
AMB
"Imre Ament" wrote:

> Hi!
> I have to change a complate database collation. After I had changed the
> database collation I go through the tables and its columns and make a DDL
> command on collated columns :
> "ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
> It work on varchar and char columns but I receive an error message in case
> of text columns :
> "Server: Msg 4928, Level 16, State 1, Line 1
> Cannot alter column 'Ic_DbFields' because it is 'text'."
> I use OLEDB.
> Enterprise Manager can change the collation on text columns.
> What can I do?
> I nedd to use SQL-DMO?
> thanks for any help :
> Imre|||I don't think you can alter collation for blob columns.
One option can be to add a new column with desired collation, update the new
column with the value
of the old column, drop the old column and rename the new column. Column ord
er will not be
preserved, of course.
Another option is to create a new table. My guess is that this is what Enter
prise Manager does.
Imre: pressing "save change script" will show you how EM does this. I doubt
it is exposed in DMO, as
the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But yo
u could have a look, of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
> Check if you have a full-text index using that column. if so, then you hav
e
> to remove it before changing the collation.
>
> AMB
> "Imre Ament" wrote:
>|||You are right. Thanks for the comment.
AMB
"Tibor Karaszi" wrote:

> I don't think you can alter collation for blob columns.
> One option can be to add a new column with desired collation, update the n
ew column with the value
> of the old column, drop the old column and rename the new column. Column o
rder will not be
> preserved, of course.
> Another option is to create a new table. My guess is that this is what Ent
erprise Manager does.
> Imre: pressing "save change script" will show you how EM does this. I doub
t it is exposed in DMO, as
> the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But
you could have a look, of
> course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
>

Change Coalition for Instance

Hi All
Anyone able to tell me how I can change the coallition type for the server
instance (not the database) without reinstalling?
Thanks
Peterebuildm.exe
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"P" <P@.discussions.microsoft.com> wrote in message
news:9283B05F-6FB5-4698-BF2C-1A7A0E328932@.microsoft.com...
> Hi All
> Anyone able to tell me how I can change the coallition type for the server
> instance (not the database) without reinstalling?
> Thanks
> Pete|||"P" <P@.discussions.microsoft.com> wrote in message
news:9283B05F-6FB5-4698-BF2C-1A7A0E328932@.microsoft.com...
> Hi All
> Anyone able to tell me how I can change the coallition type for the server
> instance (not the database) without reinstalling?
> Thanks
> Pete
AFAIK, no.
When you install, a default collation is chosen (if you do a typical
install) based on certain criteria and then the master, model, msdb, tempdb
and Distribution database are set to that default collation.
You *may* try doing an alter database to change the collation for each
database in the system. If you take a look at the sysservers in master,
there are two collation settings there, but I don't have my charts with me
so I don't know the relationships that they represent.
Hope this helps somewhat.
Rick Sawtell
MCT, MCSD, MCDBA

Change Coalition for Instance

Hi All
Anyone able to tell me how I can change the coallition type for the server
instance (not the database) without reinstalling?
Thanks
Pete
"P" <P@.discussions.microsoft.com> wrote in message
news:9283B05F-6FB5-4698-BF2C-1A7A0E328932@.microsoft.com...
> Hi All
> Anyone able to tell me how I can change the coallition type for the server
> instance (not the database) without reinstalling?
> Thanks
> Pete
AFAIK, no.
When you install, a default collation is chosen (if you do a typical
install) based on certain criteria and then the master, model, msdb, tempdb
and Distribution database are set to that default collation.
You *may* try doing an alter database to change the collation for each
database in the system. If you take a look at the sysservers in master,
there are two collation settings there, but I don't have my charts with me
so I don't know the relationships that they represent.
Hope this helps somewhat.
Rick Sawtell
MCT, MCSD, MCDBA
|||rebuildm.exe
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"P" <P@.discussions.microsoft.com> wrote in message
news:9283B05F-6FB5-4698-BF2C-1A7A0E328932@.microsoft.com...
> Hi All
> Anyone able to tell me how I can change the coallition type for the server
> instance (not the database) without reinstalling?
> Thanks
> Pete

Change Coalition for Instance

Hi All
Anyone able to tell me how I can change the coallition type for the server
instance (not the database) without reinstalling?
Thanks
Pete"P" <P@.discussions.microsoft.com> wrote in message
news:9283B05F-6FB5-4698-BF2C-1A7A0E328932@.microsoft.com...
> Hi All
> Anyone able to tell me how I can change the coallition type for the server
> instance (not the database) without reinstalling?
> Thanks
> Pete
AFAIK, no.
When you install, a default collation is chosen (if you do a typical
install) based on certain criteria and then the master, model, msdb, tempdb
and Distribution database are set to that default collation.
You *may* try doing an alter database to change the collation for each
database in the system. If you take a look at the sysservers in master,
there are two collation settings there, but I don't have my charts with me
so I don't know the relationships that they represent.
Hope this helps somewhat.
Rick Sawtell
MCT, MCSD, MCDBA|||rebuildm.exe
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"P" <P@.discussions.microsoft.com> wrote in message
news:9283B05F-6FB5-4698-BF2C-1A7A0E328932@.microsoft.com...
> Hi All
> Anyone able to tell me how I can change the coallition type for the server
> instance (not the database) without reinstalling?
> Thanks
> Pete