Really tough Stored Procedure Debug Problem.

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Ian (ian_at_NoWhere.com)
Date: 08/16/04 

Date: Mon, 16 Aug 2004 14:46:59 +0100

Hi All,

Well I have been looking at this for hours and tried all manner of things.
I am hopping that some one can give me an explanation to my problem.

I have built this Stored procedure that will import data from and excel
workbook.

And obviously it does not work. See the part that does not work bellow.
The variables and imputes are all fine, and the syntax is fine to.
The really strange part is that to test it I created a script just doing the
import from excel and it is almost identical and it works. If you look lower
down you will see that script to. And that is why I am sure the code is
correct.

The error occurs on the (SELECT * FROM #tblNewData --This is for testing)
and it says.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'InstructionID'.
@RETURN_VALUE = N/A

As mentiond this works fine on the test script but the error occures when
debuging the Stored Procedure.

Many thanks,

NOT WORKING CODE

-- Now Create the Third temp table so that we can import data from Excel
into it
 CREATE TABLE #tblNewData (
  InstructionID int NULL,
  PlainText varchar (2000) NULL,
  PlainText2 varchar (2000) NULL)

 SELECT * FROM #tblNewData --This is for testing

 -- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE

 -- This is how many times that insert will occure
 SET @iLoopControl = 1
 SET @ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)

 WHILE @iLoopControl <= @lNoOfTimes --Loop round inserting
    BEGIN
       -- Create the Data table name to use for the import
       SET @ImportDataName = 'ImportData' + CAST(@iLoopControl as
varchar(10))

     -- Import the new data from excel into it Table.
  SET @sSQL = 'INSERT INTO #tblNewData ' +
   'SELECT InstructionID, PlainText, PlainText2' +
   ' FROM OPENROWSET(' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39) +
',' + @ImportFileName + ',' + @ImportDataName + ')'

  PRINT @sSQL
  SET ANSI_NULLS OFF
  SET ANSI_WARNINGS OFF
  EXEC(@sSQL) --This will run the insert
  SET ANSI_NULLS ON
  SET ANSI_WARNINGS ON

  SET @sSQL = ''
  Select * From #tblNewData --This is for testing

  -- Create the @sSQL to get the data from the the table
  SET @sSQL = 'INSERT INTO #tblBatchDataHolder SELECT ' + @sSelectSQL +
   ' FROM tbl_BTP_Instructions WHERE (ScenarioID = ' +
cast(@lScenarioID_CopyFrom as varchar(500)) + ')'
   + ' ORDER BY InstructionOrder'

  --PRINT @sSQL
  EXEC(@sSQL) --This will run the insert
  SET @sSQL = ''
  Select * From #tblBatchDataHolder --This is for testing

  --This now changes the data
  UPDATE #tblBatchDataHolder
   SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
   FROM #tblNewData INNER JOIN #tblBatchDataHolder
   ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID

  Select * From #tblBatchDataHolder --This is for testing

  -- And now we move the new data into the update table ready for reordering
  -- Create the @sSQL for inserting
  SET @sSQL = 'INSERT INTO #ReOrder SELECT ' + @sSelectSQL +
   ' FROM #tblBatchDataHolder WHERE (ScenarioID = ' +
cast(@lScenarioID_CopyFrom as varchar(500)) + ')'
   + ' ORDER BY InstructionOrder'

  --PRINT @sSQL
  EXEC(@sSQL) --This will run the insert
  SET @sSQL = ''
  Select * From #ReOrder

        DELETE FROM #tblBatchDataHolder
        DELETE FROM #tblNewData --This clears out the old data ready for the
new data
        SELECT @iLoopControl = @iLoopControl + 1
    END

WORKING CODE

 DECLARE @lNoOfTimes int
 DECLARE @iLoopControl int
 DECLARE @ImportFilePath varchar(255)
 declare @ImportFileName varchar(400)
 DECLARE @ImportDataName varchar(200)
 DECLARE @sSQL varchar(2000)

 -- Now Create the Third temp table so that we can import data from Excel
into it
 CREATE TABLE #tblNewData (
  InstructionID int NULL ,
  PlainText varchar (2000) NULL ,
  PlainText2 varchar (2000)NULL )

 -- THIS IS WHERE WHE START THE CLEVER STUFF OF COMBINING THE EXCEL DATA
WITH THAT IN THE DATABASE

 -- This is how many times that insert will occure
 SET @iLoopControl = 1
 SET @lNoOfTimes = 3
 SET @ImportFilePath = '\\Sesgb102\RPT\Import_Test.xls'
 SET @ImportFileName = CHAR(39) + 'Excel 5.0;Database=' + @ImportFilePath +
';HDR=YES;IMEX=1' + CHAR(39)

 WHILE @iLoopControl <= @lNoOfTimes --Loop round inserting
    BEGIN
       -- Create the Data table name to use for the import
       SET @ImportDataName = 'ImportData' + CAST(@iLoopControl as
varchar(10))

       -- Import the new data from excel into it Table.
  SET @sSQL = 'INSERT INTO #tblNewData ' +
   'SELECT InstructionID, PlainText, PlainText2' +
   ' FROM OPENROWSET (' + CHAR(39) + 'Microsoft.Jet.OLEDB.4.0' + CHAR(39)
+ ',' + @ImportFileName + ',' + @ImportDataName + ')'

  PRINT @sSQL
  SET ANSI_NULLS ON
  SET ANSI_WARNINGS ON
  EXEC(@sSQL) --This will run the insert
  SET ANSI_NULLS OFF
  SET ANSI_WARNINGS OFF

  SET @sSQL = ''
  Select * From #tblNewData
  DELETE FROM #tblNewData

  SET @iLoopControl = @iLoopControl +1
    END

 DROP TABLE #tblNewData