SQL Server: Invalid Column Name

SQL Server: Invalid Column Name

Whenever this happens to me, I press Ctrl+Shift+R which refreshes intellisense, close the query window (save if necessary), then start a new session which usually works quite well.

SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

I suspect that you have two tables with the same name. One is owned by the schema 'dbo' (dbo.PerfDiag), and the other is owned by the default schema of the account used to connect to SQL Server (something like userid.PerfDiag).

When you have an unqualified reference to a schema object (such as a table) — one not qualified by schema name — the object reference must be resolved. Name resolution occurs by searching in the following sequence for an object of the appropriate type (table) with the specified name. The name resolves to the first match:

• Under the default schema of the user.
• Under the schema 'dbo'.

The unqualified reference is bound to the first match in the above sequence.

As a general recommended practice, one should always qualify references to schema objects, for performance reasons:

• An unqualified reference may invalidate a cached execution plan for the stored procedure or query, since the schema to which the reference was bound may change depending on the credentials executing the stored procedure or query. This results in recompilation of the query/stored procedure, a performance hit. Recompilations cause compile locks to be taken out, blocking others from accessing the needed resource(s).

• Name resolution slows down query execution as two probes must be made to resolve to the likely version of the object (that owned by 'dbo'). This is the usual case. The only time a single probe will resolve the name is if the current user owns an object of the specified name and type.

[Edited to further note]

The other possibilities are (in no particular order):

• You aren't connected to the database you think you are.
• You aren't connected to the SQL Server instance you think you are.

Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.

SQL Server : invalid column name error after GROUP BY on subquery

SQL Server does not allow the use of this aliased column in the group by clause (others like MySql do allow it) because the group by clause is executed before select.

You have to use that case statement:

group by CASE
        WHEN (pay_method='cc') AND (user_type='subscriber') THEN 'cc-subscribed'
        WHEN (pay_method='cash') AND (user_type='subscriber') THEN 'cash-subscribed'
        ELSE 'standard'
    END

Invalid column name x and There is already an object named 'x' in the database

Don't copy into a temporary table only to delete it afterwards. Just insert directly from the original.

Also:

• Don't roll your own IDENTITY column with SELECT MAX. Use a proper IDENTITY column and get the previous value using OUTPUT or SCOPE_IDENTITY().
• Don't quote every column name with [] when it doesn't need it. I haven't bothered removing them because I haven't got all day.
• I must say, I'm unclear why you have those joins, they don't appear necessary, but I've left them in.

CREATE OR ALTER PROCEDURE [dbo].[CopyShip]
     @ShipId int,
     @ShipName nvarchar(150),
     @ShipCode nvarchar(8)
AS

SET NOCOUNT ON;

INSERT INTO Ships (
  [CruiselineId], [Name], [ShipCode], [ClassId],
  [guestNumber], [staffNumber], [creationYear],
  [weight], [length], [passagerDeck], [handicapCabins], [nationality]
)
SELECT
  [CruiselineId], @ShipName, @ShipCode, [ClassId],
  [guestNumber], [staffNumber], [creationYear],
  [weight], [length], [passagerDeck], [handicapCabins], [nationality]
FROM [dbo].Ships 
WHERE ShipId = @ShipId;

DECLARE @NewShipId int = SCOPE_IDENTITY();

-- CREATING COPY OF Cabins
INSERT INTO CabinCategory (
  [ShipId], [CabinType], [BalconySize], [MinSize], [MaxSize],
  [NoOfBeds], [Category], [HexCodes], [Description],
  [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
  [HeaderDescriptionSE], [HeaderDescriptionNO],
  [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO], [CabinCategoryIdOld]
)
SELECT
  @NewShipId, [CabinType], [BalconySize], [MinSize], [MaxSize],
  [NoOfBeds], [Category], [HexCodes], [Description],
  [LongDescription], [LongDescriptonSE], [LongDescriptionNO],
  [HeaderDescriptionSE], [HeaderDescriptionNO],
  [FreeTextField], [FreeTextFieldSE], [FreeTextFieldNO],
  [CabinCategoryIdOld]
FROM [dbo].CabinCategory cc
WHERE ShipId = @ShipId;

INSERT INTO CabinToBenefits (
  [BenefitsId]
 ,[CabinCategoryId]
 ,[ShipId]
)
SELECT B.[BenefitsId]
      ,bse2.CabinCategoryId
      ,@NewShipId
FROM [dbo].[CabinToBenefits] B
JOIN dbo.CabinCategory bse on B.CabinCategoryId = bse.CabinCategoryId
JOIN dbo.CabinCategory bse2 on bse2.[Category] = bse.Category and B.ShipId = bse2.ShipId
WHERE ShipId = @ShipId;

---- CREATING COPY OF RestaurantToSeating
INSERT INTO RestaurantToSeating (
  ShipId,
  RestaurantId,
  SeatingId
)
SELECT @NewShipId,
       bse.RestaurantId,
       rs.SeatingId 
FROM RestaurantToSeating rs
JOIN dbo.Restaurant r on rs.RestaurantId = r.RestaurantId 
JOIN dbo.Restaurant bse on r.Category = bse.Category and rs.ShipId = bse.ShipId
where rs.ShipId = @ShipId;

SQL Server show invalid column name when the column name on the table exists

You have a DROP statement before the insert. That's why you get an warning that the column is missing.

You may want to move the DROP statement before the CREATE one, or even use the new syntax DROP TABLE IF EXISTS ... if it is available in your edition.

SQL Server - Invalid column name

You cannot reference an alias in the same scope where it was defined (apart from the order by clause). Typical work arounds include a subquery or CTE.

In SQL Server though, a simple option is a lateral join:

SELECT 
    V.id, V.TypeApv, V.CreateDate, 
    P.Requestor, VE.VendorName, V.InvoiceNo, V.Hawb, 
    PA.PaymentFor, V.Amount, V.Curr, V.DueDate, V.Remarks, V.OrderNo,
    X.OrderNo2, 
    ISNULL(X.OrderNo2, V.OrderNo) AS OrderNoFinal
FROM APV_AF V
LEFT JOIN APV_Person P ON P.id = V.Requestor
LEFT JOIN APV_Vendor VE ON VE.IDVendor = V.VendorName
LEFT JOIN APV_Payment PA ON PA.IDPayment = V.PaymentFor
OUTER APPLY (
    SELECT 
        STUFF((SELECT DISTINCT ', ' + CASE WHEN [e-SAM Case]='Subsequent' OR [e-SAM Case]='DDT' AND [Local SAP PO] LIKE '5%' OR [e-SAM Case]='FBS 4'
        THEN PoNo ELSE [Local SAP PO] END
                FROM v_copo VC
                WHERE VC.AWB1 = V.Hawb
        FOR XML PATH('')), 1, 1, '') AS OrderNo2
) X
ORDER BY V.CreateDate DESC

---

Related Topics