Changing column size in existing databese?

I must increase column (filed) size in existing datebase but without using
Enterprise manager...(Becouse we use MSDE on our clients PCs)
The Filed is part of primary and foreign key constraints...
And every constraint has diferent index number in each database...
for example (PK_something_9e382hjl8), and I don't know how to pick this
value before "drop constraint" command....

Thank you very much...[posted and mailed, please reply in news]

Poted (dario1975@.post.hnet.hr) writes:
> I must increase column (filed) size in existing datebase but without
> using Enterprise manager...(Becouse we use MSDE on our clients PCs) The
> Filed is part of primary and foreign key constraints... And every
> constraint has diferent index number in each database... for example
> (PK_something_9e382hjl8), and I don't know how to pick this value before
> "drop constraint" command....

Moral: name your constraint explicitly according to some standardized
scheme, so you easily can find the names.

This query gives you the name of the PK for a table:

select name from sysobjects
where xtype = 'PK'
and parent_obj = object_id('tablename')

This query lists all referencing foreign key for a table:

select object_name(constid), object_name(fkeyid), col_name(fkeyid, fkey)
from sysforeignkeys
where rkeyid = object_id('depots')
order by constid, fkey

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Changing Column Name on a table

Hi All,
I am trying to change column name on an existing table. I am using SQL
Server 7.

As the table is quite big, it is taking quite long time to do it.
By the way I could change the column name only through the Enterprize
Manager.

Is it possible to change the column name using SQL script?
Why the change of column name will depend on the size of the table?

Thanks for your answer.

-MoklesYou can change a column name using sp_rename.

EXEC sp_rename 'MyTable.OldColumnName', 'NewColumnName', 'COLUMN'

See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mokles" <mokles@.rogers.com> wrote in message
news:wIGdnaGM46YtX_jcRVn-oA@.rogers.com...
> Hi All,
> I am trying to change column name on an existing table. I am using SQL
> Server 7.
> As the table is quite big, it is taking quite long time to do it.
> By the way I could change the column name only through the Enterprize
> Manager.
> Is it possible to change the column name using SQL script?
> Why the change of column name will depend on the size of the table?
> Thanks for your answer.
> -Mokles|||Hi Mokles

Have you tried to rename the field using sp_rename.

Enterprise manager does the job using same kind of scripts but its not
intelligent enough I have observed in various scenarios that sometimes
it accomplishes simple tasks using long approach.

I wonder if there are any indexes on the table ?

Regards
Shehzad

"mokles" <mokles@.rogers.com> wrote in message news:<wIGdnaGM46YtX_jcRVn-oA@.rogers.com>...
> Hi All,
> I am trying to change column name on an existing table. I am using SQL
> Server 7.
> As the table is quite big, it is taking quite long time to do it.
> By the way I could change the column name only through the Enterprize
> Manager.
> Is it possible to change the column name using SQL script?
> Why the change of column name will depend on the size of the table?
> Thanks for your answer.
> -Mokles|||I could do it using Sp_Rename.
Enterprize Manager was using complicated method to do the simple task
Anyway thanks for the help

-Mokles

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:6Qk9d.2605$Al3.1466@.newssvr30.news.prodigy.co m...
> You can change a column name using sp_rename.
> EXEC sp_rename 'MyTable.OldColumnName', 'NewColumnName', 'COLUMN'
> See the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "mokles" <mokles@.rogers.com> wrote in message
> news:wIGdnaGM46YtX_jcRVn-oA@.rogers.com...
> > Hi All,
> > I am trying to change column name on an existing table. I am using SQL
> > Server 7.
> > As the table is quite big, it is taking quite long time to do it.
> > By the way I could change the column name only through the Enterprize
> > Manager.
> > Is it possible to change the column name using SQL script?
> > Why the change of column name will depend on the size of the table?
> > Thanks for your answer.
> > -Mokles|||Today I tried to do it with sp_Rename and it worked well and fast.

Previously I was using profiler to see what the Enterprize Manager was
doing. And really it was using complicated method to change the column name.

Thanks all for the help

-Mokles

"Shehzad Shabbir" <shehzad@.internav.com> wrote in message
news:503e40d5.0410071952.643d25ee@.posting.google.c om...
> Hi Mokles
> Have you tried to rename the field using sp_rename.
> Enterprise manager does the job using same kind of scripts but its not
> intelligent enough I have observed in various scenarios that sometimes
> it accomplishes simple tasks using long approach.
> I wonder if there are any indexes on the table ?
> Regards
> Shehzad
>
> "mokles" <mokles@.rogers.com> wrote in message
news:<wIGdnaGM46YtX_jcRVn-oA@.rogers.com>...
> > Hi All,
> > I am trying to change column name on an existing table. I am using SQL
> > Server 7.
> > As the table is quite big, it is taking quite long time to do it.
> > By the way I could change the column name only through the Enterprize
> > Manager.
> > Is it possible to change the column name using SQL script?
> > Why the change of column name will depend on the size of the table?
> > Thanks for your answer.
> > -Mokles

Changing column data type to Unicode data type

Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?

Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.

Yes you need a project and a package. To get started with SSIS projects and packages, you should run through the SSIS tutorial. See http://msdn2.microsoft.com/en-us/library/ms170419(SQL.90).aspx - the steps of the tutorial take you through the project and package creation and into working with data flow

When you need to convert data, this BOL entry gives the steps for using the Data Conversion Component. http://msdn2.microsoft.com/en-us/library/ms140321.aspx

Donald

|||

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

|||

J. Lewis wrote:

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

J,

There appears to be some confusion between SQL Server Database Engine and SQL Server Integration Services.

Tables are stored in SQL Server database engine and can be manipulated using ALTER TABLE.

CAST and CONVERT are T-SQL fuctions. T-SQL is a programming language used to manipulate the DATA that is stored in tables (note the distinction here between ALTER TABLE which only operates on the table itself).

DT_WSTR is a data type within SQL Server Integration Services. It is NOT a data type within SQL Server Database Engine. Hence, CAST and CONVERT will not work on columns of type DT_WSTR.

With all that in mind, can you explain again exactly what it is you require to be able to do?

-Jamie

|||

Thank you greatly -- your explanation is really clear and very helpful. One does not always see the "big picture", when just looking at individual BOL pages!

What trying to do is is set-up to use the Term Extraction Transformation which as we understand it requires use of the DT_WSTR or DT_NTEXT data types. This is a fairly limited, focused job we were trying to complete in SQL Server 200 5. It had sadly, frankly not fully hit us that there were different data types across various components of SQL Server.

As we have a bit "in /out" job to do here, we are now at least hoping to find a simple, but reasonably complete, example script to set up a project/package to read in an input file, convert a data type in a column, and then run a Term Extraction.

Thank you for your help.

|||

Term Extraction Transform is part of SQL Server Integration Services so you are in the right place.

It sounds like you are a beginner so I would recommend you first watch this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US and this: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273477&EventCategory=5&culture=en-US&CountryCode=US to introduce yourself to the product.

As for term extraction, I haven't seen much material although I vaguely recall a webcast that Donald Farmer (further up this thread) did in which it was mentioned. I can't find that webcast though. Hopefully Donald will reply and let you know.

-Jamie

sql

Changing an existing Crystal 8.0 report to use stored proc

Hello,
I have approximately 34 Crystal 8 reports that currently run off embedded sql statements. I need to change these to run off stored procedures, and am not sure how to convert them.

The returned columns are the same, although the queries have changed slightly.

If I attempt to directly change the SQL through "Show SQL Query" it doesn't take. I also tried "Set Location" which only replaces the table name with the sp name, and also fails.

If I build a new report from scratch, it works, but this is very time consuming and error prone.

Can someone tell me how to change the existing reports to use the sp's?

Thanks,
JamesShow SQL Query wont' allow u to change existing sql.

Report object has SQLQueryString String type. Gets or sets SQL query string. Read/Write Can be written only when formatting idle which according to crystal allows you to change sql at runtime. Maybe this would help. Change this to your procedure name systematically.

Thanks

Changing all existing tables collations in one step

If you're wanting to change the whole collation of your database then
(in SQL 2000) the best way is to script out the db, recreate it with
the new collation as default, then DTS your data across. I think that
if you changed it table by table then every new table you create will
then have to be changed as well.
Hopefully there's something in SQL 2005 that allows a complete db
collation change, as I expect most people have run into the issue of a
database being restored from a backup from a server that used a
different collation (I often found for some reason the collation got
set to LATIN1_GENERAL_CPL_CI_AS on peoples default install of SQL for
no apparent reason).Thank you.
"Will" wrote:

> If you're wanting to change the whole collation of your database then
> (in SQL 2000) the best way is to script out the db, recreate it with
> the new collation as default, then DTS your data across. I think that
> if you changed it table by table then every new table you create will
> then have to be changed as well.
>
> Hopefully there's something in SQL 2005 that allows a complete db
> collation change, as I expect most people have run into the issue of a
> database being restored from a backup from a server that used a
> different collation (I often found for some reason the collation got
> set to LATIN1_GENERAL_CPL_CI_AS on peoples default install of SQL for
> no apparent reason).
>

Changing all existing tables collations in one step

I can change table collation first table column by column
then second table and so on (Alter table table_name
alter column column_name COLLATE < collation_name> ).
How can I change all existing tables collations in one step ?
Where can I find some program in Transact-SQL or some procedure to solve
this problem ?Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:

