June 9, 2010 at 8:16 am
Dear all,
I would to get advice from you all about this scenarion:
I need to design a DTS package and output the file into excel format.
However, it is not a direct data transform task, instead i use a stored
procedure to retrieve the data.
I am able to insert the sql query "exec sp......" as it prompts out
the error that stating invalid object due to temp table in that
stored procedure.
Another way, I try to use "execute SQL task" in DTS, but how can i
output the file to excel format?
Please advice, thank you
June 23, 2010 at 6:38 am
Hi
You could try the simple approach of creating a table that the stored procedure can use to store the data temporarily. Then get the stored procedure to insert into that table (truncating first of course to remove the data from previous runs) instead of producing a result set. You can then have the execute sql task to run the procedure, followed by the transform data task to export the table data to excel.
There may be more elegant ways of doing this, but this would at least work.
Regards,
Duncan
June 24, 2010 at 2:34 pm
Hi Golden,
I don't have much experience with this particular T-SQL command . . .
but I believe OPENROWSET may do the trick for you.
There a couple of gotcha's with this command - do your homework.
(The links below outline the requirements.)
MSDN reference
http://msdn.microsoft.com/en-us/library/aa276850(v=SQL.80).aspx)">(http://msdn.microsoft.com/en-us/library/aa276850(v=SQL.80).aspx)
Topic discussion (not exactly this problem . . . but a good reference.)
http://www.sqlservercentral.com/Forums/Topic436306-8-1.aspx
Re-link of a good article/blog (as discussed above.)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Hope this helps & good luck!
M
Free Expert Advice . . .
http://xkcd.com/627/
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply