Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

##table question? Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 9:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
hello.

here is the scenario:
sp 1 inserts data in #table, #table is in sp2 and sp3
sp 2 and sp3 are datasources for 2 report dataregions.

sp 1-> sp2
Sp 1->sp3

both sp2 and sp3 have #temp table. I want to make ## global and use it with both sp2 and sp3 without using it twice.

Wondering if you could advise with?
if I should create ##table in Sp2 and do the insert but how do i use it in sp3 i mean reference wise.

thank you !!
Post #1349731
Posted Friday, August 24, 2012 9:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Will sp2 ever be run while the table already exists? As in, while another copy of sp2 is running, or sp3 is running, or any other data is accessing the table?

If so, you'll need to either reconsider the design (probably your best bet), or make the table creation conditional, or make the proc wait and loop a check on the table, so it doesn't try to create a table that already exists.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1349741
Posted Friday, August 24, 2012 9:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
thanks for replying. I did not understand what you mean.

Post #1349755
Posted Friday, August 24, 2012 9:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
If you create a globabl temporary table, one that starts with ## instead of just #, and you have a stored procedure that creates that table, then if the procedure is run while the table already exists, the procedure will fail.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1349758
Posted Friday, August 24, 2012 9:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
oh. I am now thinking off making changes in sp1 adding ## table in there and then using that in sp2 and sp3.
how do i reference those in sp2 and sp3? i mean sharing of global table among sp.
thanks.
Post #1349764
Posted Friday, August 24, 2012 9:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Same as any other table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1349777
Posted Friday, August 24, 2012 10:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Just make sure that your sp1 is called by one caller/user at the same time...



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1349789
Posted Monday, August 27, 2012 5:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
GSquared (8/24/2012)
Same as any other table.


and should i drop the ## table in sp2 and sp3 both?
Post #1350326
Posted Monday, August 27, 2012 5:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 14, 2014 3:19 PM
Points: 158, Visits: 334
Eugene Elutin (8/24/2012)
Just make sure that your sp1 is called by one caller/user at the same time...

thank you.
Post #1350331
Posted Monday, August 27, 2012 1:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, April 13, 2014 9:51 PM
Points: 4,570, Visits: 8,315
SQL_path (8/27/2012)
GSquared (8/24/2012)
Same as any other table.


and should i drop the ## table in sp2 and sp3 both?


Generally the good practice is to drop objects within the same procedure where they've been created.

Of course, there are exclusions to every rule.
Post #1350547
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse