April 2, 2008 at 1:06 pm
Hello,
TSQL puzzle which I cannot solve, maybe someone here can offer up an idea or two.
Scenario:
- a set of stored procedures whose names reside in a mapping table (ie sp1, sp2, ...., spN)
- another script runs 1 or more of them (ie exec(sp1) or exec(sp1).....exec(spN) )
- if there are side effects I want to suppress them temporarily, yet capture the results, if any, then later take action(s) based on the stored results
Problem:
- the number of columns the stored procedures produce is variant, so creating a temp table(s) and using an insert exec is not going to work. Also the order in which they run can be variant. (ie user could say: exec(sp3), exec(sp5), exec(sp1) or exec(sp5), exec(sp4))
- using OPENROWSET and then EXEC is working for a small number of iterations but is 'blowing up' after the number of iterations exceeds 40 or so.
Illustrated Example: (works for say 24 iterations but craps for 48)
100 stored procs
50 of them have results of 1 or more rows
50 of them have results of 0 rows
SET @sql = SELECT A.* INTO _DataTable FROM OPENROWSET( yadaYada ..@dbName, @spName, etc) A
EXEC(@sql)
SELECT * FROM _DataTable
DROP TABLE _DataTable
Observations:
- TRAN levels are fine no nesting going on
- no LOCKS or BLOCKS in SMS
Questions:
- is there something OPENROWSET is doing that is killing me?
Thanks, in advance, for even reading all this mumbo jumbo.
April 3, 2008 at 3:00 am
How about normalizing results?
Write out a record per column/value pair from the SP?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply