SQL DTS query using WHILE Statement successfully executes (only updating 200 of 1494 records)

[chriskoch]

I am running a DTS Package.
I have a temp table with 1494 records. I am inserting a 'Y' or
'N'into a temp table #HasClaims.

The TempTable name with the Provider Id's(PRPR_ID) is
#TempFACETSNODu pes
The @identityID is an identity field counting back from 1494 to 1

I count back from the Max value of the identityid (1494) in the While
loop until I get through all the records. The idea is to check for the
existance of a claims and authorization record and put a 'Y' or 'N'
record in the temptable #HasClaims.

This is running in the Execute SQL Task object of the DTS Package.
The Package runs successfully but only inserts 200 rows into the new
temp table. There should be a row for each provider. Each time it
runs, the number of rows it returns is different. Sometimes it is 205,
then 185, then 210, before it completes the DTS package.

Has anyone run into While looping problems within an Execute SQL task
in a DTS package(SQL 2000)like this
--------------------------------------------------------------
SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODu pes
While @identityID >= 1

BEGIN
@PRPRID is the placeholder for the PRPR_ID (Provider)
SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODu pes WHERE IDENTITYID =
@identityID

IF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CL AIM CLCL WHERE
CLCL.PRPR_ID = @PRPRID)
BEGIN
INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
VALUES( @PRPRID, 'Y', @identityID)
END
ELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
VALUES( @PRPRID, 'N', @identityID)

------------------------------------------------------------
SELECT @identityID = @identityID - 1
END

[Stu]

Have you tried debugging it in Query Analyzer to ensure that the
problem is not with the code instead of simply the environment in which
you run it?

I am curious why you are looping, however, when you could accomplish
this process using a LEFT JOIN, like so:

INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
SELECT DISTINCT t.PRPID,
HasClaims = CASE WHEN CLCL.CLCL_ID IS NULL THEN 'N' ELSE 'Y' END,
t.IdentityID
FROM #TempFACETSNODu pes t LEFT JOIN dbo.CMC_CLCL_CL AIM CLCL ON
t.PRPID = CLCL.PRPID

HTH,
Stu

[chriskoch]

Stu,

First of all thanks for taking the time to reply!

The database that I am querying is a Sybase database. The Output is
dumped into a SQL 2000 database.
I am using the loop because of the size of the claims and
authorizations databases. When I use any JOIN the query times out.
This way of fetching a record at a time doesn't overpower the server.

When I use Query Tool(ADO) to run the exact same SQL query against the
Sybase database, The query returns all the records from the loop with
no problem. I would be done if not for the fact that this has to be
automated and delivered weekly. I don't want to manually construct
this every week.

I have tried the sql in an ODBC version of Query Tool just in case it
was the connection. Both query tool programs pulled the data without
problem. The DTS package is the only one that doesn't finish all the
updates. I have tried running the the Task all on the Main thread but
that didn't help.

[Stu]

So, as I understand it, the SQL statement you supplied is actually
running against the Sybase database. In other words, you have a
connection defined for the Sybase server, and you're using that
connection to build a temporary table on the Sybase Server, and then
you'll extract the information from that temp table to SQL Server. Is
that correct?

If so, it is very odd that it's not working as it should. Have you
considered building a linked server between your SQL Server and the
Sybase Server, and executing the code against the linked server to see
if that will correct it?

I'm shooting in the dark, to be honest. I've never heard of anything
like this.

Stu

[chriskoch]

Stu,

First of all thanks for taking the time to reply!

The database that I am querying is a Sybase database. The Output is
dumped into a SQL 2000 database.
I am using the loop because of the size of the claims and
authorizations databases. When I use any JOIN the query times out.
This way of fetching a record at a time doesn't overpower the server.

When I use Query Tool(ADO) to run the exact same SQL query against the
Sybase database, The query returns all the records from the loop with
no problem. I would be done if not for the fact that this has to be
automated and delivered weekly. I don't want to manually construct
this every week.

I have tried the sql in an ODBC version of Query Tool just in case it
was the connection. Both query tool programs pulled the data without
problem. The DTS package is the only one that doesn't finish all the
updates. I have tried running the the Task all on the Main thread but
that didn't help.

[Stu]

