Question about parallel operations of SQLServer

Our system will support about 200 concurrent users during work time. But
some heavy queries are executed in parallel sometimes. When this is
happening, the server is really stressed and even logon to the server will
take long time. We have tried our best to optimize these heavy queries and
we can not totally eliminate the parallel process. What should we do? should
we turn off the parallel option? How does your server configured?
Thanks for any response.
Lijunreduce the setting "max degree of parallelism" to something less than the
total number of processors on the machine.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:#KXBHLKqDHA.3320@.tk2msftngp13.phx.gbl...
> Our system will support about 200 concurrent users during work time. But
> some heavy queries are executed in parallel sometimes. When this is
> happening, the server is really stressed and even logon to the server will
> take long time. We have tried our best to optimize these heavy queries and
> we can not totally eliminate the parallel process. What should we do?
should
> we turn off the parallel option? How does your server configured?
> Thanks for any response.
> Lijun
>|||Hi Lijun,
Thanks for your post. Generally, the bottleneck of the query performance
might be on many factors (mainly divided into four parts) such as the
Number and Type of Processor, Memory Size and Usage, Physical Disk I/O,
Multiple threads (Hardware and Operating System Limitations); Provider to
connect server, Network Communication Speed, Numbers of connection
(Connection Limitation); Execution Plan, Statistics, Index, Data Volume,
Returned result sets, locks/deadlocks, long-run transactions/store
procedure (SQL Server Specific Issues); Cursor Location, Coding consistent,
application designing issue (VB Specific Issues). Therefore, the
configuration on the server side is not the only thing we need to concern
for the performance of SQL Server.
In this case, how did the 200 concurrent users connect to SQL Server during
work time, using Query Analyzer or in Application?
You wrote "the server is really stressed and even logon to the server will
take long time. What do you mean by the "long time"? How many seconds do
you spend on query and logon at the worst situation?
What does the hardware configuration on our side? How many processors do
you have?
Did some jobs worked on the background inducing the server busy?
The above things that affect the query performance are all need to be
considered on your side. For detailed reference on how to perform the
configuration on the server side, please read the following article
seriously and perform some configurations which meet your needs. 319942 HOW
TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
For additional information regarding this issue please refer to the
following article on SQL Server Books Online.
Topic: "max degree of parallelism Option"
Topic: "Degree of Parallelism"
For how to troubleshoot SQL Server performance issues step-by-step, please
reference the following Microsoft technical article which accurately
determine the source of a performance issue.
224587.KB.EN-US INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=KB;EN-US;224587
For detailed information on how to perform QUERY TUNING, please follow the
directions of the following article which specifies accurate
troubleshooting unexpectedly long-lasting queries and updates and provides
very effective method to deal with the poorly performing queries:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/od
p_tun_1_536v.asp
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Sorry if I sound silly
Can you make sure there are not too many "Select * INTO..." statements in your SQL these can also HOLD locks on system tables, that can make logging into sql server slow.

Question about optimization

Hi All,
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Even
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).
No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA
|||As I was reviewing my SPs, I realized that and implemented something similar.
Speed increase when I did that was minimal but any speed increase is welcomed
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far.
"Rick Sawtell" wrote:

> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> to
> take
> SELECT
> it's
> major
> Even
> since
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>

Question about optimization

Hi All,
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Eve
n
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA|||As I was reviewing my SPs, I realized that and implemented something similar
.
Speed increase when I did that was minimal but any speed increase is welcome
d
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries
.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far
.
"Rick Sawtell" wrote:

> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> to
> take
> SELECT
> it's
> major
> Even
> since
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>sql

Question about optimization

