In the past I have been using SMSS to create the stored procedures as you explain. However, when doing this, I cannot run local DNN installs since it does not close the connection to the database as expected. I need to manually terminate all the sql process and restart them before running. Anyone else?

I have never had that issue....now typically when generating scripts I am working on a different DB than my DNN test one...but even when I have that open I don't loose connection.

Which version of SQL Server are you using?

Good post. One of the things I routinely do to minimize the sheer number of replacements is to judiciously use table aliases in my procs.

For instance when joining a couple tables like modules and tabmodules I'll use something like this:

dbo.myqualifier_Modules M inner join dbo.myqualifier_TabModules TM ON M.ModuleId = TM.ModuleId

I also find it improves the readability of the procs I write as well.

Thanks, great post. Would be interesting to know how the {objectqualifier} token is intended to be used though, as the VS starter kit inserts it and a hard-coded MyCompany_ prefix on all object names. I assume that the starter kit is misleading, that the objectqualifier is intended for company and the hard-coded prefix should read ModuleName_

Hi Mitchel,
I hope you will be doing well. I usually refer your books on DNN but this time I'm here directly.
Actually, I found dataset as the fastest way to add database feature into any application and for all of my work for dnn is using a separate class library containing datasets and hard coded database connection string. The way I did it, is notable to transfer my module package to anyone because each time i request for connection string and build my class library for them.
How can i fix this now?

You can modify your code to grab the connection string from the web.config file just like a regular module would do. I don't have the code handy, but you could review the template code to see how it is done.

I realize this is an old thread, but I recently came across a related problem and wanted to share this with others.

Years ago, SQL licensing was very costly so web hosts would share a common SQL Server with multiple users for a reduced fee, allowing a single database for multiple applications. Originally the objectQualifier allowed multiple DNN applications to share a single common database.

Things have changed since then (SQL Express) and although it was a unique solution from DNN at the time, I believe it has caused, and continues to cause far more damage, time and negativity than it is worth. Unless I am missing something the {objectQualifer} and dbo. should be deprecated immediately if not sooner. (Please feel free to tell me otherwise.)

To demonstrate how bad this little menace is, paste the following script via Host|SQL and examine the results. Note that EVERYTHING reported will NOT work in a shared database (which was precisely what an objectQualifer is for!)

SELECT o.name FROM sys.objects as o inner join sys.schemas as s on o.schema_id = s.schema_id where o.name not like '%%' and o.name not like '%aspnet_%' and not s.name= 'sys' order by o.name

This should not be a module developer problem. It should not prevent use of LINQ to SQL or Entity Framework (a very special thanks to Braydon Hayes for solving those ones). In fact, the objectQualifier is probably the single most despicable task of DNN module development. How many innovative and novel ideas and modules have been shelved because of the this?

The DNN core code is a mess from this requirement. The DAL (data access layer) for any DNN module is a complete joke with curly brace delimiters and quoted strings throughout. How many times you manually edited a .SqlDataProvider file just to get it to work?

This should be top priority for DNN Corp. as well as all module developers. Just make a note in your release notes to indicate that your module does not support the objectQualifier. I dream of the day when we can use SMSS and DNN script interchangeably, along with LINQ, EF and whatever else comes along.


Goodbye and dbo., and good riddance!

Dwayne J. Baldwin raises a big question. Point is logical, lets see what DNN Core team says about this. I know DNN 5.6 still requires ObjectQuntifier and databaseOwner in sql scripts.

The objectQualifier and databaseOwner junk makes module development way more complicated than it needs to be. It makes it exceedingly difficult to leverage Entity Framework in modules. I'd love to be able to make use of the new Code First feature that is about to be released in the upcoming Entity Framework 4.1. However, I just don't see how it's possible, at least not if I want to distribute a module, since the names of the tables and other database objects are likely to be different depending on how the end user has their DNN instance configured.

Unfortunately, given the need to maintain at least some level of backward compatibility with legacy modules, I really don't see how the DNN core team could eliminate these tokens.

I just Googled "smss" and you are the 10th result and the only result that shows a photo. Congrats!