So, as I understand it, the SQL statement you supplied is actually
running against the Sybase database. In other words, you have a
connection defined for the Sybase server, and you're using that
connection to build a temporary table on the Sybase Server, and then
you'll extract the information from that temp table to SQL Server. Is
that correct?

If so, it is very odd that it's not working as it should. Have you
considered building a linked server between your SQL Server and the
Sybase Server, and executing the code against the linked server to see
if that will correct it?

I'm shooting in the dark, to be honest. I've never heard of anything
like this.

Stu

[Erland Sommarskog]

(ch*******@iwon .com) writes:

I am using the loop because of the size of the claims and
authorizations databases. When I use any JOIN the query times out.
This way of fetching a record at a time doesn't overpower the server.

Then you have something to fix. The temp table has 1494 rows that
is not much for a set-based query. For an interative loop, it's starting
to be a bit. You will take more power out of the server with the loop
that a good set-based query that inserts all rows in one go. What appears
to happen in your case, is that the loop times out as well. This is
possibly something you can control within DTS, but DTS is not my playground.

And, before I go on, if you really insist on the loop, make sure that
IdentityID is indexed.

Here are some possible variations of Stu's query:

INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
SELECT t.PRPID,
HasClaims = CASE WHEN EXISTS (SELECT *
FROM dbo.CMC_CLCL_CL AIM CLCL
WHERE t.PRPID = CLCL.PRPID)
THEN 'Y'
ELSE 'N'
END,
t.IdentityID
FROM #TempFACETSNODu pes

Or simply add a HasClaims to #TempFACETSNODu pes:

UPDATE #TempFACETSNODu pes
SET HasClaims = CASE WHEN EXISTS (SELECT *
FROM dbo.CMC_CLCL_CL AIM CLCL
WHERE t.PRPID = CLCL.PRPID)
THEN 'Y'
ELSE 'N'
END
FROM #TempFACETSNODu pes t

Or add this column with a default of 'N' and do:

UPDATE #TempFACETSNODu pes
SET HasClaims = 'Y'
FROM #TempFACETSNODu pes t
JOIN dbo.CMC_CLCL_CL AIM CLCL ON t.PRPID = CLCL.PRPID

If all these executes poorly, you need investigate why. I assume that
CMC_CLCL_CLAIM. PRDID is a primary key, so there is a good index to
use, but the Sybase optimizer may for some reason do a table scan.
But you should be able to convince it with an index hint.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(ch*******@iwon .com) writes:

I am using the loop because of the size of the claims and
authorizations databases. When I use any JOIN the query times out.
This way of fetching a record at a time doesn't overpower the server.

Then you have something to fix. The temp table has 1494 rows that
is not much for a set-based query. For an interative loop, it's starting
to be a bit. You will take more power out of the server with the loop
that a good set-based query that inserts all rows in one go. What appears
to happen in your case, is that the loop times out as well. This is
possibly something you can control within DTS, but DTS is not my playground.

And, before I go on, if you really insist on the loop, make sure that
IdentityID is indexed.

Here are some possible variations of Stu's query:

INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
SELECT t.PRPID,
HasClaims = CASE WHEN EXISTS (SELECT *
FROM dbo.CMC_CLCL_CL AIM CLCL
WHERE t.PRPID = CLCL.PRPID)
THEN 'Y'
ELSE 'N'
END,
t.IdentityID
FROM #TempFACETSNODu pes

Or simply add a HasClaims to #TempFACETSNODu pes:

UPDATE #TempFACETSNODu pes
SET HasClaims = CASE WHEN EXISTS (SELECT *
FROM dbo.CMC_CLCL_CL AIM CLCL
WHERE t.PRPID = CLCL.PRPID)
THEN 'Y'
ELSE 'N'
END
FROM #TempFACETSNODu pes t

Or add this column with a default of 'N' and do:

UPDATE #TempFACETSNODu pes
SET HasClaims = 'Y'
FROM #TempFACETSNODu pes t
JOIN dbo.CMC_CLCL_CL AIM CLCL ON t.PRPID = CLCL.PRPID

If all these executes poorly, you need investigate why. I assume that
CMC_CLCL_CLAIM. PRDID is a primary key, so there is a good index to
use, but the Sybase optimizer may for some reason do a table scan.
But you should be able to convince it with an index hint.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp