November 19, 2003 at 10:14 am
create proc extractdata
as
begin tran
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blpu_load]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[blpu_load]
select *,
IDENTITY(int,1,1) AS pro_order_temp,0 as pro_order
into blpu_load
from blpu_records
where change_type != '"S"'
insert into blpu_load
select *,0 as pro_order
from blpu_records nr
where nr.uprn in (select nl.uprn
from blpu_load nl)
and nr.change_type = '"S"'
declare @blpu_seed int
set @blpu_seed = (select max(pro_order) from nsgstreet_load)
update prcore.dbo.blpu_load
set pro_order = pro_order_temp + @blpu_seed
commit tran
===========================================
I get an error on the update line saying invalid column name 'pro_order_temp'
When I run the query in QA no error was reported and the result was fine.
Any ideas?
Thanks all.
November 19, 2003 at 10:32 am
Hmmm.
I noticed the UPDATE refers to prcore.dbo.blpu_load while the INSERT refers to the unqualified blpu_load. When you execute the stored proc check you are pointing to right db and server? Other than that...looks ok to me.
Francis
November 20, 2003 at 4:59 am
I think the issue here is for me to find a way to run query in batches inside the stored procedure. I can not use the GO batch commmand available in QA. The begin and end does not seem to work as well.
It appears columns created in a batch can not be refrenced in the same batch. They will not be available for use.
November 20, 2003 at 7:34 am
What if you removed the BEGIN TRAN and allowed SQL Server to commit prior to the update. Or at least moved the BEGIN TRAN after the DROP TABLE statement?
Francis
November 20, 2003 at 6:25 pm
Do you really need to drop the table? Perhaps you can truncate it if you are using the simple recovery model. Is the blpu_rcords table changing structure between executes?
Edited by - rstone on 11/20/2003 6:43:37 PM
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
November 20, 2003 at 7:03 pm
You could use an EXECUTE to drop and create the table via another connection to simulate the GO.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
November 21, 2003 at 3:48 am
Thanks. The execute did work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply