Stored Procedures (INSERT INTO #tmp exec proc)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedures (INSERT INTO #tmp exec proc)

Author

Topic

ct
Starting Member

3 Posts

Posted - 2006-09-29 : 11:54:07

Hi,
we have a sql stored procedure, which has following steps
1) Creats temp table
2) Insert data into temp table by executing other stored procedure (ex.
insrt into #tmp exec dbo.abc) -- it hang on this step
3)Above mentioned stored procedure dbo.abc performs folowing steps
I) creates temp table
II) Inserts into temp table from some actual tables
III) Update temp table -

Trace reports indecates execution of procedure hang at following statement

UPDATE #tmp1
SET
GRTabc = CONVERT(decimal(20,10), SUBSTRING(GRTabc_string, 2,
LEN(GRTabc_string) - 1))
, IsDefault = CASE WHEN SUBSTRING(GRTabc_string, 1, 1) = 'D' THEN 'Y'
ELSE 'N' END

Addition info:
SQL 2000 server with sp3 on windows 2003 server
1)Stored procedure uses INSERT INTO #TEMP EXEC srored procedure
2)Procedure hang at the step when it's executing other stored procedure
which suppose to return record set in it's last step 'select a,b,c,d
from #temp1'
But stored procedure hangs at update step, as mentioned above.

Exception:
If we run other procedure first (internal procedure dbo.abc, which rurns records) and then run main procedure it works fine.

Thanks and looking forward to some solution.

CT

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-29 : 14:27:59

I created a test that does exactly what you describe and it works fine, but I didn't use much data - just one column in the temp table and about 2000 rows. How much data are you using and have you done some tracing and monitoring - how large does your tempdb database become, are there any locks blocking the process? Is it possible it's not hanging, just taking a really long time?

ct
Starting Member

3 Posts

Posted - 2006-10-02 : 11:57:25

Thanks,
It suppose to pass around 1000 rows of data and it has 12 columns. It suppose to take less then 5 min but after 30 mins also it was running and trace shows, it is on the insert statement. I will check blocking process too, but it's not suppose to cause blocking as it was working fine before.
Thanks and looking forward to yr reply.

CT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources