Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
agulino
Starting Member
6 Posts |
 Posted - 2001-12-19 : 13:50:55
|
| I am attempting to use a stored procedure as the source of my DTS Transformation. The procedure has a "Select [Fields] Into" statement that drops some data in a temporary table. Then it has a Select statement that summarizes the data in the temp table. I can run the procedure OK in Query Analyzer and as a pass-through in Access to get the expected data. But when I use it as the source for my transformation no fields show up under "Source Table".I have used procedures as DTS sources successfully, although never with a temp table.I have a theory: when I run the procedure in QA I first get "(725 row(s) affected)" indicating the rows inserted into my temp table. Then I get the results of the second Select statement. Is that what's keeping DTS from seeing the Source fields? If so, what do I turn off to solve this problem?Any ideas?Anthony Gulinoagulino@stonesix.comStone Six Solutions, Inc.www.stonesix.com |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-19 : 15:11:12
|
| I've never done what you're talking about with DTS, but you might try inserting SET NOCOUNT ON and see if that helps.-------------------It's a SQL thing... |
 |
|
|
agulino
Starting Member
6 Posts |
Posted - 2001-12-19 : 15:20:33
|
| Thanks for the suggestion. That got rid of the count in QA. But I get the same behavior from DTS.Anthony Gulinoagulino@stonesix.comStone Six Solutions, Inc.www.stonesix.com |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2001-12-19 : 15:25:36
|
| Maybe I'm way off here, but wouldn't the fact that you're using a temp table as the final output of the procedure cause your problem? DTS cannot see the table because it isn't created until the procedure runs, so there's no object, or specific columns for it to base it's source view on.Mike "A program is a device used to convert data into error messages." |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-12-19 : 17:06:02
|
D'oH!  Good call Mike. Also when it runs, the temp table is probably out of scope unless you carefully construct the calls (there have been posts here about how to do that with sprocs).-------------------It's a SQL thing... |
|
|
|
agulino
Starting Member
6 Posts |
Posted - 2001-12-19 : 17:48:03
|
| Sure that's the problem. But isn't there some way to define the Transformation without having to run the procedure at the time of definition. Something like a manual override? If there isn't such a capability, there should be.Anthony Gulinoagulino@stonesix.comStone Six Solutions, Inc.www.stonesix.com |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-12-19 : 18:54:29
|
Any objection to changing the temp table to a permanent one? As a DTS job, it doesn't sound like this would happen concurrently amongst multiple users. If the sproc is used by multiple users, and just happens to use the same data you want to DTS, why not make a second sproc just like it that uses a perm table just for DTS? I know it's a little more hassle, but it's better than not having it work.To momentarily imagine myself as a Microsoft SQL Server developer (Lord, may I ever see 1% of that kind of money  ), I would be concerned about allowing DTS to access a temp table. They're flexible and temporary by nature, but the DTS transformations are not. Therefore if the structure of the temp table changes, there's no automatic way for DTS to adjust for that. You could easily end up with transformations that break, or point to the wrong destination columns.Well, so much for being devil's advocate! It would be a cool feature to DTS from a temp table, but given the above issues I don't think it'll happen. |
|
|
|
|
|
|
|
|
Stored Procedure as DTS Source