Stored procedure, which table is used ?
From: chris (anonymous_at_discussions.microsoft.com)
Date: 02/19/04
- Next message: Pavils Jurjans: "Re: Migrating from Access, boolean field problem"
- Previous message: mannie: "Re: Backups Best Practices"
- In reply to: ben brugman: "Stored procedure, which table is used ?"
- Next in thread: ben brugman: "Re: Stored procedure, which table is used ?"
- Reply: ben brugman: "Re: Stored procedure, which table is used ?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Feb 2004 07:58:04 -0800
>Which table does a stored procedure use when
>run in the Query analyser under local database.
Whichever table the proc calls.
I may not be following here. All your queries and all your
procs will run in the local db by default. The exception
to this would proably be some of the "sp_"'s which will
probably be run in Master even if called from a user db.
>-----Original Message-----
>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: Pavils Jurjans: "Re: Migrating from Access, boolean field problem"
- Previous message: mannie: "Re: Backups Best Practices"
- In reply to: ben brugman: "Stored procedure, which table is used ?"
- Next in thread: ben brugman: "Re: Stored procedure, which table is used ?"
- Reply: ben brugman: "Re: Stored procedure, which table is used ?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
