Wednesday, March 21, 2012

Running Store procedures

Hello there
I have some procedure that i would like to build store procedure for it
If two users who which their name is dbo and run the same store procedure at
the same time, and the store procedure using temp tables. will it cause
problems?
and if so what is the way to deal with these problems?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.ilHi Roy,
Do you mean the SP will create temp tables for working storage, or that the
temp tables are created outside of the procedure as a global temp table.
Basically, standard temp tables are created using the users connection and
do not overlap with other logged in users. For instance, create table
#mytable creates a table using the users current connection.
Hope this helps
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uIlQW4t2FHA.3228@.TK2MSFTNGP15.phx.gbl...

> Hello there
> I have some procedure that i would like to build store procedure for it
> If two users who which their name is dbo and run the same store procedure
> at
> the same time, and the store procedure using temp tables. will it cause
> problems?
> and if so what is the way to deal with these problems?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>|||Whell Phil
The temp tables are created on the store procedure and when the SP ends they
are destroyed
The problem is that all my users are DBO. So my question is: when two users
which their name is DBO, Running the same procedure at the same time on
diffrent data, what will happen?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
"Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
news:eCm8k#t2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi Roy,
> Do you mean the SP will create temp tables for working storage, or that
the
> temp tables are created outside of the procedure as a global temp table.
> Basically, standard temp tables are created using the users connection and
> do not overlap with other logged in users. For instance, create table
> #mytable creates a table using the users current connection.
> Hope this helps
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uIlQW4t2FHA.3228@.TK2MSFTNGP15.phx.gbl...
>
procedure
>|||Hi Roy,
Should not be a problem since the client application they use will open a
new connection per user session. So if two users login as fred, each will
get a different connection to the SQL Server. To check is out do something
like this in QueryAnalyser.
Select tempdb and issue select * from sysobjects where type="U" order by
name
You will see what I mean from this the way that the temp tables are actually
structured within the tempdb database.
Phil
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OoTI8Lu2FHA.3744@.TK2MSFTNGP10.phx.gbl...
> Whell Phil
> The temp tables are created on the store procedure and when the SP ends
> they
> are destroyed
> The problem is that all my users are DBO. So my question is: when two
> users
> which their name is DBO, Running the same procedure at the same time on
> diffrent data, what will happen?
> --
>
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
> "Phil Simpson" <phil.simpson@.nsdlsystems.com> wrote in message
> news:eCm8k#t2FHA.1420@.TK2MSFTNGP09.phx.gbl...
> the
> procedure
>|||> If two users who which their name is dbo and run the same store procedure
> at
> the same time, and the store procedure using temp tables. will it cause
> problems?
No, each user will get a #temp table in their own scope.
If you look at tempdb..sysobjects you will see names like:
#tempname___________________________4536
7
#tempname___________________________4239
7
(Too lazy to check the exact format of the trailing number/string, but it's
something like that.)
Even better might be reducing or eliminating the need for #temp tables in
the first place.
A

No comments:

Post a Comment