I'm creating a DTS package which creates a temp table, insert some
records in the temp table, export those records to an excel file and
finally drop the table..
Everything is fine except the excel file: -
1. I am unable to do select statement against the temp table (from
ActiveX Script). But if I change the table to a normal table, it works.
Is there a way I can get it done with temp table?

2. After running the DTS package more than once, I get a confirmation
messge if I was to replace the file.. How can I avoid that message?

3. Excel seems to hold the file in memory because when I open it, I get
a message that the file is in read-only mode since another process is
using it.. What am I doing wrong?

Function Main()
Set con = CreateObject("ADODB.Connection")
cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "test"
cn.Properties("Initial Catalog").Value = "test"
cn.Properties("Integrated Security").Value ="SSPI"
cn.Open
Set rs = CreateObject("ADODB.RecordSet")
sSQL = "SELECT * FROM mytable"
Set xApp = CreateObject("Excel.Application")
Set xBk = xApp.Workbooks.Open("D:\output.xls")
Set xWkSt = xBk.Worksheets("Sheet1")
xWkSt.Cells(1, 1).Value = "Name"
xWkSt.Cells(1, 2).Value = "Address"
xWkSt.Cells(1, 3).Value = "Phone"
rs.Open sSQL, cn
nRcrd = 2
DO Until rs.EOF
xWkSt.Cells(nRcrd, 1).Value = rs.Fields("name")
xWkSt.Cells(nRcrd, 2).Value = rs.Fields("address")
xWkSt.Cells(nRcrd, 3).Value = rs.Fields("phone")
rs.MoveNext
nRcrd = nRcrd + 1
LOOP
rs.Close
xBk.Save
xBk.Close
xAppl.Quit
Main = DTSTaskExecResult_Success
End Function

Are you creating and selecting from the temp table within the same task? I
believe the temp table will go out of scope after you leave the task in which
you create it.

I had the same issues with Excel asking to replace the file and keeping it
open as well. I do not know how to get ird of the replace file message, but
as far as keeping your file open, you need to close the excel application
where you are closing the workbook. So you should have xApp.Save and
xApp.Close instead of xBk.

Hope this helps.

"No_Spam" wrote:

FYI

Temporary Tables
You can create local and global temporary tables. Local temporary tables are
visible only in the current session; global temporary tables are visible to
all sessions.

Prefix local temporary table names with single number sign (#table_name),
and prefix global temporary table names with a double number sign
(##table_name).

- Books Online

"No_Spam" <no_to_spam (AT) comcast (DOT) net> wrote