Exporting Multiple Text Files from Query Output

[Zukkster]

I'm not exactly sure I'm on the right form for this but bear with me.

I'm in the process of converting an Access database to SQL Server. One of the processes runs multiple queries and then exports multiple text files from the final query. This is all working OK up to exporting the files.

The final query is in the format

CardID, Category

In Access I had some VB code that looped through the final query and created one file for each category with a list of IDs in that category - quite simple using record sets.

I can still do this but, because of the volume of data and the fact it's trying to pull it over the network, it's very slow and hammers network resources.

How can I get round this and create the files locally using the SQL Server, rather than over the network from Access?

Many Thanks

 

[Zukkster]

Right, if anyone is having the same problem I've sussed it, so here's how I did it.

I couldn't work out how to do it in SQL Server. but I already had the code in Access which I'm using as the front end to my SQL Server database. Using a linked table in Access didn't work because it was too slow and was passing masses of data over the network.

The work around was to use a Pass Through query in Access to run a Data Transformation Service Package and import the data into Access quickly. I then ran the exsiting code on the data.

The SQL code you need to run a SQL Server DTS Package is

exec master.dbo.xp_cmdshell 'DTSRun /S sqlserver /N REPORTER: Export Control /E'

where

Server Name is "sqlserver"
DTS Package Name is "REPORTER: Export Control"

This drops the data into Access quickly rather than working with a linked table