changing the types of particular columns in particular table in the database

hi,
i have more than 300 tables in the database. Out of that tables some table has column whose type is "char".I want to change only those tables' column to the type "nvarchar".
Is it possible to make the modification all at a once?
since i am changing each and every table , this takes a lots of time.

thanks in advance,
by
singam

Hi,

first of all there is no built in function for this. YOu have to do that one by one in the script, there is sure a chance to do this automagically but I am no fan of a full automatic procedure to modify the schema of a bunch of tables.

You can (as I always do ) let the SQL Server create the script for you and execute this on your own in bacthes for better error handling (as you didn′t point out if error handling is applicable for you like in SQL Server 2005)

SELECT 'ALTER TABLE ' + C.TABLE_NAME +' ALTER COLUMN ' + C.COLUMN_NAME + ' VARCHAR(50)' --New data type and length
FROM INFORMATION_SCHEMA.Columns C
INNER JOIN INFORMATION_SCHEMA.Tables T
ON
T.TABLE_CATALOG = C.TABLE_CATALOG AND
T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
T.TABLE_NAME = C.TABLE_NAME
WHERE
DATA_TYPE = 'NVARCHAR' AND --Old Type
CHARACTER_MAXIMUM_LENGTH = 50 AND --Old lenght
TABLE_TYPE = 'BASE TABLE'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Changing Table Name to Write data dynamically.

Hello there, folks.
I have 5 tables with exactly same column types, names, and constraints
but with differnt names.(e.g. table1, table2, ..., table5)
I have a stored procedure(sp_example) that writes some calculated data to
"table1".
but I would like to modify the stored procedure to write the similiar data
(but from different sources) to other tables(table2,... table5)
depending on value passed to the stored procedure
(e.g, exec sp_example '2' will write to table2, exec sp_example '3' will
write to table3 and so on)...
I have tried to use a pattern like
==============================
create procedure sp_example @.param
as
declare @.cmd as Varchar(8000)
set @.cmd = @.cmd + 'INSERT INTO ' + getTableName(@.param)
set @.cmd = @.cmd + ' SELECT * From sometable '
Exec(@.cmd)
==============================
But the problem is that stored procedure i am working with is roughly 1000
lines
and having to write "set @.cmd = @.cmd + '...'" seems like an overkill.
So is there any other way to change the "table" name only dynamically?
Thank you in advance.> I have 5 tables with exactly same column types, names, and
constraints
> but with differnt names.(e.g. table1, table2, ..., table5)
Why? Sounds like a design flaw. Use one table and add an extra column
for whatever attribute is represented by the different table names. You
can still create views with the original table names so you shouldn't
even need to change your code.
Failing that, you could consider using a partitioned view. See Books
Online for details. Good design is the right solution rather than messy
Dynamic SQL.
David Portas
SQL Server MVP
--|||> Why? Sounds like a design flaw. Use one table and add an extra column
Yeah, it seems like so but as a programmer, i have to deal with badly
designed table structures...

> Failing that, you could consider using a partitioned view. See Books
> Online for details. Good design is the right solution rather than messy
I don't think partitioned views can be applied to the problem i have after
going through the online book...

Changing data types of a variable in an expression

Greetings once again SQL friends,

Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!

I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.

The syntax I am using is as follows :

(dt_str) @.[User::MAX_OFFER_PRICE_ID]

My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.

What am I doing wrong?!

Thanks for your help in advance.

Try,

(dt_str, 6, 1252) @.[User::MAX_OFFER_PRICE_ID]

If you're using the expression builder then expand "Type Casts" in the top right hand corner and you'll see the required syntax.

-Jamie

|||

Hi Jamie,

You are my SSIS hero now

Thanks very much mate.

|||

Hi Jamie,

Just wondering, if I want to use retrieve another value from a different table, would I have to create yet another SourceSQL variable?! I am trying to think of a method that would allow me to re-use the sqlSource rather than create a new one.

My initial thoughts (after some reading) is to use a configuration file but I have not figured out the finer details of this solution.. God help me!!

Have you come across this kind of thing before? Do you have any suggestions?

Please forgive me for pestering you with my questions but I am eager to learn as much as I can about this great tool. As much as I am annoyed (for spending ages tryi8ng to work out simple thigs), I am even more determined to learn more about this beast of a tool.

Thanks for all the advice.

|||

You say "yet another SourceSQL variable" as if its a bad thing :)

If you need to extract data from a different table then create another variable - no problem with doing that.

-Jamie

|||

Hello there,

I have read your questions and i must say that i sympathize with you alot!!!

I have spent hours trying to figure out the logic behind the syntax and i must say that even though the examples appear simple in books online, they become complex to decipher when I start to get these error messages from the parser.

would you be able to share with me some of the simple syntax logic behind these expressions?

Thank you!!

|||

The syntax will likely appear familiar to C programmers, as the grammar is similar to the C language. Beyond that, the books online has a pretty good reference.

If you run into any specific errors that perplex you, please do post the expression and the error message on these forums. Most likely someone will be able to help you, and furthermore it will help the SSIS team identify cases where the error messages are not as helpful as they should be.

Thanks
Mark

Changing data types of a variable in an expression

Greetings once again SQL friends,

Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!

I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.

The syntax I am using is as follows :

(dt_str) @.[User::MAX_OFFER_PRICE_ID]

My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.

What am I doing wrong?!

Thanks for your help in advance.

Try,

(dt_str, 6, 1252) @.[User::MAX_OFFER_PRICE_ID]

If you're using the expression builder then expand "Type Casts" in the top right hand corner and you'll see the required syntax.

-Jamie

|||

Hi Jamie,

You are my SSIS hero now

Thanks very much mate.

|||

Hi Jamie,

Just wondering, if I want to use retrieve another value from a different table, would I have to create yet another SourceSQL variable?! I am trying to think of a method that would allow me to re-use the sqlSource rather than create a new one.

My initial thoughts (after some reading) is to use a configuration file but I have not figured out the finer details of this solution.. God help me!!

Have you come across this kind of thing before? Do you have any suggestions?

Please forgive me for pestering you with my questions but I am eager to learn as much as I can about this great tool. As much as I am annoyed (for spending ages tryi8ng to work out simple thigs), I am even more determined to learn more about this beast of a tool.

Thanks for all the advice.

|||

You say "yet another SourceSQL variable" as if its a bad thing :)

If you need to extract data from a different table then create another variable - no problem with doing that.

-Jamie

|||

Hello there,

I have read your questions and i must say that i sympathize with you alot!!!

I have spent hours trying to figure out the logic behind the syntax and i must say that even though the examples appear simple in books online, they become complex to decipher when I start to get these error messages from the parser.

would you be able to share with me some of the simple syntax logic behind these expressions?

Thank you!!

|||

The syntax will likely appear familiar to C programmers, as the grammar is similar to the C language. Beyond that, the books online has a pretty good reference.

If you run into any specific errors that perplex you, please do post the expression and the error message on these forums. Most likely someone will be able to help you, and furthermore it will help the SSIS team identify cases where the error messages are not as helpful as they should be.

Thanks
Mark

Changing Data Types in SqlServer

I have upsized my access database to Sql Server - Fine. Everything exported in order, but..

I haven't had any success changing the data types and sizes
Ex. [nvarchar] [255] to [varchar] [1000].

I have tried in Sql Server to:

ALTER TABLE dbo.products
ALTER COLUMN description type [varchar] [1000]

Obviously this isn't a correct statement, however, not obvious to me..

Thanks much in advance for help.Q1 I haven't had any success changing the data types and sizes Ex. [nvarchar] [255] to [varchar] [1000]. I have tried in Sql Server to:

ALTER TABLE dbo.products
ALTER COLUMN description type [varchar] [1000]

Obviously this isn't a correct statement, however, not obvious to me..


A1 If the column name is "description type", sic, consider changing it to DescriptionType, or possibly description_type, to avoid all sorts of similar issues in the future; if this is not possible, more success is likely using:

a) [description type] (sic)

rather than the unbracketed form

b) description type

In any event the general syntax is:
Alter Table [Table_Name]
Alter Column [Column_Name]

For Example:
ALTER TABLE [dbo].[products]
ALTER COLUMN [description type] [varchar] (1000)

For a demonstration example, run the following:

Use TempDB
Go

if exists (select * from dbo.sysobjects where id = object_id(N'[Demo_Information]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Demo_Information]
GO

CREATE TABLE [dbo].[Demo_Information] (
[DemoPrimaryKey] [int] IDENTITY (1, 1) NOT NULL ,
[AlterTypeTargetData] [nvarchar] (255) NULL ,
CONSTRAINT [PK_Demo_Information] PRIMARY KEY CLUSTERED
(
[DemoPrimaryKey]
)
)
Go

INSERT INTO
[dbo].[Demo_Information]
([AlterTypeTargetData])
VALUES
('012342578901234257890123425789012342578901234257 89')

INSERT INTO
[dbo].[Demo_Information]
([AlterTypeTargetData])
VALUES
('01234257890123425789')

Select 'Note Type_Name and Precision:' As 'BEFORE Alter Table statement'
Exec sp_Columns Demo_Information
Go

Alter Table [Demo_Information]
Alter Column [AlterTypeTargetData] [Varchar] (1000) Null
Go

Select 'Note Type_Name and Precision:' As 'AFTER Alter Table statement'
Exec sp_Columns Demo_Information
Go

-- To Change the type back to nVarChar (255)
-- Alter Table [Demo_Information]
-- Alter Column [AlterTypeTargetData] [nVarchar] (255) Null
-- Go

Changing data types and lengths in replicated tables

Okay, this is a real bummer. I setup a one way replication from one SQL
server to another. Now, the data types and lengths in some of my tables needs
to change to fit a new data structure but I keep receiving and error that SQL
cannot drop the table because it is replicated. I can make the changes on the
Subscriber with no problem, but the publisher is a no-go!! I thought any
table changes I made would be replicated as well - I did not realize this was
going to be an issue.
Can anyone offer any help - our process is running tonight and I have to get
this working by 1AM.
Thanks,
Nicole Hagler
Nicole, this is just the way it is. The best way IMO, is to RClick the
Publication/ Generate SQL Script(Script the steps to delete)/ Preview/ grab
the
exec sp_dropsubscription @.publication = N'', @.article = N'', @.subscriber =
N'all', @.destination_db = N'all'
exec sp_droparticle @.publication = N'', @.article = N'',
@.force_invalidate_snapshot = 1
GO
that applies to your table. Then once you make your changes you will need to
resnapshot. (Unless you can do this when there will be no data
manipulations.) Do this first in test. 99.99% of the time that I've done
this, it only wanted to resnapshot the table in question. But recenlty it
wanted to resnapshot all of the table in the Publication and I still havent
figured out why. Worst case, create a new Publication and add this table to
that.
CR
"Nicole" <Nicole@.discussions.microsoft.com> wrote in message
news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> Okay, this is a real bummer. I setup a one way replication from one SQL
> server to another. Now, the data types and lengths in some of my tables
> needs
> to change to fit a new data structure but I keep receiving and error that
> SQL
> cannot drop the table because it is replicated. I can make the changes on
> the
> Subscriber with no problem, but the publisher is a no-go!! I thought any
> table changes I made would be replicated as well - I did not realize this
> was
> going to be an issue.
> Can anyone offer any help - our process is running tonight and I have to
> get
> this working by 1AM.
> Thanks,
> Nicole Hagler
|||Create a temp table which holds the pk and the columns you wish to change.
use sp_repldropcolumn to drop the column, and then sp_repladdcolumn to add
it back with the same name, but the new data type. Then update this column
to have the same value as the column in the temp table which corresponds to
your pk.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nicole" <Nicole@.discussions.microsoft.com> wrote in message
news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> Okay, this is a real bummer. I setup a one way replication from one SQL
> server to another. Now, the data types and lengths in some of my tables
needs
> to change to fit a new data structure but I keep receiving and error that
SQL
> cannot drop the table because it is replicated. I can make the changes on
the
> Subscriber with no problem, but the publisher is a no-go!! I thought any
> table changes I made would be replicated as well - I did not realize this
was
> going to be an issue.
> Can anyone offer any help - our process is running tonight and I have to
get
> this working by 1AM.
> Thanks,
> Nicole Hagler
|||Hilary this is an awesome idea and one I will definatley take advantage of
when possible. But Im curious if you've run into problems on updating large
tables in this way due to locking?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ODahu$3pFHA.820@.TK2MSFTNGP09.phx.gbl...
> Create a temp table which holds the pk and the columns you wish to change.
> use sp_repldropcolumn to drop the column, and then sp_repladdcolumn to add
> it back with the same name, but the new data type. Then update this column
> to have the same value as the column in the temp table which corresponds
> to
> your pk.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nicole" <Nicole@.discussions.microsoft.com> wrote in message
> news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> needs
> SQL
> the
> was
> get
>

changing data types

hi!
I'd like to change the datatype of one tablefield from varchar to text.
Is this possible when the table already is populated, or will there be trouble?
obscurrDepending the version you're using.

In any way

exec sp_rename MyTable, MyOldTable
select Col1, Col2, convert(TEXT, ColVarchar) ColVarchar
into MyTable
from MyOldTable
drop table MyOldTable|||I'm using SQL Server version 7.0

Changing data type to the fields of my tables.

Hello.

i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.

I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.

thanks in advance

You can use SQL queries to generate the DDL statements that you need, something like this (use the other columns in the columns schema table to generate other things into the ALTER TABLE statements)

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] int'
+ CASE WHEN upper(IS_NULLABLE) = 'YES' THEN ' NULL' ELSE ' NOT NULL' END
from information_schema.columns
where data_type = 'tinyint'
and table_name in (select table_name from information_schema.tables where table_type = 'BASE TABLE')

Make sure you backup before you start running these scripts and test them all!

|||That looks pretty good. I was curious what this would look like with the new 2005 syntax using the DMVs. Here is what I came up with... Please note: This needs strenuous testing and a backup like the previous post suggested!!

USE tempdb
GO
IF EXISTS(SELECT * FROM sys.types where name = 'SSN')
DROP TYPE SSN
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

GO
IF OBJECT_ID('TestSSN') IS NOT NULL
DROP TABLE TestSSN
GO
CREATE TABLE TestSSN ( MySSn SSN)
GO

-- Show the tables with the UDF fields...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
GO

DECLARE @.ErrMsg varchar(255),
@.ErrNum int,
@.SQL nvarchar(max),
@.TableName sysname,
@.ColumnName sysname,
@.SchemaName sysname

DECLARE @.c CURSOR

-- create a cursor to loop through all the tables
SET @.c = CURSOR FOR
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
FOR READ ONLY

OPEN @.c
FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN -- loop
SET @.SQL = 'ALTER TABLE ' + @.SchemaName + '.' + @.TableName + ' ALTER COLUMN ' + @.ColumnName + ' varchar(32) NOT NULL'
PRINT 'Executing: ' + @.SQL
EXEC sp_executesql @.SQL

SELECT @.ErrNum = @.@.Error--, @.RowCnt = @.@.ROWCOUNT
IF (@.ErrNum <> 0)
BEGIN
RAISERROR('Error updating %s.%s table to have %s a varchar(32). Error Numer: %d', 16, 1, @.SchemaName, @.TableName, @.ColumnName, @.ErrNum)
GOTO ErrorHandler
END

FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
END -- loop
CLOSE @.c
DEALLOCATE @.c

GOTO ExitScript

ErrorHandler:
RAISERROR (@.ErrMsg, 16, 127)
GOTO ExitScript

ExitScript:
GO

-- See if we have any tables with that UDF anymore...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'