Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
Wait till "off hours"/ drop Subscription/ drop article/ change data type on both tables/ add article/ add subscription. If theres no good time to do it, you can re-snapshot the data as well.
"David" <davbarquero@.hotmail.com> wrote in message news:OvRppwFnEHA.3196@.TK2MSFTNGP10.phx.gbl...
Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
|||David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje news:eOJxZYJnEHA.3428@.TK2MSFTNGP11.phx.gbl...
David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label helloi. Show all posts
Showing posts with label helloi. Show all posts
Monday, March 19, 2012
Sunday, February 12, 2012
cast not valid
Hello:
I want to make a query that return all record that don't have one valid
cast, something like this:
select * from table where cast(field as bigint) is valid
how can I do that or some variant?. the real problem is to import some table
in a dts but when I try to convert the str to bigint, raise one execption,
that I want to jump and eliminate this record but continue, instead the dts
stop.
Best regards,
Owen.Owen wrote:
> Hello:
> I want to make a query that return all record that don't have one
> valid cast, something like this:
> select * from table where cast(field as bigint) is valid
> how can I do that or some variant?. the real problem is to import
> some table in a dts but when I try to convert the str to bigint,
> raise one execption, that I want to jump and eliminate this record
> but continue, instead the dts stop.
> Best regards,
> Owen.
CAST is not a BOOLEAN function. That is, it does not return whether a
value _can_ be converted from one type to another. It explicitly tries
to convert and throws an exception if a failure occurs. You could use
the ISNUMERIC() function or roll your own integer check function or use
the one here:
http://www.aspfaq.com/show.asp?id=2390
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SELECT *
FROM Table
WHERE (x NOT LIKE '%[^0-9]%'
AND LEN(x) BETWEEN 1 AND 18)
OR x IS NULL
David Portas
SQL Server MVP
--
I want to make a query that return all record that don't have one valid
cast, something like this:
select * from table where cast(field as bigint) is valid
how can I do that or some variant?. the real problem is to import some table
in a dts but when I try to convert the str to bigint, raise one execption,
that I want to jump and eliminate this record but continue, instead the dts
stop.
Best regards,
Owen.Owen wrote:
> Hello:
> I want to make a query that return all record that don't have one
> valid cast, something like this:
> select * from table where cast(field as bigint) is valid
> how can I do that or some variant?. the real problem is to import
> some table in a dts but when I try to convert the str to bigint,
> raise one execption, that I want to jump and eliminate this record
> but continue, instead the dts stop.
> Best regards,
> Owen.
CAST is not a BOOLEAN function. That is, it does not return whether a
value _can_ be converted from one type to another. It explicitly tries
to convert and throws an exception if a failure occurs. You could use
the ISNUMERIC() function or roll your own integer check function or use
the one here:
http://www.aspfaq.com/show.asp?id=2390
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SELECT *
FROM Table
WHERE (x NOT LIKE '%[^0-9]%'
AND LEN(x) BETWEEN 1 AND 18)
OR x IS NULL
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)