Cannot use temp table
-
- Posts: 4
- Joined: Tue 15 Dec 2015 22:27
Cannot use temp table
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)'
ParamsInfoOldBehavior:=True;
MSAccess.__UseUpdateOptimization:=false;
exec [sys].sp_describe_undeclared_parameters N'INSERT INTO #R00190100000033 (F1056,F1057,F254 VALUES (@P1,@P2,@P3)'
Re: Cannot use temp table
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.
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.
-
- Posts: 4
- Joined: Tue 15 Dec 2015 22:27
Re: Cannot use temp table
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;
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;
Re: Cannot use temp table
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/
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/
Re: Cannot use temp table
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.
-
- Posts: 4
- Joined: Tue 15 Dec 2015 22:27
Re: Cannot use temp table
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.
-
- Posts: 4
- Joined: Tue 15 Dec 2015 22:27
Re: Cannot use temp table
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)'
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)'
Re: Cannot use temp table
Thank you. I installed SP3 and all problems gone.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.
Happy Holidays to everyone
Re: Cannot use temp table
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 for the OLEDB provider:
Please let us know the results.
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: Select all
MSConnection.Options.Provider := prSQL;
-
- Posts: 3
- Joined: Tue 25 Apr 2017 20:04
Re: Cannot use temp table
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.
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.
Re: Cannot use temp table
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.