I'm rebuilding my server after a total crash - two drives in the RAID
failing at the same time. Pretty much a worst case scenario, I'm having
to rebuild. I did have backup routines set up on my databases, so I
didn't lose them. However, the DTS scripts did not get backed up so I'm
having to rewrite them. This is relevent because this used to work, and
now does not.

What I'm doing is essentially adding new records, running a query in
the DTS job to pull in the records after the last one in the existing
SQL table. I have to do it this way because the table in question is
huge, containing months of data. The whole point of the SQL database is
basically a cache so that the last week's worth of data can be pulled
out reliably. The DTS job works, that is if I go in and manually change
the global variable to the last NUMKEY in the SQL table it will go out
and grab all the new ones from the Oracle database. What isn't working
correctly is the Stored Procedure that pulls the data in. I'm not sure
what changed, but this doesn't work now. Here's the Stored Procedure


DECLARE @run varchar(255)
DECLARE @Start varchar(255)

SET @Start = (select top 1 NUMKEY from TBLCACHE order by NUMKEY desc)
SET @run = 'dtsrun /S "(local)" /N "Import Cache" /A "Cache
Start":"19"="' + @Start + '" /W "0" /E'
EXEC master..xp_cmdshell @run


Taking the @Start information into the Query Analyzer returns the
correct value. Further, it doesn't return any error messages when I try
to run the SP in the Query Analyzer, it just keeps running and running.
When I cancel it could take five minutes or more to finally end.

Any ideas what could be the problem?

1) remember gLoBal VaRiable names are cASe SenSitIVE
so it may be that in the package the globale variable is CACHE Start
where as your command line is Cache Start (which would create a second
global variable)
To see exactly what is happening with your Package try using this script
in an activex script task:
http://www.mutuallybeneficial.com/co...ing_Script.txt
From this article:
http://www.mutuallybeneficial.com/in...s_portable.htm
You will then be able to see what global variables you have and how they are
created

2) I have never gotten any type other than string (8) to work consistently

--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David (AT) MutuallyBeneficial (DOT) com

<Maddman75 (AT) gmail (DOT) com> wrote