When updating our solutions packags we noticed that dbup-sqlserver has a dependency to Microsoft.Azure.Services.AppAuthentication >= 1.6.2 which is deprecated. Suggesting an alternate implementation with regards to the suggestion in issue DbUp/DbUp#641.

I get the following error when using a SqlTableJournal or when using a custom Journal that implements TableJournal.

If I change the DeployChanges.To... code and remove the .JournalTo(journal) line, the problem goes away.

My main goals is to add more columns to the SchemaVersions table, by adding what release version a script was applied on. If there's an easier way to do this please can someone let me know.

Upgrade failed due to an unexpected exception:
System.NullReferenceException: Object reference not set to an instance of an object.
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection[T](Func`2 actionWithResult)
   at DbUp.Engine.UpgradeEngine.GetScriptsToExecuteInsideOperation()
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()
System.NullReferenceException: Object reference not set to an instance of an object.
   at DbUp.Engine.Transactions.DatabaseConnectionManager.ExecuteCommandsWithManagedConnection[T](Func`2 actionWithResult)
   at DbUp.Engine.UpgradeEngine.GetScriptsToExecuteInsideOperation()
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()

using DbUp.Engine;
using DbUp.Engine.Output;
using DbUp.SqlServer;
using System.Data.SqlClient;

namespace SQLScriptDeploymentTool
{
    internal class Program
    {
        private static string ConnectionString => "Data Source=(local);Initial Catalog=db;User Id=dbuser;password=dbpass;";

        static void Main()
        {
            var connectionString = BuildConnectionString("(local)", "db", "dbuser", "dbpass");
            var journal = GenerateJournal(ConnectionString);

            var version = string.Empty;
            DatabaseUpgradeResult result;
            if (version != string.Empty)
            {
                result = PerformRollback(journal, ConnectionString, version);
            }
            else
            {
                result = PerformRollforward(journal, ConnectionString, version);
            }

            if (!result.Successful)
            {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(result.Error);
                Console.ResetColor();
                Console.ReadLine();
                return;
            }

            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("Success!");
            Console.ResetColor();
            Console.ReadLine();
            return;
        }

        private static DatabaseUpgradeResult PerformRollback(SqlTableJournal journal, string connectionString, string version)
        {
            var sqlScripts = RetrieveScriptstoRollback(version);

            var upgrader =
                DeployChanges.To
                    .SqlDatabase(connectionString)
                    .JournalTo(journal)
                    .WithScripts(sqlScripts)
                    .LogToConsole()
                    .Build();

            return upgrader.PerformUpgrade();
        }

        private static DatabaseUpgradeResult PerformRollforward(SqlTableJournal journal, string connectionString, string version)
        {
            var upgrader =
                DeployChanges.To
                    .SqlDatabase(connectionString)
                    .JournalTo(journal)
                    .WithScriptsFromFileSystem("C:/Projects/P-Platform/MppGlobal.BuildResources/SqlScripts/PostSSDT/P-Core/228.0/Rollforward")
                    .LogToConsole()
                    .Build();

            return upgrader.PerformUpgrade();
        }

        private static SqlTableJournal GenerateJournal(string connectionString)
        {
            var connectionManager = new SqlConnectionManager(connectionString);
            var logger = new ConsoleUpgradeLog();
            return new SqlTableJournal(() => connectionManager, () => logger, "dbo", "DeploymentScriptLog");
        }

        private static SqlScript[] RetrieveScriptstoRollback(string version)
        {
            IList<SqlScript> scriptsToRollback = new List<SqlScript>();
            SqlConnection connection = new(ConnectionString);
            connection.Open();

            var sql = $"SELECT [ScriptName], [Applied] FROM [dbo].[SchemaVersions] WHERE [Applied] > '{ConvertVersionToDateTime(version)}'";
            SqlCommand command = new(sql, connection);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                scriptsToRollback.Add(new SqlScript(reader.GetString(0), ""));
            }

            return scriptsToRollback.ToArray();
        }

        private static string BuildConnectionString(string server, string database, string username, string password)
        {
            var conn = new SqlConnectionStringBuilder
            {
                DataSource = server,
                InitialCatalog = database
            };

            if (!String.IsNullOrEmpty(username))
            {
                conn.UserID = username;
                conn.Password = password;
                conn.IntegratedSecurity = false;
            }
            else
            {
                conn.IntegratedSecurity = true;
            }

            return conn.ToString();
        }

        private static string ConvertVersionToDateTime(string version)
        {
            var year = int.Parse(version[0..4]);
            var month = int.Parse(version[4..6]);
            var day = int.Parse(version[6..8].ToString());

            return $"{year}-{month}-{day}";
        }
    }
}

I cant get it to work because you only accept connection as a string! And I need to get Azure token first and then establish a connection. Please advise if there any options. urgent project!

        private async Task<string> GetToken()
        {

            var ctx = new AuthenticationContext("https://login.microsoftonline.com/xxx");
            var result = await ctx.AcquireTokenAsync("https://database.windows.net/", new ClientCredential("xxx", "xxx"));
            return result.AccessToken;
        }

and then

using (var conn = new SqlConnection("Data Source=tcp:xxx.database.windows.net,1433;Initial Catalog=xxx;"))
            {
                conn.AccessToken = await GetToken();

                return await conn.QueryASync<T>(sql);
            }

The classes for the azure sql integrated auth are conditionally removed from compilation in version 4.5.0 when they shouldn't be.

If you look at the assemblies from nuget.org you can see that the AzureSqlConnectionManager class is missing

There should be a class called AzureSqlConnectionManager as long as the SUPPORTS_AZURE_AD is set.

It appears as though these are set for net46 and netstandard2.0 but the files are still missing for those package versions.

Hi,

While calling the EnsureDatabase.For.SqlDatabase(connectionString, 1020, DbUp.SqlServer.AzureDatabaseEdition.Standard, "SQL_Latin1_General_CP850_BIN2"); it's throwing System.Data.SqlClient.SqlException: 'Incorrect syntax near 'standard'.' I have attached the screenshot for your reference.

While going through the repository code I figured that it's forming incorrect syntax to create the DB, it's very minor. The Edition of the SQL Server enclosed with '', instead it should have '.

The code responsible for creating the database is in SqlServerExtensions (https://github.com/DbUp/DbUp/blob/master/src/DbUp/Support/SqlServer/SqlServerExtensions.cs). If I increase the CommandTimeout then EnsureDatabase works against a SQL Azure server:

        // Create the database...
        using (var command = new SqlCommand(sqlCommandText, connection)
        {
            CommandType = CommandType.Text
        })
        {
            command.CommandTimeout = 120;
            command.ExecuteNonQuery();
        }

Unfortunately I'm new to GitHub etiquette and protocol (and having now posted, not sure why my first two sentences are in bold!) so not sure how to go about getting this into the main branch.

DbUp parses the text of each SQL script for two purposes:

1. To identify replacement tokens for our variable replacement
2. In order to split statements into batches (in SQL Server that's splitting on "GO" statements)

Currently these two purposes are handled using the same parsing logic. This issue was opened to split these two purposes into their own code paths so that each can be handled by appropriate code.

Initially the Variable Substitution parsing will keep the existing SqlParser functionality.

To split the SQL Server T-SQL statements, the parsing will be achieved using a Microsoft-supplied package called "Microsoft.SqlServer.DacFx" which includes a T-SQL parser. This offloads the responsibility for writing a performant, resiliant, compliant parser.

A simple LINQPad-based proof-of-concept to split SQL into batches using Microsoft.SqlServer.DacFx which you can see here: DacFx Parsing LINQPad POC

Edited from original comment posted by @AdrianJSClark in DbUp/DbUp#439 (comment)

DbUp's SQL Server support should allow the processing of .sql scripts that make use of sqlcmd utility commands and scripting variables.

sqlcmd Scripting Variables

sqlcmd scripting variables are referenced using $(variableName). This is only slightly different than a DbUp variable reference which uses $variableName$. In order for DbUp to properly handle sqlcmd scripting variable references, two DbUp classes must be changed.

• DbUp.Engine.Preprocessors.StripSchemaPreprocessor.cs
  This class removes references to the "schema" variable when no value is provided by the caller. In addition to removing DbUp references to the variable, e.g., $schema$., it must also remove sqlcmd references, e.g., $(schema).
• DbUp.Engine.Preprocessors.VariableSubstitutionPreprocessor.cs.
  This class replaces variable references with their values from the variables array passed to the constructor. This class must recognize both sqlcmd and DbUp variable references for replacement.

Note that these changes should be non-breaking. That is to say that scripts that use the existing DbUp variables references will still be handled properly. In fact, the use of both DbUp-style variable references, e.g., $variableName$, and sqlcmd-style variable references, e.g., $(variableName) should be supported.

sqlcmd Commands

sqlcmd supports a number of commands. These commands are processed by sqlcmd before the Transact-SQL script is submitted to the SQL Server engine for processing.

These sqlcmd commands must start in column 1 of the line and should be prefaced with a colon character, ":". Technically, the colon character is optional in order to support backward compatibility with the osql command-line utility. However, in the real world, sqlcmd commands almost always begin with a colon character, e.g., :setvar or :r.

DbUp should not attempt to support the functionality of the sqlcmd commands. Rather, DbUp should convert every sqlcmd command to a double-dash comment and return it as part of the content of the script to be processed by SQL Server. So, the following sqlcmd commands

:setvar ScriptPath "C:\Scripts\"
:r $(ScriptPath)ScriptFile.sql

becomes the following.

-- :setvar ScriptPath "C:\Scripts\"
-- :r $(ScriptPath)ScriptFile.sql

This requires the following changes.

• DbUp.Support.SqlServer.SqlCommandReader.cs This class reads individual Transact-SQL commands out of a script file. When parsing the script text, this class must recognize asqlcmd` command and convert it to a comment before returning it.

Are there any plans to add support for creating a SQL Server Database with an elastic pool? Obviously the elastic pool must exist first, but thought that may be provided by the end-user. There's currently a few overloads in SqlServerExtensions for some different editions of Azure Sql, but I couldn't find any support for creating a database in an elastic pool.

The syntax is:

CREATE DATABASE somedatabase ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = [poolname] ));

Hi

Let's start with the code (using the latest version of DbUp in a .NET Core 3.1 application).

var upgradeEngine = DeployChanges.To
                    .SqlDatabase("snip")
                    .LogToConsole()
                    .WithScriptsAndCodeEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                    .Build();

Console.WriteLine("Discovered scripts:");
foreach (var script in upgradeEngine.GetDiscoveredScripts())
    Console.WriteLine("\t" + script.Name);

Calling GetDiscoveredScripts or GetExecutedButNotDiscoveredScripts will both call GetDiscoveredScriptsAsEnumerable, which in turn will call EmbeddedScriptAndCodeProvider.GetScripts which will then call ScriptsFromScriptClasses. This is a problem since it will call DatebaseConnectionManager.ExecuteCommandsWithManagedConnection and here the 'transactionStrategy' field is null, because OperationStarting has never been called.

If a script both selects a column as a name using double quotes (i.e. SELECT 1 AS "Test/*") and uses a GO command, the upgrade process will fail with message 'Incorrect syntax near GO'. Removing the GO command, or converting the double quotes to either single quotes or square brackets fixes the issue.

Tested on SqlServer 2016 and 2017.

Code to reproduce:

static void Main(string[] args)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

            /* 
             * SELECT 'Test' AS "Test/*"
             * GO
             */
            var sql = "SELECT 'Test' AS \"Test/*\"\nGO";
            var script = new DbUp.Engine.SqlScript("Tester", sql);

            var builder = DbUp.DeployChanges.To
                .SqlDatabase(connectionString)
                .JournalTo(new NullJournal())
                .WithScripts(script)
                .WithTransaction()
                .LogToConsole()
                .Build();

            var result = builder.PerformUpgrade();
            Debug.Assert(!result.Successful); // Fails

            /*
             * SELECT 'Test' AS "Test/*"
             */
            sql = "SELECT 'Test' AS \"Test/*\"";
            script = new DbUp.Engine.SqlScript("Tester", sql);

            builder = DbUp.DeployChanges.To
            .SqlDatabase(connectionString)
            .JournalTo(new NullJournal())
            .WithScripts(script)
            .WithTransaction()
            .LogToConsole()
            .Build();

            result = builder.PerformUpgrade();
            Debug.Assert(result.Successful); // Succeeds


            /* 
             * SELECT 'Test' AS 'Test/*'
             * GO
             */
            sql = "SELECT 'Test' AS 'Test/*'\nGO";
            script = new DbUp.Engine.SqlScript("Tester", sql);

            builder = DbUp.DeployChanges.To
                .SqlDatabase(connectionString)
                .WithScripts(script)
                .JournalTo(new NullJournal())
                .WithTransaction()
                .LogToConsole()
                .Build();

            result = builder.PerformUpgrade();
            Debug.Assert(result.Successful); // Succeeds
        }
    }

If a script contains comments directly followed by or preceded by a GO statement, the script will not split into commands properly and does not remove the GO. This can be remedied by introducing whitespace around the GO, but as this is valid sql syntax this whitespace should not be necessary.

Code to reproduce:

var sut = new SqlCommandSplitter();
var commands = sut.SplitScriptIntoCommands("/*comment*/GO--comment").ToArray();
var expectedResult = new string[] { "/*comment*/", "--comment"};
Debug.Assert(Enumerable.SequenceEqual(commands, expectedResult));

Expected behavior:
As this is valid sql syntax, the GO should be removed and the resulting commands should be parsed as in expectedResult.

Actual behavior:
Fails, as the SplitScriptIntoCommands function returns the entire string, including the GO, as one command.

DbUp MS SQL Server schema should use datetime2 instead of datetime. This is really a minor issue in a sense. datetime data type is mostly deprecated, datetime2 does the job better. datetime2 offers also increase precision if that is required. But for just in-place replacement datetime2(3) can be used.

datetime does some rounding also so currently the timestamps aren't really accurate to 100 fractional seconds unlike it looks. datetime also takes up 8 bytes of storage, with datetime2(3), only 7 bytes are consumed (with full precision the same though, 8 bytes). These are the notable differences between the two.

I saw you also had discussions changing to datetimeoffset, that would be fine too and make this obsolete.

MS documentation:
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql

Referencing this post.

I want to use a SQL connection string like
Server=myserver.database.windows.net,1433;Database=mydatabase;UID=a;Authentication=Active Directory Interactive

but the app fails with a Keyword not supported: 'authentication' exception. This is because System.Data.SqlClient doesn't understand the new authentication schemes supported in Microsoft.Data.SqlClient that support Sql Azure.

Upgrading to Microsoft.Data.SqlClient is a relatively painless process and would resolve this issue. Would you accept a PR for this?

Note: I applied the overloads to .SqlDatabase suggested in #7 but this did not help.