July 16, 2004 at 10:30 am
Is it possible to create a stored procedure without having SQL Server check to validate that all tables and columns are present? I have some SP's that I want to insert into a database ahead of time so that when the tables get converted the SP's will already be there. Is there a supported way to do this?
July 16, 2004 at 10:49 am
Shouldn't be any problem. It just can't set up dependencies if an object doesn't exist yet. Once the tables are there, it should work ok. Then, if you want to update the dependencies, simply re-create the procedure.
Steve
July 16, 2004 at 11:01 am
There is a problem doing this. The tables do exist, but the columns I am referencing do not currently exist in the database I want to create the procedure in. This is the error that I am getting.
Server: Msg 213, Level 16, State 4, Procedure UpdateFinancialSwapPrice, Line 29
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 1, Procedure UpdateFinancialSwapPrice, Line 41
Insert Error: Column name or number of supplied values does not match table definition.
The error message is correct. I am just wondering if there is a way to get the procedures in SQL Server without it checking for the columns.
I'm not worried about the dependencies at all.
Any suggestions?
July 16, 2004 at 11:35 am
OK.. Dumb question here:
Why in this good earth would you want to knowingly install sp's 1st and create the columns later??? This is asking for HUGE problems.....
Someone or something could come along and say "Oh, here is the procedure I need to run.." KABOOM!
Safest and best practice (IMO) is to do the table mod THEN install sp...
This way you won't have any problems.....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 16, 2004 at 11:47 am
I stand corrected. I was thinking of stored procedures instead of tables.
I think the only way to store your procedures in SQL would be to create the tables. Are you working with a production database? Is that why you are waiting before adding the tables/columns? If so, consider creating a test (development) version of your database where you can get your process fully developed (and tested) before moving it into production.
Steve
July 16, 2004 at 11:50 am
Alright, AJ Ahrens, before you criticize the question, maybe you should find out if there a logical reason behind it.
The reason is because we do not have complete control of the system. It is a purchased system that we have built side systems to integrate with, thus needing our own stored procedures. Since the vendor made a major upgrade to their product they will go through a conversion process to update some of their tables. So I want to put all of our developer's SP's referencing the new version of the tables in the old version of production so when the vendor upgrades the production database over the weekend, the vendor does not have to worry about any of our SP's. Our SP's will just be there when the conversion is over and our side systems will work, that is if I can get the SP's in there.
So does anyone have any suggestions?
Thanks
July 16, 2004 at 2:20 pm
No way that I know of.
I've had problems with developers providing me table and SP changes, and having to rerun the SP changes as they were dependent on the tables changes.
Why not just give who ever is applying the vendors changes your script and have them apply them when they are done.
KlK
July 16, 2004 at 2:25 pm
I just didn't want the vendor to have to deal with stuff that they don't need to. It leaves a little more room for error.
Thanks though.
July 16, 2004 at 4:38 pm
I was just trying to point out a real world scenario. If I offended you I'm sorry. That was not the intent. The intent was to show that what you are requesting could take your system down by running code against columns/tables that don't exist.
As far as HOW to fix this issue given the fact that your vendor is upgrading over the weekend....
Why not create a job that is scheduled to run Very late Sunday or early Monday that either your vendor can manually kick, you can kick, OR the system can just run by itself that would install the sp's.
This would give you the proper dependancies, links, code checking, etc.. that you are looking for.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply