?
Solved

Error 208 - Invalid Object Name

Posted on 2002-04-17
20
Medium Priority
?
15,401 Views
Last Modified: 2011-08-18
I'm trying to write my first ever stored procedure and I get an invalid obect name 'dbo.spRPT.rptSLR_Imprt_LC' when I try to run it.  I have pasted my stored procedure below.  What am I missing???

if exists (select * from sysobjects where id = object_id('dbo.spRPT_rptSLR_Imprt_LC') and sysstat & 0xf = 4)
     drop procedure dbo.spRPT_rptSLR_Imprt_LC
GO

CREATE PROCEDURE spRPT_rptSLR_Imprt_LC
     @sBeginDate     char(8),
     @sEndDate     char(8)
AS

DECLARE
     @dtBeginDate smalldatetime,
     @dtEndDate smalldatetime

SELECT  refno            = main.refno,
     amt            = main.amt,
     applicant     = main.applicant,
     acctparty_mnem     = main.acctparty_mnem,
     Date_Entered     = main.DateEntered,
     Date_Issued     = (SELECT MIN(logtime) FROM LOC_IBSWIN..mleventlog mleventlog
                  WHERE mleventlog.refno = main.refno
                           AND eventname = 'ISSAPPL'),
     user_name      = ISNULL(main.user_name, '<Not Entered>'),
     userid          = main.userid


FROM

 (SELECT DISTINCT
     refno           = lc.refno,
        amt           = lc.amt,
     applicant      = lc.applicant,
     acctparty_mnem      = lc.acctparty_mnem,
     DateEntered      = internal.scf_rcd_dt,
     user_name      = ISNULL(usertable.user_name, '<Not Entered>'),

       userid          = mleventlog.userid
   FROM
      LOC_IBSWIN..lc lc INNER JOIN LOC_IBSWIN..internal internal ON lc.refno = internal.refno
      INNER JOIN LOC_IBSWIN..mleventlog mleventlog ON internal.refno = mleventlog.refno
      INNER JOIN LOC_IBSWIN..usertable usertable ON mleventlog.userid = usertable.user_id
   WHERE
     mleventlog.eventname = 'ISSENTL'
     AND mleventlog.refno IN(SELECT DISTINCT refno
                    FROM LOC_IBSWIN..mleventlog mleventlog
                       WHERE (refno LIKE 'INO%' OR refno LIKE 'IGT%')
                         AND eventname = 'ISSAPPL'
                         AND logtime >= @dtBeginDate
                         AND logtime <= @dtEndDate)) main
ORDER BY userid

GO

GRANT  EXECUTE  ON dbo.spRPT_rptSLR_Imprt_LC TO public
GO

GRANT  EXECUTE  ON dbo.spRPT_rptSLR_Imprt_LC TO IBSREPORT
GO

GRANT  EXECUTE  ON dbo.spRPT_rptSLR_Imprt_LC TO IBSUSER
GO
0
Comment
Question by:jasonboetcher
[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
  • 8
  • 5
  • 3
  • +3
20 Comments
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6947873
just a quick point,

are you calling dbo.spRPT.rptSLR_Imprt_LC or dbo.spRPT_rptSLR_Imprt_LC (notice the underscore instead of the dot)????

Matt.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6947886
Perhaps you need to verify the procedure name wherever it appears.  In the error message it looks as if a '.' instead of an '_' was used in the name, which would make it a "bad" name.  But that error is not in the code you posted -- that code ran fine on both SQL 7.0 and SQL 2000 databases.
0
 

Author Comment

by:jasonboetcher
ID: 6948323
I did mis-type my error; I apologize; it should read "invalid obect name 'dbo.spRPT_rptSLR_Imprt_LC'.  I am using SQL 6.5 if that makes a difference....
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6948442
How many characters can an object name contain in SQL 6.5?  (The name used is 21.)
0
 

Author Comment

by:jasonboetcher
ID: 6948640
I looked at other stored procedure in the database that run fine and there is one that is 29 characters.  I could not find any documentation on maximum name lengths...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6948791
There really doesn't look to be anything wrong with the commands per se.

Maybe you already have another object, perhaps a different type of object, with the same name?
0
 

Author Comment

by:jasonboetcher
ID: 6948815
I checked all of the other objects in the database and none have that name...
0
 
LVL 2

Expert Comment

by:ScottNewman
ID: 6949090
listening...
0
 
LVL 4

Expert Comment

by:TheSpirit
ID: 6950561
Is the server configured to be case sensetive ? In which case the object names must match exactly
0
 

Author Comment

by:jasonboetcher
ID: 6950797
I don't know where to check that setting, but I don't see any case inconsistencies within my code...
0
 
LVL 2

Expert Comment

by:ScottNewman
ID: 6950847
I'm not sure how object names are stored in 6.5 (been a bit), but I'm thinking it may be the dbo. in front of the name.  In 7+ it's not stored with the owner name appended to it in sysobjects, so I suspect it may be that way as well in 6.5.  Makes sense relationally.

Try taking the dbo. off the name, and if you want the owner to be dbo run it under a login that has dbo status.

Hope that helps!!!
Newman
0
 

Author Comment

by:jasonboetcher
ID: 6951259
Here is a stored procedure that already existed in the database that ran clean for me; it has the dbo....

if exists (select * from sysobjects where id = object_id('dbo.spPROF_updCustomerDates') and sysstat & 0xf = 4)
     drop procedure dbo.spPROF_updCustomerDates
GO

/****** Object:  Stored Procedure dbo.spPROF_updCustomerDates    Script Date: 1/30/02 3:05:52 PM ******/
CREATE PROCEDURE spPROF_updCustomerDates
AS

UPDATE     ProfCustomers
SET     dtClosed = ref.Closed,
     dtOpened = ref.Opened
FROM     (SELECT     lCustomerID,
          Opened = MIN(dtOpened),
          Closed = CASE      WHEN MIN(ISNULL(dtClosed,"01/01/1900")) = "01/01/1900" THEN NULL    
                    ELSE MAX(dtClosed) END
      FROM     Profrefnos
      GROUP BY lCustomerID) ref
WHERE     ref.lCustomerID = ProfCustomers.lCustomerID
GO
0
 
LVL 2

Expert Comment

by:ScottNewman
ID: 6951283
try running select * from sysobjects where sysstat
& 0xf = 4

Look at the name field and see if it has the .dbo on the front, just for ****s and giggles.

I can't see why if the other procedure worked, this one does not.  I'm baffled.

Newman
0
 

Author Comment

by:jasonboetcher
ID: 6951301
I ran that query and the name field does NOT have dbo in front of it....
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6951308
By the way, here is alternative way to check for SP existence:

IF OBJECT_ID('dbo.spRPT_rptSLR_Imprt_LC','P') IS NOT NULL

or even just:

IF OBJECT_ID('dbo.spRPT_rptSLR_Imprt_LC') IS NOT NULL

as long as you don't other types of objects the same, which seems likely to be true.

Similarly, to list all sps from sysobjects, you can do the following:

SELECT *
FROM sysobjects
WHERE xtype = 'P'
AND name NOT LIKE 'dt[_]%'
0
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6951736
what happens if you run the script seperately

eg.

run the if exists bit, then the create.

which bit gets the error the drop, the create or the permissions bit.

or do you get the error executing the sp.

this does seem very strange.

Matt.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6951886
It really is strange.  Could you also possibly copy/paste the exact error msg you're getting?
0
 

Author Comment

by:jasonboetcher
ID: 6953366
Here is the exact error message I get:

"Microsoft SQL-DMO

Error 208: [SQL Server] Invalid object name 'dbo.spRPT_rptSLR_Imprt_LC'."

This part of the sp runs without error:

if exists (select * from sysobjects where id = object_id('dbo.spRPT_rptSLR_Imprt_LC') and sysstat & 0xf = 4)
     drop procedure dbo.spRPT_rptSLR_Imprt_LC
GO

When I run the rest is when I get the above error....
0
 

Accepted Solution

by:
jasonboetcher earned 0 total points
ID: 6954607
Ok, I got it to work.  I took off the GRANT EXECUTE statements and it ran clean.  After it ran I then added the GRANT EXECUTE statements and it ran clean again.  Any ideas why this happens like this?????
0
 

Expert Comment

by:CleanupPing
ID: 9280613
jasonboetcher:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Independent Software Vendors: 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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

764 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