Help! T-SQL, Calling multiple stored procs?

I have to load a webpage table up with values from a database as such:

The Webpage Expects a Final result set of rows with 5 columns each built as below:

For each Row Returned by StoredProc1

Write Columns 1-3 = StoredProc2

Write Column 4 = StoredProc3

Write Column 5 = StoredProc4

Next Row

But I am not sure how to write the conditionals to CALL the stored procs i wrote and loop by a return value from StoredProc1.

Any help is much appreciated!

THANKS!

I would prefer to create separate stored proc with entire logic encapsulated within it, if I were you. Loops are fast and typical in client languages, but in SQL it's better not to be overused.|||So create just one big stored proc to do it all?
|||Yes. And calling stored procs from within stored procs is not so fast.|||

Can you be a little more specific about what each stored proc does? I mean, if it is a set of procs you are writing, then I doubt this is the best way to do it. I would be more inclined to write one stored procedure with one SELECT statement that joins 3 larger sets together (perhaps in temp tables) on the common value from a fourth set.

select *

from QueryFromSP1

outer join (QueryFromSP2) as QueryFromSP2
on QueryFromSP1.key = QueryFromSP2.key

...

The queries could be temp tables, or function calls, depending on the need, but a cursor like you have suggested will probably be a dog in performance (and not one of those really fast dogs either. Think hound dog on a hot Mississippi morning dogs.)

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?
Hi
Sounds like blocking at table level. If network connection issues occur, DTC
notices it and aborts.
Regards
Mike
"Chan" wrote:

> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999% fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using "transaction"
> get the same error over and over and over again till i restart MSDTC service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?
|||Hi Mike
i am a litlte bit newbie for mssql
just typed "'sp_lock" to see if there is a table level lock in query analyzer
and no table lock information found on the target database which causing
troubles
would you please let me know how you find lock information?
one more thing i did not mention
number of ports opened is 10, not microsofts' recommendation minimum of 15~20
any comments helps me
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Sounds like blocking at table level. If network connection issues occur, DTC
> notices it and aborts.
> Regards
> Mike
> "Chan" wrote:

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expire
d
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?Chan,
You should probably call MS product support and let them take a look at
this. It's hard to determine what can cause these errors without some heavy
duty troubleshooting. It could be a leak in the provider or some other
MSDTC-related problem.
Adrian
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:E472A035-D0A2-49E1-AD2A-373FE573AB5E@.microsoft.com...
> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999%
> fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expi
red
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using
> "transaction"
> get the same error over and over and over again till i restart MSDTC
> service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?
Chan,
You should probably call MS product support and let them take a look at
this. It's hard to determine what can cause these errors without some heavy
duty troubleshooting. It could be a leak in the provider or some other
MSDTC-related problem.
Adrian
"Chan" <Chan@.discussions.microsoft.com> wrote in message
news:E472A035-D0A2-49E1-AD2A-373FE573AB5E@.microsoft.com...
> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999%
> fine
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expired
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using
> "transaction"
> get the same error over and over and over again till i restart MSDTC
> service
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?

Help! [Microsoft][ODBC SQL Server Driver]Timeout expired

Hi all...
Environment
OS : Windows 2000 Advanced Server(SP4)
SQL Server : Microsoft SQL Server 2000(SP3)
DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
firewall
i have four databases, each size are 8G, 400M, 18G, 500M
there is a firewall between COM+ Server and SQL Server
so, i have configured port configuration for MSDTC as recommended by
Microsoft Support site
http://support.microsoft.com/kb/250367
almost for the past 1.5 years. my com+ application has worked 99.9999% fine
but from about a month ago i got this error at every particular NIGHT
time(ie. 22:00)
on particular database
[Microsoft][ODBC SQL Server Driver]Timeout expired
Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expire
d
MSG-->OR_20001_SN_ReceiptOrder
IDispatch error #3121 Error Count:1 GetLastError Number:0.
once this error occurrs, all other components which are using "transaction"
get the same error over and over and over again till i restart MSDTC service
or
SQL Server Database.
really strange thing is that this error NEVER occurrs at a daytime
i found the fact that sp starts and never completed after tracing by using
query profiler
is this related to network resource or table locking?
has anybody seen this case? or any ideas?Hi
Sounds like blocking at table level. If network connection issues occur, DTC
notices it and aborts.
Regards
Mike
"Chan" wrote:

> Hi all...
> Environment
> OS : Windows 2000 Advanced Server(SP4)
> SQL Server : Microsoft SQL Server 2000(SP3)
> DB Accessing : MSDTC, ODBC, COM+(VC++) calling Stored Procedure through
> firewall
> i have four databases, each size are 8G, 400M, 18G, 500M
> there is a firewall between COM+ Server and SQL Server
> so, i have configured port configuration for MSDTC as recommended by
> Microsoft Support site
> http://support.microsoft.com/kb/250367
> almost for the past 1.5 years. my com+ application has worked 99.9999% fin
e
> but from about a month ago i got this error at every particular NIGHT
> time(ie. 22:00)
> on particular database
> [Microsoft][ODBC SQL Server Driver]Timeout expired
> Com Error : DESC-->[Microsoft][ODBC SQL Server Driver]Timeout expi
red
> MSG-->OR_20001_SN_ReceiptOrder
> IDispatch error #3121 Error Count:1 GetLastError Number:0.
> once this error occurrs, all other components which are using "transaction
"
> get the same error over and over and over again till i restart MSDTC servi
ce
> or
> SQL Server Database.
> really strange thing is that this error NEVER occurrs at a daytime
> i found the fact that sp starts and never completed after tracing by using
> query profiler
> is this related to network resource or table locking?
> has anybody seen this case? or any ideas?|||Hi Mike
i am a litlte bit newbie for mssql
just typed "'sp_lock" to see if there is a table level lock in query analyze
r
and no table lock information found on the target database which causing
troubles
would you please let me know how you find lock information?
one more thing i did not mention
number of ports opened is 10, not microsofts' recommendation minimum of 15~2
0
any comments helps me
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Sounds like blocking at table level. If network connection issues occur, D
TC
> notices it and aborts.
> Regards
> Mike
> "Chan" wrote:
>