November 16, 2003 at 11:34 pm
Stored Procedures? Oh, come on... that answer's just plain silly. Where in BOL does it state that the ability to append a number to a stored proc name is for the support of a "versioning" capability? Nowhere. The appending of a number is a design consideration to allow all SP's to be dropped with a single DROP.
This use of the SP number for "versioning" is maybe an "idea worth considering" but it's fraught with danger. How many times would all versions of an SP get dropped when some dopey DBA (eg. me) submits what they think is a simple DROP PROC statement?
Cheers,
- Mark
Cheers,
- Mark
November 17, 2003 at 1:58 am
Steve & Mark are both absolutely right!
The answer is WRONG!
November 17, 2003 at 1:59 am
I think that this particular stored procedure facility would be best called "instancing".
I certainly wouldn't call it a versioning facility, primitive or otherwise.
I have experimented in the past with have a stored procedure for a table and having version 1 doint the get, version 2 doing the set etc. to try and create and interface.
As Mark says, the problem comes when you delete the stored procedure. It is basically an accident waiting to happen.
November 17, 2003 at 2:12 am
ALright floks, Let's hear from the guys running this show. What is your reference for this particular question. This answer looks suspiciously like someones interpetation of what the sp numbering can do and not anything from the actual literature.
Richard L. Dawson
Sql Server Support Engineer
ICQ# 867490
Richard L. Dawson
Microsoft Sql Server DBA/Data Architect
I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
November 17, 2003 at 2:23 am
I got it wrong too, in full knowleged of ordinal numbers SPs and the version behaviour of DTS I picked DTS.
Never a fan of complaining becaue I got it wrong, I still have to say that the answer here is rubbish. The ordinal numbers on SPs can be quite useful for a number of things, usually when you want to build a whole set of SPs on the fly to do parts of the same job, and then drop them in one go. I have never heard of it used for versioning. You might as well just append the number to the end of the sproc. It's a bad idea because if you call an ordinaled SP without the number the first (ie mysp;1) is executed, so you end up using the oldest version of the code.
DTS packages do, on the other hand (the correct one), have a versioning capability. Make one, make a few changes and then right click it and go down to "Versions..." in the context menu and there it is. You can't execute the old version, but you can open it and save it under a new name.
Ahh well, nay mind
Keith Henry
DBA/Developer/BI Manager
Keith Henry
November 17, 2003 at 4:29 am
Ok I am a proponent (big word for the day) ok stored procedure grouping. There biggest advantage are to break out code so that individual execution plans can be stored for situation many folks do
if
begin
code here
end
else
begin
alternate code here
end
but to call that limited revisioning is not correct. That is in no way a function of goruping but a fact someone can utilize if they choose sobut to suggest that would also be to suggest that you could rename a table, view or most any other object with appending a date to create a limited versioning.
The question presents a concept not followed as norm and not a standard so therefore does not truely apply to the question at hand. DTS is the only one that supports any versioning whatsoever, all the others can be done by various means of choice based on their actual functionality.
November 17, 2003 at 5:50 am
I didn’t know about this little feature of stored procedures, so I find it quite intriguing. However, it doesn’t “feel” right as a versioning control. I would consider it highly likely that the base version “spYourProcedure” would be called by mistake, instead of “spYourProcedure;#”. Also, the next Create Procedure without a “version number” overwrites the whole shebang (“shebang” is technical jargon for the entire procedure).
I would think the versioning approach similar to the one suggested by Randy Dyess in http://www.sql-server-performance.com/rd_sp_text.asp is a truer versioning strategy.
Larry Ansley
Atlanta, GA
Larry Ansley
Atlanta, GA
November 17, 2003 at 6:39 am
I thought this was a no-brainer, which is why I was a little suspicious when it was worth 2 points instead of 1. I don't mind trick questions, but this one really is pretty obscure. And, I think there could arguably be two correct answers.
November 17, 2003 at 6:50 am
Ok, I got it wrong !?!? too. But I will have to join the rest of this posters because "VERSIONING" is something way past that functionality.
Can you get rid of just one "VERSION"?
I can do that with ANY version-aware product, so I hope the above makes it clear that "Versioning" is not correct in this context!
Just my 2 cents
* Noel
November 17, 2003 at 7:05 am
As keithh said, DTS packages have versioning built in. Sprocs do not. If you want versioning, use a source control package! The suggestion to use the instancing features for version control is a very bad idea.
--
Adam Machanic
whoisactive
November 17, 2003 at 7:37 am
quote:
Ok, I got it wrong !?!? too. But I will have to join the rest of this posters because "VERSIONING" is something way past that functionality.Can you get rid of just one "VERSION"?
I can do that with ANY version-aware product, so I hope the above makes it clear that "Versioning" is not correct in this context!
Just my 2 cents
Yes buy doing
DROP PROCEDURE SPNAME;number
November 17, 2003 at 8:28 am
One more for the "I got it wrong?!!" crowd. I've been doing a lot of DTS work and one of my biggest complaints is that you can accidentally save an old version of your DTS ove a new one if you open up more than one designer. Luckily there's versioning to let you get back the one you wanted but given this feature it's primative at best
One other perspective on this QOD is the use of the word "object". I think if the question had specified "DATABASE OBJECT" we might have all picked stored procedure as a DTS package is not a DB object in the same sense that a Table, View or Stored Proc is. I really hate it when a language issue wnds up in a wrong answer but it sure seems to happen a lot around here!
November 17, 2003 at 8:29 am
Since when is a DTS package considered DDL? This is new to me!!
"Keep Your Stick On the Ice" ..Red Green
November 17, 2003 at 12:03 pm
OK, since I submitted the question, I have to agree that DTS was probably a poor alternate choice - I was not looking to trip people up, but give people some cause to look this up. But here is the quote from SQL Server 2000 Performance and Tuning (MS Press), which is where I ran across this "feature":
"Another benefit of SQL Server stored procedures for developers is the version information. In SQL Server you can place a semi-colon and a numeric identifier after the stored procedure name. You can use this numeric identifier as a versioning tool, which allows developers to stagger deployment throughout the enterprise. Essentially, if sp_mystoredproc;2 is called by the newest version of the application while sp_mystoredproc;1 (the ";1" is assumed if no number is given) is called, then both versions of the application might continue to work, even if the number of columns, result sets or data types differ." (p. 322).
FWIW, I did specify in my submission that I was investigating the utility of this "feature", as I am dubious of its utility.
Silly or not, it's true, and it IS in BOL: look at the [number] argument in CREATE PROCEDURE. I honestly don't know what they were thinking, but it's there.
So: sorry for the wording.
Edited by - R. Kevin Gunther-Canada on 11/17/2003 12:08:05 PM
Edited by - R. Kevin Gunther-Canada on 11/17/2003 12:08:58 PM
November 17, 2003 at 12:24 pm
Ok IN SQL BOL it states the following
quote:
;numberIs an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement. For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.
The key is this "Is an optional integer used to group procedures of the same name".
In other words can is designed to group related procedures of the same name. This is not a versioning feature. The Author of the book needs to redo his wording and state he has found a side bennifit to himself by using the grouping part of stored procedures creation to allow limited versioning.
Also as SQL is still based in part upon the original Sybase egine you will find this in their documentation
quote:
Procedure groupsThe optional semicolon and integer number after the name of the procedure in the create procedure and execute statements allow you to group procedures of the same name so that they can be dropped together with a single drop procedure statement.
Procedures used in the same application are often grouped this way. For example, you might create a series of procedures called orders;1, orders;2, and so on. The following statement would drop the entire group:
drop proc orders
Once procedures have been grouped by appending a semicolon and number to their names, they cannot be dropped individually. For example, the following statement is not allowed:
drop proc orders;2
To run Adaptive Server in the evaluated configuration, you must prohibit grouping of procedures. This prohibition ensures that every stored procedure has a unique object identifier and can be dropped individually. To disallow procedure grouping, a System Security Officer must reset the allow procedure grouping configuration parameter. For information, see the System Administration Guide.
Still no mention of versioning, it is plan and simple a way to use a common name for multiple possilities making it easy to drop all at once if you might ever need to. This is a poor question, does present an option but it is not being a common use or documented feature will vary rarly be answered, plus as stated given the DTS option will definently not draw a look due to it's non-standard nature.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply