Re: Stored procedure, which table is used ?
From: ben brugman (ben_at_niethier.nl)
Date: 02/20/04
- Next message: Fabien: "I can't stat SQL in a user account"
- Previous message: corinne: "SQL Licence Key"
- In reply to: Tibor Karaszi: "Re: 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: Fri, 20 Feb 2004 10:09:31 +0100
Thanks Tibor,
> something like sp_MS_mark.... or something along those lines. Use at own
> risk etc etc.
First I'll try to use a "USE" option, because this is supported.
Is there a reasoning behind the local versus master table use?
(I am confused.)
For example in a master sp_ .....
select 'Tibor' as name into demotable <--- local database
select * from demotable <--- master database (error
if not exists).
exec ('select * from demotable') <--- local database
drop table demotable <--- local database
Seems that during DDL the local database is used and for
the select the master database is used.
ben brugman
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:ODpKn649DHA.1948@TK2MSFTNGP12.phx.gbl...
> I believe that you can mark a stored procedure as a "proper" system stored
> procedure (meaning some bits are set in sysobjects.status for the proc)
and
> doing so it will reference local table even for non system tables. I
haven't
> really played with this as it isn't supported. There's a system proc
called
> something like sp_MS_mark.... or something along those lines. Use at own
> risk etc etc.
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
>
> "ben brugman" <ben@niethier.nl> wrote in message
> news:%23ixkxNv9DHA.2736@TK2MSFTNGP10.phx.gbl...
> > 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: Fabien: "I can't stat SQL in a user account"
- Previous message: corinne: "SQL Licence Key"
- In reply to: Tibor Karaszi: "Re: 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
|
