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
6,717 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now