Exporting the results of a view as a text file

[scliffe]

Hi,

This may be blindingly obvious but I'm having trouble exporting the results of a view on SQL to a text file (which is then imported into another package. I've 6 files to export - the table was easy enough with DTS but views aren't listed and I need to be able to automate it to execute each day. Any pointers on where to look? Let me know if I need to provide more info.

Simon

 

[mutley1]

Hi Simon,

I take it you are on SQL 2000 here?

You need to check a couple of things

1. Is you server connection in the package set to the correct database where the views are held?
2. In the data pump task, have you scrolled down to the bottom of the list (assuming your views start vw_xxx). Testing it here and I can't see any issue with selecting a view as the source in the data pump task.
3. Do you (or the login you are connected and creating the DTS package as) have rights to the view?

If you can provide anything else that might be strange, but I can't see a problem testing here.

HTH,

M.

 

[scliffe]

Hi Mutley1,

The process I'm doing is as follows (this works no problem for the table). Yes, it is SQL Server 2000. I'm running the DRS Import/Export Wizard through the Enterprise Manager (just putting this all in for completeness so ignore anything not relevant!)

The Datasource is "Microsoft OLE DB Provider for SQL Server". Server is local, database KIM. So far so good.

The Destination is selected as "Text File" and the file name entered.

Copy Tables and views from the source database selected. The drop down list for the source only lists tables. They are in the correct database but I don't see any reason why none of the views are visible([KIM].[dbo].[l_a01] tops the list. Can I manually enter the view?

UPDATE: I've just gone into the DTS Package to manually test it and hey presto, the views are available... Problem solved thanks. None the wiser why the wizard didn't help but then I'm no expert!

 

[mutley1]

How bizarre......I just tried it using the wizard and you're spot on - the views don't show in there. I was testing manually previously.

MS Wizards eh? More like kids party magicians than Gandalf.....

Good luck.

cheers,

M.