vsdbcmd.exe logging

Question

I am running this utility as part of an MSI that installs/updates our databases. Is there any way to have this tool log db deployment details? or at least any exceptions that are encountered. I don't see logging options in the command line reference found here:

http://msdn.microsoft.com/en-us/library/dd193258.aspx

Thanks.

- Phil 

Answer 1

Hi Phil,

There is not a logging switch.  You should be able to pipe the output to file. The default is that all errors and actions are output during execution.  You can also set it to quiet.


Example:

 

vsdbcmd.exe /a:Import /cs:"Server=.\SQL2005;Integrated Security=true;Pooling=false;Initial Catalog=NorthwindRO;" /dsp:Sql  /model:"NorthwindVSDBCMD.dbschema" >> IMPORTDBLOG.TXT

PAUSE

vsdbcmd.exe /a:Deploy /cs:"Server=.\SQL2005;Integrated Security=true;Pooling=false" /dsp:Sql /dd+ /model:"NorthwindVSDBCMD.dbschema"  /p:TargetDatabase="NewNorthWind" >> DEPLOYDBLOG.TXT

PAUSE

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition)

Answer 2

I requested a logging switch for vsdbcmd back in september but it didn't make it indo the GDR RTM... can this be added for the next release?

I am using the pipe approach now but it is a little disconcerning not knowing what is going on until the entire process is completed. 

-----

Ability to create a log file during the execution to write logging information into the specified logfile. 

/Log Logfile or /L Logfile

/Log .\logs\Database1_V2_Upgrade.log

 

So as vsdbcmd.exe is executing it writes to the log file. Include execution start time, end time, property values used from config files or overrides specified on the command line, executing steps as shown in the command window, detailed error messages etc.. Possibly additional flags to control the verbosity of the included output.

For example...

Start Time: 11:44 AM 9/11/2008

Action: Deploy
ManifestFile: Database1.deploymanifest
ModelFile: Database1.dbschema
DeploymentScriptFile: Database1.sql
Target Server: MYSERVERNAME
Target Database: Database1
Connection String: Server=MYSERVERNAME;Database=Database1;Trusted_Connection=yes;    (x out passwords if sql login).
[any other relevant properties settings etc...]

Comparing database model Database1.dbschema to Database1 on MYSERVER.
 TSD00258        The project and target databases have different collation settings, deployment errors might result.

 

Generating Database1.sql deployment update script.
 TSD00000        Deployment script generated C:\Deploy\Database1.sql

 

Executing Database1.sql deployment update script.
 TSD00000        Altering dbo.Products...
 TSD00000        Altering dbo.Users...
 TSD00000        Altering dbo.Vendors...

 

End Time: 11:53 AM 9/11/2008

  

It would be good if the file was opened for shared access while it is being written to so another application could read the log file as it is still being written to.  This would be useful for a UI application that could shell out to vsdbcmd.exe and also show the ouput of the log file as it is being written.

 
Thanks
-- Steven

Answer 3

Steven, thanks for the suggestion.  We do have this as a possible future feature.

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition)

Answer 4

Hello Barclay,

Any update on this?

Logging is a pretty major feature when it comes to automated builds/deploys...

Cheers,
Steven

Answer 5

No new news on logging capability being added to vsdbcmd.

---

Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.

Answer 6

Hi,

Is there any work arround or option avaliable in 2010 i.e. logging of VSDBCMD.exe

Regards

---

AMA

Answer 7

Here's a possible solution.  Call vsdbcmd.exe using Process and StartInfo from a C# console application.

Source borrowed from this post: http://stackoverflow.com/questions/5967821/c-capturing-python-exe-output-and-displaying-it-in-textbox

You'll have to modify the application to accept command line arguments and add whatever logging needed.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading;
using System.IO;

namespace VSDBCMDRunner
{
  public class Program
  {
    static void Main(string[] args)
    {
      Program p = new Program();
      p.RunVsDbCmd();

      Console.WriteLine("Done!");
      Console.ReadLine();
    }


    private void RunVsDbCmd()
    {
      Process pro = new Process();
      pro.StartInfo.RedirectStandardOutput = true;
      pro.StartInfo.RedirectStandardError = true;
      pro.StartInfo.UseShellExecute = false;
      pro.StartInfo.CreateNoWindow = true;
      pro.EnableRaisingEvents = true;
      pro.OutputDataReceived += new DataReceivedEventHandler(OnDataReceived);
      pro.ErrorDataReceived += new DataReceivedEventHandler(OnDataReceived);

      /*
       * "C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd" 
       * /a:Deploy /manifest:CompanyAnalysis.deploymanifest /p:DeploymentConfigurationFile=CompanyAnalysis_Database.sqldeployment /p:SqlCommandVariablesFile=CompanyAnalysis_Database.sqlcmdvars /cs:"Data Source=knl-db-01;User ID=sa;Password=mypassword"
      */

      pro.StartInfo.FileName = @"C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe";
      pro.StartInfo.Arguments = "/a:Deploy /manifest:CompanyAnalysis.deploymanifest /p:DeploymentConfigurationFile=CompanyAnalysis_Database.sqldeployment /p:SqlCommandVariablesFile=CompanyAnalysis_Database.sqlcmdvars /cs:\"Data Source=knl-db-01;User ID=sa;Password=mypassword\"";

      try
      {
        pro.Start();
        pro.BeginOutputReadLine();
        pro.BeginErrorReadLine();
        pro.WaitForExit();
      }

      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
      }
    }

    private void OnDataReceived(object sender, DataReceivedEventArgs e)
    {
      if (e.Data != null)
      {
        string temp = (e.Data) + Environment.NewLine;
        appendText(temp);

      }
    }
    
    public void appendText(string text)
    {
      Console.WriteLine("text = " + text);
    }
  }
}

Answer 8

I run my msi from bat file to log, also make sure sqlce installed

 

@echo off

SSCERuntime_x86-ENU.msi /q

SSCERuntime_x64-ENU.msi /q

msiexec /i install.msi /lv* dbinstall.log

 

 

Answer 9

Hi Barclay,

Are there any news regarding this?

If I redirect output to a file (like this "vsdbcmd.exe .... > mylog.txt"), then the red error messages are not logged to the standard output but rather to the console window. 

This makes the log useless as it does not contain any error messages.

Since the amount of SQL commands is big, I quickly find myself not being able to scroll up enough in the console window to find out what went wrong.

 

Thanks,

Dor.

 

Answer 10

Thanks Barclay.........!