Using DTS w/stored procedures - posting results to an Access database

[RogueSuit]

I created a cursor named CrossReport and a DTS package that has 3 elements
1.Standard connection to a data source
2. Transform Data Task
3. Connection to an Access Database

On the Transform Data Task “TDT” I used SQL query and used the statement – EXEC CrossReport. I then preview the data - it returns the field names but no values in the fields.

I tested the cursor in the query analyzer and it returned data.

The question: Why does the exec not return any data to the TDT? Do I need to create a temp table (instance) of the data generated from the cursor then call the temp table from the DTS package? – if so….how could I pull this off?

Thx.

RogueSuit

 

[tlbroadbent]

I have a few questions about your process but could probably answer them with a look at the stored proc. Can you post it here?

Thanks, Terry

X-) "I don't have a solution, but I admire your problem."

 

[RogueSuit]

Below you will find the st proc – also, if you can think of one...is there a better way to wildcard the "like CrossSell____" comparison? I attempted a “*” splat – but it did not work as I though it would.

Thx in advance…


DECLARE ActiveCatalog CURSOR
READ_ONLY
FOR SELECT CatalogName
FROM Catalog.dbo.CatalogGlobal
WHERE COLUMNPROPERTY( OBJECT_ID('' + CatalogName +
'_CatalogProducts'),'CreateDate','AllowsNull') IS NOT NULL

DECLARE @Catalog varchar(6)
DECLARE @SQLText varchar(8000)
DECLARE @Return int
DECLARE @Action varchar(6)


Set @SQLText = ''

OPEN ActiveCatalog

FETCH NEXT FROM ActiveCatalog into @Catalog
WHILE (@@fetch_status <> -1)
BEGIN

--PRINT @Catalog
SET @SQLTEXT = 'Select ''' + @Catalog + ''' AS CatName, f.ProductID AS ParentSku, f.Name AS ParentSkuName, t.ProductID AS ChildSku, t.Name AS ChildSkuName, t.DisplayInd, t.oid AS ChildID,
f.oid AS ParentID, a.Name, getdate() AS DateGenerated
FROM [' + @Catalog + '_CatalogRelationships] a INNER JOIN
[' + @Catalog + '_CatalogProducts] f ON a.from_oid = f.oid INNER JOIN
[' + @Catalog + '_CatalogProducts] t ON a.to_oid = t.oid
Where (a.Name like ''CROSS_SELL'' or a.Name like ''CROSS_SELL____'' or a.Name like ''CROSS_SELL_____'')
ORDER BY ParentSku'

--PRINT @SQLText
EXEC (@SQLText)

FETCH NEXT FROM ActiveCatalog into @Catalog

END



CLOSE ActiveCatalog
DEALLOCATE ActiveCatalog



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[tlbroadbent]

The SQL wildcard is &quot;%&quot; (without the &quot.

It appears that your SP is creating multiple record sets - one for each EXEC (@SQLText). DTS only handles the first record set. I would insert each result set into a temp table and then in the last statement select the records to return from the temp table.

Also make sure no other output occurs in the SP.
1- Don't print any lines
2- SET NOCOUNT ON to suppress record sounts on the temp table inserts.
Terry

X-) &quot;I don't have a solution, but I admire your problem.&quot;

 

[RogueSuit]

Terry:

You will have to forgive me - this is my first go @ a &quot;real&quot; SP... could you give me an idea on how the syntax might be laid out – I know that there is a certain way you create a temp table without crushing the resources on the server….but I have never actually done it…..any insight would be most appreciated.

Thx for the ideas - I know that this is generally a bad thing to do in SQL - but it is a report that will run only once a month (hopefully...)

Thx again.

Nick

 

[tlbroadbent]

Before opening the cursor, create the temporary table with columns in the same order as out of EXEC (@SQLText).

Create #temp (...define columns and data types...)

Then add INSERT #temp just before EXEC (@SQLText).

The command would look like...

INSERT #temp EXEC (@SQLText) Terry

X-) &quot;I don't have a solution, but I admire your problem.&quot;

 

[RogueSuit]

Thanks Terry - It worked like a charm....!!! I appreciate all of your help.