CODEWITHSUNDEEP
sql-serverstored-proceduressql-merge
Mou
Mou

Reputation: 16282

SQL Server: Merge statement compilation error

I have used SQL Server MERGE statement in my stored procedure and when compiling the stored procedure I'm getting this error:

The object reference [dbo].[Student].[ID] differs only by case from the object definition [dbo].[Student].[Id].

My table structure is:

CREATE TABLE [dbo].[Student] 
(
    [Id]        INT          NOT NULL,
    [FirstName] VARCHAR (50) NULL,
    [LastName]  VARCHAR (50) NULL,
    [StateID]   INT          NULL,
    [CityID]    INT          NULL,
    [IsActive]  BIT          NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
)

My stored procedure is:

CREATE PROCEDURE [dbo].USP_UpdateStudent
AS
    Declare @Data xml  

    SET @Data = '<?xml version="1.0" encoding="utf-16"?>
                 <Students>  
                    <Student>    
                       <ID>2</ID>    
                       <FirstName>Tridip</FirstName>    
                       <LastName>Bhattacharjee</LastName>    
                       <IsActive>true</IsActive>    
                       <StateID>2</StateID>    
                       <CityID>4</CityID>  
                    </Student>
                 </Students>'  
    BEGIN TRY
        MERGE INTO Student as Trg  
        USING (SELECT
                   d.x.value('id[1]', 'varchar(MAX)') as ID,
                   d.x.value('FirstName[1]', 'varchar(MAX)') as FirstName ,  
                   d.x.value('LastName[1]', 'varchar(MAX)') as LastName,
                   d.x.value('StateID[1]','int') as StateID,
                   d.x.value('CityID[1]','int') as CityID,
                   d.x.value('IsActive[1]','bit') as IsActive
               FROM
                   @data.nodes('/Students/Student') as  d(x)) AS Src ON Trg.ID = Src.ID  

        WHEN MATCHED THEN
            UPDATE 
            SET Trg.FirstName = Src.FirstName,  
                Trg.LastName = Src.LastName,
                Trg.StateID = Src.StateID,
                Trg.CityID = Src.CityID,
                Trg.IsActive = Src.IsActive

        WHEN NOT MATCHED BY TARGET THEN
            INSERT (FirstName, LastName, StateID, CityID, IsActive) 
            VALUES(Src.FirstName, Src.LastName, Src.StateID, Src.CityID, Src.IsActive);   
    END TRY
    BEGIN CATCH
      -- Insert Error into table
      --INSERT INTO #error_log(message)
      --VALUES (ERROR_MESSAGE());
    END CATCH

What's my mistake causing this error? Thanks

Edit

Got 2 good links to connect sql compact db by SSMS and LinqPad. here is the link

https://stackoverflow.com/a/16692386/728750 https://dba.stackexchange.com/questions/47534/how-do-i-view-a-microsoft-sql-server-compact-database

Thanks

Upvotes: 5

Views: 6470

Answers (2)

ChuckB
ChuckB

Reputation: 1346

If you don't care about the case settings in the SQL, then you can disable this warning in the Project Settings by unchecking the "Validate casing on identifiers" box enter image description here

Upvotes: 5

Matt Gibson
Matt Gibson

Reputation: 38238

This isn't a SQL Server error as such, just a warning from your development tools. It's telling you that you're being inconsistent with the case of your object names, in this case mixing and matching Id and ID (note the lower case "d").

In most cases with SQL Server, it won't actually matter and your SQL will still run; in my experience most installations are case-insensitive. But being consistent is generally a good thing, and might prevent future problems, so I'd just fix your code so you consistently use the column name exactly as it's declared in your table definition.

Upvotes: 2

Related Questions