Problem with NativeClient 2008 and 2012

[Romano]

Hi,
we are still using Native Client 2005 for our application. We would like to go to the upper version of Native Client but we have problems. Try my example. With Native Client 2005 all works OK, but with Native Client 2008 or 2012 we are getting exception "Invalid object name '#tmpTestTempTable'." I tested it on MS SQL 2008 R2 and MS SQL Server 2012.
Is it some wrong in server settings? Or is it bug in SDAC?

Thanks
Roman Krupicka

Code: Select all

CREATE PROC dbo.MyTestProc
  @ID INT
AS
SET NOCOUNT ON
-- this condition must be False, if it is True, exception won't appear
IF EXISTS(SELECT*FROM sys.objects where object_id = -1)
BEGIN
  CREATE TABLE #tmpTestTempTable(ID INT)
  INSERT #tmpTestTempTable(ID)VALUES(@ID)
  DROP TABLE #tmpTestTempTable
END

Code: Select all

program SDAC_NativeClient;
uses
  Forms,
  Unit2 in 'Unit2.pas' {Form2};
{$R *.res}
begin
  Application.Initialize;
  Application.MainFormOnTaskbar := True;
  Application.CreateForm(TForm2, Form2);
  Application.Run;
end.

Code: Select all

unit Unit2;

interface

(*
CREATE PROC dbo.MyTestProc
  @ID INT
AS
SET NOCOUNT ON

-- this condition must be False, if it is True, exception won't appear
IF EXISTS(SELECT*FROM sys.objects where object_id = -1)
BEGIN
  CREATE TABLE #tmpTestTempTable(ID INT)

  INSERT #tmpTestTempTable(ID)VALUES(@ID)

  DROP TABLE #tmpTestTempTable
END
*)

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, MemDS, DBAccess, MSAccess, OLEDBAccess;

type
  TForm2 = class(TForm)
    MSConnection1: TMSConnection;
    MSQuery1: TMSQuery;
    Button2005: TButton;
    Button2012: TButton;
    Button2008: TButton;
    LabelError: TLabel;
    procedure Button2005Click(Sender: TObject);
    procedure Button2008Click(Sender: TObject);
    procedure Button2012Click(Sender: TObject);
  private
    procedure ExecQuery(AVersion: TNativeClientVersion; const AInfo: string);
  end;

var
  Form2: TForm2;

implementation

{$R *.dfm}

procedure TForm2.Button2005Click(Sender: TObject);
begin
  ExecQuery(nc2005, '2005');
end;

procedure TForm2.Button2008Click(Sender: TObject);
begin
  ExecQuery(nc2008, '2008');
end;

procedure TForm2.Button2012Click(Sender: TObject);
begin
  ExecQuery(nc2012, '2012');
end;

procedure TForm2.ExecQuery(AVersion: TNativeClientVersion; const AInfo: string);
begin
  try
    MSConnection1.Connected := False;
    MSConnection1.Options.NativeClientVersion := AVersion;
    MSConnection1.Connected := True;

    MSQuery1.SQL.Add(
     'SET NOCOUNT ON'                 + #13 +
     'SET ANSI_NULLS ON'              + #13 +
     'SET ANSI_NULL_DFLT_ON ON'       + #13 +
     'SET ANSI_PADDING ON'            + #13 +
     'SET ANSI_WARNINGS ON'           + #13 +
     'SET QUOTED_IDENTIFIER ON'       + #13 +
     'SET CURSOR_CLOSE_ON_COMMIT OFF' + #13 +
     'SET CONCAT_NULL_YIELDS_NULL ON' + #13 +
     'SET IMPLICIT_TRANSACTIONS OFF'  + #13 +
     'SET DATEFORMAT dmy'             + #13 +
     'SET DATEFIRST 1'                + #13 +
     'SET XACT_ABORT ON'              + #13 +
     'SET ARITHABORT ON'              + #13 +
     'SET NUMERIC_ROUNDABORT OFF'     + #13 +
     'SET DEADLOCK_PRIORITY LOW'      + #13 +
     'SET LOCK_TIMEOUT -1');
    MSQuery1.Execute;


    MSQuery1.SQL.Clear;
    MSQuery1.SQL.Add('EXEC dbo.MyTestProc :ID');
    MSQuery1.ParamByName('ID').AsInteger := 1;
    MSQuery1.Prepare;

    MSQuery1.Execute;

    LabelError.Caption := AInfo + ': OK';
  except
    on E: Exception do
      LabelError.Caption := AInfo + ': ' + E.Message;
  end;
end;

end.

Code: Select all

object Form2: TForm2
  Left = 0
  Top = 0
  Caption = 'Form2'
  ClientHeight = 184
  ClientWidth = 462
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object LabelError: TLabel
    Left = 15
    Top = 145
    Width = 12
    Height = 13
    Caption = '...'
  end
  object Button2005: TButton
    Left = 125
    Top = 25
    Width = 75
    Height = 25
    Caption = 'nc2005'
    TabOrder = 0
    OnClick = Button2005Click
  end
  object Button2012: TButton
    Left = 125
    Top = 95
    Width = 75
    Height = 25
    Caption = 'nc2012'
    TabOrder = 1
    OnClick = Button2012Click
  end
  object Button2008: TButton
    Left = 125
    Top = 60
    Width = 75
    Height = 25
    Caption = 'nc2008'
    TabOrder = 2
    OnClick = Button2008Click
  end
  object MSConnection1: TMSConnection
    Authentication = auWindows
    Options.AutoTranslate = False
    Options.Provider = prNativeClient
    Options.NativeClientVersion = nc2005
    Options.DefaultSortType = stCaseInsensitive
    Connected = True
    Left = 40
    Top = 20
  end
  object MSQuery1: TMSQuery
    Connection = MSConnection1
    Options.SetFieldsReadOnly = False
    Options.TrimVarChar = True
    Left = 40
    Top = 60
  end
end

[AndreyZ]

Hello,

There was changing in SQL Native Client 10 caused by support of Table-Valued Parameters. You can find more information about this here:
http://social.msdn.microsoft.com/Forums ... 24c8e58de2
http://social.msdn.microsoft.com/Forums ... 2a51275f5c
As a solution, you should not use the Prepare method. Here is a code example:

Code: Select all

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('EXEC dbo.MyTestProc :ID');
MSQuery1.ParamByName('ID').AsInteger := 1;
MSQuery1.Execute;

We cannot influence such SQL Native Client behaviour. For more information, please write to the Microsoft support.

[Romano]

Hi Andrey,
thanks for info. We deleted Prepare and it seems that all works OK now.

Thanks
Roman Krupicka

[AndreyZ]

I am glad I could help. If any other questions come up, please contact us.