Thursday, February 21, 2013

Error: Unable to Open Database Project in Visual Studio 2012:

Error: Unable to Open Database Project in Visual Studio 2012: This version of SQL Server Data Tools is not compatible with the database run time components installed on this.


To fix this problem you need to install SQL Server Data Tools – November 2012 update

then try opening same project, you will get instruction saying (needs migration) with project file,
To migrate your project you need to Right Click on the Project name and select “Reload Project”.

All done here.

Friday, February 08, 2013

Error: "No column name was specified for column 1 of 'SOURCE'" in SQL Server Merge statement

when I was working with Merge statement with SQL Server 2008 then while writing this I got this error and after couple of min i got the issues 

Declare
      @Id         Int = 10,
      @Name       Varchar(20) = 'Kesharwani',
      @Salary     Int = 30000
          
MERGE INTO [Temp] as TARGET
      USING (SELECT @Id,@Name,@Salary) AS
SOURCE ON TARGET.empId = SOURCE.empId
      WHEN MATCHED THEN
            UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary
      WHEN NOT MATCHED THEN
            INSERT (empId,Name,Salary)
VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);

While running above query sometime I got this error: No column name was specified for column 1 of 'SOURCE'.

The problem in the above query the alias name not there on the columns in Select statement, to resolve this error above query can be written in following way

Declare
      @Id         Int = 10,
      @Name       Varchar(20) = 'Kesharwani',
      @Salary     Int = 30000
          
MERGE INTO [Temp] as TARGET
      USING (SELECT @Id AS empId,@Name AS Name,@Salary AS Salary) AS
SOURCE ON TARGET.empId = SOURCE.empId
      WHEN MATCHED THEN
            UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary
      WHEN NOT MATCHED THEN
            INSERT (empId,Name,Salary)
VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);