I've written this procedure that is a meta data manager if you will. It looks at a vocabulary table and will create or alter tables to be in line with the meta data. It works great and has for a couple of years now. However, I just rewrote it in an effort to make it more compact, efficient, etc. Now, our system uses at least three databases. One contains shared data, the next holds only system data for the app like individual user settings, etc (atslogin is the name) then would be an actual database for client data or accounts as we call them. Clients could have one or 100 of those. Please, no comments about moving everything in to one DB as that is not always possible to to hierarchy of multiple business models.
Now, on to the issue. This procedure used to exists in the local database (one or many) now I keep it in atslogin so in effect, it looks "down" on all other databases and does it's thing. It adds columns, expands them, creates tables, views, keys blah blah blah.
Here is the issue. When it creates a new column the ordinal position is out of whack. I've got a table where I keep dropping the last column, run my procedure to add it and find that the ordinal position has increased by one each time. Add it and the value is 48 for example. Drop it and add it again and it is now 49 and all the while there is a gap between say 47 and 49. This is being written to syscolumns and the view INFORMATION_SCHEMA.[columns].
This is a big deal because if I find columns are out of order I wont attempt to alter the table. Trouble is the columns are in the proper order, I just can buy what the system is saying. Anyone ever seen this? Even if I add a column as the db owner I'm seeing this. This is SQL 2k. Below is the exact version
------------------------------------------------------------------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)ooohhh dba-one, a padwon problem this is. meaningless the order of the data in the database is as Jedi Kaiser will readily remind you he will.|||Personally, some things are just not meant to be automated in my opinion
You'd have to post some code snipets though on what you are doing.
I use ERWin to do what you are doing, but I usually unload, drop and create the objects, then load|||Yoda say this ordinal problem not be a 0 to 1 issues. The voc file reads 0 for the first position while the system stuffs begins at 1. You didn't really think a Jedi master like me would be so stupid as to over look that did you?|||exec usp_madeof 'wcsub'
select colid,* from syscolumns where object_name(id)='wcsub' order by colid
Both will return the following in the results:
DOHFILING varchar 1 48
FORMTYPE varchar 2 53
(from sysobjects)
48 DOHFILING
53 FORMTYPE
Now, usp_made of is a procedure I wrote. It returns results in a fashion like Oracle's "desc" function. All that procedure does is read the INFORMATION_SCHEMA.[columns] view and order by ordinal position. Now, everytime I drop formtype and re add the column 53 will become 54 and so on. dohfiling will remain 48. This is nuts.|||Personally, some things are just not meant to be automated in my opinion
I very much agree. When I find columns out of order I refuse to automate the process. However, the columns are not out of order technically. I just can believe what syscolumns or other entries read. Even running alter table .. add column ... in the local DB as the owner is causing this so I can even remove my procedure from the matter.|||I tore out a couple hairs this week trying to get a Data Dictionary Collection script to work. Eventually, I just dumped the idea of checking columns by column ID and resorted to sorting the lower cased names of the columns and going by that. I figured if the column name exists, then I am OK. If it is not there at all, then I have to delete the column from the data dictionary. Or, if I find a column that is out of order, it must have been added, so in the data dictionary it goes. If I went by columnID I could be dropping good columns and re-adding them later on, when I find them again at the "end" of the table. Would that work for you?
EDIT: Bottom line is you have to work around the quirks in your RDBMS. And a non-contiguous set of column IDs counts as a quirk to me.|||Here read this.
http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
Once I read that, it only re-enforced dropping and re-creating the tables|||This has never happened and it is not an acceptable flaw of any RDBMS. Besides, my application expects things to be in a certain order and if it isn't, it will puke. I didn't design it that way but it is the way of things. If the table is created wrong then fine, manual intervention. If the system tables are wrong then how can I buy anything else they may tell me?|||This application is run all over the place at my clients sites. If it were an in house app I wouldn't mind dropping and recreating tables but that is horribly impractical in this case. Plus, I've had a few programmers here attempt to be clever and do things like that only to see it result in data loss. I can't attempt to automate the recreation of tables and moving data around. Too much risk for my taste.|||Personally, some things are just not meant to be automated in my opinionSo true. Experience is the difference between knowing what CAN be done, and what SHOULD be done. "Fail-safe systems fail by failing to fail safely."|||This application is run all over the place at my clients sites.
I don't understand, I would have a script that would run all the sql and apply the changes.
What kind of development lifecycle do you use?
And what's the frontt end written and how do you deploy that?|||The app is a VB/.Net app for insurance claims. It is a very complicated application as well. Scripts are fine in many cases but calls to my procedure are built in to it. When we make changes to table structures we simply send the meta data table. The app will then call my procedure to inspect and alter if needed, any involved tables. No single client has a standard version of our app because we will customize anything they want but the databases are largely the same with the exception of "user tables"
If we send an update that would say depend on a new table or two or many, we send the meta data file. The app will then test for existence and if they are not there my procedure creates the tables, index, keys, so on. Why send a meta data file and then have to manually do everything?
It seems everyone thinks I'm some kind of dummy for doing this! Our app has been doing things like this for nearly 20 years and the last 10 has been with Oracle or SQL Server depending on which DB the client wants to run it against. I just don't understand how this issue has only appeared now and not before.|||I can't be the only one who has run in to this can I?|||No single client has a standard version of our app because we will customize anything they want...and that is the root of the problem. Trying to be all things to all customers. I saw this as a problem back when I was a consumer of software, and I see it as a problem now that I am a producer of software. Client database get customized out the wazoo until they become administrative nightmares requiring the type of code you have had to implement (albeit for 20 years).
Software vendors should be experts in their industry and code their products according to best practices. Client modification of the database schema should not be allowed.
"You can customize it any way you want" is ultimately a flawed philosophy, and its reductio-ad-absurdum conclusion is an empty box with instructions to install the database server of the client's choice.|||You misunderstand. You may not tamper with the database but we will customize our programs. That is what I meant by that. Our Workers' Comp claim table is called WCCLM1 for example. Every client has this and if they get an update, they may need additional columns or columns expanded. if they add something to it, the app wont see it, etc
Oh and we don't ship a shrink wrapped product and tell them to plug it in to whatever they want. We support either Oracle or SQL Server only. We do a data conversion in most cases as well. We are hardly as stupid or sloppy as some may have understood.
Basically, client modifications to the database are not allowed and there is not a single thing that is arbitrary about the database design. Geez, all I need to do is add some columns or expand them without having to write a script every time. This isn't rocket science or reinvention of the wheel.|||Oh and one more thing. "You can customize it anyway you want" is true as long as you pay us to modify it. We don't ship code. We don't let clients ever have that, much less modify it. This isn't some open source trash. This is a 100k plus application.|||So you still run into the headache of maintaining code for as many versions as you have clients, but you have found a way to charge for the administrative hassle.
I'm not dissing your business plan. Whatever you and your clients agree to and makes you money is fair game. But I don't see how you can be surprised that rolling out updates in such a situation is a pain in the keister. It is difficult by design.|||Well, technically we only have two versions, 5.4 of our old system and 6.424 of out newer .NET version. Both use the same database, both use the same programs at any client site. What may be different is tables for a customized screen for a client claim program, Perhaps a custom AP, HR, etc interface. Our code and/or business model is not the mess some of you think it is. I guess I'm just not explaining it well.
Still, forget about all that. Even if it was a wreck, what does that have to do with what I consider a substantial flaw in this RDMS? Not a damn thing. The bottom line is that I should be able to add a column (oh and this happens even if I add the column manually and not via stored procedure in case anyone missed that) and have that recorded in the proper ordinal position in the system tables and views.
The feed back I'm getting here is that I'm stupid for expecting to actually utilize the alter table command. Someone explain why it's there if it can't be relied upon? You guys can talk all day long about how we distribute our app but no one can seem to offer anything of value with regard to the issue at hand.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.|||No ... the feedback you are getting is that order has no meaning in a relational database. Each column relates information about the primary key. No matter what order I create and populate columns in the table, if I want them to come out in a specific sequence, I must specify that order in the select statement!
Funny how I don't have this issue in Oracle. You don't even know what the hell you are talking about. Since when did I say what order they are in, inside the database matters to me? I never did. The application depends on things being a certain way. I didn't design that shit. Programmers did and now I have to deal with it. Still, how dare I expect things to be correct in SYSTEM TABLES! I'm not an idiot so I wish you people would stop assuming I am.
Thanks for nothing. All of you.|||I think the problem is that when you add a column by altering a table it increments the column number. If you must ensure that the column numbers do not have gaps, then you must use DROP/CREATE.
I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?|||sweet Jesus the booty pleaser.
I bang this drum everywhere I go. You make money by coding a version of your product once and selling it a million times, not by selling your product and coding it a million times. my current employer still does not understand this concept yet either.|||I already got that but I don't have to do this with Oracle. You have to understand something. A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that. WTF would I want to do things by hand on one RDMS and then have the luxury of automation on another?I realize that it doesn't help you much that your code relies on things that it shouldn't, and that is causing your problems. I think that we've all been there with code that was written long ago by folks that made assumptions that don't hold true with newer tools.
If your code relies on specific orders of attributes, and no gaps in the values in system tables (when your code shouldn't even know what those number are or that those gaps exist), then your code is faulty. You can work around the fault in your code by using the drop and create that Blindman suggested.
I'm sorry that you are stuck maintaining this code, but all we are trying to do is help... Getting hostile with us won't make your job any easier, and if you irritate the people that help you, that will probably make your job harder.
-PatP|||See, no one is helping me. That is why I'm getting a bit hot. Your wrote that my code is faulty. No it isn't. I'm under certain constraints of an application. One of them is my procedure being called by the application. Now suppose I do this drop/create deal. What if the table has ten million rows? That will take some time to run. Meanwhile, the user thinks that the app is hung up and does something stupid that maybe even implicit transactions may not be able to help?
Again, I have to work around what programmers have done, not them working around me. Just so everyone knows, I could note care less what order columns are in the table. The programmers, for whatever reason, do. If a table is created or altered outside of the order of the data dictionary table bad things happen. That IS NOT MY DESIGN.
I could create some rolling number sequence in SQL Server to get past this. My point is, why should I have to? I can't irritate people who help me because I've got no help. ya know? Everyone is talking like this is just normal. It just isn't.
Go ahead and lock this thread. It's going nowhere. Fast.|||Oh and before it is locked, if it is. Understand that my gripe isn't with this ordinal position as much as it makes me wonder what else I can't believe from the system. What else is bad? Is that so nuts of me?|||The system tables are correct, based on the rules for SQL Server. Please explain what you can't believe. I don't get what the problem is.
If your code isn't faulty, then it works "as is". If your code is faulty, then Blindman's suggestion will help you work around that fault. Again, I don't get what the problem is.
Plase help me to understand.
-PatP|||Why would you say the tables are correct? There is a gap in the ordinal value? What rules specifically are you speaking of. If I've got a table with ten columns, I add a column, it is eleven in ordinal position. If I drop it for the sake of doing it and re add it, it is still eleven in reality, not twelve as SQL Server will record it. Then on to higher numbers if I kept dropping it and re adding it. Now of course that isn't going to happen (continuous dropping/re add) but I'm just trying to get the point across.
Now in Oracle, I can do this all day long and user_tab_columns will show the correct column_id (1,2,3,4,5,6,7,8,9,10,11) no matter how many times I drop and re add a column. As I've stated before, I could easily create some rolling number while looking at syscolumns or something and just override what the ordinal value is in the table but I just don't like that. I'd rather do "fieldlocation+1<>colid" (note field location in my meta file begins with zero as opposed to 1 in syscolumns) to find something out of order, or a column that needs to be added. If I find something out of order, my procedure returns a message saying I wont do anything. In this case, manual intervention is required and that is how it should be. It is the application that will puke if columns are out of order, not my code. I really have to detect this though.
So, to sum it up, I'd love for colid to be in an order without gaps regardless of a column being dropped and then a new one added. That isn't right that SQL Server does anything else. And I'll say it again, what else is screwy if that is happening? Just like Blinds example about adding length to a column. My procedure does that, too. Now I can't expand a column correctly because maybe what is in syscolumns is actually wrong?
I'm not saying I can't get past this particular issue, I just wonder what else is wrong and I resent the fact I should have to jump through hoops to get a true sequential order from syscolumns.|||Being a user forum, we can really only deal with "what is", rather than "what should be". None of us can change the way syscolumns records column IDs. Perhaps you should take this to Microsoft, and see what they say?|||A procedure I write in SQL Server, I've got to write a like version for Oracle. There is no way around that.Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.|||I could poop in one hand and wish in the other to see which fills up faster! Those people at MS would be of no use. I'm sure they would offer a work around of "don't do that". I've found all kinds of Crazy things with SQL Server. Like looking at sysindexes in a particular database as another user in a different database. I get totally erratic results. That is just an example.
I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.|||Yes. Are you just now catching on to the fact that Oracle and SQL Server are two different database engines? There are a lot of things that are easy to do in SQL Server that are difficult to do in Oracle as well. Frankly, the idea of a single set of code that will run on both Oracle and SQL Server is a myth propogated by software sales people that are either bad programmers, ignorant programmers, or dishonest programmers.
You are complaining that the system tables in SQL Server are not correct.
YES THEY ARE! THEY WORK VERY WELL FOR WHAT THEY WERE INTENDED TO DO. SQL SERVER DOES NOT CARE WHETHER THERE ARE GAPS IN THE VALUES.
The system tables are designed to be used by...wait for it..wait for it...the SYSTEM! Your application has no business using them in the way it does, but if you insist upon it then you must abide by the rules of SQL Server, just as you have to abide by the rules of Oracle when using Oracle's system tables.
DUH.
Dude, get over yourself. I know that they are two different RDMS. I never said one set of code should run for both. I wrote that functionality that exists for one back end needs to exist for the other. That means two different procedures. However, I'd like to keep things simple. Thanks for the heads up though. And if the system tables are not intended to be used by anything other than the system, How should I determine a tables true structure? You can save your smart ass responses. They've been as little help as the rest of your post.|||I've stopped looking for an answer from anyone because I know what I have to do. What got my back up is what I perceived as people thinking I was nuts for expecting things in certain tables to be in what I consider a sensible state. That is all.
Don't think so. You got upset because you define Oracle as sensible. I disagree. Cursors suck. Since oracle doesn't fit the SQL Server paradigm, Oracle must not be sensible, and a piece of c**p when it comes to the efficient use of limited system resources.
We agree to disagree!|||I never said one was perfect. Getting data from Oracle is like getting blood from a rock at times. Sometimes things are easy over "here" and hard "there" I never said one was better than the other. I mentioned Oracle as something to compare the situation with. Regardless, I've got to deal with things in both Oracle and SQL. It is just how things are.|||dba_one, we can all sympathize with your predicament, and i'm sure many of us can understand your frustration
however, at no point are you allowed to say things like "Dude, get over yourself" or "You can save your smart ass responses"
that's just not allowed|||Heck, that is tame but regardless, I didn't come here to fight. I didn't come here to have anyone just talk down to me, either. Regardless, I was looking for insight on a particular issue but there isn''t much that can be done about this particular thing so I've got to deal with it. So be it. I'm not mad, I'm just not the nice guy all the time. No harm intended and no harm done.|||OK.
Basically, the answer to your post boils down to:
A) The internals of SQL Server was not designed with your application in mind.
B) We can't change the internals of SQL Server.
C) No, not many people on this forum have run into the problem you are experiencing, because we have not designed applications such as yours.
D) We sympathize with your predicament.
Now lets all go back to playing nice.|||What the hell...fow did I miss all of this?|||Did you ever show us the code that is making these changes btw?
That might help out alot|||What the hell...fow did I miss all of this?
fow indeed!
:)
.[Orderdetails], INCLUDE_STATISTICS, EXCLUSIVE), $ADJUSTEDPROBABILITY, 3)