> I can change table collation first table column by column
> then second table and so on (Alter table table_name
> alter column column_name COLLATE < collation_name> ).
> How can I change all existing tables collations in one step ?
> Where can I find some program in Transact-SQL or some procedure to solve
> this problem ?
You can change the collation only column by column, not in one step.
So you can read the info you need:
--
select so.name as TableName, sc.name as ColName, sc.collation from
syscolumns sc left join sysobjects so on so.id = sc.id
where IsNull(sc.name,'') > '' and so.xtype = 'U'
and sc.collation is not NULL
--
create a cursor, loop through this query and do the ALTER TABLE where the
collation is not like you need.
bye, Helmut|||Thank you.
"helmut woess" wrote:

> Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:
>
> You can change the collation only column by column, not in one step.
> So you can read the info you need:
> --
> select so.name as TableName, sc.name as ColName, sc.collation from
> syscolumns sc left join sysobjects so on so.id = sc.id
> where IsNull(sc.name,'') > '' and so.xtype = 'U'
> and sc.collation is not NULL
> --
> create a cursor, loop through this query and do the ALTER TABLE where the
> collation is not like you need.
> bye, Helmut
>

Changing a cubes DSV ?

Is it possible to change the DSV used by an existing cube in the designer?

Alternativeliy I thought about scipting a new cube and change the:

<DataSourceViewID>dsvname</DataSourceViewID>

The only way I found for changing the cube’s DSV in the UI was through recreating the cube. But you can certainly run a cube alter script that changes all instances of the <DataSourceViewID> to the DSV ID of your choice. Tried it using Adventure Works sample project and worked fine.

Hope this helps,

Artur

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 "text filegroup" property of an existing table

How can I change the property "text filegroup" of an existing Table?

Thanks
Markus

In enterprise manager, right click the table and choose "design table", then click the "table and index properties" icon (2nd from left, next to save) and then set the Text Filegroup option as appropriate.|||I'm using SQL Server 2005|||right click on the table and select properties. you have the option to change it there.|||at the properties tab, General, everything is write protected (SQL Server Management Studio)|||TextFilegroup is set at the creation of the table via the TextImage_On {fg}. In sql2k, when you modify the TextFilegroup property, the system actually:
1. create a new table with textimage_on
2. insert into the new table with the old table data
3. drop the old table
4. rename the new table to old

The above functionality still exists and works the same in sql2k5. Right click on the table; select "modify"; press F4 to bring up the properties pane; change the TextFilegroup as desired.

change sql server from CS to Case Insensitive

Hi,
I setup a SQL server with case sensitive. How can I change
it into case insensitive?
If I use rebuildm, would it destroy my existing db?
When I ran rebuildm.exe, I got error message "Rebuild
Master failed with error -1:". Any hint?
Thank you for your help.|
| Hi,
| I setup a SQL server with case sensitive. How can I change
| it into case insensitive?
--
I will assume that you are using SQL Server 2000. In this version, sort
order can be different up to column level.
To change the server-wide sort order, you need to perform the following:
1. Use rebuildm to rebuild the master database and specify the desired case
insensitive sort order.
2. Use Alter database to specify the sort order per database.
One concern when moving from case sensitive to case sensitive is you might
encounter duplicate object names. For instance, when you move from case
sensitive to case insensitive sort order a table named "tAbLe1" will be the
same as a table named "TaBlE1".
Hope this helps,
--
Eric Cárdenas
SQL Server support|||> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
1.5 Restore the databases.
> 2. Use Alter database to specify the sort order per database.
3. Use ALTER TABLE ... ALTER COLUMN to change collation for all existing
columns. And drop constraints and indexes to be able to do this.
Changing collation is (still) not a trivial task, unfortunately.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:AXhdme7vDHA.2772@.cpmsftngxa07.phx.gbl...
> |
> | Hi,
> | I setup a SQL server with case sensitive. How can I change
> | it into case insensitive?
> --
> I will assume that you are using SQL Server 2000. In this version, sort
> order can be different up to column level.
> To change the server-wide sort order, you need to perform the following:
> 1. Use rebuildm to rebuild the master database and specify the desired
case
> insensitive sort order.
> 2. Use Alter database to specify the sort order per database.
> One concern when moving from case sensitive to case sensitive is you might
> encounter duplicate object names. For instance, when you move from case
> sensitive to case insensitive sort order a table named "tAbLe1" will be
the
> same as a table named "TaBlE1".
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>

Change owner of User Defined Data Types

Please let me know if there is a method for changing the owner of existing User Defined Data Types. I cannot drop and recreate them as the datatypes are in use.
Thanks.Try, sp_changeobjectowner.
Refer to BOL for more information.|||sp_changeobjectowner cannot be used to change the owner of UDTs as it only works with tables, views, user-defined functions and stored procedures.|||Then only way is drop and recreate.|||As I stated in my original post, it is not possible to drop them as they are in use.|||Use specified SP to change owner for that table, which would be applied by default. Apart from this I have no other idea.