MDAC 2.8 not accepting nulls

Hi,
My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
clustered SqlServer 2005 database. SqlServer definition says null values are
acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
accepted. The same application connecting to the same database/table from
other clients using up to MDAC 2.7 do not have any problem. Is this a bug? I
f
so any idea on ways to get around it?
Any input is appreciated.
Thanks.
--
SankarVSWhat object are you using in ADODB to update the table?
Would you mind posting some example code? I am trying to reproduce.
Thanks,
Adam
"sankarvs" wrote:

> Hi,
> My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
> clustered SqlServer 2005 database. SqlServer definition says null values a
re
> acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
> accepted. The same application connecting to the same database/table from
> other clients using up to MDAC 2.7 do not have any problem. Is this a bug?
If
> so any idea on ways to get around it?
> Any input is appreciated.
> Thanks.
> --
> SankarVS|||The application uses ODBC (System DSN) connection to the SqlServer database.
A simple insert statement fails:
insert into temp1( gf_code, gf_key ) values( :sGFCode, :sGFKey )
where gf_key is a foreign key linked to another string column
SankarVS
"Goose" wrote:
[vbcol=seagreen]
> What object are you using in ADODB to update the table?
> Would you mind posting some example code? I am trying to reproduce.
> Thanks,
> Adam
> "sankarvs" wrote:
>

MDAC 2.8 not accepting nulls

Hi,
My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
clustered SqlServer 2005 database. SqlServer definition says null values are
acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
accepted. The same application connecting to the same database/table from
other clients using up to MDAC 2.7 do not have any problem. Is this a bug? If
so any idea on ways to get around it?
Any input is appreciated.
Thanks.
SankarVS
What object are you using in ADODB to update the table?
Would you mind posting some example code? I am trying to reproduce.
Thanks,
Adam
"sankarvs" wrote:

> Hi,
> My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
> clustered SqlServer 2005 database. SqlServer definition says null values are
> acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
> accepted. The same application connecting to the same database/table from
> other clients using up to MDAC 2.7 do not have any problem. Is this a bug? If
> so any idea on ways to get around it?
> Any input is appreciated.
> Thanks.
> --
> SankarVS
|||The application uses ODBC (System DSN) connection to the SqlServer database.
A simple insert statement fails:
insert into temp1( gf_code, gf_key ) values( :sGFCode, :sGFKey )
where gf_key is a foreign key linked to another string column
SankarVS
"Goose" wrote:
[vbcol=seagreen]
> What object are you using in ADODB to update the table?
> Would you mind posting some example code? I am trying to reproduce.
> Thanks,
> Adam
> "sankarvs" wrote:

Maximum Row Size in SQL Server 2000

Using this code:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (8000) NULL ,
[COMMENT2] [varchar] (8000) NULL
) ON [PRIMARY]
GO
I get the error: Warning: The table 'test' has been created but its maximum
row size (16029) exceeds the maximum number of bytes per row (8060).
However, I can create a table with smaller field lengths:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (10) NULL ,
[COMMENT2] [varchar] (10) NULL
) ON [PRIMARY]
GO
I can then alter the field lengths in Enterprise Manager back to 8000 and
not get the error. I also notice I can import data from a text file and a
table will be created with numerous varchar fields that are each 8000 in
size. Why cannot I create the table with multiple 8000 length fields but SQ
L
Server allows me to modify an existing table or import into a table that has
multiple 8000 length fields?
Thank you.Hi,
like you said, you get a "Warning" no error. SQL Server just wants to
keep you informed that the data *might* be truncated, if you insert
more than 8000 characters, but the table *will* be created.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||The warning you get is only a warning. The table is still created. Read the
warning text carefully.
The table is created, but if you , for a row, try to have > 8060 bytes (when
you do INSERT or
UPDATE), then that operation will fail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> Using this code:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (8000) NULL ,
> [COMMENT2] [varchar] (8000) NULL
> ) ON [PRIMARY]
> GO
> I get the error: Warning: The table 'test' has been created but its maximu
m
> row size (16029) exceeds the maximum number of bytes per row (8060).
> However, I can create a table with smaller field lengths:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (10) NULL ,
> [COMMENT2] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
> I can then alter the field lengths in Enterprise Manager back to 8000 and
> not get the error. I also notice I can import data from a text file and a
> table will be created with numerous varchar fields that are each 8000 in
> size. Why cannot I create the table with multiple 8000 length fields but
SQL
> Server allows me to modify an existing table or import into a table that h
as
> multiple 8000 length fields?
> Thank you.
>
>
>|||Thank you for the information. So I can have a table with numerous varchar
8000 fields and I'm ok as long as a single inserted row does not contain mor
e
than 8060 characters. Is this correct?
"Tibor Karaszi" wrote:

> The warning you get is only a warning. The table is still created. Read th
e warning text carefully.
> The table is created, but if you , for a row, try to have > 8060 bytes (wh
en you do INSERT or
> UPDATE), then that operation will fail.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
>
>|||Try it :
INSERT INTO TEST VALUES (REPLICATE('*', 80000), REPLICATE('*', 44))
A +
Brian P a écrit :[vbcol=seagreen]
> Thank you for the information. So I can have a table with numerous varcha
r
> 8000 fields and I'm ok as long as a single inserted row does not contain m
ore
> than 8060 characters. Is this correct?
> "Tibor Karaszi" wrote:
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Correct. And, btw, this restriction has been removed in SQL Server 2005 ("pa
ge overflow").
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> Thank you for the information. So I can have a table with numerous varcha
r
> 8000 fields and I'm ok as long as a single inserted row does not contain m
ore
> than 8060 characters. Is this correct?
>|||Brian
But be aware that SQL Server 2005 row_overflow data only applies to variable
length fields.
You cannot have multiple char(8000) columns, for example.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Correct. And, btw, this restriction has been removed in SQL Server 2005
> ("page overflow").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...|||Thanks everyone for the good information. I appreciate the feedback.
Brian
"Kalen Delaney" wrote:

> Brian
> But be aware that SQL Server 2005 row_overflow data only applies to variab
le
> length fields.
> You cannot have multiple char(8000) columns, for example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
>
>

Maximum Row Size in SQL Server 2000

Using this code:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (8000) NULL ,
[COMMENT2] [varchar] (8000) NULL
) ON [PRIMARY]
GO
I get the error: Warning: The table 'test' has been created but its maximum
row size (16029) exceeds the maximum number of bytes per row (8060).
However, I can create a table with smaller field lengths:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (10) NULL ,
[COMMENT2] [varchar] (10) NULL
) ON [PRIMARY]
GO
I can then alter the field lengths in Enterprise Manager back to 8000 and
not get the error. I also notice I can import data from a text file and a
table will be created with numerous varchar fields that are each 8000 in
size. Why cannot I create the table with multiple 8000 length fields but SQL
Server allows me to modify an existing table or import into a table that has
multiple 8000 length fields?
Thank you.Hi,
like you said, you get a "Warning" no error. SQL Server just wants to
keep you informed that the data *might* be truncated, if you insert
more than 8000 characters, but the table *will* be created.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||The warning you get is only a warning. The table is still created. Read the warning text carefully.
The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
UPDATE), then that operation will fail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> Using this code:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (8000) NULL ,
> [COMMENT2] [varchar] (8000) NULL
> ) ON [PRIMARY]
> GO
> I get the error: Warning: The table 'test' has been created but its maximum
> row size (16029) exceeds the maximum number of bytes per row (8060).
> However, I can create a table with smaller field lengths:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (10) NULL ,
> [COMMENT2] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
> I can then alter the field lengths in Enterprise Manager back to 8000 and
> not get the error. I also notice I can import data from a text file and a
> table will be created with numerous varchar fields that are each 8000 in
> size. Why cannot I create the table with multiple 8000 length fields but SQL
> Server allows me to modify an existing table or import into a table that has
> multiple 8000 length fields?
> Thank you.
>
>
>|||Thank you for the information. So I can have a table with numerous varchar
8000 fields and I'm ok as long as a single inserted row does not contain more
than 8060 characters. Is this correct?
"Tibor Karaszi" wrote:
> The warning you get is only a warning. The table is still created. Read the warning text carefully.
> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
> UPDATE), then that operation will fail.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> > Using this code:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (8000) NULL ,
> > [COMMENT2] [varchar] (8000) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I get the error: Warning: The table 'test' has been created but its maximum
> > row size (16029) exceeds the maximum number of bytes per row (8060).
> >
> > However, I can create a table with smaller field lengths:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (10) NULL ,
> > [COMMENT2] [varchar] (10) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I can then alter the field lengths in Enterprise Manager back to 8000 and
> > not get the error. I also notice I can import data from a text file and a
> > table will be created with numerous varchar fields that are each 8000 in
> > size. Why cannot I create the table with multiple 8000 length fields but SQL
> > Server allows me to modify an existing table or import into a table that has
> > multiple 8000 length fields?
> >
> > Thank you.
> >
> >
> >
> >
> >
> >
>
>|||Try it :
INSERT INTO TEST VALUES (REPLICATE('*', 80000), REPLICATE('*', 44))
A +
Brian P a écrit :
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
> "Tibor Karaszi" wrote:
>> The warning you get is only a warning. The table is still created. Read the warning text carefully.
>> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
>> UPDATE), then that operation will fail.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
>> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
>> Using this code:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (8000) NULL ,
>> [COMMENT2] [varchar] (8000) NULL
>> ) ON [PRIMARY]
>> GO
>> I get the error: Warning: The table 'test' has been created but its maximum
>> row size (16029) exceeds the maximum number of bytes per row (8060).
>> However, I can create a table with smaller field lengths:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (10) NULL ,
>> [COMMENT2] [varchar] (10) NULL
>> ) ON [PRIMARY]
>> GO
>> I can then alter the field lengths in Enterprise Manager back to 8000 and
>> not get the error. I also notice I can import data from a text file and a
>> table will be created with numerous varchar fields that are each 8000 in
>> size. Why cannot I create the table with multiple 8000 length fields but SQL
>> Server allows me to modify an existing table or import into a table that has
>> multiple 8000 length fields?
>> Thank you.
>>
>>
>>
>>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Correct. And, btw, this restriction has been removed in SQL Server 2005 ("page overflow").
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
>|||Brian
But be aware that SQL Server 2005 row_overflow data only applies to variable
length fields.
You cannot have multiple char(8000) columns, for example.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Correct. And, btw, this restriction has been removed in SQL Server 2005
> ("page overflow").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
>> Thank you for the information. So I can have a table with numerous
>> varchar
>> 8000 fields and I'm ok as long as a single inserted row does not contain
>> more
>> than 8060 characters. Is this correct?|||Thanks everyone for the good information. I appreciate the feedback.
Brian
"Kalen Delaney" wrote:
> Brian
> But be aware that SQL Server 2005 row_overflow data only applies to variable
> length fields.
> You cannot have multiple char(8000) columns, for example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> > Correct. And, btw, this restriction has been removed in SQL Server 2005
> > ("page overflow").
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> > news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> >> Thank you for the information. So I can have a table with numerous
> >> varchar
> >> 8000 fields and I'm ok as long as a single inserted row does not contain
> >> more
> >> than 8060 characters. Is this correct?
> >>
>
>