Fresh install of W2K and SQL Server 2000 w/ Service Pack 3. Due to
security reasons, I'm supposed to remove a whole plethora of
permissions.
So I go into Enterprise Manager, Master table, "User-Defined
Functions", and right-click on one of the functions. I'm supposed to
remove the "Select" permission for "Public" from a function named
"fn_list_extendedproperty".
So I find the function, right-click it, choose "Properties", and get
the following error:
"Microsoft SQL-DMO
Error 0: [SQL-DMO]Unable to retrieve the text from
UserDefinedFunction object
'system_function_schema.fn_list_extendedproperty'."
Not a big deal, so I think. This happened on another box before and I
just removed the permissions successfully anyway. But this time, it
won't let me remove the permissions. I get the following error:
"Microsoft SQL-DMO (ODBC SQLState 42S02) [Note: "s" may be a "5"]
Error 208: Invalid object name
'system_function_schema.fn_list_extendedproperty'
Any idea what's going on here?
I found this thread on Google, but I'm not sure it applies:
http://groups.google.com/groups?q=%2B%22SQL+Server%22+%2B%22Unable+to+retrieve+the+text%22&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=u0Z0STHnCHA.2364%40TK2MSFTNGP12&rnum=1
(BTW, the owner is 'system_function_schema', not 'dbo'... is that
significant?)
Also, I have Admin rights on both the local machine and within SQL
Server.
Please advise. Thank you.Hi Doug,
The article applies in this situation. We cannot view system user-defined
functions using Enterprise Manager. According to my test, changing
permissions in Enterprise Manager also encounters the same error. As far as
the functions feature is concerned, THERE IS NO SUCH SCHEMA AS
"system_function_schema". When using SYSTEM functions, the syntax is to
prefix the (single-part!) name with two colons, as:
select * from ::fn_listextendedproperty(NULL, 'user', 'dbo','table',
'authors', NULL, NULL)
I think we need to change syspermissions table directly if we want to
change permissions for system user-defined functions currently.
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: Doug <None@.hotmail.com>
| Newsgroups: microsoft.public.sqlserver.server
| Subject: Can't change permissions on user-defined function (SQL2K)
| Organization: No
| Reply-To: Nothanks
| Message-ID: <4722jv075dgkf36blujp8uo958m18262kt@.4ax.com>
| X-Newsreader: Forte Agent 1.93/32.576 English (American)
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Lines: 39
| Date: Wed, 06 Aug 2003 13:58:04 GMT
| NNTP-Posting-Host: 141.158.179.85
| X-Complaints-To: abuse@.verizon.net
| X-Trace: nwrdny03.gnilink.net 1060178284 141.158.179.85 (Wed, 06 Aug 2003
09:58:04 EDT)
| NNTP-Posting-Date: Wed, 06 Aug 2003 09:58:04 EDT
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-lei1.dfn.de!news-fra1.dfn.de!npeer.de.kpn-eurorings.net!news-out.n
uthinbutnews.com!propagator2-sterling!news-in-sterling.nuthinbutnews.com!cyc
lone1.gnilink.net!spamkiller2.gnilink.net!nwrdny03.gnilink.net.POSTED!53ab27
50!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:299768
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Fresh install of W2K and SQL Server 2000 w/ Service Pack 3. Due to
| security reasons, I'm supposed to remove a whole plethora of
| permissions.
|
| So I go into Enterprise Manager, Master table, "User-Defined
| Functions", and right-click on one of the functions. I'm supposed to
| remove the "Select" permission for "Public" from a function named
| "fn_list_extendedproperty".
|
| So I find the function, right-click it, choose "Properties", and get
| the following error:
|
| "Microsoft SQL-DMO
| Error 0: [SQL-DMO]Unable to retrieve the text from
| UserDefinedFunction object
| 'system_function_schema.fn_list_extendedproperty'."
|
| Not a big deal, so I think. This happened on another box before and I
| just removed the permissions successfully anyway. But this time, it
| won't let me remove the permissions. I get the following error:
|
| "Microsoft SQL-DMO (ODBC SQLState 42S02) [Note: "s" may be a "5"]
| Error 208: Invalid object name
| 'system_function_schema.fn_list_extendedproperty'
|
| Any idea what's going on here?
|
| I found this thread on Google, but I'm not sure it applies:
|
|
http://groups.google.com/groups?q=%2B%22SQL+Server%22+%2B%22Unable+to+retrie
ve+the+text%22&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=u0Z0STHnCHA.2364%40TK2MSFTNG
P12&rnum=1
|
| (BTW, the owner is 'system_function_schema', not 'dbo'... is that
| significant?)
|
| Also, I have Admin rights on both the local machine and within SQL
| Server.
|
| Please advise. Thank you.
|
|sql
Showing posts with label pack. Show all posts
Showing posts with label pack. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Can't apply Sql 2k server service pack 4 on XP workstation.
After install sql 2000 server - client tools on Win XP workstation, it won't
allow to install sql 2000 server Service Pack 4 (it will quit to desktop
immediately without any error or warning) But we can intall it on win2000
workstation.
- Is it normal that can't apply or not need to apply service pack on winXP
clients?
- How can I check which sql 2000 server service pack applied on clients?
No that wouldn't be considered normal. You do want to apply
the service packs to PC where the client tools are
installed.
You can open Query Analyzer and go to Help, About to
determine the build number of the client tools.
Build numbers and corresponding service packs are listed in
this article:
http://support.microsoft.com/kb/321185
-Sue
On Thu, 14 Jun 2007 09:46:14 +0800, "Pleo"
<pleoel@.hotmail.com> wrote:
>After install sql 2000 server - client tools on Win XP workstation, it won't
>allow to install sql 2000 server Service Pack 4 (it will quit to desktop
>immediately without any error or warning) But we can intall it on win2000
>workstation.
>- Is it normal that can't apply or not need to apply service pack on winXP
>clients?
>- How can I check which sql 2000 server service pack applied on clients?
>
sql
allow to install sql 2000 server Service Pack 4 (it will quit to desktop
immediately without any error or warning) But we can intall it on win2000
workstation.
- Is it normal that can't apply or not need to apply service pack on winXP
clients?
- How can I check which sql 2000 server service pack applied on clients?
No that wouldn't be considered normal. You do want to apply
the service packs to PC where the client tools are
installed.
You can open Query Analyzer and go to Help, About to
determine the build number of the client tools.
Build numbers and corresponding service packs are listed in
this article:
http://support.microsoft.com/kb/321185
-Sue
On Thu, 14 Jun 2007 09:46:14 +0800, "Pleo"
<pleoel@.hotmail.com> wrote:
>After install sql 2000 server - client tools on Win XP workstation, it won't
>allow to install sql 2000 server Service Pack 4 (it will quit to desktop
>immediately without any error or warning) But we can intall it on win2000
>workstation.
>- Is it normal that can't apply or not need to apply service pack on winXP
>clients?
>- How can I check which sql 2000 server service pack applied on clients?
>
sql
Thursday, March 8, 2012
Cannot Update Identity Column
I am running SQL Server 2000 with Service pack 3. I have a database with a
transactional publication that is not setup for immediate or queued updating.
The tables I am replicating have a primary key that is also an identity
column. The publication pushes a subscription to a database on the same
instance of SQL Server. The articles are setup to “Keep the existing table
unchanged”. The tables in the subscriptions are exact copies of the
publication tables except the identity column is setup: (not for
replication). I also have the Identity Seed setup so that neither the
publication nor the subscription will use the same Identity. I have put in a
Check Constraint on all tables to make sure of this. With this setup I can
update the publication table and it replicates fine to the subscription table
I also can update and insert records into the subscription table directly,
but when I update the publication table I get this error when it tries to
replicate to the subscription table: "Cannot Update Identity Column 'Table
Name'. All I want to do is replicate my production databases to an exact copy
that I will use for testing. So I never want to have the subscription update
the publication, but I do want to be able to insert and update the
subscription for testing and have the subscription be up to date with the
publication database. Am I missing something to get this setup correctly or
is there a better way to do this?
Thanks for your help,
Brandon
what columns are you updating? By chance you might be updating the pk or the
identity column. Some apps update each column including the pk or identity
column.
There are two strategies to handle this problem. The first is to comment out
the identity column update you see in the second part of the proc. The
second strategy is to replace the update proc that SQL Server uses with the
proc generated by the output of this command.
sp_scriptdynamicupdproc 1
where 1 is the article id obtained from sysarticles.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>I am running SQL Server 2000 with Service pack 3. I have a database with a
> transactional publication that is not setup for immediate or queued
> updating.
> The tables I am replicating have a primary key that is also an identity
> column. The publication pushes a subscription to a database on the same
> instance of SQL Server. The articles are setup to "Keep the existing table
> unchanged". The tables in the subscriptions are exact copies of the
> publication tables except the identity column is setup: (not for
> replication). I also have the Identity Seed setup so that neither the
> publication nor the subscription will use the same Identity. I have put in
> a
> Check Constraint on all tables to make sure of this. With this setup I can
> update the publication table and it replicates fine to the subscription
> table
> I also can update and insert records into the subscription table directly,
> but when I update the publication table I get this error when it tries to
> replicate to the subscription table: "Cannot Update Identity Column 'Table
> Name'. All I want to do is replicate my production databases to an exact
> copy
> that I will use for testing. So I never want to have the subscription
> update
> the publication, but I do want to be able to insert and update the
> subscription for testing and have the subscription be up to date with the
> publication database. Am I missing something to get this setup correctly
> or
> is there a better way to do this?
> Thanks for your help,
> Brandon
>
|||I was running a simple update statement in Query Analyzer: "Update Test set
TestColumn = 25 where TestID = 1". The stored procedure which is created by
default was trying to update the primary key like you said, so I commented
that out of the sp and that worked.
Thanks!
Brandon
"Hilary Cotter" wrote:
> what columns are you updating? By chance you might be updating the pk or the
> identity column. Some apps update each column including the pk or identity
> column.
> There are two strategies to handle this problem. The first is to comment out
> the identity column update you see in the second part of the proc. The
> second strategy is to replace the update proc that SQL Server uses with the
> proc generated by the output of this command.
> sp_scriptdynamicupdproc 1
> where 1 is the article id obtained from sysarticles.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Brandon" <Brandon@.discussions.microsoft.com> wrote in message
> news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>
>
transactional publication that is not setup for immediate or queued updating.
The tables I am replicating have a primary key that is also an identity
column. The publication pushes a subscription to a database on the same
instance of SQL Server. The articles are setup to “Keep the existing table
unchanged”. The tables in the subscriptions are exact copies of the
publication tables except the identity column is setup: (not for
replication). I also have the Identity Seed setup so that neither the
publication nor the subscription will use the same Identity. I have put in a
Check Constraint on all tables to make sure of this. With this setup I can
update the publication table and it replicates fine to the subscription table
I also can update and insert records into the subscription table directly,
but when I update the publication table I get this error when it tries to
replicate to the subscription table: "Cannot Update Identity Column 'Table
Name'. All I want to do is replicate my production databases to an exact copy
that I will use for testing. So I never want to have the subscription update
the publication, but I do want to be able to insert and update the
subscription for testing and have the subscription be up to date with the
publication database. Am I missing something to get this setup correctly or
is there a better way to do this?
Thanks for your help,
Brandon
what columns are you updating? By chance you might be updating the pk or the
identity column. Some apps update each column including the pk or identity
column.
There are two strategies to handle this problem. The first is to comment out
the identity column update you see in the second part of the proc. The
second strategy is to replace the update proc that SQL Server uses with the
proc generated by the output of this command.
sp_scriptdynamicupdproc 1
where 1 is the article id obtained from sysarticles.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>I am running SQL Server 2000 with Service pack 3. I have a database with a
> transactional publication that is not setup for immediate or queued
> updating.
> The tables I am replicating have a primary key that is also an identity
> column. The publication pushes a subscription to a database on the same
> instance of SQL Server. The articles are setup to "Keep the existing table
> unchanged". The tables in the subscriptions are exact copies of the
> publication tables except the identity column is setup: (not for
> replication). I also have the Identity Seed setup so that neither the
> publication nor the subscription will use the same Identity. I have put in
> a
> Check Constraint on all tables to make sure of this. With this setup I can
> update the publication table and it replicates fine to the subscription
> table
> I also can update and insert records into the subscription table directly,
> but when I update the publication table I get this error when it tries to
> replicate to the subscription table: "Cannot Update Identity Column 'Table
> Name'. All I want to do is replicate my production databases to an exact
> copy
> that I will use for testing. So I never want to have the subscription
> update
> the publication, but I do want to be able to insert and update the
> subscription for testing and have the subscription be up to date with the
> publication database. Am I missing something to get this setup correctly
> or
> is there a better way to do this?
> Thanks for your help,
> Brandon
>
|||I was running a simple update statement in Query Analyzer: "Update Test set
TestColumn = 25 where TestID = 1". The stored procedure which is created by
default was trying to update the primary key like you said, so I commented
that out of the sp and that worked.
Thanks!
Brandon
"Hilary Cotter" wrote:
> what columns are you updating? By chance you might be updating the pk or the
> identity column. Some apps update each column including the pk or identity
> column.
> There are two strategies to handle this problem. The first is to comment out
> the identity column update you see in the second part of the proc. The
> second strategy is to replace the update proc that SQL Server uses with the
> proc generated by the output of this command.
> sp_scriptdynamicupdproc 1
> where 1 is the article id obtained from sysarticles.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Brandon" <Brandon@.discussions.microsoft.com> wrote in message
> news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>
>
Saturday, February 25, 2012
Cannot start sql agent in 2005
I get this message when I try to start a sql 2005 server agent. Im
running windows 2003 service pack 1 and sql 2005 service pack 1. The
strange thing is the agent and sql service are running as a domain
account with sysadmin privileges.
Can anyone help?
SQLServerAgent could not be started (reason: SQLServerAgent must be
able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
member of the SysAdmin role).You can see the login attributes used by Agent if you in SSMS right-click Agent, the Connection
page. Make sure that this account/login exists and a login in SQL Server and that it has sysadmin
privileges in SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blakmk" <M.G.Blakey@.googlemail.com> wrote in message
news:1162379472.947639.182570@.b28g2000cwb.googlegroups.com...
>I get this message when I try to start a sql 2005 server agent. Im
> running windows 2003 service pack 1 and sql 2005 service pack 1. The
> strange thing is the agent and sql service are running as a domain
> account with sysadmin privileges.
> Can anyone help?
> SQLServerAgent could not be started (reason: SQLServerAgent must be
> able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
> member of the SysAdmin role).
>
running windows 2003 service pack 1 and sql 2005 service pack 1. The
strange thing is the agent and sql service are running as a domain
account with sysadmin privileges.
Can anyone help?
SQLServerAgent could not be started (reason: SQLServerAgent must be
able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
member of the SysAdmin role).You can see the login attributes used by Agent if you in SSMS right-click Agent, the Connection
page. Make sure that this account/login exists and a login in SQL Server and that it has sysadmin
privileges in SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blakmk" <M.G.Blakey@.googlemail.com> wrote in message
news:1162379472.947639.182570@.b28g2000cwb.googlegroups.com...
>I get this message when I try to start a sql 2005 server agent. Im
> running windows 2003 service pack 1 and sql 2005 service pack 1. The
> strange thing is the agent and sql service are running as a domain
> account with sysadmin privileges.
> Can anyone help?
> SQLServerAgent could not be started (reason: SQLServerAgent must be
> able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
> member of the SysAdmin role).
>
Cannot start sql agent in 2005
I get this message when I try to start a sql 2005 server agent. Im
running windows 2003 service pack 1 and sql 2005 service pack 1. The
strange thing is the agent and sql service are running as a domain
account with sysadmin privileges.
Can anyone help?
SQLServerAgent could not be started (reason: SQLServerAgent must be
able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
member of the SysAdmin role).You can see the login attributes used by Agent if you in SSMS right-click Ag
ent, the Connection
page. Make sure that this account/login exists and a login in SQL Server and
that it has sysadmin
privileges in SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blakmk" <M.G.Blakey@.googlemail.com> wrote in message
news:1162379472.947639.182570@.b28g2000cwb.googlegroups.com...
>I get this message when I try to start a sql 2005 server agent. Im
> running windows 2003 service pack 1 and sql 2005 service pack 1. The
> strange thing is the agent and sql service are running as a domain
> account with sysadmin privileges.
> Can anyone help?
> SQLServerAgent could not be started (reason: SQLServerAgent must be
> able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
> member of the SysAdmin role).
>
running windows 2003 service pack 1 and sql 2005 service pack 1. The
strange thing is the agent and sql service are running as a domain
account with sysadmin privileges.
Can anyone help?
SQLServerAgent could not be started (reason: SQLServerAgent must be
able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
member of the SysAdmin role).You can see the login attributes used by Agent if you in SSMS right-click Ag
ent, the Connection
page. Make sure that this account/login exists and a login in SQL Server and
that it has sysadmin
privileges in SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Blakmk" <M.G.Blakey@.googlemail.com> wrote in message
news:1162379472.947639.182570@.b28g2000cwb.googlegroups.com...
>I get this message when I try to start a sql 2005 server agent. Im
> running windows 2003 service pack 1 and sql 2005 service pack 1. The
> strange thing is the agent and sql service are running as a domain
> account with sysadmin privileges.
> Can anyone help?
> SQLServerAgent could not be started (reason: SQLServerAgent must be
> able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a
> member of the SysAdmin role).
>
Sunday, February 19, 2012
Cannot setup SQL Server 2000 on W2003 Server
I have installed Windows Server 2003 Web Edition from scratch on a new system disk. I have updated with the latest service pack and security upgrades.
When I try to install SQL Server 2000 Standard Edition, the initial setup dialog Autoruns OK. I click on "SQL Server 2000 components", then on "Install Database Server". For a second or so a "Demoshield" pop-up appears, but that vanishes and nothing else happens.
Can anyone suggest what might be wrong? I am running on P4 1.7Ghz but only have 128Meg memory until the new chips arrive. I have around 15Gig disk space free on the drive I want to install on.
Thanks
Looks like this is not a supported configuration.
http://support.microsoft.com/default.aspx?scid=kb;en-us;819258
Subscribe to:
Posts (Atom)