newsequentialid() default value

[Former Member]

My DBMS is SQL Server 2012 and I would like to add newsequentialid() to the default values list for a column. I tried clicking on the "create" button next to the drop down and was able to create the value. It shows up in the list and as the default value in the column listing but it isn't showing up in the SQL preview. If I choose newid() it shows up fine. I did not add newid() it was already there. What am I doing wrong? I'm using PD 16.5

Thanks,

Mark

[Former Member · Accepted solution]

I tried to reproduce your finding. When i generate my test Table the following DDL is generated by PD:

/*==============================================================*/
/* Table: TABLE_1                                               */
/*==============================================================*/
create table TABLE_1 (
   COLUMN_1             int                  null
)
go

execute sp_bindefault NEWSEQUENTIALID, 'TABLE_1.COLUMN_1'
go

I think that the procedure call is implementing the Default to Columns_1. Do you have sth. similar in your DDL?

kind Regards

Klaus 

[Former Member · Accepted solution]

This is what I get from PD...

if exists (select 1
            from  sysobjects
           where  id = object_id('t_test')
            and   type = 'U')
   drop table t_test
go

/*==============================================================*/
/* Table: t_test                                                */
/*==============================================================*/
create table t_test (
   id                   uniqueidentifier     not null,
   col1                 varchar(10)          null
)
go

execute sp_bindefault newsequentialid__, 't_test.id'
go

alter table t_test
   add constraint PK_T_TEST primary key (id)
go

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

This is what I want...

if exists (select 1
            from  sysobjects
           where  id = object_id('t_test')
            and   type = 'U')
   drop table t_test
go

/*==============================================================*/
/* Table: t_test                                                */
/*==============================================================*/
create table t_test (
   id                   uniqueidentifier     not null default newsequentialid(),
   col1                 varchar(10)          null
)
go

alter table t_test
   add constraint PK_T_TEST primary key (id)
go

I'm sure Im doing something incorrectly I just don't know what it is.

[Former Member · Accepted solution]

To achieve this kind if DDL

   id                   uniqueidentifier     not null default newsequentialid(),

you have to modify the unlying DBMS file. I recommend you copy the original DBMS file and work on the copy, you can do this in Tools/Resources/DBMS/New.. copy from MS SQL 2012. Name the file so taht you can distinguish it from the original one.

Go to your PDM and change the DBMS file to your new one.

Edit the DBMS (Database/Edit current Database), in the left window pane you find a section called "Script / Keywords", in "ReservedDefault" you find a list of commonly used default values. Just enter newsequentialid() to the list.  Save the changes.

If you now want to set the default value for a column you may choose newsequentialid() form the list of values. 

[Former Member · Accepted solution]

I finally figured it out. It was easy once you know.

I was creating a default object in the model instead of a default value for the dbms. Once I figured out how to delete the default object in the model it stopped showing up in the list to choose from. I was able to create the default value for the dbms as you described and assign it to the column. It is working fine now. I was able to add newsequentialid() and getutcdate() to the default value list.

Thanks for your help.