| Mary Chipman [MSFT] 2006-10-24, 6:40 pm |
| Would it work if you performed the operation in separate steps? If you
have the data schema (column names, data types) you can write interop
code to create the tables in the mdb. Once you have the tables, then
you can write a pass-through query to pull the data from the stored
procedure. Basically the more layers of API you have, the slower it's
going to be, and if you're doing inserts one row at a time with
200,000 rows, then you can expect to wait a while.
--Mary
On 12 Sep 2006 07:26:13 -0700, santhoshks@gmail.com wrote:
[color=darkred]
>Hi thanks for the reply.
>I am already using the interop assembly to write into .mdb.
>my problem is when i used the interop assembly, i need to create the
>access object first and then insert my dataset into the Access table.
>When the number of records is very high e.g. around 200,000 the time
>taken to insert the dataset into the access table is over 45
>mins..Instead i want to use the Access object which is available in DTS
>packages because when I tried to write into .mdb using the access
>object in DTS package, the same 200,000 records got inserted int 5 mins
>which is because SQL makes use of the data link object to write into
>MS-Access.
>
>The problem now i have in front of me is when i perform a DTS task to
>write the query output to a .mdb file, I cannot use stored proc in its
>direct form as the output columns of a stored proc wont be available to
>create the table in MS-Access but when the query is executed directly
>the output columns are automatically created in the MS-Access table.
>
>Regards,
>Santhosh
>
>Mary Chipman [MSFT] wrote:
|