Accessing more than: ole

Showing posts with label ole. Show all posts

Sunday, February 12, 2012

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
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
> >>
> >
>
>

Ad hoc update to system catalogs is not supported?

Ad hoc access to OLE DB provider MSOLAP has been denied

Hi:

When I am trying to connect to OLAP I am getting this error...Ad hoc access to OLE DB provider MSOLAP has been denied.You must access this provider through a linked server.

I have configured the linked server.How do i fix this...?

Thanks,

Pramod

This is not an AS specific issue and out of my area of expertise, but it appears to be a security issue. Doing a quick search on the error message it appears by default that only SQL system admins are allowed to use OpenRowset/OpenDataSource queries. The information at http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.oledbprovidersettings.disallowadhocaccess.aspx may be usefull in resolving this.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied

Trying to open a SQL Server query but am getting this message:

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Heres the script:

Insert into OpenDataSource(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
Extended properties=Excel 5.0')...[PivotSh$]
(Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month, Qtr,
Description_Contract, Activity_Type_Grouped, Sum_of_Spells)

SelectProvider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen Financial_Month between '1' and '3' Then 'Qtr 1'
When Financial_Month between '4' and '6' Then 'Qtr 2'
When Financial_Month between '7' and '9' Then 'Qtr 3'
When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End as Qtr,
Description_Contract,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
SUM(Total_Spells) as Sum_of_Spells

From[Busobjects].Capacity_Planning.dbo.tbl_BaseLine_UHL_Spells_Tre nd_0708 a

Inner Join OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="H:\Imran\Capacity Planning 0708\UHL Spells Apr 05 to Sept 07 TEST.xls";
Extended properties=Excel 5.0')...[SPECIALTY TREND by Month$] as PR
On PR.P1 = Commissioner_Code
AND PR.P2 = LSS_Flag
AND PR.P3 = Description_Contract
AND PR.P4 = (Case When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type END)

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_HRG_Tariff_0708 d
on a.HRG_Code = d.HRGCode

Groupby Provider_Code, Commissioner_Code, LSS_Flag, Financial_Year, Financial_Month,
CaseWhen Financial_Month between '1' and '3' Then 'Qtr 1'
When Financial_Month between '4' and '6' Then 'Qtr 2'
When Financial_Month between '7' and '9' Then 'Qtr 3'
When Financial_Month between '10' and '12' Then 'Qtr 4' Else '' End,
/*Specialty_Code,*/ Description_Contract, --Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End--, HRG_Code + ': '+ d.HRGDesc,
--Specialised_Services_Code + ': '+c.Description

Quote:

Originally Posted by flickimp

Trying to open a SQL Server query but am getting this message:

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Heres the script:

From[Busobjects].Capacity_Planning.dbo.tbl_BaseLine_UHL_Spells_Tre nd_0708 a

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

LeftOuter Join [Busobjects].Capacity_Planning.dbo.tbl_Refs_IP_HRG_Tariff_0708 d
on a.HRG_Code = d.HRGCode

Try the following query to read the data from excel sheet

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Documents and Settings\Administrator\Desktop\rolemenu.xls',
'SELECT * FROM [Sheet1$] where MenuItemId is not null')

make sure your excelsheet is on server...

for more information visit the following link

http://www.databasejournal.com/features/mssql/article.php/3692831

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Sunday, February 12, 2012

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?

Ad hoc access to OLE DB provider MSOLAP has been denied

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Accessing more than

Blog Archive

About Me