Column data type - Msg 207 Invalid column name

Question

Column data type - Msg 207 Invalid column name

gjoelson 29755

SSC Eights!

Points: 848
More actions
October 30, 2018 at 10:26 am

#378409

Hello ,
Can someone point me in the right direction here. I'm having a hard time figuring this out.
I have a field - [cust_po] [varchar](20) NULL, and get a Msg 207 Invalid column name. when trying to insert. I'm using an "insert into" select , the source field is an alpha numeric and has values such as this ... SSS-BLAN-2014
I tried changing the target field data type to all kinds including float etc. but no luck.
hopefully its something simple
Thanks
Greg J
FROM [EES_app].[dbo].[coitem] a
left join [EES_App].[dbo].[co] B on a.co_num =b.co_num

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Can you give us an example of an entire query which fails - something which we can run in isolation?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 2

The error message is clear: "Invalid column name...." The error message also tells you which column name is invalid, but you didn't include that part in your post. You need to fix the name (not the data type) of the invalid column.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 3

Phil Parkin - Tuesday, October 30, 2018 11:42 AM
Can you give us an example of an entire query which fails - something which we can run in isolation?

Here is a sample of what I was trying to do , this is a simple insert , but my actual task is an "insert into select" . but I broke it out like this so I could try understand what was happening.
This column issue is ,[cust_po] to get it to work I have to add single quotes ' '

USE [SSSUTIL]
GO

INSERT INTO [dbo].[CustomerOrders]
           ([co_num]
           ,[cust_po]
           ,[co_line]
    )
     VALUES
           (38322
           ,B123
           ,2)
GO

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 4

drew.allen - Tuesday, October 30, 2018 11:50 AM
The error message is clear: "Invalid column name...." The error message also tells you which column name is invalid, but you didn't include that part in your post. You need to fix the name (not the data type) of the invalid column.
Drew

Drew, thanks but I'm not sure I see what the problem with the name is ? I'm using a [Cust_po] I'm using other columns with an underscore as well and they are not a problem ?
hankst

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Looks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

gjoelson 29755 - Tuesday, October 30, 2018 12:56 PM
Phil Parkin - Tuesday, October 30, 2018 11:42 AM
Can you give us an example of an entire query which fails - something which we can run in isolation?
Here is a sample of what I was trying to do , this is a simple insert , but my actual task is an "insert into select" . but I broke it out like this so I could try understand what was happening.
This column issue is ,[cust_po] to get it to work I have to add single quotes ' '
USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
           ([co_num]
           ,[cust_po]
           ,[co_line]
    )
     VALUES
           (38322
           ,B123
           ,2)
GO

B123 is a string and therefore needs to be 'B123'

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 7

Lynn Pettis - Tuesday, October 30, 2018 1:27 PM
Looks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.

Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.

CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........

Thanks

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 8

Phil Parkin - Tuesday, October 30, 2018 1:30 PM
gjoelson 29755 - Tuesday, October 30, 2018 12:56 PM
Phil Parkin - Tuesday, October 30, 2018 11:42 AM
Can you give us an example of an entire query which fails - something which we can run in isolation?
Here is a sample of what I was trying to do , this is a simple insert , but my actual task is an "insert into select" . but I broke it out like this so I could try understand what was happening.
This column issue is ,[cust_po] to get it to work I have to add single quotes ' '
USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
           ([co_num]
           ,[cust_po]
           ,[co_line]
    )
     VALUES
           (38322
           ,B123
           ,2)
GO
B123 is a string and therefore needs to be 'B123'

Yes, correct when I add the quotes it inserts. But since I'm doing a "Insert into Select" I cant do that. any suggestions ?

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

gjoelson 29755 - Tuesday, October 30, 2018 2:16 PM
Phil Parkin - Tuesday, October 30, 2018 1:30 PM
gjoelson 29755 - Tuesday, October 30, 2018 12:56 PM
Phil Parkin - Tuesday, October 30, 2018 11:42 AM
Can you give us an example of an entire query which fails - something which we can run in isolation?
Here is a sample of what I was trying to do , this is a simple insert , but my actual task is an "insert into select" . but I broke it out like this so I could try understand what was happening.
This column issue is ,[cust_po] to get it to work I have to add single quotes ' '
USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
           ([co_num]
           ,[cust_po]
           ,[co_line]
    )
     VALUES
           (38322
           ,B123
           ,2)
GO
B123 is a string and therefore needs to be 'B123'
Yes, correct when I add the quotes it inserts. But since I'm doing a "Insert into Select" I cant do that. any suggestions ?
Thanks

