QOD 17th Nov
-
Ok, yes, I got it *wrong*, and it is actually interesting to know that I *can* version my SP's, but by saying that DTS Packages is *not* the correct answer, does that mean that the question author believes that the DTS Versioning is better than "limited versioning capability"???
Steve.
-
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 -
Steve & Mark are both absolutely right!
The answer is WRONG!
-
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.
-
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 ArchitectI can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.
-
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
According to everyone I know I "do something with computers?" for a living, so there you go. -
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.
-
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 -
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.
-
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 -
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 -
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
-
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!
-
Since when is a DTS package considered DDL? This is new to me!!
"Keep Your Stick On the Ice" ..Red Green -
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
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply