Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know very little about DTS packages in the first place (I'm a programmer not a dba) and have been stuck with the task of filtering rows based on the results of a stored procedure call that returns a date, where the desired result would be something like this:
" ( WHERE SpecialDate >= SPROC_Returned_Date )".

The stored procedure lives on a local DB server and the rows are all coming from elsewhere using a DB/C FS 3 driver connection. I'm having a very hard time figuring out how to connect the SQL query on the Transform Data Task that does the row retrieval to the date returning stored procedure.

Where should I even start?

Update: I've been able to create a view that returns one row with the date I'm interested in from the stored procedure. However the DB/C FS 4 connection doesn't seem capable of interpreting anything involving the use of parameters. I'm at a loss here as it seems I'm just about 90% here but can find any useful information about this DB/C FS4 ODBC driver.

share|improve this question
    
Are you checking the current date or a range before returning a result? –  Eric Higgins Mar 26 '12 at 18:46
    
The stored procedure takes the current date and subtracts 60 days from it and returns the result. The idea is to only get records that are newer than the sproc returned date. –  torpy Apr 3 '12 at 15:27

2 Answers 2

In SQL Server 2000, you cannot use the results of a stored proc directly in another query. However, you can insert them into a #temp table and then join to that. But you must create the temp table structure first, you can't use select into.

share|improve this answer
    
I will look into that, not as familiar with temp tables as I probably should be. Thanks very much. –  torpy Apr 3 '12 at 15:29

Due to inability to get this working in SQL2000 I've since installed a 2008R2 instance and started working on an SSIS based package. It appears there is a way to dynamically generate the SQL statement I need using expressions and user variables.

In general I'm guessing the major problem is the limitations of the DB/C FS4 driver although I've still been unable to find anything useful about it.

So in short I think the best answer here is to move to a new SQL Server version if possible.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.