?
Solved

The specified schema name "sys" either does not exist or you do not have permission to use it.

Posted on 2008-06-12
5
Medium Priority
?
7,048 Views
Last Modified: 2012-06-27
Not sure why this would appear.
I am trying to create a sproc in a user db. This sproc calls master.sys.xp_readerrorlog
I am SA on the server.

Have I got my path wrong (mater.sys.....) or is SA <> SA in 2000?!
0
Comment
Question by:QPR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:i2mental
ID: 21773887
try

master..sys.xp_readerrorlog

or

master.dbo.sys.xp_readerrorlog

0
 
LVL 29

Author Comment

by:QPR
ID: 21773961
exactly the same error (tried both versions)

BEGIN

   IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
   BEGIN
      RAISERROR(15003,-1,-1, N'securityadmin')
      RETURN (1)
   END
   
   IF (@p2 IS NULL)
       EXEC master..sys.xp_readerrorlog @p1
   ELSE
       EXEC master..sys.xp_readerrorlog @p1,@p2,@p3,@p4
END
0
 
LVL 29

Author Comment

by:QPR
ID: 21773983
ok red herring.
It was actually the create sp line (line 1) that was wrong even though the error claimed to be on line 31

CREATE PROC [sys].[sp_readerrorlog]
needed to be
CREATE PROC [sp_readerrorlog]

I was creating the sproc in a user db (not master)
I copied the code word for word from this article.....
http://www.mssqltips.com/tip.asp?tip=1476

Do you know why I got the error
0
 
LVL 8

Accepted Solution

by:
i2mental earned 2000 total points
ID: 21774018
If you were trying to name a sp sys.readerrorlog then you need to refer to it as [sys.readerrorlog]. The way you wrote it it was trying to refer to the sys (System) schema.  If you refer to it in braces, it is giving it the name 'sys.sp_readerrorlog'.

I would advise using your own naming scheme to avoid that being confused with a built in stored procedure. Also there is a slight performance hit when nameing a stored procedure starting with "sp_" as SQL server will first query the master table looking for it as those are what it uses for system store procs.
0
 
LVL 29

Author Comment

by:QPR
ID: 21774126
Good info, thanks.
The author of the article had it as
[sys].[sp_readerrorlog]

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question