SP works in QA but not Data Transformation Query
-
OK, this one really has me stumped.
I have a stored procedure loosely based on one of the crosstab sp's from this site. I had to make some modifications since the sp as coded returned three resultsets, two of which were useless to me in my particular app. The actual crosstab was returned in resultset 3. So I modified the sp to insert into temp tables and selected the crosstab from the final temp table as the last step in the sp. I run the sp under Query Analyzer and it returns the exact data I wanted.
I then want to incorporate this into a DTS job to export the output of the SP to a CSV file. I have a connection to my database and a connection to a CSV file. I set up a Transform Data task between the two and copy/paste the EXEC from QA (minus the "@RC=") into the SQL Query window of the Transform Data task. When I click the Preview button it says "No rowset was returned from the SQL statement."
How is that possible. I even went so far as to copy/paste the modified EXEC statement back into QA and run it exactly as it appears in the Transform Data task and it returns data (at least the grid window has all the appropriate columns and rows with data in them...).
Anyone have any ideas? I've been beating my head against BOL for the last 4 hours trying to come up with a reason and I can't find anything.
-
There was a PRINT statement hidden in the original code I purloined for the SP. Once I got rid of that the Preview button in the Transform Data task displayed the result set.
Now my problem is that I can't seem to get column definitions to set up Transformations. I can see the ResultSet in the Preview (see above) but when I go to set up Transformations there are no columns defined in either the source or destination tabs. I tried pre-populating the target CSV file with column names (yes, the "First row has column names" option is set...) but that didn't help.
How do I get data from an SP into a CSV file? I've seen a couple of threads on here about it. I think I'm following the steps (DB connection, Output Text File connection pointing to a CSV file, Transform Data task with the SP in the SQL Query pane), but I can't get past how to define the transformations....
Help!!
-
I just tried a simple example of an SP that just selects * from Northwind..Employees and the DTS Transform picked up the columns ok. So, that's not the problem.
What about putting an Execute SQL task in front of the transform to execute the proc, populating the temp tables? Then in the transform, your source would simply be the select from the "final" temp table.
-
I'm not familiar with the code here but does each column have a heading? I've known this to cause problems. Also (I think you say that you do this), does the statement that executes the stored procedure use exec.. or just call the stored procedure name. For some reason you need to use the exec.. syntax sometimes even if there is no other command in the query.
-
Bill, I'm not familiar with using the Execute SQL task that way. How do I get the output from the SP into a temp table that I can pass to the next step?
Julie, yes, I am using EXEC and yes, each column has a heading. Are you saying that having headings causes a problem or not having headings causes a problem?
I may just write a Crystal Report to cross-tab this data and the heck with it....
-
Sorry, I meant that not having headings can cause a problem as the transform task then doesn't know what to call each item being transferred. I suppose you might get a similar problem if the headings had embedded spaces.
-
This whole thing was sparked by a need to take all the tasks on my Outlook calendar (that's how I keep track of all the 15 minute interruptions I have to deal with 20 times a day), roll them up by project and then cross-tab them across the week so I can input my time into our time accounting system without spending hours trying to manually add up all the little pieces scattered throughout the week.
Turns out the easiest way was Crystal Reports with my Outlook Calendar as the datasource!! I have a prompt for From Date, calculate To Date, exclude records that are marked as "Out of Office" (personal time, vacations, etc) and then just tell Crystal to cross-tab the data by "subject" (=Project) across date. I get a nice matrix that almost exactly matches my time accounting input screen.
Sometimes the best solutions aren't initially so obvious. This was literally one of those "in the shower" inspirations. It beats exporting the calendar data to Excel, uploading it to SQL, massaging it in SQL, trying to cross-tab it in SQL then downloading it to Excel just to print it. (Yes, I could have done a pivottable in Excel, but that would have to be respecified every time since the column names are dates and therefore change every week and I really didn't want to write Excel automation).
-
Glad to hear you solved it. Although it's a moot point now, to answer your question:
As I understood it, you were able to see the output you wanted in QA, and that data was ultimately inserted into a final temp table (If not, then you certainly could insert into a temp table). I would suggest modifying the stored proc to insert into final temp table within the proc. (Re-reading your question, perhaps you are asking how to do “select * into ##temptable from proc”. That cannot be done as far as I know, although you can do “insert into ##temptable exec proc” – but that requires table creation first).
So, use the query from QA, which inserts into the final temp table as the sql statement in an execute sql task.
Note: To make this work I had to use a global temp table(##temptable).
Then in the transform task source, use a sql query such as “select * from ##temptable”. When you click on destination, it should bring up the Define Columns window. Click “Populate from Source” (your query) and then Execute. The transformations should now be set with the columns from your query.
However, it’s probably a good thing you found another way. Since you mentioned in your last post that the “column names are dates and therefore change every week”, I think you would have to manually redo the transformations as in the above paragraph to re-set the column names (or spend a lot of time creating something more complicated).
All’s well that end’s well.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply