SqlCmd - Issues / Questions

Apr 17, 2009 at 11:59 PM
How to change variable values at runtime ?

 I have defined couple of Variables and after executing one Sqlcmd I would like to change the value of one the variable so that the next SqlCmd would get the updated value. How do I do this ?

Issues:

1. SeverityLevel - I have to set it to at least 1 as it always gives an error. ( This is already reported by someone else and there is a work item )
2. QueryTimeOut - Documentation say's that if I don't specify this it would not timeout, but it does timeout making me set it to max value.

Thanks,
Kannan
Coordinator
Apr 20, 2009 at 9:36 PM
Hi Kannan

Can you post an example of your script (pseudo style if you like) and what you are trying to accomplish.

1. This fix is in the 3.5.3.0 branch which we will ship a little earlier than planned (within the next few weeks).
2. This documentation is based on the info found here: http://msdn.microsoft.com/en-us/library/ms162773.aspx. Could you post a sample.

Thanks

Mike
Apr 20, 2009 at 10:32 PM
Thanks Mike. After I posted I did find there was a restore action to restoring db, but that also didn't seem to have an option to move the data files to a different path. My question though was in general can I change variable values in my ms build script and re-run the same sql file.

Example: Let's say I have a generic RestoreDB.sql like below.

RestoreDB.sql

use master
go

PRINT N'Restoring $(DbName)....'

RESTORE DATABASE [$(DbName)] FROM  DISK = N'$(BackupPath)\$(DbName).bak' WITH  FILE = 1, 
      MOVE N'$(DbName)_Data' TO N'$(DataPath)\$(DbName).mdf', 
      MOVE N'$(DbName)_Data1' TO N'$(DataPath)\$(DbName)_1.ndf', 
      MOVE N'$(DbName)_Data2' TO N'$(DataPath)\$(DbName)_2.ndf', 
      MOVE N'$(DbName)_log' TO N'$(DataPath)\$(DbName)_3.ldf',  NOUNLOAD,  REPLACE, STATS = 10
GO

Assume I have these variables defined.

<ItemGroup>
        <Variable Include="DbName">
            <Value>MyDb1</Value>
        </Variable>
 <Variable Include="DataPath">
            <Value>C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA</Value>
        </Variable>
 <Variable Include="BackupPath">
            <Value>C:\BackupFiles\</Value>
        </Variable>
    </ItemGroup>
 
I would like to run this tasks.

   <MSBuild.ExtensionPack.SqlServer.SqlCmd
  TaskAction="Execute"
  Server="(local)"
  Database="$(DbName)"
  InputFiles="RestoreDb.sql"
  OutputFile="Run.log"
  SeverityLevel="1"
  Variables="@(Variable)"
  QueryTimeOut="65535" />

Then just change the DbName from within the above msbuild script and run the above task again.