Accessing more than: system

Showing posts with label system. Show all posts

Thursday, March 29, 2012

Adding a ODBC link to many computers

Is there any way of adding an OBDC connection to multiple computers without
going to each system. I need to push out a new SQL Server connection, does
anyone know an easy way.
Thanks!
Use a DSN-less connection.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"HenryG" <HenryG@.discussions.microsoft.com> wrote in message
news:49B3EF04-D325-462C-9FFC-A79600D0CAD9@.microsoft.com...
> Is there any way of adding an OBDC connection to multiple computers
> without
> going to each system. I need to push out a new SQL Server connection,
> does
> anyone know an easy way.
> Thanks!
sql

Tuesday, March 27, 2012

Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed table
s
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and th
e
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:

> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we ha
ve
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split databas
e
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>

Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed tables
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and the
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:
> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we have
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split database
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>sql

Sunday, March 25, 2012

Adding a Distributor

I have a current merge replication system with a publisher server that is its
own distributor, and four remote subscriber servers. I now want to make the
present publisher/distributor a distibutor, and add a new publisher only
server. What is the easiest method?
Can I avoid having to recreate the whole system and remake the subscribers?
Thanks for advice.
You will have to script out your publications and subscriptions. Do a final
synchronization, drop them, and then migrate to the new publisher.
Then recreate everything.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"MDP" <MDP@.discussions.microsoft.com> wrote in message
news:0578784C-DA1B-40D5-9903-3F7228E79EA5@.microsoft.com...
>I have a current merge replication system with a publisher server that is
>its
> own distributor, and four remote subscriber servers. I now want to make
> the
> present publisher/distributor a distibutor, and add a new publisher only
> server. What is the easiest method?
> Can I avoid having to recreate the whole system and remake the
> subscribers?
> Thanks for advice.

Adding a Delete functionality to my Shopping Cart

Hello,

I'm in the progress of developing a shopping cart system that operates with an SQL database in Visual Web Developer 2005. I've managed to successfully add items to the cart and display them, but I'm having trouble providing the user with the option of removing items from the cart.

My understanding so far is that I've got to adjust the DELETE SQL statement of the data source. At first I was thinking along the lines of a simple statement:

DELETE * FROM ShoppingCart WHERE CartID=@.CartID AND CategoryID=@.CategoryID AND ProductID=@.ProductID

However, I've realised that the parameters I need for this SQL query aren't automatically passed in when a user clicks the "Delete" text of the Delete field (at least I think this is the problem).

The error message which I'm getting when I try to remove an item from the cart is as follows:

Incorrect syntax near '*'.

If anyone could let me know where I'm going wrong and point me in the right direction I'd be really grateful.

Thank you,

Luke

Hi Luke,

There should not be an asterisk (*) in your DELETE statement. If you remove that you might have better luck.|||

Thanks very much for your help - I thought it might have been a really stupid mistake like that! Anyway everything is working fine now.

Thanks again,

Luke

Thursday, March 22, 2012

Adding a "DB Version" Table: Suggestions?

I'm putting a little table in my DB that I'll call zstblVersion.

