error in query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

error in query

Author

Topic

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 09:11:23

I'm trying to run the following update query:
UPDATE c_d_feecode_btype
SET techcode = twos_table_test.techcode
from c_d_feecode_btype, twos_table_test
WHERE c_d_feecode_btype.id = twos_table_test.id and
c_d_feecode_btype.id in (SELECT c_d_feecode_btype.id FROM c_d_FeeCode_btype WHERE c_d_feecode_btype.techcode Like '02%')
and c_d_feecode_btype.techcode is not null and c_d_feecode_btype.techcode <> '' and c_d_feecode_btype.techcode <> 'N/A'

But have this error:
Server: Msg 208, Level 16, State 1, Procedure z_sp_check_environment, Line 31
Invalid object name '#z_sessions_active'.

Thanks for any ideas.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2010-09-21 : 09:41:12

What does the stored proc z_sp_check_environment do, this appears to be executing somewhere happen in a trigger on the table your trying to update

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 09:57:57

Obviously, your error is not with thias code

in any case



	UPDATE c
	   SET techcode = twos_table_test.techcode
	  FROM c_d_feecode_btype c
INNER JOIN twos_table_test t
		ON c_d_feecode_btype.id = twos_table_test.id 
	 WHERE c_d_feecode_btype.techcode Like '02%'

/*
and c_d_feecode_btype.id in (
		SELECT c_d_feecode_btype.id 
		  FROM c_d_FeeCode_btype 
		 WHERE c_d_feecode_btype.techcode Like '02%')
*/
	  AND c_d_feecode_btype.techcode is not null 
	  AND c_d_feecode_btype.techcode <> '' 
	  AND c_d_feecode_btype.techcode <> 'N/A'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 10:03:20

I didn't think the table had a trigger. How do I tell if it does in SQL 2000? This is script for z_sp_check_environment:
CREATE PROCEDURE z_sp_check_environment
--$$SOURCE mssa NTCOPATH copTest 09/17/2008 10:43:54 2.2.0 **DO_NOT_EDIT_THIS_LINE**
--$$CONVERTED : This SQL has been converted from SYBASE to MS-SQL. Some syntax might have been changed.
@z_table Varchar(30) ="",
@z_mode Varchar(15) = NULL OUTPUT,
@z_session Varchar(15) = NULL OUTPUT,
@z_txn Varchar(10) = NULL OUTPUT,
@z_appnum Integer = NULL OUTPUT,
@z_sql_log_flag Bit = 0 OUTPUT,
@z_audit_trail char/*__NL__*/(1) = "Y" OUTPUT

AS
/* Check for proper environment values (e.g. logging turned on)
and return some other environment values.
SQL generates an automatic Rollback if the environment is undefined.
May also be called with a nill table just to get the environment variables

RETURN VALUE:
-1 Unauthorized Access (not used)
1 Environment OK

MODIFIED: 1/94 pjl -- Created
3/ 8/95 smb -- Unknown
1/31/96 pjl -- Unknown
5/30/96 jjg -- Removed audit trail code
7/26/06 af7421 - widen session ID (CQ 16928)

*/
BEGIN

SELECT @z_session=session_id,
@z_txn=transaction_cnt,
@z_mode=edit_mode,
@z_appnum=Convert(Integer,app_num),
@z_sql_log_flag = Convert(Bit,IsNULL(sql_logger_flag,0)),
@z_audit_trail = audit_trail
FROM #z_sessions_active

/*
If can't find the session, assume user is a developer coming in from the PowerBuilder
database painter or administrator - just return okay status.
*/
IF (@z_session) IS NULL
SELECT @z_session="?" , @z_txn="0", @z_mode="new", @z_appnum=0, @z_sql_log_flag = 0

RETURN 1

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thanks!!

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 10:09:51

where is the temp table created?

you will NEVER see it in this execution...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 10:38:40

I thought in the same database. I created the temp table using query analyzer with this:
SELECT DISTINCT a.id, a.name, b.techcode INTO twos_table_test
FROM c_d_feecode as a, c_d_feecode_btype as b
WHERE a.id = b.id and a.active_flag = 'A' and b.techcode like '02%';

Thanks again.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 10:41:51

That's not a temp table

Your temp table in yor sproc (#z_sessions_active) was created where and when

temp tables (except global temp tables ##table) are only aroung for the life of a thread

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 10:58:50

I'm sorry, I don't understand this. There is a table called z_sessions_active. There is nothing in it.

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 11:14:19

Is this because I'm using a subquery? If so, I'll work on using the inner join.

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 11:33:12

denise,

I'm getting lost..please restate your problem and what you are trying to do.

Tell us what you are running or doing

Understand the temp table in your sproc will never be seen

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 12:02:47

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 12:18:28

So you have a trigger calling a stored procedure it appears

and the procedure references a temp table that does not exist in the execution thread

Not that I uderstand what you are trying to do...

But you need to find out where the temp table is being created

If it is being used by many processes, then it could be a problem

open the table in management studio and look for the triggers on c_d_feecode_btype...right click on the trigger and script it to a new query window and post that code

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 12:20:32

and use this update


	UPDATE c
	   SET techcode = twos_table_test.techcode
	  FROM c_d_feecode_btype c
INNER JOIN twos_table_test t
		ON c_d_feecode_btype.id = twos_table_test.id 
	 WHERE c_d_feecode_btype.techcode Like '02%'

you don't need the other predicates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

denise
Starting Member

14 Posts

Posted - 2010-09-21 : 12:40:35

What I'm tring to do is to update all the techcodes for each id to one that starts wit02 if any given id has a techcode that starts with 02.
Example,
ID Code
123 024071
123 034071
234 035678
234 065678

In this case results would be:
ID Code
123 024071
123 024071
234 035678
234 065678

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources