Invalid column name (No not the Quote Issue)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Invalid column name (No not the Quote Issue)

Author

Topic

BarryC
Starting Member

6 Posts

Posted - 2007-12-06 : 14:05:53

I get the error message "Invalid column name 'agentid'." in a stored procedure.
Here is the involved code:


create table #temp_ccd
(
agentid int,
resourceName nvarchar(50),
Interval_Start_Time datetime,
Calls_In int,
Calls_Out int,
Calls_Internal int
)

--Do some stuff that I didn't include in this sample....


INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)
	SELECT c2.resourceID, c2.Interval_Start_Time, sum(c2.Calls_In) AS callsIn, sum(c2.Calls_Out)AS callOut, sum(c2.Calls_Internal) as callsInt
		FROM #temp_ccd2 c2
		GROUP BY c2.agentid, c2.Interval_Start_Time

It yacks on the line INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)

Any ideas as to what could be the problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-06 : 14:22:34

Your code gives a bunch of errors. First, you are creating #temp_ccd, yet using #temp_ccd2 in the select. After I fix that, I get:

Msg 207, Level 16, State 1, Line 15
Invalid column name 'resourceID'.

I just don't have the time to track all of this down. Please provide an example that produces your error and not one that produces other errors when we run it on our systems.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

BarryC
Starting Member

6 Posts

Posted - 2007-12-06 : 14:28:37

It is a subprocedure. It won't run without being called by the (extensive) parent procedure.
The creation of #temp_ccd2 is another thing I left out.
I don't understand how you would run it anyway without all the tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-06 : 14:35:32

We don't need all of your code. What we need is an example which illustrates your problem.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-06 : 14:43:49

Post the structure to #temp_ccd2 at least since you are using it to insert into #temp_ccd. Does #temp_ccd2 have a column named agentid?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-06 : 15:02:19

maybe this:

INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)
	SELECT c2.resourceID, c2.Interval_Start_Time, sum(c2.Calls_In) AS callsIn, sum(c2.Calls_Out)AS callOut, sum(c2.Calls_Internal) as callsInt
		FROM #temp_ccd2 c2
		GROUP BY c2.resourceidagentid, c2.Interval_Start_Time

BarryC
Starting Member

6 Posts

Posted - 2007-12-07 : 08:50:07

[code]ALTER PROCEDURE sp_Agent_Calls
@starttime datetime,
@endtime datetime

AS

set nocount on

-- events from ContactCallDetail table

create table #temp_ccd
(
agentid int,
resourceName nvarchar(50),
Interval_Start_Time datetime,
Calls_In int,
Calls_Out int,
Calls_Internal int
)

create table #temp_ccd2
(
resourceID int,
Interval_Start_Time datetime,
Calls_In int,
Calls_Out int,
Calls_Internal int
)

INSERT INTO #temp_ccd2
(put something in there.)

INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)
SELECT c2.resourceID, c2.Interval_Start_Time, sum(c2.Calls_In) AS callsIn, sum(c2.Calls_Out)AS callOut, sum(c2.Calls_Internal) as callsInt
FROM #temp_ccd2 c2
GROUP BY c2.resourceID, c2.Interval_Start_Time[/code]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-07 : 09:01:39

quote:

INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)
SELECT c2.resourceID, c2.Interval_Start_Time, sum(c2.Calls_In) AS callsIn, sum(c2.Calls_Out)AS callOut, sum(c2.Calls_Internal) as callsInt
FROM #temp_ccd2 c2
GROUP BY c2.resourceID, c2.Interval_Start_Time

are you still asking a question or just confirming that Lamprey's suggestion solved your problem?

Em

BarryC
Starting Member

6 Posts

Posted - 2007-12-07 : 09:31:43

No Joy. Lamprey's suggestion did not solve it.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-12-07 : 10:45:22

So what error are you getting now? In your first post you had:

INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)
SELECT c2.resourceID, c2.Interval_Start_Time, sum(c2.Calls_In) AS callsIn, sum(c2.Calls_Out)AS callOut, sum(c2.Calls_Internal) as callsInt
FROM #temp_ccd2 c2
GROUP BY c2.agentid, c2.Interval_Start_Time

In your follow up post you have:

INSERT #temp_ccd (agentid, Interval_Start_Time, Calls_In, Calls_Out, Calls_Internal)
SELECT c2.resourceID, c2.Interval_Start_Time, sum(c2.Calls_In) AS callsIn, sum(c2.Calls_Out)AS callOut, sum(c2.Calls_Internal) as callsInt
FROM #temp_ccd2 c2
GROUP BY c2.resourceID, c2.Interval_Start_Time

You changed agentid to resourceid in your GROUP BY. So are you still getting the same error (invalid column name agentid)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources