sproc is skipping steps... - 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

SQL Server 2000 Forums

Transact-SQL (2000)

sproc is skipping steps...

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-29 : 13:51:28

Hello all...

Tody I'm having a problem with a stored procedure that performs many steps; it imports data, export using bcp, creates table, renames tables, etc...

My problem is that when I initially run the proc after performing a new file import, the sproc gives me an error stating that it can't find a table name. When I copy and past the sproc in query analyzer and higlight each step of the sproc and execute them seperately, everything works fine.

Any ideas?

thanks.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-29 : 14:08:22

quote:

it imports data, export using bcp, creates table, renames tables

SQL Server places restrictions on ALTER TABLE statements run in a given batch. In particular you cannot modify a table through ALTER TABLE and then reference the modification in the same batch. In QA, running statements one at a time runs them in their own batch; the statements through the sproc all run in the same batch. You will have to either use dynamic SQL, split up the proc into pieces, or rework the process so it doesn't rename objects.

Jonathan Boott, MCDBA
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-29 : 14:52:18

When the sp compiles (on first run) it will resolve object names and structures. If they don't exist for the compile or change during running then you will get errors.
You can create an object then call another sp which will be recompiled on each run.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-08-29 : 16:30:52

How can I split up the Proc into several pieces so that it runs?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-29 : 19:41:38

You can't alter structures and access them in the same sp so wherever you try to access data in a table you've created or altered put that processing in other SP's and pass data to them via parameters or temp tables. And create the SPs with recompile to be on the safe side.

as
instead of
create table x ...
insert x select * from tbl
alter table x add v int
select * from x
go
use

as
create table x ...
exec ins
alter table x add v int
exec sel
go
create proc ins
as
insert x select * from tbl
go
create proc sel
as
select * from x
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources