Devart Forums

Posted: **Wed 27 Jul 2011 13:50**

Hi
I use SQL Server 2008 R2 with replication in many other SQL servers.
When I use TUniQuery for inserting data, I set DefaultValues = True because I want to know the default values before posting the record.
But when the table is replicated, there is a new field (GUID data type) that has a default value (newsequentialid()) that cannot be invoked by the client ...

So I've got the following error when I insert a new record:
"La funzione predefinita newsequentialid() può essere utilizzata solo in un'espressione DEFAULT per una colonna di tipo 'uniqueidentifier' in un'istruzione CREATE TABLE o ALTER TABLE. Impossibile combinarla con altri operatori per costituire un'espressione scalare complessa."
(sorry but my SQL server is in Italian ... ErrorNo = 302, Gravity = 16).

When I use SQL Server 2000, the error does not appear because the column has a default value newid() that can invoked by the client ... so there's no problem ...

This is a sample table you can create:

Code: Select all

CREATE TABLE [dbo].[testtable](
	[field1] [int] NOT NULL,
	[field2] [varchar](50) NULL,
	[field3] [uniqueidentifier] ROWGUIDCOL  NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[testtable] ADD  CONSTRAINT [DF_testtable_field3]  DEFAULT (newsequentialid()) FOR [field3]
GO

And this is the pas:

Code: Select all

unit Unit2;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, MemDS, DBAccess, Uni, Grids, DBGrids, ExtCtrls, DBCtrls;

type
  TForm2 = class(TForm)
    DBNavigator1: TDBNavigator;
    DBGrid1: TDBGrid;
    UniConnection1: TUniConnection;
    UniQuery1: TUniQuery;
    DataSource1: TDataSource;
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form2: TForm2;

implementation

{$R *.dfm}

uses sqlserveruniprovider;

procedure TForm2.FormCreate(Sender: TObject);
begin
   uniquery1.Open;
   uniquery1.FieldByName('field3').Required := false;
end;

end.

And this is the dfm:

Code: Select all

object Form2: TForm2
  Left = 0
  Top = 0
  Caption = 'Form2'
  ClientHeight = 251
  ClientWidth = 535
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBNavigator1: TDBNavigator
    Left = 8
    Top = 8
    Width = 240
    Height = 25
    DataSource = DataSource1
    TabOrder = 0
  end
  object DBGrid1: TDBGrid
    Left = 8
    Top = 39
    Width = 497
    Height = 204
    DataSource = DataSource1
    TabOrder = 1
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object UniConnection1: TUniConnection
    ProviderName = 'SQL Server'
    Database = 'gphswcam'
    Username = 'sa'
    Server = '.'
    LoginPrompt = False
    Left = 184
    Top = 80
  end
  object UniQuery1: TUniQuery
    Connection = UniConnection1
    SQL.Strings = (
      'select *'
      'from testtable')
    RefreshOptions = [roAfterInsert, roAfterUpdate, roBeforeEdit]
    Options.DefaultValues = True
    Left = 264
    Top = 136
  end
  object DataSource1: TDataSource
    DataSet = UniQuery1
    Left = 312
    Top = 184
  end
end

Posted: **Wed 27 Jul 2011 15:18**

Hello,

We cannot influence such SQL Server behaviour. So, there are two solutions:
- do not set the DefaultValues property to True;
- change the default value of your GUID field to NEWID() .

Devart Forums

DefaultValues on SQL Server table with active replication

DefaultValues on SQL Server table with active replication