Cannot use temp table

[Stephan Mercier]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.