Cannot add rows to sysdepends...
-
I am writing a new sproc (e.g. sp_sproc1) which calls to other sprocs in it. When the new sproc is called I receive the statement,
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sp_sprocIn'. The stored procedure will still be created.
I can't figure out why this is.
First I dropped both procedures from the database and created sp_sprocIn, then created sp_sproc1; I still receive an error. Could someone please give me some other reasons this may happen.
Thank you, Brett
-
This is basically a warning message, however try to create a script with both the stored procedures together in a single file with the called procedure at the top and calling procedure below it. It should work
Prasad Bhogadi
www.inforaise.com -
#1 creating sp_name stored-procedures are not good. The system tries to search master 1st and then your user database
#2 Are you explicitly creating as dbo and looking for dbo in your EXEC? i.e. EXEC mydb.dbo.sp_name
#3 Sounds like you are dropping creating in the correct order but I would check to make sure that you are in the proper database as well. i.e. USE mydb...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thank you for the responses, I have over 160 sprocs in this database stating with sp_, so it's a little late now. I have tried mydb.dbl.sp_name and it didn't make a difference.
Like I said earlier, this sproc calls two others and only this one is generating the warning. The sproc in the error is being called from other sprocs and this warning didn't come up. I know that I can continue on with my life as the sproc is called, but I like references and I really want to figure out what is causing this.
-
Prasad already gave you the answer. Just create the called procs first and then the master one last and you'll be fine.
run this script to recreate your situation
create proc a
as
exec b
go
print 'proc A was created : this is where you get the error'
go
create proc b
as
select 0
go
print 'proc B was created : no error here'
go
print 'drop proc a and b'
drop proc a
drop proc b
go
create proc b
as
select 0
go
print 'proc B was created : you get no error because proc b doesn''t need any uncreated proc to work'
go
create proc a
as
exec b
go
print 'proc A was created : since proc B is already created, sql server can add the dependency in sysdepends and you get no err msg'
drop proc a
drop proc b
-
Thank you for the responce, Remi
This is the whole reason I posted this; I did recreate the sprocs as you listed and I still receive the warning.
Thank you for your time and effort.
-
You're supposed to get the warning only in the first create of my script. This shows why you get the error (a needs b and b is no created yet). However if you create them in the reverse order you won't get any error message. The only way you could still be getting the same error message is that the sp is recursive and therefore you'll always get the error message and there's nothing you can do about that. Maybe you can post the code you are using so we can really see what's going on.
-
So There isn't anything else that could call this?
Both sprocs being called have been in the database for weeks, hense the reason that I am confused on why this is happening. Is there a way I can look deaper (log file or something) into why the warning is occuring.
This is what I did. The contents of the sprocs are not rocket science.
create sproc a
as
select 0
go
create sproc b
as
select 1
go
create sproc c
as
exec a
exec b
select 2
go
and the error/warning is on b.
-
No I'm talking about the code of your procs.
EDITED
N/m I just reread your code and if the layout is like this then you shouldn't be getting the error message. But then again it's not the real code so we are obviously missing something.
-
Brett, I tried doing the same but I didn't get any errors.
Try using
exec sp_depends c
to view dependencies of procedure c.
-
Dyslexia strikes again. I am embarrassed to admit it, but I will anyway.
sproc is sp_checkDeductionsAtDiv
referenced as sp_checkDeducitonsAtDiv
I new it just didn't make sense.
Thanks for the help.
-
Recursive algorithm or error in proc?
-
Don't be embarrased it happens to me usually when my cafeine levels are not high enough
* Noel -
That's why I should start drinking coffee.. would give me an excuse for those kind of days...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply