Conversion from SQL 7.0 to SQL 2000

We have recently converted a SQL 7.0 database to SQL
2000. There are a number of tables that have default
values set for inserting data. When accessing via the
front-end application (ASP page), the tables are not
inserting the default values instead it is inserting
<NULL> into the field. Any insight?Tom,
Thanks for the quick response. I did include the defaults
in the migration and the tables indicates the default
values (as well as the sp_help on the table). Our front
end application was running successfully when using the
SQL 7.0 database. We are not explicitly inserting the
NULL values into the table. If there are no values for
the specific fields it should just insert the default
values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>1. The defaults were not added in the migration. Use
sp_help on the table in question.
>2. The front-end is actually explicitly inserting
NULL's. Use the Profiler to find out.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in
message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL
>2000. There are a number of tables that have default
>values set for inserting data. When accessing via the
>front-end application (ASP page), the tables are not
>inserting the default values instead it is inserting
><NULL> into the field. Any insight?
>|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This isn't an INSERT statement, since the word INSERT does not appear =anywhere in it. I'd be looking for something along the lines of:
INSERT MyTable (Col1, Col2) VALUES ('X', 3)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"jp" <jp@.p.com> wrote in message =news:eADPuc6VDHA.1676@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert statement:
exec sp_cursor 180150001, 4, 0, N'tbl_Worksheets', @.L2 =3D '01', @.RepID ==3D '886048', @.SalesWk =3D 30, @.Name =3D 'FORTUNE, AMY = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D =36.00, @.WMU1 =3D 0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 ==3D 165.60, @.Sales_UC1 =3D 70.55, @.Cancels1 =3D 8, @.Cancels_Comp1 =3D =0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 =3D NULL, @.WMU2 =3D =NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, =@.Cancels_UC2 =3D NULL, @.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, =@.Comp3 =3D NULL, @.Sales3 =3D NULL, @.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D =NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, @.Cancels_UC3 =3D =NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, =@.Other_Doe =3D ' ', @.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:O768Gn2VDHA.1676@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give you, i.e. what was the exact T-SQL =INSERT statement going into SQL Server. Also, have you tried something =like the following in QA?:
insert MyTable default values
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message =news:5a6201c35767$b817c780$a001280a@.phx.gbl...
Tom,
Thanks for the quick response. I did include the defaults in the migration and the tables indicates the default values (as well as the sp_help on the table). Our front end application was running successfully when using the SQL 7.0 database. We are not explicitly inserting the NULL values into the table. If there are no values for the specific fields it should just insert the default values assigned in the tables. Am I missing something?
Laura
>--Original Message--
>Two possibilities:
>
>1. The defaults were not added in the migration. Use sp_help on the table in question.
>2. The front-end is actually explicitly inserting NULL's. Use the Profiler to find out.
>
>-- >Tom
>
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>
>"Laura Reynolds" <lreynolds@.dialamerica.com> wrote in message news:0a6901c35762$54949120$a601280a@.phx.gbl...
>We have recently converted a SQL 7.0 database to SQL >2000. There are a number of tables that have default >values set for inserting data. When accessing via the >front-end application (ASP page), the tables are not >inserting the default values instead it is inserting ><NULL> into the field. Any insight?
> --=_NextPart_000_000D_01C35787.64B12BC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This isn't an INSERT statement, since =the word INSERT does not appear anywhere in it. I'd be looking for =something along the lines of:
INSERT MyTable (Col1, Col2) =VALUES ('X', 3)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"jp" wrote in =message news:eADPuc6VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
Tom,
this is the insert =statement:
exec sp_cursor 180150001, 4, 0, =N'tbl_Worksheets', @.L2 =3D '01', @.RepID =3D '886048', @.SalesWk =3D 30, @.Name =3D ='FORTUNE, AMY &nbs=p;  =; = ', @.BranchNo =3D '239', @.Job1 =3D '32', @.Hours1 =3D 36.00, @.WMU1 =3D =0.00, @.Comp1 =3D 453.60, @.Sales1 =3D 63, @.Sales_Comp1 =3D 165.60, @.Sales_UC1 =3D 70.55, =@.Cancels1 =3D 8, @.Cancels_Comp1 =3D 0.00, @.Cancels_UC1 =3D 8.75, @.Job2 =3D NULL, @.Hours2 ==3D NULL, @.WMU2 =3D NULL, @.Comp2 =3D NULL, @.Sales2 =3D NULL, @.Sales_Comp2 =3D NULL, =@.Sales_UC2 =3D NULL, @.Cancels2 =3D NULL, @.Cancels_Comp2 =3D NULL, @.Cancels_UC2 =3D NULL, =@.Job3 =3D NULL, @.Hours3 =3D NULL, @.WMU3 =3D NULL, @.Comp3 =3D NULL, @.Sales3 =3D NULL, =@.Sales_Comp3 =3D NULL, @.Sales_UC3 =3D NULL, @.Cancels3 =3D NULL, @.Cancels_Comp3 =3D NULL, =@.Cancels_UC3 =3D NULL, @.Downtime =3D 0.00, @.TWIG =3D 0.00, @.Referral_Bonus =3D 0.00, =@.Add_Comp =3D 60.00, @.Inc_Bonus =3D 0.00, @.Other_Bonus =3D 0.00, @.Other_Doe =3D ' ', =@.Guarantee =3D 8.00, @.TimeStamp =3D NULL
thanks
"Tom Moreau" = wrote in message news:O768Gn2VDHA.1676=@.TK2MSFTNGP10.phx.gbl...
What did the Profiler trace give =you, i.e. what was the exact T-SQL INSERT statement going into SQL Server. =Also, have you tried something like the following in QA?:

insert MyTable default values
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Laura Reynolds" --Original Message-->Two =possibilities:>>1. The defaults were not added in the migration. Use sp_help on the =table in question.>2. The front-end is actually =explicitly inserting NULL's. Use the Profiler to find =out.>>-- =>Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON =Canada>www.pinnaclepublishing.com/sql>>>"Laur=a Reynolds" We have recently converted a SQL 7.0 database to SQL >2000. =There are a number of tables that have default >values set for =inserting data. When accessing via the >front-end application (ASP =page), the tables are not >inserting the default values instead it is inserting > into the field. Any insight?>

---

--=_NextPart_000_000D_01C35787.64B12BC0--