November 3, 2022 at 9:46 am
Hi, i have an issue with a few queries in SQL Server 2019 that no validation is done while saving an SP, so that if referenced columns are incorrect, i am not warned about this, and i find out about this way further in the testing process.
CREATE OR ALTER PROCEDURE dbo.Testing
@ReferenceID VARCHAR(10)
AS
SELECT
ws.WS,
ws.iDoNotExist
FROM dbo.WS_Setup AS ws
CROSS APPLY dbo.iAlsoDoNotExist(NULL) AS bt; -- COMMENT THIS TO ENABLE VALIDATION
As expected, ws.iDoNotExist does not exist, neither does the Table Value Function dbo.iAlsoDoNotExist, but still this ALTER is executed without problems, "Commands completed successfully.".
If i comment the CROSS APPLY, i get an error 207 "Invalid column name 'iDoNotExist'.", completely as expected. Why is there no validation if i add a CROSS APPLY? Is it too complex for MSSQL, do i need to change server-settings so there is validation here, or is this a bug in MSSQL? I want my code to be valid if i save it, and be warned if there are issues 🙂
November 3, 2022 at 11:39 am
It's called deferred name resolution. It allows you to create a procedure, view, or in your case, function, on objects that do not yet exist. It does this under the assumption that they will exist when executed. It's really useful for dynamic data loading where you create a table, throw data into it, then clean it with a procedure that is created ahead of time, then toss the table when done. Stuff like that. Here's an article discussing it from Kendra Little. The trick is, figure out how to validate your code before you deploy it. A staging database that is simply a copy of production to allow for tests of deployment scripts is one way I've addressed this kind of issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 3, 2022 at 3:47 pm
Hi, thanks for your quick resonse! It looks like a feature then, but my main concern is that i want the validation in my editor, rather than during deployment. We are using Flyway Desktop to migrate databases, and we do get the error here (our staging database), but i would prefer the validation to be sooner than during the migration (or the verification of the migration-file). The migration is a very big one, with about 2000 objects, as we are syncing a forked repo back to it's Upstream repo. I already pinpointed the problem to be in 5 objects, but these have about 100 columns each, and tying them trial-and-error in a migration validation takes a lot of time and effort. I just want Server Management Studio to warn me what problems i can expect during migration.
I was hoping i could just enable the normal validation for MSSQL / Server Management Studio, but i will read Kendra Little's article hoping there is a solution there 🙂
Thanks!
November 3, 2022 at 5:26 pm
... but my main concern is that i want the validation in my editor, rather than during deployment.
I'd be a bit concerned about that process... it sounds like there is no prior testing in a Development or UAT environment. Just sayin'.... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2022 at 2:04 pm
I'd suggest adding a Continuous Integration step into your setup. Just deploy the code, without data, into a database, on commit of the code (or a push to a particular branch, there are options here). That will happen extremely fast, and will act as a validation step before you get anywhere near your Staging environment, let alone production.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 4, 2022 at 4:41 pm
I'd suggest adding a Continuous Integration step into your setup. Just deploy the code, without data, into a database, on commit of the code (or a push to a particular branch, there are options here). That will happen extremely fast, and will act as a validation step before you get anywhere near your Staging environment, let alone production.
That still seems to be missing the all important Unit Testing and UAT and the frequently important Performance Testing. Doesn't anyone ever test actual code functionality before deploying to production anymore?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2022 at 9:35 am
The only thing the AintTim is asking imho is:
Why does the validation of a non existing column in the existing table, dbo.WS_setup (assuming it's a table), works if there is no join with a non existing object.
He doesn't write about testing nor does he write about putting it in production without testing. Assuming he does this or that Grant is advising him to do so completely ignores the question.
Setting apart from the fact it's really important to do so, a test of the object would have detected this!
Interesting discussion though, but, again imho, not his question, which is:
Can I get validation error everytime?
No you can't, as Grant answered it's a feature. Which can't be turned off:
But IntelliSense does highlight the problem, so maybe his really question could be:
Can I prevent the execution of a query when IntelliSense gives me errors?
Cheers, Tonie
See you at PASS this year?
https://passdatacommunitysummit.com/speakers/TonieHuizer
November 7, 2022 at 12:19 pm
Thanks all! But as i mentioned in my opening-post, the error came up in the testing process (verifying the migration from Flyway Desktop), so there already are a lot of steps before this reaches production. We have testing and UAT, but that was not the issue.
I just want MSSMS to warn be before the test-stage, as i am warned about other syntax-errors by MSSMS, if i don't include non-existing objects, so i won't even push it over to Migration (then testing, then production....). So no worries Jeff, there are a few steps before total failure 😉
IntelliSense may help, but some SP's are too darn big, and IntelliSense just stops highlighting possible errors halfway. I might need to tweak IntelliSense by increasing the memory size.
Thank you all for pushing me in the right directions. I still feel "deferred name resolution" is a nice feature, but i should be able to disable it using some sort of server-command, but given the amount of articles i found now, i'm not the only one 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply