Changing columns name in a temp table

Dear All,

I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.

trying to use:

exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'

I get:

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @.objname is ambiguous or the claimed @.objtype (COLUMN) is wrong.

There is no mistype, the table name and column name are correct.

Can temp table's column names not be altered?

If yes, how?

Thanks in advance!Hi gorgenyi

Let's cover the obvious stuff first of all to get an idea of what you are doing and why.
1) Why not define your temp table with the correct column names in the first place?
2) Why not alias your column names in the result set you create from the temp table?|||ok, here it goes.

I created a report with a stored procedure which displays resource allocation to projects in the BD pipeline week by week for the next 10 weeks ahead.

the end reult looks like this:

Project name, w1, w2, w3, w4, w5, w6 etc...
Prj A 1 2 2 1
Prj B 1 2 2 2.5 2 2
Prj C .5 1 1 1.5 1
...

I'm using w1, w2, w3 for the weeks ahead during the stored procedure for multiple joins, however I'd like to change them to the always relevant week numbers programatically in the follwoing format '2007-17', '2007-18', '2007-19' ... etc at the end so I can publish these as the datagrid's header.

Did I explain clearly? What would you recomend? Thanks for your help on this.|||You are making the all too common mistake of trying to use SQL and/or Query Analyzer as a reporting environment. It is not designed for this.
What you are trying to do is modify the way the data is displayed, and this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
Dynamic column names are BAD, because then whatever reporting tool you use will never know what output schema to expect.|||this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.Somehow you conspired to miss with all those I think. I suspect this is just some .NET front end.

You can (I think) dynamically set up your column headers in a datagrid but I am afraid I cannot remember how.

blindman is right though - this is best handled in your front end rather than SQL.|||...or .Net or ASP.|||It just seemed very simple, and must say I do not have experience of the reporting tools. Here is what I did:

temp table to get next 10 weeks ids
temp table to get active BD items and the 10 columns with 0 values
then a double loop where I update each cell with a resource number to the appropriate BD item and the appropriate week number.

Dispay the data in datagrid, with headings for the weeks as Week 1, Week 2, etc. while adding the sums up / week in ASP.NET and do various other things like colouring background etc.

But I'd like to get the actually internal week reference displayed on the grid, which of course chnage weekly, so want to do it dynamicaly. But yes, point taken on ASP.NET won't know what col name they will be.

And yes, this is ASP.NET front end.|||Some ideas from the web. Maybe consider posting in an ASP.NET site if you can't follow them. You defo do not want to do this at the database.
http://www.thescripts.com/forum/thread320779.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_20976549.html

Changing Column size/type with Derived Column

I have a number of date columns that are parsed as DT_WSTR (6) and I have written a Derived Column converting them into DT_DATE via this (found on the forums) type expression:
(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))
But I really want to replace the current column, not create a new one. If I use "replace" the data is forced to be a DT_WSTR (6), and I get a truncation error at run-time.
Simeon
Simeon,
You're stuck with it I'm afraid. You can't change the type of a column in the derived column component. Its not a ahrdship to add it as a new column though, jsut don't use the existing one that's all!

-Jamie|||Could you change the source to return a larger column. This would solve your problem.|||Cheers Jamie,
That's what I thought, I was just hoping there was a way to keep it "clean".
On a related thought, I am finding that each time I alter any component near the top of a data flow, I end up needing to delete and re-add most the down stream components due to fields mismatching. Is this why people appear to be building there packages via code?
Simeon.
|||This does depend on the component, some just need double clicking on and the meta data should correct it self, others require you to select the mapped columns. The latter is generally when you change the names of components and inputs.

You shouldn't have to delete components though, I find that surprising.|||

SimonSa wrote:

Could you change the source to return a larger column. This would solve your problem.

That might work, but the derived column sets the type to DT_WSTR, so I'm not sure that putting a entry that is cast to DT_DATE would not upset it also.
|||

I was finding this while I was developing my source component. I had it wired to Raw Files (then later Trash Destinations) with Data Viewers to inspect the data. Running the package (after reloading BI) would give errors, so I found it easier to delete the source and it four outputs, and re-wire.
But going forward I'll try double clicking, and checking the mappings.

Simeon
|||Do you need to cast it to a date? If you do then you will have to have a new column, and the derived column is the best solution|||I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.|||

SimonSa wrote:

I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.

It was. I was slowly adding support for different data types, then adding support for foreign keys. The source component is like a flat file parser, but it handles files that have different rows (with different columns) that have relationships based on order. So really n tables with the foreign keys implied by what a row follows.
I was (for simplicity) developing support incrementally, with the relations setup by a function. The next step is to put that information into a configuration file.
|||A column is identified by it's lineage Id. Deleteing a column and adding it back, even with the same name and same data type properties will cause it to change lineage Id. This means downstream components that have referenced that column (by lineage Id) are now invalid. Opening the UI should bring up the mapping dialog, and one of the options is Map by Name. This normally solves most issues.

A well behaved component will not recreate the output buffer columns each time, but rather detect invalid columns, and remove, add new columns if required, and fix any columns in can detect on both sides, or leave alone matching columns. This can be a pain, as it is lots more code, but try the samples such as the ADO Source for some good template code.

Changing column order in a table

This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!Dan,

I hope this doesn't fall into your 5 commandments ;-) If by any reason the
order of columns is important to you, you can use a view. I do understand
that I'm not supposed to ask you why you want to do this, but why!!? :-) If
you tell us your original problem then you might be lucky enough to find
somebody that can give you some other alternatives. I'm sure you have a
reason for this. Without revealing much information about what you want to
do it's really hard to find the solution.

Good luck,
Shervin

"Dan Newton" <dnewton@.scriptsave.com> wrote in message
news:280c01a4.0312091312.2f52e02@.posting.google.co m...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!|||dnewton@.scriptsave.com (Dan Newton) wrote in message news:<280c01a4.0312091312.2f52e02@.posting.google.com>...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!

You will have to create a new table, copy the data, then rename it.
This may be tedious, but it will work and it is supported. The only
'short cut' would be to attempt to hack syscolumns, but since many
columns are 'internal use only' and direct updates to system tables
are not supported, you have no guarantee of success, and no support
from Microsoft if you have problems (which may not appear at first).

Or as Shervin suggested, if you can provide more information on what
your issue is, there may be an alternative solution.

Simon

Changing column name

Hi all,

I have a table that has integrity constraints in place and it is populated with the data. I need to rename one of the columns in the table. I am hesitant to use sp_rename procedure because when I used it before I get a warning message that says "changing any part of an object name could break scriptd and stored procedure." how big of a problem is it? Is there any other way to do it without hearting anything? Thanks.All that means is that you will have to update references to the column name in any code that has already been created.sql

Changing column data type constraint

I am trying to change the data type of two columns in a SQL database.The columns were created using the“smallint” data type.Since they are used to identify document and document sections rather than mathematical functions, I think they should have been constrained to a text data type such as nvarchar.When I try to concatenate with a query the result is a mathematical addition of the numbers, butI am trying to combine the two numbers as a string with a "-" between them.

I have not had any success in changing the data type of the two columns.Apparently, the original database was set up for full text search and won’t let me change the column data type.I keep getting this error message:

'Full Documents' table

- Unable to modify table.

Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.

The statement has been terminated.

I ran a query to increase the timeout period (which succeeded in increasing the timeout but still got the same error message when trying to change the column data type). My research suggests that this is really a matter of the column data constraints related to the full text search issue.

Any suggestions on how to change the data type of these columns?

Since this is a general SQL question, I'm moving it to a more general forum where you'll get a better answer.

Mike

Changing column data type

I want to change a column's data type from bit to int. There are data in the table already. I'm wondering if it is save/correct way to issue the following command to change the data type for that column.

ALTER TABLE database_table
ALTER COLUMN my_bit_columnINT;

Thanks.

