Stored procedure, which table is used ?
From: ben brugman (ben_at_niethier.nl)
Date: 02/19/04
- Next message: Luiz Lima: "Error: Could not complete cursor operation because table schema changed...."
- Previous message: Marshall: "SQL-DMO Service already running error"
- Next in thread: chris: "Stored procedure, which table is used ?"
- Reply: chris: "Stored procedure, which table is used ?"
- Reply: Tibor Karaszi: "Re: Stored procedure, which table is used ?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Feb 2004 15:19:51 +0100
A stored procedure in the master database.
Which table does a stored procedure use when
run in the Query analyser under local database.
If I do a 'normal' select from, it tries to read the
table form master.
During an exec the table comes from local database.
Why the difference ?
What should I do to 'force' the 'local' table from database ?
Using an exec around all my queries is less elegant.
(more confusing and error prone).
Is there a design that I am missing ?
ben brugman
RESULTS FROM HERE :
OKKI
------
Master
(1 row(s) affected)
OKKI
----------
LocalDatabase
THE DEMO CODE :
-- In the master database.
use master
go
CREATE PROCEDURE sp_demo AS
select * from demotable
exec ('select * from demotable')
GO
-- In the master database.
use master
select 'Master' as OKKI INTO DEMOTABLE
-- In another database.
use LocalDatabase
select 'LocalDatabase' as OKKI INTO DEMOTABLE
--In the query analyser run the sp_demo under
--the another database.
-- -- print 'RESULTS FROM HERE :' use LocalDatabase exec sp_demo use LocalDatabase drop table demotable use master drop table demotable drop procedure sp_demo
- Next message: Luiz Lima: "Error: Could not complete cursor operation because table schema changed...."
- Previous message: Marshall: "SQL-DMO Service already running error"
- Next in thread: chris: "Stored procedure, which table is used ?"
- Reply: chris: "Stored procedure, which table is used ?"
- Reply: Tibor Karaszi: "Re: Stored procedure, which table is used ?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
