How to change data type of column from int to bigint in replication

Hi,
Currently, we have a table in sql server 2000 which has one column as
int data-type and which is part of transactional replication we
replicate this table to oracle. The oracle version is 9.2. Now we want
to change the data type of this column to bigint what would be the
best way to alter this table so that we don't have to drop the
replication?
Thanks
If this is the pk you will have to drop the subscribers and publications. If
it is a non-key column you should try to use sp_repladdcolumn and
sp_repldropcolumn.
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
<kulkarni.ninad@.gmail.com> wrote in message
news:1171302064.597470.203130@.s48g2000cws.googlegr oups.com...
> Hi,
> Currently, we have a table in sql server 2000 which has one column as
> int data-type and which is part of transactional replication we
> replicate this table to oracle. The oracle version is 9.2. Now we want
> to change the data type of this column to bigint what would be the
> best way to alter this table so that we don't have to drop the
> replication?
> Thanks
>
|||Please take a look at http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql

How to capture return value from 'execute'

declare @.table varchar(100);

declare @.q varchar(100);

declare @.key bigint;

select @.table = 'key_table';

select @.q = 'select key from ' + @.table;

select @.key = exec(@.q); -> not working.

Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||

In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.

Thanks,

|||

create a temp table and use this type

insert into #tempTable

exec ( @.yourExecString )

Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.

|||

Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.

declare @.table sysname;

declare @.q nvarchar(100);

declare @.key bigint;

select @.table = N'key_table';

select @.q = 'select @.key = key from dbo.[' + @.table + N']';

exec sp_executesql @.q, N'@.key bigint output', @.key output;

select @.key

go

Be careful with sql injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB