SQL Server Performance Forum – Threads Archive
How to change text in syscommnents?
We have a lot of refferenced views to other server and now we need to change server name into other. Does anyone know any way to change for IE: create view TT as select * from server123.DB.dbo.table to create view TT as select * from server345.DB.dbo.tableSpeaking in other words is there any way to convert one symbols combination into other Thanks in advance
Why do you want to change the system data?
Its not recommended to change the data in system tables. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I need to change linked server name in a view. And would like to do that not changing one by one manualy. Syscomments holds database object codes. Yes I know that it’s not recommended but I’ll change it only in user views, so there wouldn’t be any damage to the system
I think you are trying to change ctext which is in binary format. So I think as satya mantion it is not advisable to change this field.
quote:Originally posted by zvidas
We have a lot of refferenced views to other server and now we need to change server name into other. Does anyone know any way to change for IE:
create view TT as select * from server123.DB.dbo.table
to
create view TT as select * from server345.DB.dbo.table
Speaking in other words is there any way to convert one symbols combination into other Thanks in advance
Speaking in other words is there any way to convert one symbols combination into other Thanks in advance
sp_help syscomments
select text from syscommnets No the object codes are in nvarchar(8000) data type
Yes but text is an computed table hence you can’t do any modification to that
see at EM , for the column text it is computed from the Ctext (convert(nvarchar(4000),case when ([status] & 2 = 2) then (uncompress([ctext])) else [ctext] end))
quote:Originally posted by zvidas
sp_help syscomments
select text from syscommnets No the object codes are in nvarchar(8000) data type
sp_help syscomments
select text from syscommnets No the object codes are in nvarchar(8000) data type
Yes, you are right. There is a more simple way to do that with a cursor by altering view. Thanks again
Yes that should be a good way without doing any midifications to sys tables directly
good luck
quote:Originally posted by zvidas
Yes, you are right. There is a more simple way to do that with a cursor by altering view.
Thanks again
The proper way is to use ALTER PROCEDURE, ALTER FUNCTION, etc. – as applicable. If you are depending on remote server names and names of other databases, you should challenge the reason why those names are changed.
]]>