Invalid Object Name using TMSLoader with temp table

[charlesmcallist]

I'm getting an "Invalid object name" error when using TMSLoader component to load data into a temporary table.
The following code works if #TestTable is renamed to be TestTable
Help is greatly appreciated!

Here is my code:

Code: Select all

procedure TForm6.Button1Click(Sender: TObject);
const
  CTableName = '#TestTable';
begin
  MSConnection1.Connected := True;
  
  MSQuery1.SQL.Text := Format('CREATE TABLE %s (Test1 int primary key, Test2 varchar(10))', [CTableName]);
  MSQuery1.Execute;

  try
    MSLoader1.TableName := CTableName;
    MSLoader1.Load;

    ShowMessage('Done');

  finally
    MSQuery1.SQL.Text := Format('DROP TABLE %s', [CTableName]);
    MSQuery1.Execute;
  end;

end;

procedure TForm6.MSLoader1PutData(Sender: TMSLoader);
var
  I: Integer;
begin
  for I := 1 to 10 do
  begin
    MSLoader1.PutColumnData(0, I, I);
    MSLoader1.PutColumnData(1, I, 'Test');
  end;
end;

Here is the dfm:

Code: Select all

  object Button1: TButton
    Left = 20
    Top = 15
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object MSLoader1: TMSLoader
    Connection = MSConnection1
    TableName = 'TestTable'
    Columns = 
    OnPutData = MSLoader1PutData
    Left = 150
    Top = 130
  end
  object MSConnection1: TMSConnection
    Database = 'TEST'
    Authentication = auWindows
    Server = 'CHARLES\SQLEXPRESS'
    Connected = True
    Left = 130
    Top = 85
  end
  object MSQuery1: TMSQuery
    Connection = MSConnection1
    Left = 170
    Top = 85
  end

[charlesmcallist]

I can get it to work using a global temporary table, e.g. ##TestTable
I think i'll just go with this for now, but would be better if it wasn't a global table.

[Dimon]

The point is that SDAC doesn't support loading in temporary tables. To solve the problem use a global temporary table.

[Senya_L]

The point is that SDAC doesn't support loading in temporary tables. To solve the problem use a global temporary table.

Same problem. It should be noted that SDAC doesn't support global temporary tables in some cases. One needs to slightly modify author's example:

Code: Select all

procedure TForm6.Button1Click(Sender: TObject);
const
  CTableName = '##TestTable';
begin
  MSConnection1.Connected := True;
  MSConnection.StartTransaction;
  try
  MSQuery1.SQL.Text := Format('CREATE TABLE %s (Test1 int primary key, Test2 varchar(10))', [CTableName]);
  MSQuery1.Execute;

    try
      MSLoader1.TableName := CTableName;
      MSLoader1.Load;

      ShowMessage('Done');

    finally
      MSQuery1.SQL.Text := Format('DROP TABLE %s', [CTableName]);
      MSQuery1.Execute;
    end;
    MSConnection.Commit;
  except
    MSConnection.Rollback;
	raise;    
  end;

end; 

Inspecting source code of SDAC I've found that reason is that TMSLoader creates new OLEDB session which cannot access temporary table while explicit transaction is not commited (see TOLEDBLoader.Prepare). What can you advise for that case? Why new session is created?

[Dimon]

This behaviour is determined by SQL Server and we can't influence it. We create new session to request the IOpenRowset interface for fast loading data in a table.