Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Firedac SQL Server Temporary table


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Dec 10, 2017 12:39 AM Last Post By: Dmitry Arefiev
Mikael Lenfors

Posts: 99
Registered: 3/6/01
Firedac SQL Server Temporary table  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 16, 2017 9:05 AM
Hello!

I have exactly the same issue as mentioned in the text below! When i create a temporary table in SQL server from a FDQuery and then try to read the same table from the same FDQuery i get exception "Invalid object name '#<my temp table name>'"

I tried the DirectExecute option, both on the FDQuery and the FDConnection but with same result.

How can I use a temporary table in SQL server?

Best regards, Mikael

---

Q6: Why does the application raise the "Invalid object name '#<my temp table name>'" exception?

A: FireDAC application may raise the exception above when it works with Microsoft SQL Server local temporary tables. To demonstrate this, the following code reproduces the issue:
FDQuery1.ExecSQL('select * into #TmpOrd from [Orders]');
FDQuery1.Open('select * from #TmpOrd');
To resolve the issue, set TFDQuery.ResourceOptions.DirectExecute to True. This is also required when the application is extensively using:
Local temporary tables in the client SQL;
And/or the dynamic client SQL.
As an option, consider the use of global temporary tables.

Mikael Lenfors

Posts: 99
Registered: 3/6/01
Re: Firedac SQL Server Temporary table  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 30, 2017 5:55 AM   in response to: Mikael Lenfors in response to: Mikael Lenfors
Ok, I thiink I found the problem! It doesn't work if there is a Parameter involved!

I created a new VCL application, Delphi 10.2. Dropped a TFDConnection, TFDQuery and a TButton on it and defined a Connection to my SQL server.

See the following code and the error!

Please help, really need a solution for this.

Procedure TForm15.Button1Click(Sender: TObject);
begin

FDQuery1.Close;
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add('Select 1 As Test Into #Test');
FDQuery1.SQL.Add('Where 1 = :Param'); // If I comment out these two lines it works!
FDQuery1.ParamByName('Param').AsInteger := 1; //
FDQuery1.ExecSQL;

FDQuery1.Close;
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add('Select * From #Test');
FDQuery1.Open; // Here I get '[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name ''#Test''.'

end;

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firedac SQL Server Temporary table  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 1, 2017 10:48 PM   in response to: Mikael Lenfors in response to: Mikael Lenfors
Consider this as a limitation of ODBC API. So, you have two workarounds:
* do not use parameters;
* use global temp table.

--
With best regards,
Dmitry
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firedac SQL Server Temporary table  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 3, 2017 11:02 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Additionally, you can try to put both commands - SELECT INTO and SELECT FROM - into a single batch command.

--
With best regards,
Dmitry
Mikael Lenfors

Posts: 99
Registered: 3/6/01
Re: Firedac SQL Server Temporary table  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 5, 2017 8:44 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Ok, thanks.

I can't do it in the same query as I have to do lots of processing inbetween.

Is this a bug? I'm using lates FireDac and the MSSQL driver.

Best regards, Mikael
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firedac SQL Server Temporary table  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 10, 2017 12:39 AM   in response to: Mikael Lenfors in response to: Mikael Lenfors
Is this a bug? I'm using lates FireDac and the MSSQL driver.

See my answer above.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02