A what?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 10

gjoelson 29755 - Tuesday, October 30, 2018 2:16 PM
Phil Parkin - Tuesday, October 30, 2018 1:30 PM
gjoelson 29755 - Tuesday, October 30, 2018 12:56 PM
Phil Parkin - Tuesday, October 30, 2018 11:42 AM
Can you give us an example of an entire query which fails - something which we can run in isolation?
Here is a sample of what I was trying to do , this is a simple insert , but my actual task is an "insert into select" . but I broke it out like this so I could try understand what was happening.
This column issue is ,[cust_po] to get it to work I have to add single quotes ' '
USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
           ([co_num]
           ,[cust_po]
           ,[co_line]
    )
     VALUES
           (38322
           ,B123
           ,2)
GO
B123 is a string and therefore needs to be 'B123'
Yes, correct when I add the quotes it inserts. But since I'm doing a "Insert into Select" I cant do that. any suggestions ?
Thanks

What is the datatype of the source column and what is the datatype of the target column?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 11

gjoelson 29755 - Tuesday, October 30, 2018 2:13 PM
Lynn Pettis - Tuesday, October 30, 2018 1:27 PM
Looks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.
Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.
CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........
Thanks

You reference TWO fields name cust_po: one in the target table (which you've checked) and one in EES_App.dbo.co (which you haven't checked). I'm guessing that it's not a valid column in the second table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 12

Phil Parkin - Tuesday, October 30, 2018 2:32 PM
gjoelson 29755 - Tuesday, October 30, 2018 2:16 PM
Phil Parkin - Tuesday, October 30, 2018 1:30 PM
gjoelson 29755 - Tuesday, October 30, 2018 12:56 PM
Phil Parkin - Tuesday, October 30, 2018 11:42 AM
Can you give us an example of an entire query which fails - something which we can run in isolation?
Here is a sample of what I was trying to do , this is a simple insert , but my actual task is an "insert into select" . but I broke it out like this so I could try understand what was happening.
This column issue is ,[cust_po] to get it to work I have to add single quotes ' '
USE [SSSUTIL]
GO
INSERT INTO [dbo].[CustomerOrders]
           ([co_num]
           ,[cust_po]
           ,[co_line]
    )
     VALUES
           (38322
           ,B123
           ,2)
GO
B123 is a string and therefore needs to be 'B123'
Yes, correct when I add the quotes it inserts. But since I'm doing a "Insert into Select" I cant do that. any suggestions ?
Thanks
What is the datatype of the source column and what is the datatype of the target column?

Source column is nvarchar and target column is Varchar (20)
did try the target with various other type including nvarchar.

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 13

drew.allen - Tuesday, October 30, 2018 2:47 PM
gjoelson 29755 - Tuesday, October 30, 2018 2:13 PM
Lynn Pettis - Tuesday, October 30, 2018 1:27 PM
Looks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.
Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.
CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........
Thanks
You reference TWO fields name cust_po: one in the target table (which you've checked) and one in EES_App.dbo.co (which you haven't checked). I'm guessing that it's not a valid column in the second table.
Drew

Drew, yup it is valid in the source table , I'm using some joins and it exists in the [CO] on the join

gjoelson 29755 SSC Eights! Points: 848 More actions · Answer 14

gjoelson 29755 - Tuesday, October 30, 2018 3:19 PM
drew.allen - Tuesday, October 30, 2018 2:47 PM
gjoelson 29755 - Tuesday, October 30, 2018 2:13 PM
Lynn Pettis - Tuesday, October 30, 2018 1:27 PM
Looks to me that cust_po may not be the column name in the table. Of course we don't know that as we can't see what you can see.
Lynn,
so the [cust_po] is in the destination table below is a sample of some of the columns and specifically the Cust_po column. also I have screenshot the query and attached the files.
CREATE TABLE [dbo].[CustomerOrders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[co_num] [varchar](20) NULL,
[cust_po] [varchar](20) NULL,
[co_line] [int] NULL,
[co_release] [int] NULL,
[item] [char](25) NULL, ........
Thanks
You reference TWO fields name cust_po: one in the target table (which you've checked) and one in EES_App.dbo.co (which you haven't checked). I'm guessing that it's not a valid column in the second table.
Drew
Drew, yup it is valid in the source table , I'm using some joins and it exists in the [CO] on the join
FROM [EES_app].[dbo].[coitem] a
left join [EES_App].[dbo].[co] B on a.co_num =b.co_num

I meant to add that , that its a column on table B , so yes its on both the source and target tables) I'm wondering if the join has something to do with it ?