Cannot use 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
Stephan Mercier
Posts: 4
Joined: Tue 15 Dec 2015 22:27

Cannot use temp table

Post by Stephan Mercier » Tue 15 Dec 2015 22:37

I am creating a temp table (#tablename) and then try to insert records in it and I get: "Invalid object name". The problem seem to be that SDAC is formulating my insert into a stored procedure (command below) and the temp table is not visible inside the stored procedure. This was working with previous version of SDAC (4.5) and is not working on version 6.9. Note that I am still using D5 and I have the parameters below set. Is there any solution to this issue?

ParamsInfoOldBehavior:=True;
MSAccess.__UseUpdateOptimization:=false;

exec [sys].sp_describe_undeclared_parameters N'INSERT INTO #R00190100000033 (F1056,F1057,F254 VALUES (@P1,@P2,@P3)'

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Cannot use temp table

Post by azyk » Wed 16 Dec 2015 10:47

We have investigated the behavior of SDAC versions 4.5 and 6.9 and made sure that both versions worked with temporary tables in the the same way: when the TMSQuery.Options.AutoPrepare option was enabled after creating a temporary table, and a further attempt to prepare an INSERT query for this table - the "Invalid object name '#TableName'" error occured.

In case when the TMSQuery.Options.AutoPrepare option was disabled, then after creating a temp table, an attempt to prepare an INSERT query for this table was performed successfully in both versions of SDAC.

Stephan Mercier
Posts: 4
Joined: Tue 15 Dec 2015 22:27

Re: Cannot use temp table

Post by Stephan Mercier » Wed 16 Dec 2015 20:13

The AutoPrepare is false by default and I am not changing this. I tried to set it to false explicitly without success. I provided the trace from SQL. It shows that you are using "sp_describe_undeclared_parameters" to run the INSERT statement. Temp tables cannot cross the stored procedure boundary so how could this work? Below is some pseudo code to explain what we do. Thanks for your help.

MSQUERY.SQL.Clear;
MSQUERY.SQL.Add(CREATE TABLE #NAME (F1 VARCHAR(10),F2 VARCHAR(10))
MSQUERY.Execute;
MSQUERY.SQL.Clear;
MSQUERY.SQL.Add(INSERT INTO #NAME (F1,F2) VALUES (:F1,:F2)
MSQUERY.Prepare;
MSQUERY.ParambyName('F1').AsString='1';
MSQUERY.ParambyName('F2').AsString='1';
MSQUERY.Exec;

Petriukx
Posts: 12
Joined: Tue 06 Jun 2006 11:17

Re: Cannot use temp table

Post by Petriukx » Fri 18 Dec 2015 10:05

Hello

I have a similar problem with uniDac SQL Server provider.
I get this error message: The metadata could not be determined because statement ‘Select * from #temp_Sample’ uses a temp table.

The funniest thing is that this appears only in one of many SQL Servers 2012

I haven’t found any differences among the servers no matter how hard I tried, but it looks like somehow it’s related to SISS and sp_describe_* functions and the temporary table (if I use global temporary table ##temp_Sample - no problems arise)

Here is a link with the similar problem and (not the best) work around:
http://raresql.com/2013/06/15/sql-serve ... emp-table/

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Cannot use temp table

Post by azyk » Fri 18 Dec 2015 10:57

Please execute the following project and let us know the results.

Code: Select all

program SDAC_temp_table;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils,
  ActiveX,
  MSAccess,
  OLEDBAccess;

var
  MSConnection: TMSConnection;
  MSQuery: TMSQuery;
begin
  ParamsInfoOldBehavior := True;
  __UseUpdateOptimization := False;

  CoInitialize(nil);
  MSConnection := TMSConnection.Create(nil);
  MSQuery := TMSQuery.Create(nil);

  MSConnection.Server := '*****';
  MSConnection.Database := '*****';
  MSConnection.Username := '*****';
  MSConnection.Password := '*****';
  MSConnection.LoginPrompt := False;

  MSQuery.Connection := MSConnection;

  MSQuery.SQL.Clear;
  MSQuery.SQL.Add('CREATE TABLE #NAME (F1 VARCHAR(10),F2 VARCHAR(10))');
  MSQuery.Execute;
  MSQuery.SQL.Clear;
  MSQuery.SQL.Add('INSERT INTO #NAME (F1,F2) VALUES (:F1,:F2)');
  MSQuery.Prepare;
  MSQuery.ParambyName('F1').AsString := '1';
  MSQuery.ParambyName('F2').AsString := '1';
  MSQuery.Execute;

  MSQuery.SQL.Clear;
  MSQuery.SQL.Add('SELECT * FROM #NAME');
  MSQuery.Open;
  Writeln('F1=' + MSQuery.FieldByName('F1').AsString);
  Writeln('F2=' + MSQuery.FieldByName('F2').AsString);

  MSQuery.Free;
  MSConnection.Free;

  Readln;
  CoUninitialize;
end.



Stephan Mercier
Posts: 4
Joined: Tue 15 Dec 2015 22:27

Re: Cannot use temp table

Post by Stephan Mercier » Fri 18 Dec 2015 15:41

Hi Petriukx, your issue could be related to SQL 2012 SP1. We had a similar issue which we fixed by installing SP2 of SQL 2012.

Stephan Mercier
Posts: 4
Joined: Tue 15 Dec 2015 22:27

Re: Cannot use temp table

Post by Stephan Mercier » Fri 18 Dec 2015 20:06

Azyk, I tried the test program that you provided (thank you) and I get the same error "Invalid object name #NAME". The only thing I changed is the authentication method (MSConnection.Authentication:=auWindows;). Below is the corresponding SQL trace. How can this be working if the #NAME is created outside of the stored procedure?

SET NO_BROWSETABLE ON
CREATE TABLE #NAME (F1 VARCHAR(10),F2 VARCHAR(10))
SET NO_BROWSETABLE OFF
exec [sys].sp_describe_undeclared_parameters N'INSERT INTO #NAME (F1,F2) VALUES (@P1,@P2)'

Petriukx
Posts: 12
Joined: Tue 06 Jun 2006 11:17

Re: Cannot use temp table

Post by Petriukx » Sat 19 Dec 2015 06:40

Stephan Mercier wrote:Hi Petriukx, your issue could be related to SQL 2012 SP1. We had a similar issue which we fixed by installing SP2 of SQL 2012.
Thank you. I installed SP3 and all problems gone.
Happy Holidays to everyone

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Cannot use temp table

Post by azyk » Thu 24 Dec 2015 11:18

We have reproduced the described behavior and investigated it. The reason for such behavior is not SDAC, but a specificity of SQL Server Native Client version 11, and we can't affect it.

To solve the issue, try to use a SQL Native Client provider version lower than 11 or the standard OLEDB provider for connection. Such code will use SQL Native Client provider 10:

Code: Select all

MSConnection.Options.Provider := prNativeClient;
MSConnection.Options.NativeClientVersion := nc2008;
Code for the OLEDB provider:

Code: Select all

MSConnection.Options.Provider := prSQL;
Please let us know the results.

zarnegar2160
Posts: 3
Joined: Tue 25 Apr 2017 20:04

Re: Cannot use temp table

Post by zarnegar2160 » Fri 13 Oct 2017 18:00

when I close one form where include mschange notification , mschange notification on the second form, it does not work
and application show this error message:
invalid object name 'SDAC_NS-53_QUEUE'
My program has several forms, each with a mschange notification
But when multiple forms with tools are simultaneously opened , The mschange notification fails in other forms.Forms are free and Nil when closing.

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Cannot use temp table

Post by azyk » Wed 18 Oct 2017 14:13

We tried to reproduce the issue according to your description, however the issue was not reproduced. Please compose a small test project to reproduce it and send to us using the contact form at our site http://www.devart.com/company/contactform.html. Also include test DB backup(BAK) into the project.

Post Reply