jasonboetcher
asked on
Error 208 - Invalid Object Name
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_rptSL R_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
if exists (select * from sysobjects where id = object_id('dbo.spRPT_rptSL
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
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
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.
ASKER
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....
How many characters can an object name contain in SQL 6.5? (The name used is 21.)
ASKER
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...
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?
Maybe you already have another object, perhaps a different type of object, with the same name?
ASKER
I checked all of the other objects in the database and none have that name...
listening...
Is the server configured to be case sensetive ? In which case the object names must match exactly
ASKER
I don't know where to check that setting, but I don't see any case inconsistencies within my code...
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
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
ASKER
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_updC ustomerDat es') and sysstat & 0xf = 4)
drop procedure dbo.spPROF_updCustomerDate s
GO
/****** Object: Stored Procedure dbo.spPROF_updCustomerDate s 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
if exists (select * from sysobjects where id = object_id('dbo.spPROF_updC
drop procedure dbo.spPROF_updCustomerDate
GO
/****** Object: Stored Procedure dbo.spPROF_updCustomerDate
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
ELSE MAX(dtClosed) END
FROM Profrefnos
GROUP BY lCustomerID) ref
WHERE ref.lCustomerID = ProfCustomers.lCustomerID
GO
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
& 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
ASKER
I ran that query and the name field does NOT have dbo in front of it....
By the way, here is alternative way to check for SP existence:
IF OBJECT_ID('dbo.spRPT_rptSL R_Imprt_LC ','P') IS NOT NULL
or even just:
IF OBJECT_ID('dbo.spRPT_rptSL R_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[_]%'
IF OBJECT_ID('dbo.spRPT_rptSL
or even just:
IF OBJECT_ID('dbo.spRPT_rptSL
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[_]%'
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.
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.
It really is strange. Could you also possibly copy/paste the exact error msg you're getting?
ASKER
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_rptSL R_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....
"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_rptSL
drop procedure dbo.spRPT_rptSLR_Imprt_LC
GO
When I run the rest is when I get the above error....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
are you calling dbo.spRPT.rptSLR_Imprt_LC or dbo.spRPT_rptSLR_Imprt_LC (notice the underscore instead of the dot)????
Matt.