Hi All,
I know that SQL server cursors are general a bad idea and the first thing to
go when optimizing. They're heavy and slow and generally avoidable. On
occasion it can happen that you get stuck with an SP that you need to
optimize and moving it to dynamic SQL is not an option (even though speed
wise it would be better). So here's my question for all those DB experts.
I have logic that requires me to get a list of rows from one table and take
each row and execute another SP using that rows data. Which is faster
1. FAST_FORWARD CURSOR
OR
2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A SELECT
TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
The cursor is easier to understand code and seems like less queries but it's
a dreaded evil cursor. My test db is small so it's hard for me to see major
performance differences so I'm hoping somebody knows the answer already. Even
with my small data, once I got the endless recursion solved, it seems like
the while loop might actually be faster (hard to tell though).
Is there a 3 way to achieve this functionality?
Oh yeah and I can't use the query analyzer to analyze the performance since
some of my inner sp's use temp tables (to avoid using CURSORS).No one will be able to say for sure... This is one of those things you'll
have to test... If cursors work better, then use them...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
> to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
> take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
> it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> performance differences so I'm hoping somebody knows the answer already.
> Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
> since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>|||"lee" <lee@.discussions.microsoft.com> wrote in message
news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> Hi All,
> I know that SQL server cursors are general a bad idea and the first thing
to
> go when optimizing. They're heavy and slow and generally avoidable. On
> occasion it can happen that you get stuck with an SP that you need to
> optimize and moving it to dynamic SQL is not an option (even though speed
> wise it would be better). So here's my question for all those DB experts.
> I have logic that requires me to get a list of rows from one table and
take
> each row and execute another SP using that rows data. Which is faster
> 1. FAST_FORWARD CURSOR
> OR
> 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
SELECT
> TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> The cursor is easier to understand code and seems like less queries but
it's
> a dreaded evil cursor. My test db is small so it's hard for me to see
major
> performance differences so I'm hoping somebody knows the answer already.
Even
> with my small data, once I got the endless recursion solved, it seems like
> the while loop might actually be faster (hard to tell though).
> Is there a 3 way to achieve this functionality?
> Oh yeah and I can't use the query analyzer to analyze the performance
since
> some of my inner sp's use temp tables (to avoid using CURSORS).
>
Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
column and skip the delete. It will probably be faster than doing the
deletes on a large lookup table.
DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
DECLARE @.i int
DECLARE @.Lookup nvarchar(100)
INSERT @.tmp (SomeValue)
SELECT Value FROM LookupTable
SELECT @.i = COUNT(*) FROM @.tmp
WHILE @.i > 0
BEGIN
-- Get lookup value from temp table
SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
-- Process
EXEC SomeProc @.Lookup
SET @.i = @.i - 1
END
Rick Sawtell
MCT, MCSD, MCDBA|||As I was reviewing my SPs, I realized that and implemented something similar.
Speed increase when I did that was minimal but any speed increase is welcomed
at this stage.
I've just done what I didn't want to do. I've started grouping a lot of the
SP's into single large SPs that can handle some of the load in batch queries.
Speed increase is dramatic when doing this.
I've gone from 23 seconds (on my dev machine with no indexes) to traverse
20000 records in 16 tables to about 11 seconds. Speed is better but the
maintenance team is going to hate me for this.
FYI: From my tests the FAST_FOWARD cursor is faster then the While loop.
I've left the While loop in places with Large amount of rows and Cursor in
places with small amount of rows. Seems to be the best of both worlds so far.
"Rick Sawtell" wrote:
> "lee" <lee@.discussions.microsoft.com> wrote in message
> news:64E22F44-072E-44E5-AA43-EE7EE5398F5C@.microsoft.com...
> > Hi All,
> >
> > I know that SQL server cursors are general a bad idea and the first thing
> to
> > go when optimizing. They're heavy and slow and generally avoidable. On
> > occasion it can happen that you get stuck with an SP that you need to
> > optimize and moving it to dynamic SQL is not an option (even though speed
> > wise it would be better). So here's my question for all those DB experts.
> >
> > I have logic that requires me to get a list of rows from one table and
> take
> > each row and execute another SP using that rows data. Which is faster
> >
> > 1. FAST_FORWARD CURSOR
> > OR
> > 2. WHILE LOOP THAT CHECKS TILL COUNT = 0 AND INSIDE THE LOOP DOING A
> SELECT
> > TOP 1 TO GET MY ROW DATA AND REMOVING THE ROW FROM THE TEMP TABLE
> >
> > The cursor is easier to understand code and seems like less queries but
> it's
> > a dreaded evil cursor. My test db is small so it's hard for me to see
> major
> > performance differences so I'm hoping somebody knows the answer already.
> Even
> > with my small data, once I got the endless recursion solved, it seems like
> > the while loop might actually be faster (hard to tell though).
> >
> > Is there a 3 way to achieve this functionality?
> >
> > Oh yeah and I can't use the query analyzer to analyze the performance
> since
> > some of my inner sp's use temp tables (to avoid using CURSORS).
> >
> >
> Instead of doing a TOP 1 and then deleting, you could create an IDENTITY
> column and skip the delete. It will probably be faster than doing the
> deletes on a large lookup table.
> DECLARE @.tmp TABLE (IDCol IDENTITY(1,1), SomeValue nvarchar(100))
> DECLARE @.i int
> DECLARE @.Lookup nvarchar(100)
>
> INSERT @.tmp (SomeValue)
> SELECT Value FROM LookupTable
> SELECT @.i = COUNT(*) FROM @.tmp
> WHILE @.i > 0
> BEGIN
> -- Get lookup value from temp table
> SELECT @.Lookup = Value FROM @.tmp WHERE @.tmp.IDCol = @.i
> -- Process
> EXEC SomeProc @.Lookup
> SET @.i = @.i - 1
> END
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
>