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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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