November 13, 2014 at 5:23 am
I have one drop and create procedure script in one sql session and execute the procedure in other session. Now when i am compiling the procedure in first session, it gets completed successfully. Now when i move to other sesison and try to execute the procedure, it gives me error saying
"Invalid column name 'ColumnName'."
Now when I execute the procedure in same session in which procedure was compiled, it runs successfully. Once run, I go back to my other session and it runs again. Have anyone seen such issue ?
November 13, 2014 at 6:34 am
I'm honestly not sure. It sounds like something was cached at the client of the other session. Recompiling the procedure from the first session would invalidate the execution plan in cache, so the call from the second session should cause a new plan to be created. Any error seems like it would be from the client. When you say session, which application are we talking about, or are we talking about different applications?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 13, 2014 at 7:36 am
I'm working in SQL server only
November 13, 2014 at 8:10 am
Then that's odd. I can't recreate it. Here's what I did. I opened a session and used this code:
CREATE PROCEDURE dbo.TestingColumns
(@SalesOrderID INT)
AS
SELECT soh.SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderID = @SalesOrderID;
Then I opened a second query window and executed that procedure like this:
EXEC dbo.TestingColumns @SalesOrderID = 43683;
Then I ran this code in the first window:
ALTER PROCEDURE dbo.TestingColumns (@SalesOrderID INT)
AS
SELECTsoh.SalesOrderID,
soh.OrderDate
FROMSales.SalesOrderHeader AS soh
WHEREsoh.SalesOrderID = @SalesOrderID;
So that added a column, just like you're saying. I went back to the second window, executed the proc again, without executing it from the first window and it worked.
Either I don't understand your issue, entirely possible, or there's something else about the situation that I'm missing, again, entirely possible.
Is this an accurate test?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 14, 2014 at 5:01 am
To summarize, i've this simple procedure definition in one sql session.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.testproc') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.testproc
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.testproc
@param1 INT = NULL,
@param2 INT = NULL
AS
BEGIN
/*
exec dbo.testproc @param1 = 10, @param2 = 20
*/
IF OBJECT_ID('TEMPDB..#TEMP_TABLE') IS NOT NULL BEGIN DROP TABLE #TEMP_TABLE END
CREATE TABLE #TEMP_TABLE
([COL2][VARCHAR] (50) NULL,,
[COL3][VARCHAR] (50) NULL,
[COL4][VARCHAR] (50) NULL)
SELECT IDENTITY(INT, 1, 1) AS Sno, COL2, COL3, COL4, COL5, COL6
INTO #TEMP1
FROM dbo.TEMPDBR (NOLOCK)
WHERE COL1 = @param1
AND COL12 = 1
ORDER BY COL2, COL3, COL4
SELECT IDENTITY(INT, 1, 1) AS Sno, COL2, COL3, COL4, COL5, COL6
INTO #TEMP2
FROM dbo.TEMPDBR (NOLOCK)
WHERE COL1 = @param2
AND COL12 = 1
ORDER BY COL2, COL3, COL4
INSERT INTO #TEMP_TABLE
(COL2, COL3, COL4)
SELECTCOALESCE(T1.COL2, T2.COL2),
COALESCE(T1.COL3, T2.COL3),
COALESCE(T1.COL4, T2.COL4),
@param1, @param1
FROM #TEMP1T1
FULL OUTER JOIN #TEMP2T2
ON T1.COL2 = T2.COL2
AND T1.COL3 = T2.COL3
ORDER BY COALESCE(T1.COL2, T2.COL2),
COALESCE(T1.COL3, T2.COL3),
COALESCE(T1.COL5, T2.COL5)
END
After running the whole code above, when i execute the procedure in same session using highlighted execute command (in the comment), it runs fine.
However if the same query if i try to run in different sql session, it gives me error:
exec dbo.testproc @param1 = 10, @param2 = 20
November 14, 2014 at 5:09 am
In the other session (where it doesn't run), is there an existing temp table created with matching names? You won't get a create table error if there is, SQL uniquifies the names, but I have heard of this error occuring in that situation (one reason I hate generic names like #Temp1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2014 at 5:17 am
Nope. I was getting following error in second spid (as per my presented scenario):
"Invalid column name 'Col5'."
And the name #Temp1 is taken here just for the example. Actually following naming convention.
November 14, 2014 at 5:44 am
CREATE TABLE #TEMP_TABLE
([COL2][VARCHAR] (50) NULL,,
[COL3][VARCHAR] (50) NULL,
[COL4][VARCHAR] (50) NULL)
definitely will not compile, please correct procedure code.
November 14, 2014 at 5:52 am
Isnt this your Issue
INSERT INTO #TEMP_TABLE
(COL2, COL3, COL4)
SELECTCOALESCE(T1.COL2, T2.COL2),
COALESCE(T1.COL3, T2.COL3),
COALESCE(T1.COL4, T2.COL4),
@param1, @param1
FROM #TEMP1T1
FULL OUTER JOIN #TEMP2T2
ON T1.COL2 = T2.COL2
AND T1.COL3 = T2.COL3
ORDER BY COALESCE(T1.COL2, T2.COL2),
COALESCE(T1.COL3, T2.COL3),
COALESCE(T1.COL5, T2.COL5)
The insert into only Lists 3 columns in the values section while you are trying to insert 5 columns.
Unless we have an incorrect piece of code.
also there is no need for the ORDER BY COALESCE statements, simply Alias the columns and then order by them
EG
SELECTCOALESCE(T1.COL2, T2.COL2) AS C1,
COALESCE(T1.COL3, T2.COL3) AS C2,
COALESCE(T1.COL4, T2.COL4) AS C3,
@param1, @param1
FROM #TEMP1T1
FULL OUTER JOIN #TEMP2T2
ON T1.COL2 = T2.COL2
AND T1.COL3 = T2.COL3
ORDER BY C1,C2,C3
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2014 at 5:55 am
Makes me wonder what else is in the real cost that's missing from the post. The successful execution in one session and not the other has me puzzled (as it makes no sense) but without the real code, the odds of seeing the problem go down. We can only see what you post.
November 14, 2014 at 6:28 am
Yeah, I have to agree. The posted code fails to compile. I can edit it, but I'm guessing I won't see the error if I do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 14, 2014 at 6:37 am
sqlnaive (11/14/2014)
And the name #Temp1 is taken here just for the example. Actually following naming convention.
Ok, so the posted code is not the code that's failing, but an edited version of it. I'm done guessing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2014 at 1:58 am
I faced a similar issue a while back. In my case there was a dynamic SQL block in the proc which was creating problems due to creation temp tables.
One way to isolate the problem is that you comment the code in proc and keep un-commenting line by line until you reach the point of error. If the proc is too large then do the same activity in code blocks instead of each line.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply