# Miscellaneous > General Database Discussions >  Problem with DTS while using #Temp, temporary tables

## Avadhoot

I've created one stored procedure which uses #temp, temporary table.
When I schedule it to generated output in CSV file using DTS, it flags me an error
as ,
'Context: Error Calling GetColumnInfo. Your Provider dows not support all the interfaces/methods required by DTS'

Can anyone tell me why this error occurs? Or is it true that one cannot use Temporary tables in generating output in CSV or text file using DTS ?
 I am using MS-SQL Server 2000

----------


## Stephen

At design time, DTS tries to establish what columns are supplied by the source query.  For SQL Server, it does this by using "SET FMTONLY ON" and then "running" the SQL statement for the source.  The intent is that the column information only is returned to DTS.  
The downside of doing this is that the statement is not actually executed which means that the temporary table never comes into existance.  Hence, SQL Server is unable to establish what columns are to be returned and you end up with the error you are getting.

This is a design time issue only.  Your stored procedure will work as a data source when the package is run.  

To workaround the problem, you will need to find a way to return a recordset to your package that matches what the stored procedure.  Perhaps create a table with the same definition, develop the package and when you are happy with the package, replace the source for the datapump with you stored procedure.

----------


## Rawhide

Or if you can, use the Table data type instead of a temp table.

----------


## hspoulsen

Rawhide,
I've tried your suggestion, but it still comes up with 
'Context: Error Calling GetColumnInfo. Your Provider dows not support all the interfaces/methods required by DTS'
Do you know if this is a viable solution?
TIA
Henrik

----------


## rmiao

Tried with global temp table or staging table?

----------


## hspoulsen

rmiao,
I have not tried a global temp table, as I thought that other users could see this?
I do not know what a staging table is.
Best regards,
Henrik

----------


## rmiao

Global temp table is available to all sessions, and staging table is regular table to hold temp data that waiting process.

----------


## hspoulsen

rmiao,
I ended up using a "staging table" even if I did not know the word for it.
It doesn't look very nice, but it'll have to do.
Thank you for helping out.
Henrik

----------

