How to use a relative path when connecting to a (localdb) with SQLCMD?

Question

ok, so we're using MS LocalDB as a database to power the regression tests on our app. we have a quite complicated schema and release profile so we use 'create db on attach' in C# code, then use SQLCMD to run our scripts to bring the db up to the current version, insert some test data, run our tests, tear everything down etc. Normally this works fine.

I recently noticed that if the path nesting of the MDF file is too deep, then SQLCMD fails with a "Sqlcmd: '-d': Argument too long (maximum is 128 characters)" error, which is correct because the -d param to sqlcmd is a database name, which can only be 128 chars long.

"no worries" I thought to myself, "I'll just make it a relative path and everything will be awesome!". Oh how wrong I was dear reader! Changing the dbname to a path relative to the sqlcmd working directory simply resulted in Login errors to sqlcmd:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'DOMAIN\myUser'..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Cannot open database "Source\Solution.Name\Solution.Name.Tests.Regression\bin\DEVT\TestDb.mdf" requested by the login. The login failed..

the command like used by sqlcmd was equivalent to the below:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" -S (localdb)\v11.0 -i "path\to\baseline.sql" -E -e -b -r1 -d "Source\Solution.Name\Solution.Name.Tests.Regression\bin\DEVT\TestDb.mdf"

where the sqlcmd working dir was the parent to the Source dir. Sqlcmd was being run from our C# code like this:

        var process = new Process
        {
            StartInfo = { FileName = "C:\\Program Files\\Microsoft SQL Server\\110\\Tools\\Binn\\SQLCMD.EXE" }
        };

        process.StartInfo.Arguments = " -S (localdb)\v11.0 -i \"path\\to\\baseline.sql\" -E -e -b -r1 -d \"Source\\Solution.Name\\Solution.Name.Tests.Regression\\bin\\DEVT\\TestDb.mdf\"";
        process.StartInfo.WorkingDirectory = "C:\users\myUser\projects\projectName\";
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.CreateNoWindow = true;
        process.StartInfo.RedirectStandardError = true;

        try
        {
            process.Start();
            string error = process.StandardError.ReadToEnd();
            process.WaitForExit();
        }
        catch(Exception e)
        {
            //error handling, print error string etc
        }

The localDB is attached earlier in our regression tests thusly:

            SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\TestDb.mdf;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30");
            conn.Open();

So, I can easily use a relative path in our C# Connection string with "AttachDbFilename=|DataDirectory|\TestDb.mdf;". I can also work around this problem by relocating the project to the root of the C:\ drive and shortening the path, but this seems like a hacky workaround.

Is there any way to use a relative path in the argument to sqlcmd so that I don't have to use an absolute path? every example I've found online of using sqlcmd with localdb uses absolute paths, the only relative paths are in the connection strings of C# apps, which we already use. thanks in advance.

How to use a relative path when connecting to a (localdb) with SQLCMD?

0 Answers0