("..stbl" for System Table, "z" so it sinks to the bottom of the list and
doesn't look like something to do with the app)

The purpose is to let somebody look at the DB and divine which version they're
seeing.

So far, I've got 3 columns:

- VersionID
- VersionDescription (freeform text description of the changes in this version
of the DB)
- AppVersionRequired (numeric field, containing the lowest version of the front
end that will work with this version of the DB)

Opinions? Suggestions?
--
PeteCresswellHi

I have seen several application that use this sort of thing, and they can be
useful for support.

You may want to add entries for the release date, date upgraded/installed,
the username who upgraded, previous version number, OS (at time of
install!), possibly SQL Server versions (at time of install) etc

These could be put in a readonly filegroup, although you would need to do a
little more when doing the upgrades.

John

"(Pete Cresswell)" <x@.y.z> wrote in message
news:hkkeovomiv3jidkll7mda1jrkk4ptuc1p2@.4ax.com...
> I'm putting a little table in my DB that I'll call zstblVersion.
> ("..stbl" for System Table, "z" so it sinks to the bottom of the list and
> doesn't look like something to do with the app)
> The purpose is to let somebody look at the DB and divine which version
they're
> seeing.
> So far, I've got 3 columns:
> - VersionID
> - VersionDescription (freeform text description of the changes in this
version
> of the DB)
> - AppVersionRequired (numeric field, containing the lowest version of the
front
> end that will work with this version of the DB)
> Opinions? Suggestions?
> --
> PeteCresswell

Monday, March 19, 2012

Add user assembly in managed stored procedure

I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??

There are limitations to what you can do with CLR assemblies, that is the reason for the new SQL Server project template in VS 2005. Try the link below for more detailed info from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp

|||I found out how to do it, it can be done. Just add the user developed assembly to the SQL database assemblies and that will make it callable from a managed database object. The downsize as I see it is that you need to keep a repository of assemblies outside of the GAC...

Sunday, March 11, 2012

Add table and column descriptions/comments using script

Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?Yes. See sp_addextendedproperty in the Books Online
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> > Is it possible to add descriptions/comments to tables and columns using a
> > system stored procedure?
>
>

Thursday, March 8, 2012

Add Permission Stored Procedure!

Hi all,
Just wondering, is their a system stored procedure i can use to grant
permission to all objects in a specified db to a specified user ?
Cheers,
AdamAdam,
What is your goal - read access or modification capability? Might look at
the db_datareader and db_datawriter fixed databse roles.
HTH
Jerry
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:%23Xs4Z5d1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Just wondering, is their a system stored procedure i can use to grant
> permission to all objects in a specified db to a specified user ?
> Cheers,
> Adam
>|||Few hours ago:
http://groups.google.de/group/micro...9344927e9915da5
HTH, Jens Suessmeyer.

Add New Row Problems

This code runs with no errors.
The problem is nothing actually gets updated.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim mEmp As New YessClass1
Dim EmpID As Long
Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

'Dim mRdg As YessClass1 = New YessClass1
Dim lngID As Long
'This is to save a record to tblFeedback
Dim strSelect As String = "SELECT * FROM tblFeedback"
'now create the data adapter object and connect it to
'sql string and the connection
Dim dscmd As New SqlDataAdapter(strSelect, Conn)
' Load a data set.
ds = New DataSet
'data Adapter fill with dataset, name of dataset
dscmd.Fill(ds, "Feedback")
SqlConnection1.Close()
'get the EmployeeID and the Rounding RecordID
Try
mEmp = CType(Session("objEmp"), YessClass1)
EmpID = mEmp.Employee
Try
Dim dt As DataTable = ds.Tables.Item("FeedBack")
Dim rowFeedback As DataRow = dt.NewRow

With rowFeedback
.Item("RoundingID") = RdgID
.Item("QuestionID") = Me.lstQuestion.SelectedItem.Value
.Item("ResponseActionID") = Me.lstAction.SelectedItem.Value
.Item("EmployeeID") = Me.lstDirector.SelectedItem.Value
.Item("ActivityID") = Me.lstActivity.SelectedItem.Value
.Item("Feedback") = Me.txtFeedback.Text
If Me.ckReq.Checked = True Then
.Item("Acknowledgement") = 1
.Item("AckDate") = Now
End If
End With

dt.Rows.Add(rowFeedback)

Catch ex As Exception

Response.Write("Error occured" & ex.Message)
End Try
Catch ex As Exception
Response.Write("ERROR: " & ex.Message)
End Try
End SubWhere are you actually updating the database? Adding the row to the DataTable does not do it. You need to call .Update somewhere on the DataSet.

Sunday, February 12, 2012

Ad hoc updates to system catalogs are not enabled

Hi,
Some time ago, following a security recommendation, I deleted
sp_change_users_login. Now I want it back. I scripted it as create
from another server. When I try to run it in QA as sa I get "Ad hoc
updates to system catalogs are not enabled The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_change_users_login,
Line 197
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this.
Thanks,
PeterHi Peter,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Please try to perform the following SQL statements before you run the
statements for adding the stored procedure.
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
After adding the stored procedure, please perform the following SQL
statements for safe reason.
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
allow updates Option
Use the allow updates option to specify whether direct updates can be made
to system tables. By default, allow updates is disabled (set to 0), so
users cannot update system tables through ad hoc updates. Users can update
system tables using system stored procedures only. When allow updates is
disabled, updates are not allowed, even if you have the appropriate
permissions (assigned using the GRANT statement).
When allow updates is enabled (set to 1), any user who has appropriate
permissions can update system tables directly with ad hoc updates and can
create stored procedures that update system tables.
For more information regarding SP_CONFIGURE, please refer to the following
article on SQL Server Books Online.
Topic: "SP_CONFIGURE"
Topic: "allow updates Option"
Thanks for choosing Microsoft.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Peter,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup.
My name is
Billy and it's my pleasure to further assist you with this issue.
I believe Machael has pointed out the root cause of your issue and his solut
ion is accurate
and workable on your side. For your benefits, here I'd like to follow up wit
h something
important you should pay more attention to, as the system catalogs are very
critical to the
operation of SQL Server.
Please keep in mind that updating fields in system tables can prevent an ins
tance of SQL
Server from running or can cause data loss. If you create stored procedures
while the allow
updates option is enabled, those stored procedures always have the ability t
o update
system tables even after you disable allow updates. On production systems, y
ou should not
enable allow updates except under the direction of Microsoft Product Support
Services.
It is stongly recommend that you enable allow updates only in tightly contro
lled situations.
Prevent other users from accessing SQL Server while you are directly updatin
g system
tables by restarting an instance of SQL Server from the command prompt with
sqlservr -m.
This command starts an instance of SQL Server in single-user mode and enable
s allow
updates.
After successfully updating the system catalogs, please remember changing th
e allow
updates back to 0 AT ONCE, and then restart the instance services.
For more information on how to operate it in minimal configuration mode, ple
ase see the
following topic in Books Onlinie:
"Starting SQL Server with Minimal Configuration"
Thanks for choosing Microsoft.
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

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 29, 2012

Adding a ODBC link to many computers

Tuesday, March 27, 2012

Adding a new datafile

Adding a new datafile

Sunday, March 25, 2012

Adding a Distributor

Adding a Delete functionality to my Shopping Cart

Thursday, March 22, 2012

Adding a "DB Version" Table: Suggestions?

Monday, March 19, 2012

Add user assembly in managed stored procedure

Sunday, March 11, 2012

Add table and column descriptions/comments using script

Thursday, March 8, 2012

Add Permission Stored Procedure!

Add New Row Problems

Sunday, February 12, 2012

Ad hoc updates to system catalogs are not enabled

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?

Accessing more than

Blog Archive

About Me