Trouble scripting SQL database with renamed SP's
Hi...
Is there a way to force a syscomments update?
As SQL doesn't update syscomments on a rename operation, I need to make sure the syscomments table is correct before I run my SQLDMO scripting tool, otherwise some renamed SP's won't have a CREATE script at all.
Thanks!
Alex
Is there a way to force a syscomments update?
As SQL doesn't update syscomments on a rename operation, I need to make sure the syscomments table is correct before I run my SQLDMO scripting tool, otherwise some renamed SP's won't have a CREATE script at all.
Thanks!
Alex
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi!
But isn't any way to update/fix the SYSCOMMENTS?
I never knew about this and this project involved more than a dozen developers, I don't have a way to identify the objects that were renamed.
Another strange thing is that the Management Studio (SQL2005) generates the scripts correctly.
I read that the scripting functionalities use SQLDMO too... being true, where's the difference?
How can I currently fix this?
Thanks!
Alex
But isn't any way to update/fix the SYSCOMMENTS?
I never knew about this and this project involved more than a dozen developers, I don't have a way to identify the objects that were renamed.
Another strange thing is that the Management Studio (SQL2005) generates the scripts correctly.
I read that the scripting functionalities use SQLDMO too... being true, where's the difference?
How can I currently fix this?
Thanks!
Alex
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"I wonder if you could look for orphaned ids in the sys.syscomments table?"
Yep... that's a thought.
Still, I don't thing there are orphaned rows on the syscomments.
I say this because the old name appears related to the new name.
I don't have 2 CREATE statemens, one for the old name and one for the new, I only have one CREATE statement, so the link must be correct, only the name stored at syscomments is wrong.
This said, and without having any idea of the structure of syscomments at the moment, couldn't we validate the link and overwrite the name with the correct one?
Another strange things...
The DROP statement is generated correctly.
The comments and the IF NOT EXISTS statement before the CREATE is generated correctly.
Why is the CREATE statement the only wrong?
Example of a real script output:
/****** Object: StoredProcedure [dbo].[sp_SearchEntity] Script Date: 06/06/2007 18:15:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Sear
DROP PROCEDURE [dbo].[sp_SearchEntity]
/****** Object: StoredProcedure [dbo].[sp_SearchEntity] Script Date: 06/06/2007 18:21:06 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Sear
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[sp_SearchCustomer]
@Name nvarchar(200),
@ContributorNumber int,
@ErrorCode INT OUTPUT
as
..........
WHY?!?
Thanks!
Alex
Yep... that's a thought.
Still, I don't thing there are orphaned rows on the syscomments.
I say this because the old name appears related to the new name.
I don't have 2 CREATE statemens, one for the old name and one for the new, I only have one CREATE statement, so the link must be correct, only the name stored at syscomments is wrong.
This said, and without having any idea of the structure of syscomments at the moment, couldn't we validate the link and overwrite the name with the correct one?
Another strange things...
The DROP statement is generated correctly.
The comments and the IF NOT EXISTS statement before the CREATE is generated correctly.
Why is the CREATE statement the only wrong?
Example of a real script output:
/****** Object: StoredProcedure [dbo].[sp_SearchEntity] Script Date: 06/06/2007 18:15:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Sear
DROP PROCEDURE [dbo].[sp_SearchEntity]
/****** Object: StoredProcedure [dbo].[sp_SearchEntity] Script Date: 06/06/2007 18:21:06 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Sear
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE procedure [dbo].[sp_SearchCustomer]
@Name nvarchar(200),
@ContributorNumber int,
@ErrorCode INT OUTPUT
as
..........
WHY?!?
Thanks!
Alex
FROM http://www.simple-talk.com/community/blogs/andras/archive/2006/05/10/783.aspx
Note that SQL Server 2005s Management Studio, although being really slow, tries to help to solve problems arising from possible previous use of sp_rename. When one right clicks on a stored procedure (or any other textual object) and selects modify or script, the name of the procedure is replaced with the correct fully qualified name. So considering our above example, even though SQL Server still stores the original procedure definition that uses the name foo , Management Studio offers it for alter as [dbo].[bar]
Note that SQL Server 2005s Management Studio, although being really slow, tries to help to solve problems arising from possible previous use of sp_rename. When one right clicks on a stored procedure (or any other textual object) and selects modify or script, the name of the procedure is replaced with the correct fully qualified name. So considering our above example, even though SQL Server still stores the original procedure definition that uses the name foo , Management Studio offers it for alter as [dbo].[bar]
refer:
http://support.microsoft.com/kb/243198