Temp Table not working within DTS Package VB Script

  • We have SQL Server 2000 (SP3). I am trying to execute a VBScript (see code below) within a DTS Package. The script creates a temp table and then tries to select records from the temp table. On the second statement which selects records from the temp table I get the "invalid object name" error message. What do I need to do to correct this problem? Can temp tables be used in this way within a DTS Package? (The funny thing is, this script works one computer but not on another computer.)

    Thanks in advance, Kevin

    mySQLManger_TempTabText = "SELECT name,broker INTO ##temptabMultCurr FROM FXdaily WHERE dt = '" & dt & "'

    mySourceConn.Execute mySQLManger_TempTabText

    mySQLManger_TempTabText = "SELECT name,broker INTO ##temptabOneCurr FROM ##temptabMultCurr GROUP BY name,broker "

    mySourceConn.Execute mySQLManger_TempTabText

  • Any particular reason why you're doing all this in VBScript, versus using the DTS designer to add a connection and a couple of Execute SQL tasks ?

     

  • I am using VBScript to do some other processing with the Table Data.

  • Which implies some need to use row-at-a-time cursor like processing in VBScript, versus set-based T-SQL UPDATEs to the data ? What exactly are you trying to do ?

     

     

  • I am currently evaluating Visual Studio 2005. I use SQL server 2005 to create all table and business logic and us VB only to create windows forms and take out report from tables or procedure. I find this method more convenient and writing business logic in T-SQL is more straight forward

    V.Kadal Amutham

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply