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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly‚ÄĒanytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

17 Experts available now in Live!

Get 1:1 Help Now