XML FeedsThis question came up recently so I decided to blog about it
If you have your stored procedures in source control and you want to see if they are the same as the one on the server, all you need to do is script out the procs and do a diff right?
Let's see what happens, create this proc
- Create procedure prTest
- as
- set nocount on
- if exists (select 1 from sys.procedures where name = 'prTest')
- print 'yes'
- else
- print'no'
Now script it out, you do this by finding the proc under your database-->programmability-->stored procedures. Right click on the proc, select script procedure as and then choose create to and pick a new window
Your procedure will look like this
- /****** Object:StoredProcedure [dbo].[prTest] Script Date: 04/28/2009 10:07:19 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- Create procedure [dbo].[prTest]
- as
- set nocount on
- if exists (select 1 from sys.procedures where name = 'prTest')
- print 'yes'
- else
- print'no'
Do you see the problem? The first line has a comment and this will make your proc unique every time you script it out. Fortunately you can disable this by unchecking include descriptive headers. The process to do this is different for SQL Server 2000, 2005 and 2008
For SQL Server 2000 it is under the formatting tab in the Generate SQL Script object (see image below)
For SQL Server 2005 it is under Tools-->Options-->Scripting(see image below)
For SQL Server 2008 it is under Tools-->Options-->SQL Server Object Explorer-->Scripting(see image below)
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Interesting... But I may be missing why this would be an issue. Can you elaborate? 
chopstik ,if you script out the proc store it in source control, script out the same proc 5 minutes later also store it in source control and then you do a diff they will look different because the first comment will have a different timestamp on it
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.