Msg 259, Ad hoc updates to system catalogs are not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Msg 259, Ad hoc updates to system catalogs are not

Author

Topic

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-01 : 08:16:16

I got this error in SQL 2005, version which I'm using is Microsoft SQL Server 2005 - 9.00.5292.00 (Intel X86) Apr 13 2011 15:56:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2, v.4789), looks like sys.server is a catalog view for linked or remote server registered. Can not be updated. Am i right on it? If Yes, is there any other way to allow these updates, Query which I'm Running is

update SYNERGY.DBO.syscolumns
set collationid = '872468488' where collationid is not null
go

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-01 : 10:18:53

Nope. The system tables cannot be directly changed. It's a good thing, because back on SQL 2000 (when they could), people broke their databases beyond repair doing so. The statement you're trying to run could cause severe errors if it was allowed

To change the collation of a column, use ALTER TABLE, specifically ALTER TABLE <table name> ALTER COLUMN <column name> <data type> <collation>

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 14:13:47

why not use ALTER TABLE ALTER COLUMN to change collation setting of column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-06 : 00:46:49

So i need to do this;

ALTER TABLE SYNERGY.DBO.SYS.COLUMNS
ALTER COLUMN COLLATIONID

instead of this

update SYNERGY.DBO.syscolumns
set collationid = '872468488' where collationid is not null

?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 01:15:08

quote:
Originally posted by ShayaN_

So i need to do this;

ALTER TABLE SYNERGY.DBO.SYS.COLUMNS
ALTER COLUMN COLLATIONID

instead of this

update SYNERGY.DBO.syscolumns
set collationid = '872468488' where collationid is not null

?

yep something like

ALTER TABLE SYNERGY.DBO.SYS.COLUMNS
ALTER COLUMN COLLATIONID <datatype> <NULL | NOT NULL> COLLATE <collation setting>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-06 : 01:28:03

quote:
Originally posted by visakh16

quote:
Originally posted by ShayaN_

So i need to do this;

ALTER TABLE SYNERGY.DBO.SYS.COLUMNS
ALTER COLUMN COLLATIONID

instead of this

update SYNERGY.DBO.syscolumns
set collationid = '872468488' where collationid is not null

?

yep something like

ALTER TABLE SYNERGY.DBO.SYS.COLUMNS
ALTER COLUMN COLLATIONID <datatype> <NULL | NOT NULL> COLLATE <collation setting>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dear could not get ya,

ALTER TABLE SYNERGY.DBO.SYS.COLUMNS
ALTER COLUMN COLLATIONID '872468488' where collationid is not null <NULL | NOT NULL> COLLATE <collation setting>

what is collation setting :-s can you please give me the exact query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 01:37:31

you need to replace datatype with actual datatype, put NULL or NOT NULL accordingly and add your collation name after COLLATE.
b/w where did you get value 872468488 from?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-06 : 02:04:05

Basically I'm updating my database from the data which i got name Synergy, from there i use update here is the some top rows of query

EXECUTE sp_attach_single_file_db @dbname = N'synergy',
@physname = N'd:\syn_files\Synergy\Synergy_50250326_01102011_04102011.mdf'
GO

-------------------
--SETTING COLLATION
-------------------
update SYNERGY.DBO.syscolumns
set collationid = '872468488' where collationid is not null
go

now in this row i'm getting the error which i pasted above, 1 more thing this data was extracted from SQL 2000 and now I'm uploading this data to SQL 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 02:08:43

nope i was asking where you get collation id value from?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-06 : 04:55:34

If the vendor sent you that code to run, call them and tell them that they are idiots (I mean it). They're sending you code that can't run and, if it could, could destroy the database.

--
Gail Shaw
SQL Server MVP

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-10 : 07:43:04

quote:
Originally posted by GilaMonster

If the vendor sent you that code to run, call them and tell them that they are idiots (I mean it). They're sending you code that can't run and, if it could, could destroy the database.

--
Gail Shaw
SQL Server MVP

This code was running ok in SQL 2000 but now its not running even i have changed the Query To ALTER TABLE AND ALTER COLUMN,
Code
ALTER TABLE SYNERGY.DBO.syscolumns
ALTER COLUMN collationid = '872468488' where collationid is not null
and the error is
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 07:51:40

whatever you're doing is syntactically wrong as specified already. you need to use

ALTER TABLE SYNERGY.DBO.syscolumns
ALTER COLUMN collationid <data type here> COLLATE <collation name>

not id value as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-10 : 09:10:54

quote:
Originally posted by visakh16

whatever you're doing is syntactically wrong as specified already. you need to use

ALTER TABLE SYNERGY.DBO.syscolumns
ALTER COLUMN collationid <data type here> COLLATE <collation name>

not id value as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What is data type and Collation name ? : (

Here is the result of

SELECT * FROM SYScolumns
WHERE COLLATIONID = '872468488'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 14:58:53

then it should be


ALTER TABLE <yourtable>
ALTER COLUMN <columnname> <data type here> COLLATE SQL_Latin1_General_CP1_CI_AS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-10 : 23:34:47

quote:
Originally posted by ShayaN_

quote:
Originally posted by GilaMonster

If the vendor sent you that code to run, call them and tell them that they are idiots (I mean it). They're sending you code that can't run and, if it could, could destroy the database.

--
Gail Shaw
SQL Server MVP

This code was running ok in SQL 2000

If by 'ok' you mean 'will run but could potentially cause irreparable corruption', then yes, it used to run 'ok'

That's terrible code, it's a terrible thing to do to a SQL database at all, it could cause all sorts of problems.

--
Gail Shaw
SQL Server MVP

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-11 : 01:09:42

quote:
Originally posted by visakh16

then it should be

ALTER TABLE <yourtable>
ALTER COLUMN <columnname> <data type here> COLLATE SQL_Latin1_General_CP1_CI_AS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dear Can you please tell me what should i write in Data type ? and @ Dear Gila Monster, I was running this code in SQL 2000 since 6 or 7 month, i never faced any kind of issue, but due to some bugs in SQL 2000 i have to move my all databases to SQL 2005 now this is the only problem which i am facing so far in SQL 2005 and still i can't get through this. Visakh has helped me alot Thanks visakh for every thing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:22:51

i didnt understand why you need to change collation of all columns directly in syscolumns table. why dont alter collation of databse itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ShayaN_
Starting Member

22 Posts

Posted - 2011-10-11 : 01:32:31

quote:
Originally posted by visakh16

i didnt understand why you need to change collation of all columns directly in syscolumns table. why dont alter collation of databse itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Look dear, i always get MDF file from other person, and i do upload it to my database daily, and in that database this collationid is 872468488 i need to update those rows which have 872468488 except null in to ma database well i have changed query to this;

ALTER TABLE SYNERGY.DBO.syscolumns
ALTER COLUMN collationid VARCHAR(9) COLLATE SQL_Latin1_General_CP1_CI_AS

but still its giving below error;

Msg 4902, Level 16, State 1, Line 1
Cannot find the object "SYNERGY.DBO.syscolumns" because it does not exist or you do not have permissions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 02:55:15

for that what you need to do is to ude ALTER DATABASE statement rather than trying to update syscolumns view directly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-11 : 07:52:04

you are trying to update system catalog (dbo.syscolumns)which is NOT POSSIBLE in SQL Server 2005 and above versions.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-11 : 11:57:20

You need to run an ALTER TABLE for each and every column in the database you want to change. Not syscolumns (it's not even a table). Each and every single column in each and every user table in your database.

p.s. Just because you didn't notice any problems from updating the system tables doesn't mean it was safe to do so, doesn't mean it wasn't causing problems that you didn't notice.

p.p.s I am NOT your 'dear' anything!

--
Gail Shaw
SQL Server MVP

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources