| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2011-11-11 : 13:29:23
|
| How do we get SQL Server to throw an error when we create a procedure that contains a non-existent table?I was surprised today when SQL Server allowed me to declare a Stored Procedure that contained a select statment refering to a table that did not exist. There wasn't even a warning. When we made a call to this Stored Procedure, then we got the Error message. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-11-11 : 13:45:12
|
| OK. Sorry to ask first and surf later.I found this is something called deferred name resolution. Yet if I try to compile a Stored Procedure with a non-existent column on an existent table, it thows an error at compile time. To not-so-smart me, this makes no sense. |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-11-11 : 14:20:55
|
| I always have a test harness for each and every sproc I create. you could create a utility that creates the test harness for you, then there are no surprises.If you don't have the passion to help people, you have no passion |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-11 : 15:42:24
|
| Not sure if it helps, but if you set SET FMTONLY ON you can "execute" a stored proc and do some form of cursory validation without actually running the sproc. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-14 : 12:09:02
|
quote:
Originally posted by denis_the_thief
How do we get SQL Server to throw an error when we create a procedure that contains a non-existent table?
Deferred Name Resolution, as you have found out.I suppose you could do something like:SELECT MySplitValue + ' is missing'FROM dbo.MySplitFunction('TableA,TableB,TablC,DoesNotExistTable') LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES ON TABLE_NAME = MySplitValueWHERE TABLE_NAME IS NULL...CREATE MyProcNameAS... reference TableA, TableB, TablC, DoesNotExistTable ...orSELECT *FROM sys.dm_sql_referenced_entities('dbo.MyProcName', 'OBJECT')should give you an error if it references columns in a non-existent table. But I don't know if you get false-positives (e.g. for #Temp tables or the like) |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-14 : 14:33:46
|
| We have 100% bullet-proof scripts ...... even so, sometimes we get a rollout where a table failed to be created - and then an Sproc Barfs.Would be nice to have an OPTION EXPLICIT type setting: "All tables must exist, do not defer name-resolution" to catch such situations, 'coz then the Sproc Create could act as long-stop and detect the missing-table error for us. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-11-14 : 14:58:48
|
| I would say this should have been caught in dev? sqa?If you don't have the passion to help people, you have no passion |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-15 : 01:47:05
|
quote:
Originally posted by yosiasz
I would say this should have been caught in dev? sqa?
Yes, like I said, we have "100% bullet-proof scripts".But nonetheless something can go wrong in deployment. Someone forgets to run one of the scripts, in the right order ... and a table is not created. Then the Sprocs are created - no error - then at Runtime the error is found ...We have a number of tests that objects are deployed properly. Each Sproc script inserts a row in a Logging Table with the version number - we check all those. We don't check that all tables / columns / indexes / FKeys are present because, if the scripts are run correctly, they will be ... and the cost of building all the extra checking that the objects are present, and correct, isn't cost-effective (for us).But once-in-a-while we do have a failure from some unexpected cause. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-11-15 : 11:13:13
|
| Kind of off topic from the original question, but as far as deployments go, I'm using the VS 2010 and database projects. So, using the MS methodology that the DB project is the database of record, it makes post deployment schema comparisions pretty quick and painless. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-11-15 : 13:46:17
|
quote:
Originally posted by X002548
I guess I have to ask...Is , why do you ask?Don't you have sample executions comments as part of your sproc scripts?Brett
No. No, we don't. I guess you are getting at TDD?As far as I'm concerned, SQL Server should thrown a warning. If you call an SP that does not exist, you get a warning. If you refer to a column that does not exist, you get an error. This is the main reason I brought this up. I thought to myself, I could have sworn that SQL Server brings up errors for these but now I know that is for missing columns, missing tables don't result in an error or warning. I wasn't the only one in our department left sratching my head over this (maybe we are just dumb).This whole issue of whose responsibility it is, this is a whole different debate. If I had "100% bullet-proof scripts", developers might pass on the responsibility to me and be like Mr. DBA will catch all our mistakes since he has "100% bullet-proof scripts". I'm not against "100% bullet-proof scripts" or TDD etc... but it's just a whole different issue. Should this have been caught earlier? Yes, I admit it should have. This is more to do that we were in a rush. |
|
|
|
yonision
Starting Member
5 Posts |
Posted - 2011-11-15 : 15:13:15
|
| There is a free tool that checks for such SQL errors in your code and lets you know about them. It does it both using 'FMT ONLY' option, and also using its own custom SQL Compiler:http://www.nobhillsoft.com/dianalite.aspx(I work for the manufacturer, 'Nob Hill Software'. this tool is 100% free, no catches no gimmicks)Also - soon we're thinking of combining this code into our database compare tool, so when you're migrating code you'll get notified of all the SQL problems BEFORE you migrate... (e.g what procedures may refer a field you're about to remove, or change type\size, etc) what do you think about this idea??ThanksJonathan |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-11-15 : 16:12:01
|
| Thanks. I appreciate the ideas. I've got to look into them... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-16 : 03:23:19
|
quote:
Originally posted by denis_the_thief
As far as I'm concerned, SQL Server should thrown a warning. If you call an SP that does not exist, you get a warning. If you refer to a column that does not exist, you get an error.
I agree, there are a whole load of things I would like to have Warnings for. Turn 'em off, or make them more fussy/verbose, with a SETting.Not having an ORDER BY on a SELECT for example. Then you find that the missing ORDER BY just happened to give you a resultset in Clustered PK Index in DEV and QA, but not in production  And so on ...a LINT tool for SQL would do as well. Maybe that's what yonision's product does, L'll go take a look. |
|
|
|
|
No Compilation Error when table does not exist