Thursday, August 23, 2012

SQL Server Database Project - Warning is shown as Error

While trying to build a database project, I was getting an error:
Error:  SQL01945: Warning! The maximum key length is 900 bytes. The index 'xxx' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.

Although this was a warning, the project was failing to build and it was shown as a error.

The fix was to edit the project file in xml and set TreatTSqlWarningsAsErrors to False

<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>.\sql\debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TargetDatabase>xxx</TargetDatabase>
    <TreatTSqlWarningsAsErrors>False</TreatTSqlWarningsAsErrors>
    <SuppressTSqlWarnings />
    <NoWarn>
    </NoWarn>
  </PropertyGroup>

SSDT Conversion - SQL72027: File master.dacpac does not exist.

I was converting a database project to SSDT by following the steps at http://msdn.microsoft.com/en-us/library/hh272689(v=vs.103).aspx

The conversion was successful but when I tried to build the sqlproj, it gave the following error:
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets(494,5): Error:  SQL72027: File "<project directory>\master.dacpac" does not exist.

To fix this error master.dacpac needs to be copied in the project directory.
If the Target Plaform is "SQL Azure", it can be copied from
"D:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\Azure\SQLSchemas"

Otherwise based on the platform, copy from the corresponding directory from
"D:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer"

Monday, August 20, 2012

SqlPackage.exe - Automating SSDT Deployment

To know about SQL Server Data Tools (SSDT), take a look at http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx

Below are the steps for automating the SSDT deployment:
  • Create a Folder on the deployment machine. We can call it C:\SSDT
  • Make sure the files from the following folders on the development machine are copied in SSDT folder on the deployment machine:
    • C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin
    • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.TransactSql.ScriptDom\v4.0_11.0.0.0__89845dcd8080cc91
  • Copy the files from the Build Output of the SSDT Project in a Folder on the deployment machine. We can call it C:\Build
  • Make sure that you have the connection string of the Target Database where you want to depoy. We can call it connStr
  • Now you can publish or generate the scripts for the database deployment (Steps are same for Full or Incremental Deployment)
    • To Publish, run the command: C:\SSDT\SqlPackage.exe /Action:Publish /SourceFile:"C:\Build\MyDatabase.dacpac" /TargetConnectionString:"connStr"
    • To Generate Scripts, run the command: C:\SSDT\SqlPackage.exe /Action:Script /SourceFile:"C:\Build\MyDatabase.dacpac" /TargetConnectionString:"connStr" /OutPutPath:"C:\Build\MyDatabase.sql"
This is great feature for SQL Azure databases since incremental updates can be applied very easily without making the sql scripts SQL Azure compatible.

Friday, August 17, 2012

VS 2010 Load Test - Save and View Test Logs

In VS 2010 Load Tests, the failed test logs are saved by default.
This is because the setting Save Log on Test Failure is set to True

However, if you want to save the logs even for the passed tests, you can do that by changing the setting Save Log Frequency for Completed Tests

Note that the maximum number of test logs saved in both cases is controlled by the setting Maximum Test Logs

You wont be able to see these logs while the load test is running. However, once the load test is complete, you can see these logs by going to Tables->Test Details

Wednesday, August 8, 2012

TFS 2012 RC - Test Controller is shown as offline

If you go to Microsoft Test Manager->Lab Center->Controllers and see the Test Controller as offline, check the event logs on the same machine.
If you see the below error:
The description for Event ID 0 from source VSTTExecution cannot be found. Either the component that raises this event is not installed on your local computer
or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
(mtm.exe, PID 2968, Thread 13) ControllerConnectionManager : InternalConnect : System.Net.Sockets.SocketException (0x80004005): The requested name is valid,
but no data of the requested type was found
Server stack trace:
   at System.Net.Dns.GetAddrInfo(String name)
   at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)
   at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)
   at System.Runtime.Remoting.Channels.RemoteConnection.CreateNewSocket()
   at System.Runtime.Remoting.Channels.RemoteConnection.GetSocket()
   at System.Runtime.Remoting.Channels.SocketCache.GetSocket(String machinePortAndSid, Boolean openNew)
   at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.SendRequestWithRetry(IMessage msg, ITransportHeaders requestHeaders, Stream requestStream)
   at System.Runtime.Remoting.Channels.Tcp.TcpClientTransportSink.ProcessMessage(IMessage msg, ITransportHeaders requestHeaders, Stream requestStream,

ITransportHeaders& responseHeaders, Stream& responseStream)
   at System.Runtime.Remoting.Channels.BinaryClientFormatterSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.VisualStudio.TestTools.Execution.IControllerAccessManager.get_ControllerVersion()
   at Microsoft.VisualStudio.TestTools.Execution.ExecutionUtilities.GetControllerVersion(IControllerAccessManager accessManager, Boolean

tolerateOldControllers)
   at Microsoft.VisualStudio.TestTools.Controller.ControllerConnectionManager.InternalConnect(ControllerConnectionInfo controllerConnectionInfo)

the message resource is present but the message is not found in the string/message table

Find out the exact machine name for the test controller by running the following query:
USE [Tfs_DefaultCollection]
GO

SELECT [PartitionId]
      ,[InternalId]
      ,[Name]
      ,[Description]
      ,[GroupId]
      ,[LastHeartbeat]
  FROM [dbo].[tbl_TestController]
GO


Then make sure that you are able to ping the full computer name which is present in the [Name] column (exclude the port number) .
The issue I was facing was that I was able to ping the computer name but not the full computer name. Once you are able to ping the full computer name, reconfiguring the test controller should fix the issue.

TFS 2012 RC - There is no agent with the name 'XXX' registered on the controller

If you see this error in the event log on the test agent:
Unable to connect to the controller on 'yyy:6901'. There is no agent with the name 'XXX' registered on the controller.

You will also be able to see the detailed error if you click on View Log while configuring the Test Agent:
V, 2012/08/08, 18:55:31.882, Observed that agent XXX does not exist. Microsoft.VisualStudio.TestTools.Exceptions.EqtException: There is no agent with
the name 'XXX' registered on the controller.
Server stack trace:
   at Microsoft.VisualStudio.TestTools.Controller.AgentManager.GetAgentMachine(String agentName)
   at Microsoft.VisualStudio.TestTools.Controller.AgentManager.GetAgentProperties(String agentName)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.VisualStudio.TestTools.Execution.IControllerCommands.GetAgentProperties(String agentName)
   at Microsoft.VisualStudio.TestTools.ConfigCore.TestControllerHelper.AgentExists(String agentName, IControllerCommands controllerCmds)


The fix is to make sure that while configuring the test agent, you type the test controller name in the same case as the computer name of the test controller. So if the computer name of the test controller is YYY, make sure that you type YYY and not yyy