Content Injection - Upgrade issue if site uses DNN db prefix

[moorecreative]

upgrade from earlier version fails when running the 2.0.2 install upgrade. SQL errors occur and they appear to be related to DNN database prefix issues.

If a DNN db has a prefix such as "dnn_" then the install script of the module appears to incorrectly apply that prefix to database system level items such as syscolumns

for example (from the errors):

IF NOT EXISTS 
( 
       SELECT 1 
       **_FROM   dbo.[dnn_syscolumns] c 
       JOIN   dbo.[dnn_sysobjects] o_** 
       ON     c.[id] = o.[id] 
       WHERE  c.[name] = N'custom_properties' 
       AND    o.[name] = 'dnn_wns_inj_injection') 
ALTER TABLE dbo.[dnn_wns_inj_injection] ADD [custom_properties] NVARCHAR(max) NULL;

This runs correctly as soon as it is corrected to dbo.[syscolumns] vs dbo.[dnn_syscolumns]

[WillStrohl]

That's odd... I've never run into the upgrade error myself. :(

I just looked at the source, and the last version which had SQL updates was 2.0.0. 🤔

Just to be clear.... Is this the line of code you're talking about though?

https://github.com/hismightiness/dnnextensions/blob/master/Modules/WillStrohl.Injection/Providers/DataProviders/SqlDataProvider/02.00.00.SqlDataProvider#L18

Also, you're saying that the prefix on syscolumns needs to be removed in the scenarios where an objectQualifier is being used.... Right?

FYI - on a side note, any site using objectQualifier needs to be updated to not use them ASAP. You won't be able to upgrade directly in the future without doing so. They're going away. Sorry for the crap news. :( (If you need help with that, please let me know.)

[moorecreative]

Wow, that is big news! I’ve never preferred using them myself, but I’m going away is obviously going to be a big deal for future upgrades. Thanks for the heads up! Yes, this was an upgrade on a client site that I’ve inherited, and the specific upgrade didn’t work because it looked like the prefix was on system level items apart from DNN and related tables. Once I remove that part in the script it was able to complete the upgrades no problem

…

Sent from my iPhone

On Dec 27, 2019, at 11:46 AM, Will Strohl ***@***.***> wrote:  That's odd... I've never run into the upgrade error myself. :( I just looked at the source, and the last version which had SQL updates was 2.0.0. 🤔 Just to be clear.... Is this the line of code you're talking about though? https://github.com/hismightiness/dnnextensions/blob/master/Modules/WillStrohl.Injection/Providers/DataProviders/SqlDataProvider/02.00.00.SqlDataProvider#L18 Also, you're saying that the prefix on syscolumns needs to be removed in the scenarios where an objectQualifier is being used.... Right? FYI - on a side note, any site using objectQualifier needs to be updated to not use them ASAP. You won't be able to upgrade directly in the future without doing so. They're going away. Sorry for the crap news. :( (If you need help with that, please let me know.) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[WillStrohl]

In my experience, every time I've run into objectQualifiers, they weren't actually being used for what they were intended for. It has always appeared to be something that someone filled in during install, not knowing what it was intended for, nor the consequences of it. :(

By the way, removing them makes DNN more compatible with better-known vendors/extensions like 2xc and Mandeeps too. (We've had to do the work of removing them just to upgrade client websites that had one of those extensions installed.)

Thanks again for reporting this. :)

[moorecreative]

I’d second that for sure… aside from it being a tradition with some other systems like joomla, I haven’t run into a DNN instance where it didn’t cause some problem or another. Thanks Will, Ryan Moore <mailto:ryan@moorecreative.com> ryan@moorecreative.com 704-896-6057 office From: Will Strohl <notifications@github.com> Sent: Friday, December 27, 2019 3:31 PM To: hismightiness/dnnextensions <dnnextensions@noreply.github.com> Cc: moorecreative <ryan@moorecreative.com>; Author <author@noreply.github.com> Subject: Re: [hismightiness/dnnextensions] Content Injection - Upgrade issue if site uses DNN db prefix (#178) In my experience, every time I've run into objectQualifiers, they weren't actually being used for what they were intended for. It has always appeared to be something that someone filled in during install, not knowing what it was intended for, nor the consequences of it. :( By the way, removing them makes DNN more compatible with better-known vendors/extensions like 2xc and Mandeeps too. Thanks again for reporting this. :) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#178> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJ6IV54MLJGVA2S75D253DQ2ZQY7ANCNFSM4J56BXCA> .

[MaiklT]

The line must look like this:

IF NOT EXISTS (SELECT 1 FROM {databaseOwner}[syscolumns] c JOIN {databaseOwner}[sysobjects] o ON c.[id] = o.[id] WHERE c.[name] = N'custom_properties' AND o.[name] = '{objectQualifier}wns_inj_injection')

because syscolumns and sysobjects don't have a qualifier. They are System Views in SQL Server.