SQL Server Performance Forum – Threads Archive
Generating Object creation scripts
Hello Friends, I need your inputs to fix the issue related to dependency objects. I am generating SP creation scripts from Enterprise Manager->Database->SP->All Task->Generate SQL scripts for all SP. But when i am executing the scripts in other database I get below message for some SP. Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘ABC2’. The stored procedure will still be created. I understand above message i am getting because when i have created ‘ABC1’ stored procedure first without creating ‘ABC2’.For more detail look below Create procedure ABC1as
exec ABC2
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO Create procedure ABC2
as
select * from table1
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO How to avoid such type of error while generating the scripts. So that objects which is referenced inside any sp should be created first. Your inputs is needed. Thanks and Regards
Ravi Kumar
Its because you are referring that stored procedure in one of your stored procedure. First create the required procedure to avoid such errors. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I also understand that I have to create the scripts of the sp which is refered first but how to avoid such type of error if you are generating scripts for bulk of SP.I can’t manaully check SP reference.
How about running generated script twice? First time you will not have dependencies updated but I believe second run will fix the problem.
I had the same issue some time back.
I use sQL Digger and found out what are the secondory SP and ran them first and them ran the others. I didn’t have any solutions
quote:Originally posted by SQL2000DBA
Hello Friends,
I need your inputs to fix the issue related to dependency objects. I am generating SP creation scripts from Enterprise Manager->Database->SP->All Task->Generate SQL scripts for all SP. But when i am executing the scripts in other database I get below message for some SP.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘ABC2’. The stored procedure will still be created.
I understand above message i am getting because when i have created ‘ABC1’ stored procedure first without creating ‘ABC2’.For more detail look below
Create procedure ABC1
as
exec ABC2
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO Create procedure ABC2
as
select * from table1
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO How to avoid such type of error while generating the scripts. So that objects which is referenced inside any sp should be created first. Your inputs is needed. Thanks and Regards
Ravi Kumar
as
exec ABC2
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO Create procedure ABC2
as
select * from table1
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO How to avoid such type of error while generating the scripts. So that objects which is referenced inside any sp should be created first. Your inputs is needed. Thanks and Regards
Ravi Kumar
May try with other methods that were mentioned inhttp://www.sql-server-performance.com/absolutenm/templates/?a=9&z=1 here. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Hello Mmarovic, I have tried running scripts two times then also i got same dependency.Reason for getting error is because when you generate scripts using EM, it first generate drop statement all object and then create statement statement.So if we run the scripts second time also we will get same dependecny error. Thanks and Regards
Ravi Kumar
You are right, I mixed it with internal tool my (former) company use to release schema scripts. There, each object is dropped and immediatelly created after that.
]]>