Invalid Object Name using TMSLoader with temp table

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
charlesmcallist
Posts: 2
Joined: Thu 19 Jun 2008 14:13

Invalid Object Name using TMSLoader with temp table

Post by charlesmcallist » Fri 05 Jun 2009 14:35

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
Posts: 2
Joined: Thu 19 Jun 2008 14:13

Post by charlesmcallist » Fri 05 Jun 2009 20:42

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 09 Jun 2009 08:57

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

Senya_L
Posts: 2
Joined: Wed 20 Oct 2010 16:58

Post by Senya_L » Wed 20 Oct 2010 17:17

Dimon wrote: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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Mon 25 Oct 2010 10:39

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.

Post Reply