April 2, 2009 at 10:03 am
I am using DMO and vb6 to script my database. It gives me create scripts. I would like to generate ALTER scripts for the stored procedures. Is there any way to do this? I am scripting out SQL 2005 database.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
September 22, 2009 at 11:05 am
There's no way to do it in DMO directly AFAIK. I do it in one of my products, and it's not easy. You need to write a lexical parser for SQL to be able to achieve it sensibly. And then it still doesn't work in a lot of products. For example - try creating this proc
CREATE PROC
schema -- schema
.
proc -- procname
AS
print 'hello'
Then rename it, then script it out using SSMS. Fail.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 22, 2009 at 8:03 pm
Matt: FYI, I am pretty sure that SQL Server 2000 bug is fixed in SQL Server 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2009 at 1:42 am
Barry: Nope - not even in 2k8! 🙂
Output from ssms 2k8 after sp_rename 'proce', 'procm':
CREATE PROC
[dbo].[procm] -- schema
.
proce -- procname
AS
print 'hello'
GO
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 10:03 am
Matt Whitfield (9/22/2009)
... And then it still doesn't work in a lot of products. For example - try creating this procCREATE PROC
schema -- schema
.
proc -- procname
AS
print 'hello'
Then rename it, then script it out using SSMS. Fail.
As far as I can tell Matt, it DOES NOT work this way, at least not anymore.
First of all, the script that you list as an example is rejected as invalid syntax on both 2005 and 2008. So I have tried the following valid syntax:
CREATE PROC [dbo].[procm]
AS
print 'hello'
GO
When I rename it and script it from SSMS on SQL Sever 2008, I get this:
CREATE PROC [dbo].[proce]
AS
print 'hello'
GO
And when I rename it and script it from SSMS on SQL Sever 2005, I get this:
CREATE PROC [dbo].[proce]
AS
print 'hello'
As you can see, both are correct. So again, as far as I can tell, thi shas been fixed in 2005 and 2008. I do remember that this happened on SQL Server 2000, and that when 2005 came out I tested explicitly for this problem, and could not reproduce it any longer.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2009 at 10:07 am
That's a bit weird.
I can create this on my 2000, 2005 and 2008 instances. Not invalid syntax at all. Of course - i did replace the 'schema' with 'dbo' and 'proc' with 'procname' as obviously 'schema' is a keyword, and not the name of a valid schema, and again 'proc' is a keyword, and not a valid procedure name... Try the following:
CREATE PROC
dbo -- schema
.
procname -- procname
AS
print 'hello'
GO
sp_rename 'procname', 'newprocname'
Then try scripting out newprocname...
🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 10:17 am
Matt Whitfield (9/23/2009)
That's a bit weird.I can create this on my 2000, 2005 and 2008 instances. Not invalid syntax at all. Of course - i did replace the 'schema' with 'dbo' and 'proc' with 'procname' as obviously 'schema' is a keyword, and not the name of a valid schema, and again 'proc' is a keyword, and not a valid procedure name... Try the following:
CREATE PROC
dbo -- schema
.
procname -- procname
AS
print 'hello'
GO
sp_rename 'procname', 'newprocname'
Then try scripting out newprocname...
🙂
My results:
CREATE PROC
[dbo].[newprocname] -- schema
.
procname -- procname
AS
print 'hello'
???
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2009 at 10:22 am
It's because it detects that the name is different to the name that the text of the procedure believes it is - but then the parser doesn't bother to parse past the first identifier to see if there are any more identifier parts present before the AS or the parameter list.
Thus, instead of...
CREATE PROC
[dbo].[newprocname] -- procname
AS
print 'hello'
...you get...
CREATE PROC
[dbo].[newprocname] -- schema
.
procname -- procname
AS
print 'hello'
...which isn't valid.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 12:26 pm
Anyway, the short answer is, no, you can't script an ALTER in DMO.
And the 'renamed procedure' bug still exists in the client tools, just in a different way - i.e. it does not cope with the allowed syntactic constructs.
Also note that it's not a server bug - because as far as I know, no version of SQL Server has ever claimed that sp_rename would try and change the object meta data for text-based objects where the name is part of that text (procedures, views, functions, triggers).
So in fact, it's a 'feature' of the client tools that they at least *try* to correct the object name. 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 1:05 pm
RBarryYoung (9/23/2009)
Matt Whitfield (9/23/2009)
That's a bit weird.I can create this on my 2000, 2005 and 2008 instances. Not invalid syntax at all. Of course - i did replace the 'schema' with 'dbo' and 'proc' with 'procname' as obviously 'schema' is a keyword, and not the name of a valid schema, and again 'proc' is a keyword, and not a valid procedure name... Try the following:
CREATE PROC
dbo -- schema
.
procname -- procname
AS
print 'hello'
GO
sp_rename 'procname', 'newprocname'
Then try scripting out newprocname...
🙂
My results:
CREATE PROC
[dbo].[newprocname] -- schema
.
procname -- procname
AS
print 'hello'
???
Actually, it's the line-breaks in the middle of the schema.name that's messing it up. When I replaced the line-breaks with spaces, it worked fine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 23, 2009 at 1:25 pm
RBarryYoung (9/23/2009)
Actually, it's the line-breaks in the middle of the schema.name that's messing it up. When I replaced the line-breaks with spaces, it worked fine.
It's the comments actually - the change of lexical state.
Try
CREATE PROC
dbo /* schema */ . procname2 /* procname */
AS
print 'hello'
GO
sp_rename 'procname2', 'newprocname2'
No line breaks. Just as much fail.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 24, 2009 at 6:28 am
Terri-92562 (4/2/2009)
I am using DMO and vb6 to script my database. It gives me create scripts. I would like to generate ALTER scripts for the stored procedures. Is there any way to do this? I am scripting out SQL 2005 database.
Terri, have you tried using something like this:
sql = Replace(sql, "CREATE PROC", "ALTER PROC")
before you write the string to your output file?
September 24, 2009 at 6:31 am
How would that handle a procedure which created another procedure dynamically?
e.g.
CREATE PROCEDURE stproc_CreateMyProc
AS
EXEC ('CREATE PROCEDURE stproc_MyProc as SELECT ''hello'' ')
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 24, 2009 at 8:33 am
WILLIAM MITCHELL (9/24/2009)
Terri-92562 (4/2/2009)
I am using DMO and vb6 to script my database. It gives me create scripts. I would like to generate ALTER scripts for the stored procedures. Is there any way to do this? I am scripting out SQL 2005 database.Terri, have you tried using something like this:
sql = Replace(sql, "CREATE PROC", "ALTER PROC")
before you write the string to your output file?
No, I have not tried that. I was thinking that DMO must have an option to script ALTER PROC because in SSMS that is an option.
What I have now is a VB6 program that uses DMO to generate scripts for all my database objects:
For Each oDatabaseObject In oDatabase.StoredProcedures
If oDatabaseObject.SystemObject Or Left(oDatabaseObject.Name, 3) = "dt_" Then
'do nothing (bypass system objects)
Else
iScriptOptions = SQLDMOScript_Default + SQLDMOScript_Drops
strScript = oDatabaseObject.Script(iScriptOptions)
strVssItemName = oDatabaseObject.Name & ".sql"
strFileName = strCurrDirectory & "\" & oDatabaseObject.Name & ".sql"
On Error Resume Next
Call ScriptItemToFile(strFileName, strScript, strVssItemName)
nProc = nProc + 1
nTotal = nTotal + 1
End If
ndx = ndx + 1
ProgressBar.Value = ndx
Next
The output looks like this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspHIS_DeleteCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[uspHIS_DeleteCategory]
GO
/**************************************************************************************
Name:uspHIS_DeleteCategory
Author:Dung Ly
Date:06/2007
Purpose: Delete a Category Record.
Returns: 0 = SID not found, record does not exist. No error.
-101 = attempt to delete record with SID < 100 was not allowed.
-103 when record with given SID/Version is not found. Usually
means record update by another user.
otherwise, SQL error code.
****************************************************************************************
Revision History:
06/23/2008 - -Cathy Greensfelder - Add check for record not found.
***************************************************************************************/
CREATE PROCEDURE dbo.uspHIS_DeleteCategory
(@CategorySID int
,@Version int)
AS
....
What I want to achieve is this:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID(N'[dbo].[uspHIS_DeleteCategory]', N'P') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE dbo.uspHIS_DeleteCategory
AS RAISERROR(''Dummy Proc Called'',16,62)')
IF @@ERROR = 0
PRINT 'uspHIS_DeleteCategory created'
END
GO
/**************************************************************************************
Name:uspHIS_DeleteCategory
Author:Dung Ly
Date:06/2007
Purpose: Delete a Category Record.
Returns: 0 = SID not found, record does not exist. No error.
-101 = attempt to delete record with SID < 100 was not allowed.
-103 when record with given SID/Version is not found. Usually
means record update by another user.
otherwise, SQL error code.
****************************************************************************************
Revision History:
06/23/2008 - -Cathy Greensfelder - Add check for record not found.
***************************************************************************************/
ALTER PROCEDURE dbo.uspHIS_DeleteCategory
(@CategorySID int
,@Version int)
AS
...
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
September 24, 2009 at 8:41 am
Is there a particular reason you want to use ALTER as opposed to DROP then CREATE?
Do you have permissions you'd like to maintain?
As I said, changing CREATE statements to ALTER statements *reliably* is not trivial.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply