Accessing more than: update

Showing posts with label update. Show all posts

Thursday, March 22, 2012

adding a column then using it in a script

Hi,
I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.

LauraReally?

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))

INSERT INTO myTable99(Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'

SELECT * FROM myTable99

ALTER TABLE myTable99 ADD Col3 int

SELECT * FROM myTable99

UPDATE myTable99 SET Col3 = 1

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Why not post your code?|||I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.|||Originally posted by joejcheng
I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.

Did you cut and paste my code?|||I am talking about what Laura might have done. My guess is she cut and paste the script generated by SQL and put in an insert or update statement between BEGIN TRANS and COMMIT.|||Commit! Duh! Sometimes you overlook the easy stuff. Ok Thank you so much.sql

Tuesday, March 20, 2012

Adding 0 to a column

I have a column with times in

845

930

1015

1145

I need to update the column to look like this:

08:45

09:30

10:15

11:45

thanks in advance

rich

Maybe something like:

set nocount on
declare @.timeSample table (aTime integer not null)
insert into @.timeSample values (845)
insert into @.timeSample values (930)
insert into @.timeSample values (1015)
insert into @.timeSample values (1145)
select right ('0' + convert (varchar (2), aTime / 100),2) + ':' +
convert (char(2), aTime % 100) as formattedTime
from @.timeSample
-- --
-- SAMPLE OUTPUT:
-- --

-- formattedTime
-- -
-- 08:45
-- 09:30
-- 10:15
-- 11:45

sql

Monday, March 19, 2012

add to data

Hi All,
I have a database that has a field with phone numbers in.
I want to update the main part of the number to add the area code to
the main number.
I will do a selection on the postcode of the address and then I want to
add the correct area code to the beginning of the number.
Any help please.
SteveHi Steve,
First of all:
http://www.aspfaq.com/etiquette.asp?id=5006
Based on this its hard to give some suggestions but assuming some thing
here is some pseudocode
UPDATE CustomerTable
SET phoneNumber = CAST(RegionCode AS VARCHAR(10)) + CAST(phoneNumber
AS VARCHAR(100))
FROM CustomerTable C
INNER JOIN
RegionPhoeTable R
ON C.ZipCode = R.ZipCode
HTH, jens Suessmeyer.

add to data

Hi All,
I have a database that has a field with phone numbers in.
I want to update the main part of the number to add the area code to
the main number.
I will do a selection on the postcode of the address and then I want to
add the correct area code to the beginning of the number.
Any help please.
Steve
Hi Steve,
First of all:
http://www.aspfaq.com/etiquette.asp?id=5006
Based on this its hard to give some suggestions but assuming some thing
here is some pseudocode
UPDATE CustomerTable
SET phoneNumber = CAST(RegionCode AS VARCHAR(10)) + CAST(phoneNumber
AS VARCHAR(100))
FROM CustomerTable C
INNER JOIN
RegionPhoeTable R
ON C.ZipCode = R.ZipCode
HTH, jens Suessmeyer.

add to data

Tuesday, March 6, 2012

Add new record

Hallo everyone,
I have a form with unbound textboxes that forms a record,how can I add a
new record of unbound textboxes afterI have update the first record?
Can anyone help me please?
You haven't specified what development environment you are using. This
isn't a SQL Server-specific question so you'll probably get more help
posting to a group for the appropriate tool: Access? VB? C#? ASP?
David Portas
SQL Server MVP

Add new record

Hallo everyone,
I have a form with unbound textboxes that forms a record,how can I add a
new record of unbound textboxes afterI have update the first record?
Can anyone help me please?You haven't specified what development environment you are using. This
isn't a SQL Server-specific question so you'll probably get more help
posting to a group for the appropriate tool: Access? VB? C#? ASP?
--
David Portas
SQL Server MVP
--

Add new record

Hallo everyone,
I have a form with unbound textboxes that forms a record,how can I add a
new record of unbound textboxes afterI have update the first record?
Can anyone help me please?You haven't specified what development environment you are using. This
isn't a SQL Server-specific question so you'll probably get more help
posting to a group for the appropriate tool: Access? VB? C#? ASP?
David Portas
SQL Server MVP
--

Sunday, February 19, 2012

Add article to Merge Repl. Publication

I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?

Your help is greatly appreciated.

GaryHi, don't add the table to both sides. Just add the table at the publisher and include it in the publication using the ARTICLES tab of Replication-Properties in EM. Start the Snapshot-Agent. The merge agent would apply only the new table to the subscriber. It's for the subscribers running SQL2000.|||Thanks for the prompt reply. The scripts will also modify some stored procedures - is that handled by the snapshot, or are the changes just replicated?|||Hi, the merge-agent applies the new tables to the subscriber.
About stored-procedures i am not sure, can u modify the stored-procedure if it's being published? Published tables can't be modified unless we use sp_repladdcolumn/sp_repldropcolumn to add or drop columns within the published tables. See if u can modify a published stored procedure at the publisher.

Howdy!

Sunday, February 12, 2012

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.|||Thanks Greg, but it doesn't help. The question is like follows:
I restore a database from an other server, the schema (object owner) is not dbo but -for example- abcd. In sql2000 I could update the sid in the sysusers-table from master.dbo.sysxlogins to enable the connection across odbc. In sql2005 there is no sysusers-table in the restored db and no sysxlogins-table in the master-db. If I try to connect across odbc I get the message "Cannot open user default database. Login failed."
Greetings hafi|||direct updates to the system tables were never supported in SQL Server. But it looks like, in SQL 2005 they are not even ALLOWED and that is a wrong move from Microsoft. Though not supported, sometimes you can't get away without updating system tables.

For ex., try moving a log shipped database from one server to another, without losing it's synchronization.
|||Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

|||I tried through DAC (sqlcmd -A) and still can't update system tables. As per BOL, DAC allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. But, it doesn't say to anything about updating system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

I omitted to say that the server also has to be started in single-user mode, for the updates to be allowed (using the -m flag). By itself, DAC will allow you to see system tables, but to update them, you also need to have the server started in single-user mode.

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

|||I believe you can do exec your_db.sys.sp_rename to make sp_rename execute in your_db. Also if you do "use your_db; sp_rename", this will be equivalent to above. Most of system sps were moved to resource database (you can see them in sys.system_objects catalog) in SQLServer 2005. They are nowis user-db neutral and will take current db context as the one to execute in.|||Hi, I'm having the same problem, what was your solution?|||

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

All these answers are great workaround-coverage for not allowing ad hoc updates to the catalog tables. And I can see some reasoning behind not allowing DBAs to get their job done in an efficient and expedient manner. But this is just another example of executive thinking --and decision making-- by MS.

My case is hundreds of databases and no way to use the wonderful method of set processing to get the job done. Now I am begin told if I want to set the status of a hundred databases from 'Full' to 'Simple' recovery, I have to use the mouse? No way!

Will I have to hack my way into one of the 'system' procs to get the job done or is there a system function available? I do not --repeat-- do not want to place the entire server in SUM (-m) just to make an update to the catalogs.

If I need to update the catalogs, the functionality of sp_configure 'allow up', 1 and reconfigure with override needs to start working again, or the configuration function needs to allow full access to all options.

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

For your other comments, you should post them on a related forum (SQL Server Database Engine, for example). If you do not receive a useful suggestion or workaround for your issue, please open a request at:

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.
|||Thanks Greg, but it doesn't help. The question is like follows:
I restore a database from an other server, the schema (object owner) is not dbo but -for example- abcd. In sql2000 I could update the sid in the sysusers-table from master.dbo.sysxlogins to enable the connection across odbc. In sql2005 there is no sysusers-table in the restored db and no sysxlogins-table in the master-db. If I try to connect across odbc I get the message "Cannot open user default database. Login failed."
Greetings hafi|||direct updates to the system tables were never supported in SQL Server. But it looks like, in SQL 2005 they are not even ALLOWED and that is a wrong move from Microsoft. Though not supported, sometimes you can't get away without updating system tables.

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.
|||Thanks Greg, but it doesn't help. The question is like follows:
I restore a database from an other server, the schema (object owner) is not dbo but -for example- abcd. In sql2000 I could update the sid in the sysusers-table from master.dbo.sysxlogins to enable the connection across odbc. In sql2005 there is no sysusers-table in the restored db and no sysxlogins-table in the master-db. If I try to connect across odbc I get the message "Cannot open user default database. Login failed."
Greetings hafi|||direct updates to the system tables were never supported in SQL Server. But it looks like, in SQL 2005 they are not even ALLOWED and that is a wrong move from Microsoft. Though not supported, sometimes you can't get away without updating system tables.

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc updates to system catalogs are not allowed.

in SQLServer2005 how can I allow update system catalogs (by mouseclick)?

sp_configure 'allow updates', 1 - works, but I get the message

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

From Books Online:

This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported.
|||Thanks Greg, but it doesn't help. The question is like follows:
I restore a database from an other server, the schema (object owner) is not dbo but -for example- abcd. In sql2000 I could update the sid in the sysusers-table from master.dbo.sysxlogins to enable the connection across odbc. In sql2005 there is no sysusers-table in the restored db and no sysxlogins-table in the master-db. If I try to connect across odbc I get the message "Cannot open user default database. Login failed."
Greetings hafi|||direct updates to the system tables were never supported in SQL Server. But it looks like, in SQL 2005 they are not even ALLOWED and that is a wrong move from Microsoft. Though not supported, sometimes you can't get away without updating system tables.

Thanks
Laurentiu|||

Hafi, in SQL Server 2005, search for topic in Books Online "Troubleshooting Orphaned Users". This has a solution for your exact problem without doing any ad-hoc updates to system tables.

Laurentiu Cristofor wrote:
Catalog updates are still not supported.
But updates can still be peformed using the Dedicated Admin Connection - DAC. This is described in BOL. I reiterate the advice of being extra careful when making catalog changes.
Thanks
Laurentiu

|||

Thanks
Laurentiu

|||

Thanks to all for problem solving, especially to Greg, your hint "Trouleshooting Orphaned Users" was vital importance and most usefull Idea

|||

I used to be able to make my stored procedure which I loaded on master to execute in the context of the current database (not the master database) by:

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = 0xc0000001 where name = 'sp_name'

sp_configure 'allow updates', 0

reconfigure with override

how can this be done now?

christos

See the following: http://searchsqlserver.techtarget.com/originalContent/0,289142,sid87_gci1102100,00.html?bucket=NEWS&topic=301343

I dug it up during my search on this topic. If you have a stored proc in master, it can be called using:

execute('sp_procName ''param1'',''param2''')

or just general SQL commands

execute('update table set allfields = null')

I've tested this and it works for my case. Check the article out.

|||

In all, this seems a bit Draco to me --or it's as if someone just rebuilt the Berlin Wall--, this time around the system catalog tables.

|||

Even in single user mode, catalog updates are not supported by Microsoft.

https://connect.microsoft.com/feedback/default.aspx?SiteID=68&wa=wsignin1.0

Thanks
Laurentiu

Ad hoc update to system catalogs is not supported?

Hi
SQL Server 2005 Express.
25 january 2007 I added a new procedure to my database using ole automation
procedures such as sp_OACreate and sp_OAMethod.
These system procedures requires 'Ole Automation Procedures' to be enabled.
So I added the following to my installation script:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
The script run fine and my procedue also runs fine.
But suddenly today installaing a new database I get an error mesage:
"Ad hoc update to system catalogs is not supported"
when I run the commands above.
Does anyone now what has happend since january? I installed a SP2 of SQL
Server Express. Has there been any changes?
Running "reconfigure with override" works but it is not recommended to use
"with override" I have read.
Is there any other way of enabling 'Ole Automation Procedures' which is
allowed?
Help is appreciated.
Regards Kjell Arne JohansenWe need to know what or who is trying to modify the system tables. Without seeing any code, we can't
say where the problem it. My guess is that you instantiate a COM object, which in turn connect back
to SQL Server and try to modify the system table - something you cannot do in 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> Hi
> SQL Server 2005 Express.
> 25 january 2007 I added a new procedure to my database using ole automation
> procedures such as sp_OACreate and sp_OAMethod.
> These system procedures requires 'Ole Automation Procedures' to be enabled.
> So I added the following to my installation script:
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> The script run fine and my procedue also runs fine.
> But suddenly today installaing a new database I get an error mesage:
> "Ad hoc update to system catalogs is not supported"
> when I run the commands above.
> Does anyone now what has happend since january? I installed a SP2 of SQL
> Server Express. Has there been any changes?
> Running "reconfigure with override" works but it is not recommended to use
> "with override" I have read.
> Is there any other way of enabling 'Ole Automation Procedures' which is
> allowed?
> Help is appreciated.
> Regards Kjell Arne Johansen
>
>|||Hi
This is the code causing the error message to occur when it is executed.
A month ago it worked fine.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:
> We need to know what or who is trying to modify the system tables. Without seeing any code, we can't
> say where the problem it. My guess is that you instantiate a COM object, which in turn connect back
> to SQL Server and try to modify the system table - something you cannot do in 2005.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> > Hi
> >
> > SQL Server 2005 Express.
> > 25 january 2007 I added a new procedure to my database using ole automation
> > procedures such as sp_OACreate and sp_OAMethod.
> >
> > These system procedures requires 'Ole Automation Procedures' to be enabled.
> > So I added the following to my installation script:
> > sp_configure 'show advanced options', 1;
> > GO
> > RECONFIGURE;
> > GO
> > sp_configure 'Ole Automation Procedures', 1;
> > GO
> > RECONFIGURE;
> > GO
> >
> > The script run fine and my procedue also runs fine.
> >
> > But suddenly today installaing a new database I get an error mesage:
> > "Ad hoc update to system catalogs is not supported"
> > when I run the commands above.
> > Does anyone now what has happend since january? I installed a SP2 of SQL
> > Server Express. Has there been any changes?
> >
> > Running "reconfigure with override" works but it is not recommended to use
> > "with override" I have read.
> >
> > Is there any other way of enabling 'Ole Automation Procedures' which is
> > allowed?
> >
> > Help is appreciated.
> >
> > Regards Kjell Arne Johansen
> >
> >
> >
>
>|||Are you saying that below code, in itself, generates the error you posted? I just tried on my sp2
with GDR, no error message...
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
> Hi
> This is the code causing the error message to occur when it is executed.
> A month ago it worked fine.
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
>
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
>> We need to know what or who is trying to modify the system tables. Without seeing any code, we
>> can't
>> say where the problem it. My guess is that you instantiate a COM object, which in turn connect
>> back
>> to SQL Server and try to modify the system table - something you cannot do in 2005.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
>> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> > Hi
>> >
>> > SQL Server 2005 Express.
>> > 25 january 2007 I added a new procedure to my database using ole automation
>> > procedures such as sp_OACreate and sp_OAMethod.
>> >
>> > These system procedures requires 'Ole Automation Procedures' to be enabled.
>> > So I added the following to my installation script:
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> > The script run fine and my procedue also runs fine.
>> >
>> > But suddenly today installaing a new database I get an error mesage:
>> > "Ad hoc update to system catalogs is not supported"
>> > when I run the commands above.
>> > Does anyone now what has happend since january? I installed a SP2 of SQL
>> > Server Express. Has there been any changes?
>> >
>> > Running "reconfigure with override" works but it is not recommended to use
>> > "with override" I have read.
>> >
>> > Is there any other way of enabling 'Ole Automation Procedures' which is
>> > allowed?
>> >
>> > Help is appreciated.
>> >
>> > Regards Kjell Arne Johansen
>> >
>> >
>> >
>>|||Yes you are right.
The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
OVERRIDE works fine.
Regards Kjell Arne Johansen
"Tibor Karaszi" wrote:
> Are you saying that below code, in itself, generates the error you posted? I just tried on my sp2
> with GDR, no error message...
> sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE;
> GO
> sp_configure 'Ole Automation Procedures', 1;
> GO
> RECONFIGURE;
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
> > Hi
> >
> > This is the code causing the error message to occur when it is executed.
> > A month ago it worked fine.
> >
> > sp_configure 'show advanced options', 1;
> > GO
> > RECONFIGURE;
> > GO
> > sp_configure 'Ole Automation Procedures', 1;
> > GO
> > RECONFIGURE;
> > GO
> >
> >
> > Regards Kjell Arne Johansen
> >
> > "Tibor Karaszi" wrote:
> >
> >> We need to know what or who is trying to modify the system tables. Without seeing any code, we
> >> can't
> >> say where the problem it. My guess is that you instantiate a COM object, which in turn connect
> >> back
> >> to SQL Server and try to modify the system table - something you cannot do in 2005.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> >> > Hi
> >> >
> >> > SQL Server 2005 Express.
> >> > 25 january 2007 I added a new procedure to my database using ole automation
> >> > procedures such as sp_OACreate and sp_OAMethod.
> >> >
> >> > These system procedures requires 'Ole Automation Procedures' to be enabled.
> >> > So I added the following to my installation script:
> >> > sp_configure 'show advanced options', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> > sp_configure 'Ole Automation Procedures', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> >
> >> > The script run fine and my procedue also runs fine.
> >> >
> >> > But suddenly today installaing a new database I get an error mesage:
> >> > "Ad hoc update to system catalogs is not supported"
> >> > when I run the commands above.
> >> > Does anyone now what has happend since january? I installed a SP2 of SQL
> >> > Server Express. Has there been any changes?
> >> >
> >> > Running "reconfigure with override" works but it is not recommended to use
> >> > "with override" I have read.
> >> >
> >> > Is there any other way of enabling 'Ole Automation Procedures' which is
> >> > allowed?
> >> >
> >> > Help is appreciated.
> >> >
> >> > Regards Kjell Arne Johansen
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||This is because you have the option "allow updates" set to 1 (this is
obsolete in 2005 and causes the error you are seeing when you do not use
WITH OVERRIDE. Run the following
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'allow updates, 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
You should now be able to run your original script with no errors.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
> Yes you are right.
> The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
> OVERRIDE works fine.
> Regards Kjell Arne Johansen
> "Tibor Karaszi" wrote:
>> Are you saying that below code, in itself, generates the error you
>> posted? I just tried on my sp2
>> with GDR, no error message...
>> sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE;
>> GO
>> sp_configure 'Ole Automation Procedures', 1;
>> GO
>> RECONFIGURE;
>> GO
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
>> in message
>> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>> > Hi
>> >
>> > This is the code causing the error message to occur when it is
>> > executed.
>> > A month ago it worked fine.
>> >
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> >
>> > Regards Kjell Arne Johansen
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> We need to know what or who is trying to modify the system tables.
>> >> Without seeing any code, we
>> >> can't
>> >> say where the problem it. My guess is that you instantiate a COM
>> >> object, which in turn connect
>> >> back
>> >> to SQL Server and try to modify the system table - something you
>> >> cannot do in 2005.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
>> >> wrote in message
>> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > SQL Server 2005 Express.
>> >> > 25 january 2007 I added a new procedure to my database using ole
>> >> > automation
>> >> > procedures such as sp_OACreate and sp_OAMethod.
>> >> >
>> >> > These system procedures requires 'Ole Automation Procedures' to be
>> >> > enabled.
>> >> > So I added the following to my installation script:
>> >> > sp_configure 'show advanced options', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> > sp_configure 'Ole Automation Procedures', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> >
>> >> > The script run fine and my procedue also runs fine.
>> >> >
>> >> > But suddenly today installaing a new database I get an error mesage:
>> >> > "Ad hoc update to system catalogs is not supported"
>> >> > when I run the commands above.
>> >> > Does anyone now what has happend since january? I installed a SP2
>> >> > of SQL
>> >> > Server Express. Has there been any changes?
>> >> >
>> >> > Running "reconfigure with override" works but it is not recommended
>> >> > to use
>> >> > "with override" I have read.
>> >> >
>> >> > Is there any other way of enabling 'Ole Automation Procedures' which
>> >> > is
>> >> > allowed?
>> >> >
>> >> > Help is appreciated.
>> >> >
>> >> > Regards Kjell Arne Johansen
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||> This is because you have the option "allow updates" set to 1
Good catch, Jasper. I never thought of trying with allow updates set, and I wouldn't have thought
that having it set would cause this strange error message...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
> This is because you have the option "allow updates" set to 1 (this is obsolete in 2005 and causes
> the error you are seeing when you do not use WITH OVERRIDE. Run the following
> exec sp_configure 'show advanced options', 1;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> sp_configure 'allow updates, 0;
> GO
> RECONFIGURE WITH OVERRIDE;
> GO
> You should now be able to run your original script with no errors.
>
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
> news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
>> Yes you are right.
>> The command RECONFIGURE causes this error to occur while RECONFIGURE WITH
>> OVERRIDE works fine.
>> Regards Kjell Arne Johansen
>> "Tibor Karaszi" wrote:
>> Are you saying that below code, in itself, generates the error you posted? I just tried on my
>> sp2
>> with GDR, no error message...
>> sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE;
>> GO
>> sp_configure 'Ole Automation Procedures', 1;
>> GO
>> RECONFIGURE;
>> GO
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
>> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>> > Hi
>> >
>> > This is the code causing the error message to occur when it is executed.
>> > A month ago it worked fine.
>> >
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> >
>> > Regards Kjell Arne Johansen
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> We need to know what or who is trying to modify the system tables. Without seeing any code,
>> >> we
>> >> can't
>> >> say where the problem it. My guess is that you instantiate a COM object, which in turn
>> >> connect
>> >> back
>> >> to SQL Server and try to modify the system table - something you cannot do in 2005.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
>> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > SQL Server 2005 Express.
>> >> > 25 january 2007 I added a new procedure to my database using ole automation
>> >> > procedures such as sp_OACreate and sp_OAMethod.
>> >> >
>> >> > These system procedures requires 'Ole Automation Procedures' to be enabled.
>> >> > So I added the following to my installation script:
>> >> > sp_configure 'show advanced options', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> > sp_configure 'Ole Automation Procedures', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> >
>> >> > The script run fine and my procedue also runs fine.
>> >> >
>> >> > But suddenly today installaing a new database I get an error mesage:
>> >> > "Ad hoc update to system catalogs is not supported"
>> >> > when I run the commands above.
>> >> > Does anyone now what has happend since january? I installed a SP2 of SQL
>> >> > Server Express. Has there been any changes?
>> >> >
>> >> > Running "reconfigure with override" works but it is not recommended to use
>> >> > "with override" I have read.
>> >> >
>> >> > Is there any other way of enabling 'Ole Automation Procedures' which is
>> >> > allowed?
>> >> >
>> >> > Help is appreciated.
>> >> >
>> >> > Regards Kjell Arne Johansen
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>|||Only reason I knew is because it happened to me :-) It took me a while to
figure out why I was getting errors doing reconfigures and to be honest I
don't remember setting allow updates on at all.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
http://sqlblogcasts.com/blogs/sqldbatips
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> This is because you have the option "allow updates" set to 1
> Good catch, Jasper. I never thought of trying with allow updates set, and
> I wouldn't have thought that having it set would cause this strange error
> message...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
>> This is because you have the option "allow updates" set to 1 (this is
>> obsolete in 2005 and causes the error you are seeing when you do not use
>> WITH OVERRIDE. Run the following
>> exec sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE WITH OVERRIDE;
>> GO
>> sp_configure 'allow updates, 0;
>> GO
>> RECONFIGURE WITH OVERRIDE;
>> GO
>> You should now be able to run your original script with no errors.
>>
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> http://sqlblogcasts.com/blogs/sqldbatips
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
>> in message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
>> Yes you are right.
>> The command RECONFIGURE causes this error to occur while RECONFIGURE
>> WITH
>> OVERRIDE works fine.
>> Regards Kjell Arne Johansen
>> "Tibor Karaszi" wrote:
>> Are you saying that below code, in itself, generates the error you
>> posted? I just tried on my sp2
>> with GDR, no error message...
>> sp_configure 'show advanced options', 1;
>> GO
>> RECONFIGURE;
>> GO
>> sp_configure 'Ole Automation Procedures', 1;
>> GO
>> RECONFIGURE;
>> GO
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
>> wrote in message
>> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
>> > Hi
>> >
>> > This is the code causing the error message to occur when it is
>> > executed.
>> > A month ago it worked fine.
>> >
>> > sp_configure 'show advanced options', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> > sp_configure 'Ole Automation Procedures', 1;
>> > GO
>> > RECONFIGURE;
>> > GO
>> >
>> >
>> > Regards Kjell Arne Johansen
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> We need to know what or who is trying to modify the system tables.
>> >> Without seeing any code, we
>> >> can't
>> >> say where the problem it. My guess is that you instantiate a COM
>> >> object, which in turn connect
>> >> back
>> >> to SQL Server and try to modify the system table - something you
>> >> cannot do in 2005.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
>> >> wrote in message
>> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
>> >> > Hi
>> >> >
>> >> > SQL Server 2005 Express.
>> >> > 25 january 2007 I added a new procedure to my database using ole
>> >> > automation
>> >> > procedures such as sp_OACreate and sp_OAMethod.
>> >> >
>> >> > These system procedures requires 'Ole Automation Procedures' to be
>> >> > enabled.
>> >> > So I added the following to my installation script:
>> >> > sp_configure 'show advanced options', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> > sp_configure 'Ole Automation Procedures', 1;
>> >> > GO
>> >> > RECONFIGURE;
>> >> > GO
>> >> >
>> >> > The script run fine and my procedue also runs fine.
>> >> >
>> >> > But suddenly today installaing a new database I get an error
>> >> > mesage:
>> >> > "Ad hoc update to system catalogs is not supported"
>> >> > when I run the commands above.
>> >> > Does anyone now what has happend since january? I installed a SP2
>> >> > of SQL
>> >> > Server Express. Has there been any changes?
>> >> >
>> >> > Running "reconfigure with override" works but it is not
>> >> > recommended to use
>> >> > "with override" I have read.
>> >> >
>> >> > Is there any other way of enabling 'Ole Automation Procedures'
>> >> > which is
>> >> > allowed?
>> >> >
>> >> > Help is appreciated.
>> >> >
>> >> > Regards Kjell Arne Johansen
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>|||Thank you both for your help.
I don't remember setting allow updates either.
Thanks again.
Regards Kjell Arne Johansen
"Jasper Smith" wrote:
> Only reason I knew is because it happened to me :-) It took me a while to
> figure out why I was getting errors doing reconfigures and to be honest I
> don't remember setting allow updates on at all.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> http://sqlblogcasts.com/blogs/sqldbatips
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OL$AQjdgHHA.4552@.TK2MSFTNGP04.phx.gbl...
> >> This is because you have the option "allow updates" set to 1
> >
> > Good catch, Jasper. I never thought of trying with allow updates set, and
> > I wouldn't have thought that having it set would cause this strange error
> > message...
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> > news:OumUYWcgHHA.4900@.TK2MSFTNGP05.phx.gbl...
> >> This is because you have the option "allow updates" set to 1 (this is
> >> obsolete in 2005 and causes the error you are seeing when you do not use
> >> WITH OVERRIDE. Run the following
> >>
> >> exec sp_configure 'show advanced options', 1;
> >> GO
> >> RECONFIGURE WITH OVERRIDE;
> >> GO
> >> sp_configure 'allow updates, 0;
> >> GO
> >> RECONFIGURE WITH OVERRIDE;
> >> GO
> >>
> >> You should now be able to run your original script with no errors.
> >>
> >>
> >> --
> >> HTH,
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> http://sqlblogcasts.com/blogs/sqldbatips
> >>
> >>
> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
> >> in message news:07F98FD2-5464-4F0D-ACEC-8FC4CF604FD2@.microsoft.com...
> >> Yes you are right.
> >>
> >> The command RECONFIGURE causes this error to occur while RECONFIGURE
> >> WITH
> >> OVERRIDE works fine.
> >>
> >> Regards Kjell Arne Johansen
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Are you saying that below code, in itself, generates the error you
> >> posted? I just tried on my sp2
> >> with GDR, no error message...
> >>
> >> sp_configure 'show advanced options', 1;
> >> GO
> >> RECONFIGURE;
> >> GO
> >> sp_configure 'Ole Automation Procedures', 1;
> >> GO
> >> RECONFIGURE;
> >> GO
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
> >> wrote in message
> >> news:AC5E975F-39E6-482A-9C1D-700C613AA3C6@.microsoft.com...
> >> > Hi
> >> >
> >> > This is the code causing the error message to occur when it is
> >> > executed.
> >> > A month ago it worked fine.
> >> >
> >> > sp_configure 'show advanced options', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> > sp_configure 'Ole Automation Procedures', 1;
> >> > GO
> >> > RECONFIGURE;
> >> > GO
> >> >
> >> >
> >> > Regards Kjell Arne Johansen
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> We need to know what or who is trying to modify the system tables.
> >> >> Without seeing any code, we
> >> >> can't
> >> >> say where the problem it. My guess is that you instantiate a COM
> >> >> object, which in turn connect
> >> >> back
> >> >> to SQL Server and try to modify the system table - something you
> >> >> cannot do in 2005.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com>
> >> >> wrote in message
> >> >> news:EAA6BCF7-ED40-41EF-9968-AB473829DBD8@.microsoft.com...
> >> >> > Hi
> >> >> >
> >> >> > SQL Server 2005 Express.
> >> >> > 25 january 2007 I added a new procedure to my database using ole
> >> >> > automation
> >> >> > procedures such as sp_OACreate and sp_OAMethod.
> >> >> >
> >> >> > These system procedures requires 'Ole Automation Procedures' to be
> >> >> > enabled.
> >> >> > So I added the following to my installation script:
> >> >> > sp_configure 'show advanced options', 1;
> >> >> > GO
> >> >> > RECONFIGURE;
> >> >> > GO
> >> >> > sp_configure 'Ole Automation Procedures', 1;
> >> >> > GO
> >> >> > RECONFIGURE;
> >> >> > GO
> >> >> >
> >> >> > The script run fine and my procedue also runs fine.
> >> >> >
> >> >> > But suddenly today installaing a new database I get an error
> >> >> > mesage:
> >> >> > "Ad hoc update to system catalogs is not supported"
> >> >> > when I run the commands above.
> >> >> > Does anyone now what has happend since january? I installed a SP2
> >> >> > of SQL
> >> >> > Server Express. Has there been any changes?
> >> >> >
> >> >> > Running "reconfigure with override" works but it is not
> >> >> > recommended to use
> >> >> > "with override" I have read.
> >> >> >
> >> >> > Is there any other way of enabling 'Ole Automation Procedures'
> >> >> > which is
> >> >> > allowed?
> >> >> >
> >> >> > Help is appreciated.
> >> >> >
> >> >> > Regards Kjell Arne Johansen
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>

Thursday, March 22, 2012

adding a column then using it in a script

Tuesday, March 20, 2012

Adding 0 to a column

Monday, March 19, 2012

add to data

add to data

add to data

Tuesday, March 6, 2012

Add new record

Add new record

Add new record

Sunday, February 19, 2012

Add article to Merge Repl. Publication

Sunday, February 12, 2012

Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

Ad hoc updates to system catalogs are not allowed.

Ad hoc update to system catalogs is not supported?

Ad hoc update to system catalogs is not supported?

Ad hoc update to system catalogs is not supported?

Accessing more than

Blog Archive

About Me