It should work. The data will change to 0 (if it was False) or 1 (if it was True) after you change your column data type.

Changing COLLATION on a table

Hi
Can any of you help with the syntax for changing the COLLATION on all
columns in a table. I have no problem in changing the collation for the
database itself, but that doesn't affect existing columns in the database.
I can change it for one column at the time, but that's a bit cumbersome.
I've tried different variations of ALTER TABLE but it gives me a syntax
error unless I include the column name I want to change.
Regards
SteenYou need to do the columns individually. A table on its own does not have a
collation.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>|||COLLATION is defined on columns, not tables, so you will have to change one
column at a time.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>|||By the way, you can quasi-automate this, e.g.
DECLARE @.tablename VARCHAR(64)
SET @.tablename = 'something'
SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
+ ' COLLATE <new collation>'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@.tablename
AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
You will still have to deal with constraints, computed columns etc.
yourself...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>|||Aaron,
I think you may have some problems with TEXT/NTEXT columns, as you cannot
use ALTER TABLE on them.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...|||> I think you may have some problems with TEXT/NTEXT columns, as you cannot
> use ALTER TABLE on them.
Good catch... can you tell I reused an automation script from a completely
different task? ;-)|||Also, the COLLATE and NULL clauses were in the wrong order...
Here's a fixed version:
DECLARE @.tablename VARCHAR(64)
SET @.tablename = 'something'
SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
+ ' COLLATE <new collation> '
+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@.tablename
AND RIGHT(DATA_TYPE,4) = 'char'
... and now to really automate it, I leave it up to you to plug it into
sp_execresultset :)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||Thanks guys...I'll give it a try.
I was aware that the collation was specific for the columns, but I thought
there was an "out of the box" funktion that could change it for all columns
in a table.
Regards
Steen
Adam Machanic wrote:
> Also, the COLLATE and NULL clauses were in the wrong order...
> Here's a fixed version:
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + ' COLLATE <new collation> '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) = 'char'
>
> ... and now to really automate it, I leave it up to you to plug it
> into sp_execresultset :)
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
>> By the way, you can quasi-automate this, e.g.
>>
>>
>> DECLARE @.tablename VARCHAR(64)
>> SET @.tablename = 'something'
>> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
>> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
>> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
>> + ' COLLATE <new collation>'
>> FROM INFORMATION_SCHEMA.COLUMNS
>> WHERE TABLE_NAME=@.tablename
>> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>>
>> You will still have to deal with constraints, computed columns etc.
>> yourself...
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)sql

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!
Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!
|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>
sql

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it
- fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates
that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it
- fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2M
SFTNGP03.phx.gbl...
>

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>>I need to change the collation for my database from SQL_Latin1_General_CP1_CI_ASto
>>SQL_Latin1_General_CP1_CI_AI
>> Is the statement "ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AI" enough or
>> should I change every existing fields (how to do it)?
>> Any ideas? Any precautions to take?
>> Thanks!
>

Changing a table will break a view?

The other day I updated a table to add a few columns using Enterprise
Manager. When I closed the table, it said it would update several other
database items as a result (very good, I thought).
However, subsequent to this change, even the most basic view is broken, e.g.
,
CREATE VIEW [v_table] AS select * from [table]
Recreating (or altering) the view clear up the problem. But, I was surprised
!
What is the canonical way to avoid views getting out of sync with tables? Is
there another way to modify table structure that will automatically recompil
e
other database objects?
David> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
The best practice is to specify a column list rather than '*'. You can
execute sp_refreshview to refresh view meta data after changes to underlying
tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
> e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
> surprised!
> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
> David
>|||Create your views WITH SCHEMABINDING to avoid this problem. That will
"bind" the schema -- meaning that none of the underlying tables will be able
to change unless you drop the view.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
surprised!
> What is the canonical way to avoid views getting out of sync with tables?
Is
> there another way to modify table structure that will automatically
recompile
> other database objects?
> David
>|||And you can automate this refresh using something like:
-- Cycle through all view in the current database and refresh their metadata
-- to take into account any changes to the underlying objects.
DECLARE @.ViewName sysname
DECLARE views_to_refresh CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
OPEN views_to_refresh
FETCH NEXT FROM views_to_refresh INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
print 'Refreshing ' + @.ViewName
exec sp_refreshview @.ViewName
FETCH NEXT FROM views_to_refresh INTO @.ViewName
END
CLOSE views_to_refresh
DEALLOCATE views_to_refresh
Thanks!
David
"Dan Guzman" wrote:

> The best practice is to specify a column list rather than '*'. You can
> execute sp_refreshview to refresh view meta data after changes to underlyi
ng
> tables.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in messag
e
> news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
>
>sql

Changing a Columns Identity Properties

Hi,
How do I change the identity properties of an existing column using
trans-sql?
Thanks
----
--
----
--Razak wrote:
> Hi,
> How do I change the identity properties of an existing column using
> trans-sql?
> Thanks
> ----
--
> ----[/
color]
What do you mean exactly? The seed? See DBCC CHECKIDENT.
David Gugick
Imceda Software
www.imceda.com|||I meant changing the property of the column to become an IDENTITY column.
This is because the column used to be an identity column, but someone has
messed up with the db and all the identity columns inside all tables has
lost their identity property. The columns are still there. Because of this,
every "INSERT ..." sql scripts to add new records generate error.
Therefore, I need to set back the IDENTITY prop for the id columns in every
table, but I need to do it using T-SQL since the db is in remote sql server.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ea8pUeiGFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Razak wrote:
> What do you mean exactly? The seed? See DBCC CHECKIDENT.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> I meant changing the property of the column to become an IDENTITY
> column. This is because the column used to be an identity column, but
> someone has messed up with the db and all the identity columns inside
> all tables has lost their identity property. The columns are still
> there. Because of this, every "INSERT ..." sql scripts to add new
> records generate error.
> Therefore, I need to set back the IDENTITY prop for the id columns in
> every table, but I need to do it using T-SQL since the db is in
> remote sql server.
There is no supported SQL to add an identity attribute to an existing
column. Creating a new table and inserting the data between them is the
easiest way (in most cases).
You could use SQL EM, which will try and automate most of the this for
you, probably by creating a new table and then dropping the old one.
See this article for more information:
http://www.windowsitpro.com/Article...2080/22080.html
David Gugick
Imceda Software
www.imceda.com|||Thanks for your reply.. It seems like I will have to there and fix it
locally on the sever.
Thanks again
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oj8pn3jGFHA.2616@.tk2msftngp13.phx.gbl...
> Razak wrote:
> There is no supported SQL to add an identity attribute to an existing
> column. Creating a new table and inserting the data between them is the
> easiest way (in most cases).
> You could use SQL EM, which will try and automate most of the this for
> you, probably by creating a new table and then dropping the old one.
> See this article for more information:
> http://www.windowsitpro.com/Article...2080/22080.html
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> Thanks for your reply.. It seems like I will have to there and fix it
> locally on the sever.
You don't need to be on the server to fix it, unless I'm
misunderstanding what you're saying. You can connect through any query
tool and execute a custom script to make the change or possibly use SQL
EM (remotely is needed) to make the change. I would encourage you to
test all changes on a dev server first.
David Gugick
Imceda Software
www.imceda.com|||Creating a new copy of the table will break its relationship with other
tables since I need to maintain the values inside the supposedly id column.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OasMiRoGFHA.544@.TK2MSFTNGP12.phx.gbl...
> Razak wrote:
> You don't need to be on the server to fix it, unless I'm misunderstanding
> what you're saying. You can connect through any query tool and execute a
> custom script to make the change or possibly use SQL EM (remotely is
> needed) to make the change. I would encourage you to test all changes on a
> dev server first.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Razak wrote:
> Creating a new copy of the table will break its relationship with
> other tables since I need to maintain the values inside the
> supposedly id column.
But that's the only solution to your problem AFAIK. If you try and make
the change using SQL EM on a dev server, you can run Profiler at the
same time and capture all the SQL SQLEM is uing to make the change. It
may shed some light on how to script this out yourself. I think the
article I referenced goes into this quite a bit.
David Gugick
Imceda Software
www.imceda.com

Change the order of columns

By mistake I misplaced the order of my primary keys in some tables and I just dont find the way to move them to the top of each table. This must be something easy. and its ugly to see primary keys on the foot.If you are using the management studio, right click on the table in question and select "Modify" from the context menu. In the work area click the area to the left of the column name you want to move and drag it up or down to its new location.
|||

Thanks, very easy. The problem was that I always make my tables in the Diagram view. and there is no posibility to do that. Only in the table view.

So easy answer.

change table orientation

I'm looking for a stored procedure what will change the orientation of a
table, so the columns will become the rows and the rows will become the
columns, for example:
Original Table:
StateName StateLongNames StateNumber
========= ============== ===========
CA California 10
FL Florida 20
TX Texas 30
The resulting table should be:
Col1 Col2 Col3 Col4
============== ========== =========== ==========
StateName CA FL TX
StateLongNames California Florida Texas
StateNumber 10 20 30
Note 1: This is not the same as pivot or crosstab tables.
Note 2: No agregated functions are necessary.
Note 3: It has to work on SQL Server 2000.
Thank you,Check this link
http://www.sqlteam.com/item.asp?ItemID=2955
Hope this helps.
--
"wheresaldo" wrote:

> I'm looking for a stored procedure what will change the orientation of a
> table, so the columns will become the rows and the rows will become the
> columns, for example:
> Original Table:
> StateName StateLongNames StateNumber
> ========= ============== ===========
> CA California 10
> FL Florida 20
> TX Texas 30
> The resulting table should be:
> Col1 Col2 Col3 Col4
> ============== ========== =========== ==========
> StateName CA FL TX
> StateLongNames California Florida Texas
> StateNumber 10 20 30
> Note 1: This is not the same as pivot or crosstab tables.
> Note 2: No agregated functions are necessary.
> Note 3: It has to work on SQL Server 2000.
> Thank you,
>|||hi... I saw this article a couple of days ago, it does not what at need, as
it creates a pivot table. what I need is much more simpler, just flit the
orientation of the table and all the data. This is NOT the same as Pivot,
Crosstab, or Transpond procedures, but easily .
Thank you
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||A friend told me to do this, but it's outside my SQL knowledge:
1. Select count(*) for old_table. This will tell you how many columns to
create. Put this count into a variable (or user a cursor)
2. Build the string: "Create table new_table (col" + i + " varchar(50),
col" + ..... And execute it to create your table
3. Select the table structure to get the column names, use a cursor
3.a for each $col_name, Select $col_name, key from old_table order by key,
use cursor2
3.b build the string: "Insert into new_table values (" + $col_name + ", "
+ cursor2_value + ", " + cursor2_value, .... and execute.
3.c repeat loops
Does this make any sense to anyone?
"Omnibuzz" wrote:
> Check this link
> http://www.sqlteam.com/item.asp?ItemID=2955
> Hope this helps.
> --
>
>
> "wheresaldo" wrote:
>|||You can do this with CASE expressions. See the following article for an
example. A query always returns a fixed number of columns so you will
always need to construct a query to match the appropriate number of
rows, either by hand or using dynamic SQL.
http://support.microsoft.com/defaul...b;EN-US;q175574
Hopefully you realize that this is generally a very silly to even
attempt in SQL.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I tried to apply the code you mentioned for the table below and it does not
seem to give the right result. Here is an example table:
CREATE TABLE #States
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO #States VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO #States VALUES ('OH','Ohio',2, 2.25)
INSERT INTO #States VALUES ('CA','California',3, 4.5)
INSERT INTO #States VALUES ('FL','Florida',1, 13.40)
INSERT INTO #States VALUES ('FL','Florida',3, 4.50)
As you can see, there is no unique id to use.
Here is how the table looks like:
StateCode StateName StateNumber StateCost
WI Wisconsin 1 45.11
OH Ohio 2 2.25
CA California 3 4.50
FL Florida 1 13.40
FL Florida 3 4.50
and I need it to look rotated or flipped:
Col1 Col2 Col3 Col3 Col4
WI OH CA FL FL
Wisconsin Ohio California Florida Florida
1 2 3 1 3
45.11 2.25 4.50 13.40 4.50
as you can see, the columns of the resulting table will have to receive
different data types, so I was thinking they can be all vchar(50). Also, as
the number of rows from the original table might vary, the resulting table
might have different amount of columns.
I could do the rotation or flip on the UI or front-end, but the reporting
tool I'm using expects a table... this is why I'm trying to do this on a
Stored Procedure.
I'm surpriced that I have not been able to find this on the web, I think
many people might have the same need.
"David Portas" wrote:

> You can do this with CASE expressions. See the following article for an
> example. A query always returns a fixed number of columns so you will
> always need to construct a query to match the appropriate number of
> rows, either by hand or using dynamic SQL.
> http://support.microsoft.com/defaul...b;EN-US;q175574
> Hopefully you realize that this is generally a very silly to even
> attempt in SQL.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||wheresaldo wrote:
> I tried to apply the code you mentioned for the table below and it does no
t
> seem to give the right result. Here is an example table:
>
I suspect that the reason you find this difficult is that your existing
data doesn't have enough information to solvet he problem. What is the
key of your table? Every table needs a key but you haven't specified
one.
Now, in your rotated output you have put "WI" in column 1 and "OH" in
column 2. But where did you get the idea that "WI" came first and "OH"
came second? That information (1st, 2nd, etc) isn't in your table at
all! So you can't possibly write a generic query to return that result.
Lastly, what is the key in the result you want to ouput? You originally
asked for the column names in col1 but those don't exist in the data
either so you'll have to supply them in your query. Without a key you
may have a hard time constructing any useful information from the
output.
The first example in the link I posted shows how you can do it with
subqueries. By implication you need to supply the missing information
as part of those subqueries.

> I could do the rotation or flip on the UI or front-end, but the reporting
> tool I'm using expects a table... this is why I'm trying to do this on a
> Stored Procedure.
For sure you'll need to supply a tabular result set to the reporting
tool. But every reporting tool I know of will construct this kind of
pivot table from the base data - provided of course that you supply
enough information to complete the task.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for you input. Here is the code to rotate (or change the orientation
)
of a table. I'm not sure if it's the most effective and well-written code,
but it does exactly what I need it to do. Hope it can help someone else.
Feedback is welcome:
-- ########################################
######
-- Drop tables if already created.
-- Cannot use temp tables, becuase cannot
-- read structure.
-- ########################################
######
DROP TABLE source
DROP TABLE result
-- ########################################
######
-- create fake source table
-- ########################################
######
CREATE TABLE source
(
StateCode char(2),
StateName char(10),
StateNumber int,
StateCost decimal(18, 2)
)
INSERT INTO source VALUES ('WI','Wisconsin',1, 45.11)
INSERT INTO source VALUES ('OH','Ohio',2, 2.25)
INSERT INTO source VALUES ('CA','California',3, 4.5)
INSERT INTO source VALUES ('FL','Florida',1, 13.40)
INSERT INTO source VALUES ('FL','Florida',3, 4.50)
-- ########################################
######
-- Create result table.
-- The result table needs to have the same
-- number of columns as the source table
-- has rows, we do this dynamically.
-- Because a columns in the result table might
-- be pf different data types, each
-- column in the result table is a nvarchar(50).
-- ########################################
######
declare @.counter int
declare @.sql_create_table nvarchar(4000)
declare @.source_total_rows int
select @.source_total_rows=count(*) from source
set @.sql_create_table = 'CREATE TABLE result (columnheader varchar(100)'
set @.counter = 0
while @.counter < @.source_total_rows
begin
set @.counter = @.counter + 1
set @.sql_create_table = @.sql_create_table + ',col' + cast(@.counter as
char(1)) + ' varchar(50)'
end
set @.sql_create_table = @.sql_create_table + ')'
exec (@.sql_create_table)
-- ########################################
######
-- First Loop through the number of column from
-- the source table, we need this to get the
-- column names.
-- Second Loop reads each row from the source,
-- for one column, and then create an insert
-- statement to insert in the result table.
-- ########################################
######
declare @.sql_create_inserts nvarchar(4000)
declare @.source_column_name nvarchar(50)
declare @.source_number_of_columns int
declare @.temp_column_hold nvarchar(50)
SELECT @.source_number_of_columns = count(*)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'source'
set @.counter = 0
while @.counter < @.source_number_of_columns
begin
set @.counter = @.counter + 1
--#####################################
set @.source_column_name = COL_NAME(OBJECT_ID('source'), @.counter)
exec('declare cur cursor for select '+@.source_column_name+' from source')
open cur
fetch next from cur into @.temp_column_hold
set @.sql_create_inserts = 'insert into result values (''' +
@.source_column_name + ''''
while @.@.FETCH_STATUS = 0
begin
set @.sql_create_inserts = @.sql_create_inserts + ',''' +
cast(@.temp_column_hold As varchar(50)) + ''''
fetch next from cur into @.temp_column_hold
end
set @.sql_create_inserts = @.sql_create_inserts + ')'
exec(@.sql_create_inserts)
close cur
deallocate cur
--#####################################
end
select * from result
"David Portas" wrote:

> wheresaldo wrote:
> I suspect that the reason you find this difficult is that your existing
> data doesn't have enough information to solvet he problem. What is the
> key of your table? Every table needs a key but you haven't specified
> one.
> Now, in your rotated output you have put "WI" in column 1 and "OH" in
> column 2. But where did you get the idea that "WI" came first and "OH"
> came second? That information (1st, 2nd, etc) isn't in your table at
> all! So you can't possibly write a generic query to return that result.
> Lastly, what is the key in the result you want to ouput? You originally
> asked for the column names in col1 but those don't exist in the data
> either so you'll have to supply them in your query. Without a key you
> may have a hard time constructing any useful information from the
> output.
> The first example in the link I posted shows how you can do it with
> subqueries. By implication you need to supply the missing information
> as part of those subqueries.
>
> For sure you'll need to supply a tabular result set to the reporting
> tool. But every reporting tool I know of will construct this kind of
> pivot table from the base data - provided of course that you supply
> enough information to complete the task.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I would also take care of the limitations:
- number of columns cannot exceed 1000
- row must fit on a page|||wheresaldo wrote:
> Thanks for you input. Here is the code to rotate (or change the orientati
on)
> of a table. I'm not sure if it's the most effective and well-written code
,
> but it does exactly what I need it to do. Hope it can help someone else.
> Feedback is welcome:
>
This bit is dangerous:

> exec('declare cur cursor for select '+@.source_column_name+' from source')
>
It retrieves the values in an undefined order. In itself that wouldn't
be too bad but because you go on to do the same for each column there
is absolutely no guarantee that the data for columns in the result will
line up with what was in each row in the source. What you should do is
add ORDER BY <some key>... but you still don't have a key in the source
table.
If you did have a key then you wouldn't need nested loops and cursors.
You could use a single query instead, which is generally preferable to
a cursor solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Change structure in Db

Hi.
How I can change a structure(add/drop columns or add st.
procedures) in databases which are involved in Merge
Replication process? I'm using SQL Server 2000.
Thank you.
use sp_repladdcolumn and sp_repldropcolumn

change row size

Hi everybody!!
I have a table with some nvarchar columns. When I try to put the size I can't to put it greater than 4000.
How can i change the row size?
Thanks!!That's the limit for that data type. Internally SQL Server uses 2*n bytes to store it. If you want to go up to 8000 bytes and don't need to use unicode character you should switch to varchar.