We help IT Professionals succeed at work.

Trouble scripting SQL database with renamed SP's

698 Views
Last Modified: 2008-04-24
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
Comment
Watch Question

Commented:
Not that I know of
refer:
http://support.microsoft.com/kb/243198
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alexandre SimõesSoftware Architect
CERTIFIED EXPERT

Author

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alexandre SimõesSoftware Architect
CERTIFIED EXPERT

Author

Commented:
"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_SearchEntity]') AND type in (N'P', N'PC'))
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_SearchEntity]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

CREATE procedure [dbo].[sp_SearchCustomer]
@Name nvarchar(200),
@ContributorNumber int,
@ErrorCode  INT OUTPUT
as
..........



WHY?!?

Thanks!
Alex

Commented:
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]
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.