Default values do not get created in SQL 2005 tables

Got a table with fields CreatedBy and CreatedOn I set its Default Value or
Binding to Suser_Sname() and Getdate() respectively.
Testing inserting new records from my VB.NET user interface I find that
neither default values get populated, yet I have a trigger for insert or
delete that poluates the lastModifiedBy and last MOdifiedOn fields OK in
same table. This used to work fine in Server 2000 and VB6 or Vs Net 2003.
Any ideas why it no longer seems to work? It has to do with SQL server
itself I think. When I add a new record in the table itself. I get a message
saying that the new record has been added but that an error occurred when
the values were returned and in the selector column in the table view
there's a small red circle with an exclamation mark in it and the two
Created cells did not get populated. If I then click again on the execute
menu button, the two default value cells get populated OK and the
exclamation mark dissapears. It did not work that way in sql 2000, the
default values got populated on first try.
As test you can run this on the following table. On my system (the database
is on a Win2003 server, Its sql 2005 Standard and I'm running the Sql server
management studio on a Win Xp box, all the stuff has the latest service
packs) the behaviour is as described above.
/****** Object: Table [dbo].[tblCountries] Script Date: 01/02/2006 12:43:41
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCountries](
[tblCountryId] [int] IDENTITY(1,1) NOT NULL,
[Country3LetterISOCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[CountryNameL1] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[CountryNameL2] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_tblCountries_CreatedBy_1] DEFAULT (suser_sname()),
[CreatedOn] [datetime] NULL CONSTRAINT [DF_tblCountries_CreatedOn_1] DEFAULT
(getdate()),
[LastModifiedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastModifiedOn] [datetime] NULL,
[ts] [timestamp] NULL,
CONSTRAINT [PK_tblCountries_1] PRIMARY KEY CLUSTERED
(
[tblCountryId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I would really hope someone comes up with a solution. I can write triggers
that insert the default values, these seem to work OK. but It would be time
consuming for all my tables. I'd rather things worked as described in the
docs.<GGG>
Best regards and happy new year
BobHi Bob
It would have helped if you had supplied an insert statement for testing.
However, I noticed that most columns had either a default, allowed null, or
were an identity, so I tried the following insert:
INSERT INTO
[dbo]. [tblCountries](Country3LetterISOCode,Cou
ntryNameL1,CountryNameL2)
VALUES ('ABC', 'String1', 'String2')
The default values were inserted as expected. SQL Server 2005 is working
fine.
My guess is that the app is not building the insert statement correctly.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Bob" <bdufour@.sgiims.com> wrote in message
news:%23$Es1T8DGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Got a table with fields CreatedBy and CreatedOn I set its Default Value or
> Binding to Suser_Sname() and Getdate() respectively.
> Testing inserting new records from my VB.NET user interface I find that
> neither default values get populated, yet I have a trigger for insert or
> delete that poluates the lastModifiedBy and last MOdifiedOn fields OK in
> same table. This used to work fine in Server 2000 and VB6 or Vs Net 2003.
> Any ideas why it no longer seems to work? It has to do with SQL server
> itself I think. When I add a new record in the table itself. I get a
> message saying that the new record has been added but that an error
> occurred when the values were returned and in the selector column in the
> table view there's a small red circle with an exclamation mark in it and
> the two Created cells did not get populated. If I then click again on the
> execute menu button, the two default value cells get populated OK and the
> exclamation mark dissapears. It did not work that way in sql 2000, the
> default values got populated on first try.
> As test you can run this on the following table. On my system (the
> database is on a Win2003 server, Its sql 2005 Standard and I'm running the
> Sql server management studio on a Win Xp box, all the stuff has the latest
> service packs) the behaviour is as described above.
> /****** Object: Table [dbo].[tblCountries] Script Date: 01/02/2006
> 12:43:41 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[tblCountries](
> [tblCountryId] [int] IDENTITY(1,1) NOT NULL,
> [Country3LetterISOCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL,
> [CountryNameL1] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [CountryNameL2] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> [CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_tblCountries_CreatedBy_1] DEFAULT (suser_sname()),
> [CreatedOn] [datetime] NULL CONSTRAINT [DF_tblCountries_CreatedOn_1]
> DEFAULT (getdate()),
> [LastModifiedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [LastModifiedOn] [datetime] NULL,
> [ts] [timestamp] NULL,
> CONSTRAINT [PK_tblCountries_1] PRIMARY KEY CLUSTERED
> (
> [tblCountryId] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> I would really hope someone comes up with a solution. I can write triggers
> that insert the default values, these seem to work OK. but It would be
> time consuming for all my tables. I'd rather things worked as described in
> the docs.<GGG>
> Best regards and happy new year
> Bob
>
>|||Kalen Thanks,
You are right the insert statement that you tried as a result does populated
the CreatedBy ANd CreatedOn cells.
But did you try just using the SQL management Studio Open the the table and
write the data directly in the table?
When you do that, you get the exclamation point as explained in my first
post, and that is the point.
If it does not work directly in the MStudio while you do direct data entry ,
it won't work with the default insert statements generated by Visual studio.
To simplify the problem, take the following simple table.
/****** Object: Table [dbo].[TestDefaults] Script Date: 01/02/2006 17:36:40
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestDefaults](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Data] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_TestDefaults_CreatedBy] DEFAULT (suser_sname()),
CONSTRAINT [PK_TestDefaults] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Use the script to create it.
Then just open the table in Studio manager and write any data in the cell
data, then move your cursor down one line. When you then move away from the
line. The CreatedBy cell does not appear to be populated and you get what I
described in my first post. Yet if you then close the table and open it
again you will see that the cell did indeed get populated. Now if you use
any automatically dataviewgrid generated in Visual studio 2005 and use that
form in Visual studio 2005 to insert new records, the CreateBy cell in the
table will not get populated. Do exactly the same with SQL server 2000
tables and the CreatedBy cell values will get populated.
The default insert command that gets generated by Visual Studio 2005 in the
table adapter where I first noticed the problem is the following. ( I took
it out of the autogenerated code for the table adapter being used.
Me._adapter.InsertCommand.CommandText = "INSERT INTO [tblCountries]
([Country3LetterISOCode], [CountryNameL1], [CountryNam"& _
"eL2], [CurrencyName], [CurrencySymbol], [CreatedBy], [CreatedOn],
[LastModifiedB"& _
"y], [LastModifiedOn]) VALUES (@.Country3LetterISOCode, @.CountryNameL1,
@.CountryNa"& _
"meL2, @.CurrencyName, @.CurrencySymbol, @.CreatedBy, @.CreatedOn,
@.LastModifiedBy, @."& _
"LastModifiedOn);"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.Vi
sualBasic.ChrW(10)&"SELECT
tblCountryId, Country3LetterISOCode, CountryNameL1, Cou"& _
"ntryNameL2, CurrencyName, CurrencySymbol, CreatedBy, CreatedOn,
LastModifiedBy, "& _
"LastModifiedOn, ts FROM tblCountries WHERE (tblCountryId =
SCOPE_IDENTITY())"
I think that what happens is that because the parameters @.CreatedBy and
@.CreatedOn must be getting passed as null values (I don't show them in or
affect them in my UI elements) by the Visual Studio program, they override
the default values specified in the table definitions, so the net result is
that when you look at the table after doing an insert with default Visual
studio generated code, you end up not getting any values in the cells set
with a default value in the table. I'm going to try to remove these default
fields from the table adapter in the Visual studio code and see if that
solves the problem.
Thanks for the tip of looking at the insert statement, its what got me
thinking on this possibility.
Regards,
Bob
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23HlOGf8DGHA.2088@.TK2MSFTNGP09.phx.gbl...
> Hi Bob
> It would have helped if you had supplied an insert statement for testing.
> However, I noticed that most columns had either a default, allowed null,
> or were an identity, so I tried the following insert:
>
> INSERT INTO
> [dbo]. [tblCountries](Country3LetterISOCode,Cou
ntryNameL1,CountryNameL2)
> VALUES ('ABC', 'String1', 'String2')
> The default values were inserted as expected. SQL Server 2005 is working
> fine.
> My guess is that the app is not building the insert statement correctly.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Bob" <bdufour@.sgiims.com> wrote in message
> news:%23$Es1T8DGHA.2300@.TK2MSFTNGP15.phx.gbl...
>
>|||Bob (bdufour@.sgiims.com) writes:
> You are right the insert statement that you tried as a result does
> populated the CreatedBy ANd CreatedOn cells.
> But did you try just using the SQL management Studio Open the the table
> and write the data directly in the table?
If I know Kalen well, I don't think she would try Open Table, unless
you explicitly said that the problem was with that function. If you
only say "insert", people like me and Kalen will think of an INSERT
statement, and not of Open Table. That's not a function we normally use.

> When you do that, you get the exclamation point as explained in my first
> post, and that is the point.
The pop-up says that the row was indeed inserted, but there was problems
of retrieving the value. If that is because of the IDENTITY column of
the default, I don't know. In any case, the issue you have is a tools
issue, not an issue with SQL Server itself.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Further Info
Just as I thought, when you use the standard table adapters and you
autogenerate your select insert update and delete statements using the
Visual studio 2005 UI. You have to be careful NOT to include in the fields
used by your table adapters any fields that have default values defined in
the database. If you have those fields included, the automatically generated
Insert statements will pass a NULL to the insert statement and that NULL
will overrode the default value defined.
Now that's a doozy :-) but in a weird sort of way it makes sense.
Hope all this f... around helps someone.
Happy new year,
Bob
"Bob" <bdufour@.sgiims.com> wrote in message
news:%238xRVF$DGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Kalen Thanks,
> You are right the insert statement that you tried as a result does
> populated the CreatedBy ANd CreatedOn cells.
> But did you try just using the SQL management Studio Open the the table
> and write the data directly in the table?
> When you do that, you get the exclamation point as explained in my first
> post, and that is the point.
> If it does not work directly in the MStudio while you do direct data entry
> , it won't work with the default insert statements generated by Visual
> studio.
> To simplify the problem, take the following simple table.
> /****** Object: Table [dbo].[TestDefaults] Script Date: 01/02/2006
> 17:36:40 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [dbo].[TestDefaults](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [Data] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> [CreatedBy] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF_TestDefaults_CreatedBy] DEFAULT (suser_sname()),
> CONSTRAINT [PK_TestDefaults] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> Use the script to create it.
> Then just open the table in Studio manager and write any data in the cell
> data, then move your cursor down one line. When you then move away from
> the line. The CreatedBy cell does not appear to be populated and you get
> what I described in my first post. Yet if you then close the table and
> open it again you will see that the cell did indeed get populated. Now if
> you use any automatically dataviewgrid generated in Visual studio 2005
> and use that form in Visual studio 2005 to insert new records, the
> CreateBy cell in the table will not get populated. Do exactly the same
> with SQL server 2000 tables and the CreatedBy cell values will get
> populated.
> The default insert command that gets generated by Visual Studio 2005 in
> the table adapter where I first noticed the problem is the following. ( I
> took it out of the autogenerated code for the table adapter being used.
> Me._adapter.InsertCommand.CommandText = "INSERT INTO [tblCountries]
> ([Country3LetterISOCode], [CountryNameL1], [CountryNam"& _
> "eL2], [CurrencyName], [CurrencySymbol], [CreatedBy], [CreatedOn],
> [LastModifiedB"& _
> "y], [LastModifiedOn]) VALUES (@.Country3LetterISOCode, @.CountryNameL1,
> @.CountryNa"& _
> "meL2, @.CurrencyName, @.CurrencySymbol, @.CreatedBy, @.CreatedOn,
> @.LastModifiedBy, @."& _
> "LastModifiedOn);"&Global.Microsoft.VisualBasic.ChrW(13)&Global.Microsoft.
VisualBasic.ChrW(10)&"SELECT
> tblCountryId, Country3LetterISOCode, CountryNameL1, Cou"& _
> "ntryNameL2, CurrencyName, CurrencySymbol, CreatedBy, CreatedOn,
> LastModifiedBy, "& _
> "LastModifiedOn, ts FROM tblCountries WHERE (tblCountryId =
> SCOPE_IDENTITY())"
> I think that what happens is that because the parameters @.CreatedBy and
> @.CreatedOn must be getting passed as null values (I don't show them in or
> affect them in my UI elements) by the Visual Studio program, they override
> the default values specified in the table definitions, so the net result
> is that when you look at the table after doing an insert with default
> Visual studio generated code, you end up not getting any values in the
> cells set with a default value in the table. I'm going to try to remove
> these default fields from the table adapter in the Visual studio code and
> see if that solves the problem.
> Thanks for the tip of looking at the insert statement, its what got me
> thinking on this possibility.
> Regards,
> Bob
>
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23HlOGf8DGHA.2088@.TK2MSFTNGP09.phx.gbl...
>|||Bob,
Just want to let you know, I recently made a post addressing the same
peculiarity.
And after soul searching and some thought, I did some testing and have
confirmed what does seem to make sense based on the way the
"disconnected" ado works.
When your dataset datatable has a schema that includes fields with
defaults and triggers (I had an UPDATE trigger), the cache will have
<NULL> for them upon the .fill method of the TableAdapter (I used a
datagridview too). So when you make your changes or add a new record,
the update method sends Nulls (not sure if it is Null or DBNull) back
to the datasource table and hence SQL Server thinks you've given it a
value (of it is Null at this point) and doesn't set a default value. I
can only assume that this Null is different than a standard Null that
SQL Server gets from the Enterprise Manager or Query Analyser.
Also, if there is a trigger on a field, SQL Server thinks that you've
added data to that field and in my case (I tested for "If NOT
UPDATE(myfield)") the trigger did'nt fire, again
suggesting that the Null sent back to the SQL Server from the
application is different than the Null in EM or QA.
VB6 ado may have not behaved in this "disconnected" way, at least when
it came to this situation.
Any thoughts?
Christopher|||cefrancke@.yahoo.com wrote in news:1137325516.855206.30800
@.g47g2000cwa.googlegroups.com:

> So when you make your changes or add a new record,
> the update method sends Nulls (not sure if it is Null or DBNull) back
> to the datasource table and hence SQL Server thinks you've given it a
> value (of it is Null at this point) and doesn't set a default value. I
> can only assume that this Null is different than a standard Null that
> SQL Server gets from the Enterprise Manager or Query Analyser.
> Also, if there is a trigger on a field, SQL Server thinks that you've
> added data to that field and in my case (I tested for "If NOT
> UPDATE(myfield)") the trigger did'nt fire, again
> suggesting that the Null sent back to the SQL Server from the
> application is different than the Null in EM or QA.
>
You are right in that this is what happens, but you are wrong about
nulls in QA, EM.
Test this from QA:
--create a table with a default column
create table testtab (id int primary key, col1 int default 10)
--insert one record
insert into testtab (id) values (1);
select * from testtab;
--you should now see 1, 10
--insert another record
insert into testtab values(2, null);
select * from testtab
--you should now see 2, null
null IS a value, and if you explicitly sends in NULL to a table which
has a default column, SQL Server sees that you have set a value for that
column and therefore doesn't assign the default value. It isn only if
you have not set a value for the column that the default will be
assigned.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********