DotNetNuke 'Run As Script' sql option

Question

What does the 'Run as Script' option do in the Host->Sql page in DotNetNuke?

Answer 1

The "Run as Script" option allows you to paste in SQL statements that have special identifiers that are replaced by your SQL data provider automatically. These identifiers are defined in your web.config file in the dotnetnuke > data > providers section. By default, that section will look like this:

<data defaultProvider="SqlDataProvider">
    <providers>
        <clear/>
            <add name="SqlDataProvider" 
                 type="DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider"   
                 connectionStringName="SiteSqlServer" 
                 upgradeConnectionString="" 
                 providerPath="~\Providers\DataProviders\SqlDataProvider\" 
                 objectQualifier="" 
                 databaseOwner="dbo"/>
    </providers>
</data>

Notice the objectQualifier and databaseOwner attributes? If you paste a SQL statement into the Host > SQL box that contains {objectQualifer} and/or {databaseOwner}, these values will be automatically replaced with the values for those attributes above.

So let's say you paste the following SQL statement into that box with the "Run as Script" checkbox checked:

CREATE TABLE {databaseOwner}[{objectQualifier}Cars]

Assuming we're using the values in the web.config snippet above, the SQL statement that will be executed will instead be:

CREATE TABLE [dbo][Cars]

Answer 2

When "Run as Script" is not checked, you can only provide a single SQL statement. The results of that query will be displayed in a grid.

When "Run as Script" is checked, you can provide multiple SQL statements, separated by the GO keyword. In this mode, no results will be displayed, just a message that the query completed successfully (or error messages if it didn't). This is the same mode that is used when installing an extension.

Note that the statements are completely separated by the GO keyword, so you cannot have constructs (such as a transaction) that wrap a GO statement, since the beginning and end of the construct will be in completely separate sessions.