Incorrect syntax

Hi Im trying to build a database in Microsoft SQL Server 2005.
Ive written the code, but when I execute it, I keep getting an error
message (below)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'customer'.
Code below, any help would be great!
--Table structure for table 'customer'
CREATE TABLE 'customer'(
'CustID' int(10) NOT NULL AUTO_INCREMENT,
'CustName' char(50) NOT NULL,
'Address' char(50) NOT NULL,
PRIMARY KEY ('CustID')
)
--Dumping data for table 'customer'
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
(3,'DailyMurkInc','Fleet Marina');
--Table Structure for table 'deliverynote'
CREATE TABLE 'deliverynote'(
'CATref' int(20) NOT NULL auto_increment,
'CustID' int(50) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipName' char(50) NOT NULL,
PRIMARY KEY ('CATref','CustID')
)
--Dumping data for table 'deliverynote'
INSERT INTO 'deliverynote' VALUES
('01235',2,'Domestic',1,'Fan'),('03278',3,'Domesti c',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
('04577',1,'Commercial',8,'Computer'),('07853',1,' Commercial',9,'Printer'),('08453',3,'Commercial',4 ,'Computer'),('08734',3,'Industrial',6,'Heater'),
('08897',1,'Commercial',10,'Fax'),('08924',3,'Dome stic',5,'Kettle'),('08992',3,'Commercial',3,'Monit or');
--Table Structure for table 'engineer'
CREATE TABLE 'engineer'(
'EngineerName' char(50) NOT NULL,
PRIMARY KEY ('EngineerName')
}
--Dumping data for table 'engineer'
INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
--Table Structure for table 'equipmentcat'
CREATE TABLE 'equipmentcat' (
'EquipCat' char(50) NOT NULL,
PRIMARY KEY ('EquipCat')
)
--Dumping data for table 'equipmentcat'
INSERT INTO 'equipmentcat' VALUES
('Commercial'),('Domestic'),('Industrial');
--Table Structure for table 'repairer'
CREATE TABLE 'repairer' (
'RepID' int(10) NOT NULL,
'RepName' char(50) NOT NULL,
PRIMARY KEY ('RepID')
)
--Dumping data for table 'repairer'
INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
White');
--Table Structure for table 'locationid'
CREATE TABLE 'locationid' (
'LocationName' char(50) NOT NULL,
PRIMARY KEY ('LocationName')
)
--Dumping data for table 'locationid'
INSERT INTO 'locationid' VALUES
('Despatch'),('Gone_Home'),('Goods_In'),('Repairer '),('Testing');
--Table Structure for table 'location'
CREATE TABLE 'location' (
'EquipNumber' int(20) NOT NULL,
'CATref' int(20) NOT NULL,
'Testing' char(50) NOT NULL,
'Despatchdate' char(50) NOT NULL,
PRIMARY KEY ('CATref')
FOREIGN KEY ('CATref')
)
--Dumping data for table 'location'
INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
(2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
1999'),
(5,'08924','April 12th 1999, April 17th 1999','April 20th
1999'),(6,'08734','April 13th 1999','April 14th
1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
1999'),
(8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
1999','May 5th 1999');
--Table Structure for table 'testrecord'
CREATE TABLE 'testrecord' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'Date' char(50) NOT NULL,
'EngineerName' char(50) NOT NULL,
'Pass/Fail' char(20) NOT NULL,
PRIMARY KEY ('CATref','CustID')
FOREIGN KEY ('CATref','CustID','EngineerName')
)
--Dumping data for table 'testrecord'
INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush', 'April 13th
1999','Perfect','Pass')
,('08453',3,4,'Computer','April 12th
1999','Botchit','Pass'),('084531',3,4,'Computer',' May 6th
2000','Perfect','Pass'),('084532',3,4,'Computer',' May 9th
2000','Botchit','Pass'),('08734',3,6,'Heater','Apr il 13th
1999','Botchit','Pass'),
('08924',3,5,'Kettle','April 12th
1999','Perfect','Fail'),('089248',3,5,'Kettle','Ma y 6th
2000','Fudgeit','Pass'),('08992',3,3,'Monitor','Ap ril 12th
1999','Fudgeit','Pass'),('089921',3,3,'Monitor','M ay 6th
2000','Perfect','Pass');
--Table Structure for table 'equipment'
CREATE TABLE 'equipment' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipName' char(50) NOT NULL,
'Goods_In_Date' char(50) NOT NULL,
'Repairer_Date' char (50) NOT NULL,
'Despatch_Date' char(50) NOT NULL,
'Home_Date' char(50) NOT NULL,
'RepID' int(10) NOT NULL,
PRIMARY KEY ('CATref', 'CustID')
FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
)
--Dumping data for table 'equipment'
INSERT INTO 'equipment' VALUES
('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
12th 1999','April 14th 1999',2),
('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
2000','May 20th 2000',3),
('03278',3,7,'Domestic','Toothbrush','April 12th 1999','April 15th
1999','April 16th 1999','April 20th 1999',1),
('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
1999'),
('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
15th 1999'),
('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
2000','May 8th 2000','May 10th
2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
15th 1999'),
('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
1999','April 17th 1999','April 20th 1999',2),
('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
2000');
Daz
You have to specify INSERT INTO for each data to be insterted in your case
See if this helps
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway Cuttings,
Euphoria')
INSERT INTO 'customer' VALUES (2,'Sinking.com','Virtual Lane, Peckham')
INSERT INTO 'customer' VALUES (3,'DailyMurkInc','Fleet Marina');
"Daz01" <dazzaf15@.hotmail.com> wrote in message
news:1166005705.720733.30310@.73g2000cwn.googlegrou ps.com...
> Hi Im trying to build a database in Microsoft SQL Server 2005.
> Ive written the code, but when I execute it, I keep getting an error
> message (below)
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'customer'.
>
> Code below, any help would be great!
> --Table structure for table 'customer'
>
> CREATE TABLE 'customer'(
> 'CustID' int(10) NOT NULL AUTO_INCREMENT,
> 'CustName' char(50) NOT NULL,
> 'Address' char(50) NOT NULL,
> PRIMARY KEY ('CustID')
> )
> --Dumping data for table 'customer'
> INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
> Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
> (3,'DailyMurkInc','Fleet Marina');
>
> --Table Structure for table 'deliverynote'
>
> CREATE TABLE 'deliverynote'(
> 'CATref' int(20) NOT NULL auto_increment,
> 'CustID' int(50) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> )
> --Dumping data for table 'deliverynote'
> INSERT INTO 'deliverynote' VALUES
> ('01235',2,'Domestic',1,'Fan'),('03278',3,'Domesti c',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
> ('04577',1,'Commercial',8,'Computer'),('07853',1,' Commercial',9,'Printer'),('08453',3,'Commercial',4 ,'Computer'),('08734',3,'Industrial',6,'Heater'),
> ('08897',1,'Commercial',10,'Fax'),('08924',3,'Dome stic',5,'Kettle'),('08992',3,'Commercial',3,'Monit or');
>
> --Table Structure for table 'engineer'
> CREATE TABLE 'engineer'(
> 'EngineerName' char(50) NOT NULL,
> PRIMARY KEY ('EngineerName')
> }
> --Dumping data for table 'engineer'
> INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
>
> --Table Structure for table 'equipmentcat'
> CREATE TABLE 'equipmentcat' (
> 'EquipCat' char(50) NOT NULL,
> PRIMARY KEY ('EquipCat')
> )
> --Dumping data for table 'equipmentcat'
>
> INSERT INTO 'equipmentcat' VALUES
> ('Commercial'),('Domestic'),('Industrial');
>
> --Table Structure for table 'repairer'
> CREATE TABLE 'repairer' (
> 'RepID' int(10) NOT NULL,
> 'RepName' char(50) NOT NULL,
> PRIMARY KEY ('RepID')
> )
> --Dumping data for table 'repairer'
> INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
> White');
>
> --Table Structure for table 'locationid'
> CREATE TABLE 'locationid' (
> 'LocationName' char(50) NOT NULL,
> PRIMARY KEY ('LocationName')
> )
> --Dumping data for table 'locationid'
>
> INSERT INTO 'locationid' VALUES
> ('Despatch'),('Gone_Home'),('Goods_In'),('Repairer '),('Testing');
>
> --Table Structure for table 'location'
> CREATE TABLE 'location' (
> 'EquipNumber' int(20) NOT NULL,
> 'CATref' int(20) NOT NULL,
> 'Testing' char(50) NOT NULL,
> 'Despatchdate' char(50) NOT NULL,
> PRIMARY KEY ('CATref')
> FOREIGN KEY ('CATref')
> )
> --Dumping data for table 'location'
>
> INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
> 1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
> (2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
> 1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
> 1999'),
> (5,'08924','April 12th 1999, April 17th 1999','April 20th
> 1999'),(6,'08734','April 13th 1999','April 14th
> 1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
> 1999'),
> (8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
> 1999','May 5th 1999');
>
> --Table Structure for table 'testrecord'
> CREATE TABLE 'testrecord' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'Date' char(50) NOT NULL,
> 'EngineerName' char(50) NOT NULL,
> 'Pass/Fail' char(20) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> FOREIGN KEY ('CATref','CustID','EngineerName')
> )
> --Dumping data for table 'testrecord'
> INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
> 1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
> 2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush', 'April 13th
> 1999','Perfect','Pass')
> ,('08453',3,4,'Computer','April 12th
> 1999','Botchit','Pass'),('084531',3,4,'Computer',' May 6th
> 2000','Perfect','Pass'),('084532',3,4,'Computer',' May 9th
> 2000','Botchit','Pass'),('08734',3,6,'Heater','Apr il 13th
> 1999','Botchit','Pass'),
> ('08924',3,5,'Kettle','April 12th
> 1999','Perfect','Fail'),('089248',3,5,'Kettle','Ma y 6th
> 2000','Fudgeit','Pass'),('08992',3,3,'Monitor','Ap ril 12th
> 1999','Fudgeit','Pass'),('089921',3,3,'Monitor','M ay 6th
> 2000','Perfect','Pass');
>
> --Table Structure for table 'equipment'
> CREATE TABLE 'equipment' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> 'Goods_In_Date' char(50) NOT NULL,
> 'Repairer_Date' char (50) NOT NULL,
> 'Despatch_Date' char(50) NOT NULL,
> 'Home_Date' char(50) NOT NULL,
> 'RepID' int(10) NOT NULL,
> PRIMARY KEY ('CATref', 'CustID')
> FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
> )
> --Dumping data for table 'equipment'
> INSERT INTO 'equipment' VALUES
> ('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
> 12th 1999','April 14th 1999',2),
> ('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
> 2000','May 20th 2000',3),
> ('03278',3,7,'Domestic','Toothbrush','April 12th 1999', 'April 15th
> 1999','April 16th 1999','April 20th 1999',1),
> ('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
> 1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
> 1999'),
> ('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
> 1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
> 15th 1999'),
> ('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
> 2000','May 8th 2000','May 10th
> 2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
> 15th 1999'),
> ('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
> 1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
> 1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
> 1999','April 17th 1999','April 20th 1999',2),
> ('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
> 2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
> 2000');
>

Incorrect syntax

Hi Im trying to build a database in Microsoft SQL Server 2005.
Ive written the code, but when I execute it, I keep getting an error
message (below)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'customer'.
Code below, any help would be great!
--Table structure for table 'customer'
CREATE TABLE 'customer'(
'CustID' int(10) NOT NULL AUTO_INCREMENT,
'CustName' char(50) NOT NULL,
'Address' char(50) NOT NULL,
PRIMARY KEY ('CustID')
)
--Dumping data for table 'customer'
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
(3,'DailyMurkInc','Fleet Marina');
--Table Structure for table 'deliverynote'
CREATE TABLE 'deliverynote'(
'CATref' int(20) NOT NULL auto_increment,
'CustID' int(50) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipName' char(50) NOT NULL,
PRIMARY KEY ('CATref','CustID')
)
--Dumping data for table 'deliverynote'
INSERT INTO 'deliverynote' VALUES
('01235',2,'Domestic',1,'Fan'),('03278',
3,'Domestic',7,'Toothbrush'),('03452
',2,'Domestic',2,'Fan'),
('04577',1,'Commercial',8,'Computer'),('
07853',1,'Commercial',9,'Printer'),(
'08453',3,'Commercial',4,'Computer'),('0
8734',3,'Industrial',6,'Heater'),
('08897',1,'Commercial',10,'Fax'),('0892
4',3,'Domestic',5,'Kettle'),('08992'
,3,'Commercial',3,'Monitor');
--Table Structure for table 'engineer'
CREATE TABLE 'engineer'(
'EngineerName' char(50) NOT NULL,
PRIMARY KEY ('EngineerName')
}
--Dumping data for table 'engineer'
INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
--Table Structure for table 'equipmentcat'
CREATE TABLE 'equipmentcat' (
'EquipCat' char(50) NOT NULL,
PRIMARY KEY ('EquipCat')
)
--Dumping data for table 'equipmentcat'
INSERT INTO 'equipmentcat' VALUES
('Commercial'),('Domestic'),('Industrial
');
--Table Structure for table 'repairer'
CREATE TABLE 'repairer' (
'RepID' int(10) NOT NULL,
'RepName' char(50) NOT NULL,
PRIMARY KEY ('RepID')
)
--Dumping data for table 'repairer'
INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
White');
--Table Structure for table 'locationid'
CREATE TABLE 'locationid' (
'LocationName' char(50) NOT NULL,
PRIMARY KEY ('LocationName')
)
--Dumping data for table 'locationid'
INSERT INTO 'locationid' VALUES
('Despatch'),('Gone_Home'),('Goods_In'),
('Repairer'),('Testing');
--Table Structure for table 'location'
CREATE TABLE 'location' (
'EquipNumber' int(20) NOT NULL,
'CATref' int(20) NOT NULL,
'Testing' char(50) NOT NULL,
'Despatchdate' char(50) NOT NULL,
PRIMARY KEY ('CATref')
FOREIGN KEY ('CATref')
)
--Dumping data for table 'location'
INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
(2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
1999'),
(5,'08924','April 12th 1999, April 17th 1999','April 20th
1999'),(6,'08734','April 13th 1999','April 14th
1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
1999'),
(8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
1999','May 5th 1999');
--Table Structure for table 'testrecord'
CREATE TABLE 'testrecord' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'Date' char(50) NOT NULL,
'EngineerName' char(50) NOT NULL,
'Pass/Fail' char(20) NOT NULL,
PRIMARY KEY ('CATref','CustID')
FOREIGN KEY ('CATref','CustID','EngineerName')
)
--Dumping data for table 'testrecord'
INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
1999','Botchit','Pass'),('012357',2,1,'F
an','May 2nd
2000','Fudgeit','Fail'),('03278',3,7,'To
othbrush','April 13th
1999','Perfect','Pass')
,('08453',3,4,'Computer','April 12th
1999','Botchit','Pass'),('084531',3,4,'C
omputer','May 6th
2000','Perfect','Pass'),('084532',3,4,'C
omputer','May 9th
2000','Botchit','Pass'),('08734',3,6,'He
ater','April 13th
1999','Botchit','Pass'),
('08924',3,5,'Kettle','April 12th
1999','Perfect','Fail'),('089248',3,5,'K
ettle','May 6th
2000','Fudgeit','Pass'),('08992',3,3,'Mo
nitor','April 12th
1999','Fudgeit','Pass'),('089921',3,3,'M
onitor','May 6th
2000','Perfect','Pass');
--Table Structure for table 'equipment'
CREATE TABLE 'equipment' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipName' char(50) NOT NULL,
'Goods_In_Date' char(50) NOT NULL,
'Repairer_Date' char (50) NOT NULL,
'Despatch_Date' char(50) NOT NULL,
'Home_Date' char(50) NOT NULL,
'RepID' int(10) NOT NULL,
PRIMARY KEY ('CATref', 'CustID')
FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
)
--Dumping data for table 'equipment'
INSERT INTO 'equipment' VALUES
('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
12th 1999','April 14th 1999',2),
('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
2000','May 20th 2000',3),
('03278',3,7,'Domestic','Toothbrush','Ap
ril 12th 1999', 'April 15th
1999','April 16th 1999','April 20th 1999',1),
('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
1999'),('04577',1,8,'Commercial','Comput
er','May 1st 1999','May 5th
1999'),
('07853',1,9,'Commercial','Printer','May
1st 1999','May 5th
1999'),('08453',3,4,'Commercial','Comput
er','April 12th 1999','April
15th 1999'),
('084531',3,4,'Commercial','Computer','M
ay 5th 2000','May 6th
2000','May 8th 2000','May 10th
2000',1),('08734',3,6,'Industrial','Heat
er','April 12th 1999','April
15th 1999'),
('08892',3,3,'Commercial','Monitor','Apr
il 12th 1999','April 15th
1999'),('08897',1,10,'Commercial','Fax',
'May 1st 1999','May 5th
1999'),('08924',3,5,'Domestic','Kettle',
'April 12th 1999','April 13th
1999','April 17th 1999','April 20th 1999',2),
('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
2000'),('089921',3,3,'Commercial','Monit
or','May 5th 2000','May 10th
2000');Daz
You have to specify INSERT INTO for each data to be insterted in your case
See if this helps
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway Cuttings,
Euphoria')
INSERT INTO 'customer' VALUES (2,'Sinking.com','Virtual Lane, Peckham')
INSERT INTO 'customer' VALUES (3,'DailyMurkInc','Fleet Marina');
"Daz01" <dazzaf15@.hotmail.com> wrote in message
news:1166005705.720733.30310@.73g2000cwn.googlegroups.com...
> Hi Im trying to build a database in Microsoft SQL Server 2005.
> Ive written the code, but when I execute it, I keep getting an error
> message (below)
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'customer'.
>
> Code below, any help would be great!
> --Table structure for table 'customer'
>
> CREATE TABLE 'customer'(
> 'CustID' int(10) NOT NULL AUTO_INCREMENT,
> 'CustName' char(50) NOT NULL,
> 'Address' char(50) NOT NULL,
> PRIMARY KEY ('CustID')
> )
> --Dumping data for table 'customer'
> INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
> Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
> (3,'DailyMurkInc','Fleet Marina');
>
> --Table Structure for table 'deliverynote'
>
> CREATE TABLE 'deliverynote'(
> 'CATref' int(20) NOT NULL auto_increment,
> 'CustID' int(50) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> )
> --Dumping data for table 'deliverynote'
> INSERT INTO 'deliverynote' VALUES
> ('01235',2,'Domestic',1,'Fan'),('03278',
3,'Domestic',7,'Toothbrush'),('034
52',2,'Domestic',2,'Fan'),
> ('04577',1,'Commercial',8,'Computer'),('
07853',1,'Commercial',9,'Printer')
,('08453',3,'Commercial',4,'Computer'),(
'08734',3,'Industrial',6,'Heater'),
> ('08897',1,'Commercial',10,'Fax'),('0892
4',3,'Domestic',5,'Kettle'),('0899
2',3,'Commercial',3,'Monitor');
>
> --Table Structure for table 'engineer'
> CREATE TABLE 'engineer'(
> 'EngineerName' char(50) NOT NULL,
> PRIMARY KEY ('EngineerName')
> }
> --Dumping data for table 'engineer'
> INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
>
> --Table Structure for table 'equipmentcat'
> CREATE TABLE 'equipmentcat' (
> 'EquipCat' char(50) NOT NULL,
> PRIMARY KEY ('EquipCat')
> )
> --Dumping data for table 'equipmentcat'
>
> INSERT INTO 'equipmentcat' VALUES
> ('Commercial'),('Domestic'),('Industrial
');
>
> --Table Structure for table 'repairer'
> CREATE TABLE 'repairer' (
> 'RepID' int(10) NOT NULL,
> 'RepName' char(50) NOT NULL,
> PRIMARY KEY ('RepID')
> )
> --Dumping data for table 'repairer'
> INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
> White');
>
> --Table Structure for table 'locationid'
> CREATE TABLE 'locationid' (
> 'LocationName' char(50) NOT NULL,
> PRIMARY KEY ('LocationName')
> )
> --Dumping data for table 'locationid'
>
> INSERT INTO 'locationid' VALUES
> ('Despatch'),('Gone_Home'),('Goods_In'),
('Repairer'),('Testing');
>
> --Table Structure for table 'location'
> CREATE TABLE 'location' (
> 'EquipNumber' int(20) NOT NULL,
> 'CATref' int(20) NOT NULL,
> 'Testing' char(50) NOT NULL,
> 'Despatchdate' char(50) NOT NULL,
> PRIMARY KEY ('CATref')
> FOREIGN KEY ('CATref')
> )
> --Dumping data for table 'location'
>
> INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
> 1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
> (2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
> 1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
> 1999'),
> (5,'08924','April 12th 1999, April 17th 1999','April 20th
> 1999'),(6,'08734','April 13th 1999','April 14th
> 1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
> 1999'),
> (8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
> 1999','May 5th 1999');
>
> --Table Structure for table 'testrecord'
> CREATE TABLE 'testrecord' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'Date' char(50) NOT NULL,
> 'EngineerName' char(50) NOT NULL,
> 'Pass/Fail' char(20) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> FOREIGN KEY ('CATref','CustID','EngineerName')
> )
> --Dumping data for table 'testrecord'
> INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
> 1999','Botchit','Pass'),('012357',2,1,'F
an','May 2nd
> 2000','Fudgeit','Fail'),('03278',3,7,'To
othbrush','April 13th
> 1999','Perfect','Pass')
> ,('08453',3,4,'Computer','April 12th
> 1999','Botchit','Pass'),('084531',3,4,'C
omputer','May 6th
> 2000','Perfect','Pass'),('084532',3,4,'C
omputer','May 9th
> 2000','Botchit','Pass'),('08734',3,6,'He
ater','April 13th
> 1999','Botchit','Pass'),
> ('08924',3,5,'Kettle','April 12th
> 1999','Perfect','Fail'),('089248',3,5,'K
ettle','May 6th
> 2000','Fudgeit','Pass'),('08992',3,3,'Mo
nitor','April 12th
> 1999','Fudgeit','Pass'),('089921',3,3,'M
onitor','May 6th
> 2000','Perfect','Pass');
>
> --Table Structure for table 'equipment'
> CREATE TABLE 'equipment' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> 'Goods_In_Date' char(50) NOT NULL,
> 'Repairer_Date' char (50) NOT NULL,
> 'Despatch_Date' char(50) NOT NULL,
> 'Home_Date' char(50) NOT NULL,
> 'RepID' int(10) NOT NULL,
> PRIMARY KEY ('CATref', 'CustID')
> FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
> )
> --Dumping data for table 'equipment'
> INSERT INTO 'equipment' VALUES
> ('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
> 12th 1999','April 14th 1999',2),
> ('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
> 2000','May 20th 2000',3),
> ('03278',3,7,'Domestic','Toothbrush','Ap
ril 12th 1999', 'April 15th
> 1999','April 16th 1999','April 20th 1999',1),
> ('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
> 1999'),('04577',1,8,'Commercial','Comput
er','May 1st 1999','May 5th
> 1999'),
> ('07853',1,9,'Commercial','Printer','May
1st 1999','May 5th
> 1999'),('08453',3,4,'Commercial','Comput
er','April 12th 1999','April
> 15th 1999'),
> ('084531',3,4,'Commercial','Computer','M
ay 5th 2000','May 6th
> 2000','May 8th 2000','May 10th
> 2000',1),('08734',3,6,'Industrial','Heat
er','April 12th 1999','April
> 15th 1999'),
> ('08892',3,3,'Commercial','Monitor','Apr
il 12th 1999','April 15th
> 1999'),('08897',1,10,'Commercial','Fax',
'May 1st 1999','May 5th
> 1999'),('08924',3,5,'Domestic','Kettle',
'April 12th 1999','April 13th
> 1999','April 17th 1999','April 20th 1999',2),
> ('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
> 2000'),('089921',3,3,'Commercial','Monit
or','May 5th 2000','May 10th
> 2000');
>|||Do not put 'single quotes' around the table and object names.
As already noted, each row INSERTed needs its own INSERT.
Once you get those taken care of the problems that remain will be
easier to see.
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 02:28:25 -0800, "Daz01" <dazzaf15@.hotmail.com> wrote:

>Hi Im trying to build a database in Microsoft SQL Server 2005.
>Ive written the code, but when I execute it, I keep getting an error
>message (below)
>Msg 102, Level 15, State 1, Line 1
>Incorrect syntax near 'customer'.
>
>Code below, any help would be great!
>--Table structure for table 'customer'
>
>CREATE TABLE 'customer'(
>'CustID' int(10) NOT NULL AUTO_INCREMENT,
>'CustName' char(50) NOT NULL,
>'Address' char(50) NOT NULL,
>PRIMARY KEY ('CustID')
> )
>--Dumping data for table 'customer'
>INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
>Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
>(3,'DailyMurkInc','Fleet Marina');

Incorrect syntax

Hi Im trying to build a database in Microsoft SQL Server 2005.
Ive written the code, but when I execute it, I keep getting an error
message (below)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'customer'.
Code below, any help would be great!
--Table structure for table 'customer'
CREATE TABLE 'customer'(
'CustID' int(10) NOT NULL AUTO_INCREMENT,
'CustName' char(50) NOT NULL,
'Address' char(50) NOT NULL,
PRIMARY KEY ('CustID')
)
--Dumping data for table 'customer'
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
(3,'DailyMurkInc','Fleet Marina');
--Table Structure for table 'deliverynote'
CREATE TABLE 'deliverynote'(
'CATref' int(20) NOT NULL auto_increment,
'CustID' int(50) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipName' char(50) NOT NULL,
PRIMARY KEY ('CATref','CustID')
)
--Dumping data for table 'deliverynote'
INSERT INTO 'deliverynote' VALUES
('01235',2,'Domestic',1,'Fan'),('03278',3,'Domestic',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
('04577',1,'Commercial',8,'Computer'),('07853',1,'Commercial',9,'Printer'),('08453',3,'Commercial',4,'Computer'),('08734',3,'Industrial',6,'Heater'),
('08897',1,'Commercial',10,'Fax'),('08924',3,'Domestic',5,'Kettle'),('08992',3,'Commercial',3,'Monitor');
--Table Structure for table 'engineer'
CREATE TABLE 'engineer'(
'EngineerName' char(50) NOT NULL,
PRIMARY KEY ('EngineerName')
}
--Dumping data for table 'engineer'
INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
--Table Structure for table 'equipmentcat'
CREATE TABLE 'equipmentcat' (
'EquipCat' char(50) NOT NULL,
PRIMARY KEY ('EquipCat')
)
--Dumping data for table 'equipmentcat'
INSERT INTO 'equipmentcat' VALUES
('Commercial'),('Domestic'),('Industrial');
--Table Structure for table 'repairer'
CREATE TABLE 'repairer' (
'RepID' int(10) NOT NULL,
'RepName' char(50) NOT NULL,
PRIMARY KEY ('RepID')
)
--Dumping data for table 'repairer'
INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
White');
--Table Structure for table 'locationid'
CREATE TABLE 'locationid' (
'LocationName' char(50) NOT NULL,
PRIMARY KEY ('LocationName')
)
--Dumping data for table 'locationid'
INSERT INTO 'locationid' VALUES
('Despatch'),('Gone_Home'),('Goods_In'),('Repairer'),('Testing');
--Table Structure for table 'location'
CREATE TABLE 'location' (
'EquipNumber' int(20) NOT NULL,
'CATref' int(20) NOT NULL,
'Testing' char(50) NOT NULL,
'Despatchdate' char(50) NOT NULL,
PRIMARY KEY ('CATref')
FOREIGN KEY ('CATref')
)
--Dumping data for table 'location'
INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
(2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
1999'),
(5,'08924','April 12th 1999, April 17th 1999','April 20th
1999'),(6,'08734','April 13th 1999','April 14th
1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
1999'),
(8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
1999','May 5th 1999');
--Table Structure for table 'testrecord'
CREATE TABLE 'testrecord' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'Date' char(50) NOT NULL,
'EngineerName' char(50) NOT NULL,
'Pass/Fail' char(20) NOT NULL,
PRIMARY KEY ('CATref','CustID')
FOREIGN KEY ('CATref','CustID','EngineerName')
)
--Dumping data for table 'testrecord'
INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush','April 13th
1999','Perfect','Pass')
,('08453',3,4,'Computer','April 12th
1999','Botchit','Pass'),('084531',3,4,'Computer','May 6th
2000','Perfect','Pass'),('084532',3,4,'Computer','May 9th
2000','Botchit','Pass'),('08734',3,6,'Heater','April 13th
1999','Botchit','Pass'),
('08924',3,5,'Kettle','April 12th
1999','Perfect','Fail'),('089248',3,5,'Kettle','May 6th
2000','Fudgeit','Pass'),('08992',3,3,'Monitor','April 12th
1999','Fudgeit','Pass'),('089921',3,3,'Monitor','May 6th
2000','Perfect','Pass');
--Table Structure for table 'equipment'
CREATE TABLE 'equipment' (
'CATref' int(20) NOT NULL,
'CustID' int(50) NOT NULL,
'EquipNumber' int(20) NOT NULL,
'EquipCat' char(50) NOT NULL,
'EquipName' char(50) NOT NULL,
'Goods_In_Date' char(50) NOT NULL,
'Repairer_Date' char (50) NOT NULL,
'Despatch_Date' char(50) NOT NULL,
'Home_Date' char(50) NOT NULL,
'RepID' int(10) NOT NULL,
PRIMARY KEY ('CATref', 'CustID')
FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
)
--Dumping data for table 'equipment'
INSERT INTO 'equipment' VALUES
('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
12th 1999','April 14th 1999',2),
('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
2000','May 20th 2000',3),
('03278',3,7,'Domestic','Toothbrush','April 12th 1999', 'April 15th
1999','April 16th 1999','April 20th 1999',1),
('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
1999'),
('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
15th 1999'),
('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
2000','May 8th 2000','May 10th
2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
15th 1999'),
('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
1999','April 17th 1999','April 20th 1999',2),
('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
2000');Daz
You have to specify INSERT INTO for each data to be insterted in your case
See if this helps
INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway Cuttings,
Euphoria')
INSERT INTO 'customer' VALUES (2,'Sinking.com','Virtual Lane, Peckham')
INSERT INTO 'customer' VALUES (3,'DailyMurkInc','Fleet Marina');
"Daz01" <dazzaf15@.hotmail.com> wrote in message
news:1166005705.720733.30310@.73g2000cwn.googlegroups.com...
> Hi Im trying to build a database in Microsoft SQL Server 2005.
> Ive written the code, but when I execute it, I keep getting an error
> message (below)
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near 'customer'.
>
> Code below, any help would be great!
> --Table structure for table 'customer'
>
> CREATE TABLE 'customer'(
> 'CustID' int(10) NOT NULL AUTO_INCREMENT,
> 'CustName' char(50) NOT NULL,
> 'Address' char(50) NOT NULL,
> PRIMARY KEY ('CustID')
> )
> --Dumping data for table 'customer'
> INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
> Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
> (3,'DailyMurkInc','Fleet Marina');
>
> --Table Structure for table 'deliverynote'
>
> CREATE TABLE 'deliverynote'(
> 'CATref' int(20) NOT NULL auto_increment,
> 'CustID' int(50) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> )
> --Dumping data for table 'deliverynote'
> INSERT INTO 'deliverynote' VALUES
> ('01235',2,'Domestic',1,'Fan'),('03278',3,'Domestic',7,'Toothbrush'),('03452',2,'Domestic',2,'Fan'),
> ('04577',1,'Commercial',8,'Computer'),('07853',1,'Commercial',9,'Printer'),('08453',3,'Commercial',4,'Computer'),('08734',3,'Industrial',6,'Heater'),
> ('08897',1,'Commercial',10,'Fax'),('08924',3,'Domestic',5,'Kettle'),('08992',3,'Commercial',3,'Monitor');
>
> --Table Structure for table 'engineer'
> CREATE TABLE 'engineer'(
> 'EngineerName' char(50) NOT NULL,
> PRIMARY KEY ('EngineerName')
> }
> --Dumping data for table 'engineer'
> INSERT INTO 'engineer' VALUES ('Botchit'),('Fudgeit'),('Perfect');
>
> --Table Structure for table 'equipmentcat'
> CREATE TABLE 'equipmentcat' (
> 'EquipCat' char(50) NOT NULL,
> PRIMARY KEY ('EquipCat')
> )
> --Dumping data for table 'equipmentcat'
>
> INSERT INTO 'equipmentcat' VALUES
> ('Commercial'),('Domestic'),('Industrial');
>
> --Table Structure for table 'repairer'
> CREATE TABLE 'repairer' (
> 'RepID' int(10) NOT NULL,
> 'RepName' char(50) NOT NULL,
> PRIMARY KEY ('RepID')
> )
> --Dumping data for table 'repairer'
> INSERT INTO 'repairer' VALUES (1, 'Mr Green'),(2,'Mrs Brown'),(3,'Mr
> White');
>
> --Table Structure for table 'locationid'
> CREATE TABLE 'locationid' (
> 'LocationName' char(50) NOT NULL,
> PRIMARY KEY ('LocationName')
> )
> --Dumping data for table 'locationid'
>
> INSERT INTO 'locationid' VALUES
> ('Despatch'),('Gone_Home'),('Goods_In'),('Repairer'),('Testing');
>
> --Table Structure for table 'location'
> CREATE TABLE 'location' (
> 'EquipNumber' int(20) NOT NULL,
> 'CATref' int(20) NOT NULL,
> 'Testing' char(50) NOT NULL,
> 'Despatchdate' char(50) NOT NULL,
> PRIMARY KEY ('CATref')
> FOREIGN KEY ('CATref')
> )
> --Dumping data for table 'location'
>
> INSERT INTO 'location' VALUES (1,'01235','April 5th 1999, Aprl 13th
> 1999','April 14th 1999'),(10,'08997','May 3rd 1999','May 5th 1999'),
> (2,'03452','April 5th 1999','April 6th 1999'),(3,'08992','April 12th
> 1999','April 13th 1999'),(4,'08453','April 12th 1999','April 14th
> 1999'),
> (5,'08924','April 12th 1999, April 17th 1999','April 20th
> 1999'),(6,'08734','April 13th 1999','April 14th
> 1999'),(7,'03278','April 13th 1999, April 17th 1999','April 19th
> 1999'),
> (8,'04577','May 3rd 1999','May 5th 1999'), (9,'07853','May 3rd
> 1999','May 5th 1999');
>
> --Table Structure for table 'testrecord'
> CREATE TABLE 'testrecord' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'Date' char(50) NOT NULL,
> 'EngineerName' char(50) NOT NULL,
> 'Pass/Fail' char(20) NOT NULL,
> PRIMARY KEY ('CATref','CustID')
> FOREIGN KEY ('CATref','CustID','EngineerName')
> )
> --Dumping data for table 'testrecord'
> INSERT INTO 'testrecord' VALUES ('01235',2,1,'Fan','April 6th
> 1999','Botchit','Pass'),('012357',2,1,'Fan','May 2nd
> 2000','Fudgeit','Fail'),('03278',3,7,'Toothbrush','April 13th
> 1999','Perfect','Pass')
> ,('08453',3,4,'Computer','April 12th
> 1999','Botchit','Pass'),('084531',3,4,'Computer','May 6th
> 2000','Perfect','Pass'),('084532',3,4,'Computer','May 9th
> 2000','Botchit','Pass'),('08734',3,6,'Heater','April 13th
> 1999','Botchit','Pass'),
> ('08924',3,5,'Kettle','April 12th
> 1999','Perfect','Fail'),('089248',3,5,'Kettle','May 6th
> 2000','Fudgeit','Pass'),('08992',3,3,'Monitor','April 12th
> 1999','Fudgeit','Pass'),('089921',3,3,'Monitor','May 6th
> 2000','Perfect','Pass');
>
> --Table Structure for table 'equipment'
> CREATE TABLE 'equipment' (
> 'CATref' int(20) NOT NULL,
> 'CustID' int(50) NOT NULL,
> 'EquipNumber' int(20) NOT NULL,
> 'EquipCat' char(50) NOT NULL,
> 'EquipName' char(50) NOT NULL,
> 'Goods_In_Date' char(50) NOT NULL,
> 'Repairer_Date' char (50) NOT NULL,
> 'Despatch_Date' char(50) NOT NULL,
> 'Home_Date' char(50) NOT NULL,
> 'RepID' int(10) NOT NULL,
> PRIMARY KEY ('CATref', 'CustID')
> FOREIGN KEU ('RepID','EqipCat','CATref','CustID')
> )
> --Dumping data for table 'equipment'
> INSERT INTO 'equipment' VALUES
> ('01235',2,1,'Domestic','Fan','April 5th 1999','April 7th, 1, 'April
> 12th 1999','April 14th 1999',2),
> ('012357',2,1,'Domestic','Fan','May 1st 2000','May 3rd 2000','May 18th
> 2000','May 20th 2000',3),
> ('03278',3,7,'Domestic','Toothbrush','April 12th 1999', 'April 15th
> 1999','April 16th 1999','April 20th 1999',1),
> ('03452',2,2,'Domestic','Fan','April 5th 1999','April 14th
> 1999'),('04577',1,8,'Commercial','Computer','May 1st 1999','May 5th
> 1999'),
> ('07853',1,9,'Commercial','Printer','May 1st 1999','May 5th
> 1999'),('08453',3,4,'Commercial','Computer','April 12th 1999','April
> 15th 1999'),
> ('084531',3,4,'Commercial','Computer','May 5th 2000','May 6th
> 2000','May 8th 2000','May 10th
> 2000',1),('08734',3,6,'Industrial','Heater','April 12th 1999','April
> 15th 1999'),
> ('08892',3,3,'Commercial','Monitor','April 12th 1999','April 15th
> 1999'),('08897',1,10,'Commercial','Fax','May 1st 1999','May 5th
> 1999'),('08924',3,5,'Domestic','Kettle','April 12th 1999','April 13th
> 1999','April 17th 1999','April 20th 1999',2),
> ('089248',3,5,'Domestic','Kettle','May 5th 2000','May 10th
> 2000'),('089921',3,3,'Commercial','Monitor','May 5th 2000','May 10th
> 2000');
>|||Do not put 'single quotes' around the table and object names.
As already noted, each row INSERTed needs its own INSERT.
Once you get those taken care of the problems that remain will be
easier to see.
Roy Harvey
Beacon Falls, CT
On 13 Dec 2006 02:28:25 -0800, "Daz01" <dazzaf15@.hotmail.com> wrote:
>Hi Im trying to build a database in Microsoft SQL Server 2005.
>Ive written the code, but when I execute it, I keep getting an error
>message (below)
>Msg 102, Level 15, State 1, Line 1
>Incorrect syntax near 'customer'.
>
>Code below, any help would be great!
>--Table structure for table 'customer'
>
>CREATE TABLE 'customer'(
>'CustID' int(10) NOT NULL AUTO_INCREMENT,
>'CustName' char(50) NOT NULL,
>'Address' char(50) NOT NULL,
>PRIMARY KEY ('CustID')
>)
>--Dumping data for table 'customer'
>INSERT INTO 'customer' VALUES (1,'Railtrack HQ','25-49 Railway
>Cuttings, Euphoria'),(2,'Sinking.com','Virtual Lane, Peckham'),
>(3,'DailyMurkInc','Fleet Marina');

Incorrect PageAudit

I've a database in SQLServer 2K Personal Edition. The database compatibility
level is set to 8. I was trying to create deployment package using Office 2K
Developer. I am using MSDE as my backend database manager.
Howerver when I try to attach my MDF in the MSDE, it gives me error as
follows:
"Invalid database page header. PageAudit Property is Incorrect"
Can anyone help me with this problem.
Thank you in advance
Dorji
hi Dorji,
Dorji wrote:
> I've a database in SQLServer 2K Personal Edition. The database
> compatibility level is set to 8. I was trying to create deployment
> package using Office 2K Developer. I am using MSDE as my backend
> database manager.
> Howerver when I try to attach my MDF in the MSDE, it gives me error as
> follows:
> "Invalid database page header. PageAudit Property is Incorrect"
Office 2000 provides MSDE 1.0 (based on SQL Server 7.0 code base)...
you can not restore/attach SQL Server 2000/MSDE 2000 databases on SQL Server
7.0/MSDE 1.0 intances..
you can perhaps download and use MSDE Rel A, updated at sp4 level from
http://www.microsoft.com/sql/msde/do...s/download.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello Adrea,
Thank you so much for your kind response. But I still couldn't manage to
attach my database. In fact I've tried several of my databases from the same
source, none seem to work. I can attach exisitng databases (model etc) but no
mine.
What could it be?
Dorji
"Andrea Montanari" wrote:

> hi Dorji,
> Dorji wrote:
> Office 2000 provides MSDE 1.0 (based on SQL Server 7.0 code base)...
> you can not restore/attach SQL Server 2000/MSDE 2000 databases on SQL Server
> 7.0/MSDE 1.0 intances..
> you can perhaps download and use MSDE Rel A, updated at sp4 level from
> http://www.microsoft.com/sql/msde/do...s/download.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Dorji,
Dorji wrote:
> Hello Adrea,
> Thank you so much for your kind response. But I still couldn't
> manage to attach my database. In fact I've tried several of my
> databases from the same source, none seem to work. I can attach
> exisitng databases (model etc) but no mine.
>
are you trying to attach SQL Server 2000/MSDE 2000 databases on MSDE 1.0?
again, if this is the case, this is not a supported option...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
It works now. I did not know that MSDE installs in Windows security mode by
default. So I had to change it to SQL and the connection works fine.
However I've another thing to ask if you don't mind. I've multiple instance
of SQL Server and MSDE 2000. How can I connect to a particular Instance for
instance from MS Access ADP file?.
I must thank you for the DbaManager utility that you have posted on your
website. It is a great tool for MSDE users. It really helped me with my
project.
Dorji
"Andrea Montanari" wrote:

> hi Dorji,
> Dorji wrote:
> are you trying to attach SQL Server 2000/MSDE 2000 databases on MSDE 1.0?
> again, if this is the case, this is not a supported option...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||hi Dorji,
Dorji wrote:
> Hi Andrea,
> It works now. I did not know that MSDE installs in Windows security
> mode by default. So I had to change it to SQL and the connection
> works fine.
this should have little or nothing to do with the problem you reported...
changing the authentication supported mode to Mixed mode does not grant you
the option to attach invalid databases...
ok..

> However I've another thing to ask if you don't mind. I've multiple
> instance of SQL Server and MSDE 2000. How can I connect to a
> particular Instance for instance from MS Access ADP file?.
in the datalink dialog you have to select the server name...
the relative combo should be already populated with all available servers...
default instances will be in the form of "ComputerName", where named
instances will be listed as "ComputerName\InstanceName"...

> I must thank you for the DbaManager utility that you have posted on
> your website. It is a great tool for MSDE users. It really helped
> me with my project.
thank you for your interest in this prj of mine... and please feel free to
(privately) contact me for any question or concern about it, as long as for
suggestions and feedback
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanks
Sorry, I forgot to tell you to run the test query below from the tempdb to
either produce an error message or not. The connection's current db
determines compatability level.
"paul" wrote:

> Hello,
>
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
> 8.0 compatability, but you get an error message when running the query which
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>
|||For what it is worth, I would not mess with the compatibilty level of
either tempdb or model. I know the idea is that you can set model to
match what you want from a new database, but as tempdb is effectively
recreated from model each time SQL Server starts it sounds like asking
for trouble.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 11:14:08 -0700, paul
<paul@.discussions.microsoft.com> wrote:

>Hello,
>
>I've noticed that if you have model and tempdb to different compatability
>levels and restart the sql instance, the value in sysdatabases (cmptlevel)
>does not get changed correctly.
>Here's how to reproduce:
>1. set model compatability to one level, say 9.0
>2. set tempdb compatability to diff. level, say 8.0
>3. verify in SSMS db -> properties -> options
>or select * from master..sysdatabases
>4. run an old join query from tempdb to show it is actually in 8.0
>compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
>5. now restart your db instance
>6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
>8.0 compatability, but you get an error message when running the query which
>means the database is in 9.0
>Can anyone explain? or is it possible this is a bug?
>thanks
>

Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanks
Please ignore this one, I resubmitted this same ticket the to SQL Server
category of threads.
"paul" wrote:

> Hello,
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
> 8.0 compatability, but you get an error message when running the query which
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>

Incorrect behavior in 'NOT IN' subquery with OPENXML

In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery.
However, this does not always return the desired result.
I've tried to simplify this problem and I've ended up with the sql script
below. In short, it creates a table with 200 records, select all records
except two specified in an xml document. When I run the complete script (in
the Query Analyzer) I get the desired results: 198 records. But when I first
run the part of the script where the table is created and filled (up to the
line of dashes), end then run the rest of the script where the xml is
prepared and the query is executed I get another result: 200 records.
I tried this script on two different Sql Server 2000 SP4 machines, both have
the same behavior. But this behavior seems incorrect to me.
So my questions:
- Does anybody get the correct result when you execute this script in parts?
- I this behavior indeed indeed incorrect or am I not thinking straight?
- Or could this be a bug in SQL Server...?
Thanks for your help!
Some remarks:
- When the table contains 160 records or less I get the correct result.
- When I use an 'IN' subquery (so without the NOT) I always get the correct
result (so the two specified records are returned both in the 'IN' as in the
'NOT IN' subquery...).
- When I run the subquery separately it returns the expected result: two
rows with values 1 and 2.
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Test]
CREATE TABLE [dbo].[Test] (
TestId int NOT NULL PRIMARY KEY
)
DECLARE @.Value int
SET @.Value = 1
WHILE @.Value <= 200
BEGIN
INSERT INTO Test (TestId) VALUES (@.Value)
SET @.Value = @.Value + 1
END

DECLARE @.Xml nvarchar(4000)
DECLARE @.XmlHandle int
SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
SELECT TestId
FROM Test
WHERE TestId NOT IN (
SELECT TestId
FROM OPENXML(@.XmlHandle, '/Root/Row')
WITH (
TestIdint'ID'
)
)
ORDER BY TestId
EXEC sp_xml_removedocument @.XmlHandle
DROP TABLE [dbo].[Test]
SET NOCOUNT OFF
I have tried both in SQL Server 2005 and got the same result (198 rows). I
don't have an SP4 installation on my machine, but asked our test team to
investigate. It may take a couple of days though until we can get back to
you...
Best regards
Michael
"Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
> In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a
> subquery.
> However, this does not always return the desired result.
> I've tried to simplify this problem and I've ended up with the sql script
> below. In short, it creates a table with 200 records, select all records
> except two specified in an xml document. When I run the complete script
> (in
> the Query Analyzer) I get the desired results: 198 records. But when I
> first
> run the part of the script where the table is created and filled (up to
> the
> line of dashes), end then run the rest of the script where the xml is
> prepared and the query is executed I get another result: 200 records.
> I tried this script on two different Sql Server 2000 SP4 machines, both
> have
> the same behavior. But this behavior seems incorrect to me.
> So my questions:
> - Does anybody get the correct result when you execute this script in
> parts?
> - I this behavior indeed indeed incorrect or am I not thinking straight?
> - Or could this be a bug in SQL Server...?
> Thanks for your help!
> Some remarks:
> - When the table contains 160 records or less I get the correct result.
> - When I use an 'IN' subquery (so without the NOT) I always get the
> correct
> result (so the two specified records are returned both in the 'IN' as in
> the
> 'NOT IN' subquery...).
> - When I run the subquery separately it returns the expected result: two
> rows with values 1 and 2.
>
> SET NOCOUNT ON
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> OBJECT_ID(N'[dbo].[Test]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
> DROP TABLE [dbo].[Test]
> CREATE TABLE [dbo].[Test] (
> TestId int NOT NULL PRIMARY KEY
> )
> DECLARE @.Value int
> SET @.Value = 1
> WHILE @.Value <= 200
> BEGIN
> INSERT INTO Test (TestId) VALUES (@.Value)
> SET @.Value = @.Value + 1
> END
>
> ----
>
> DECLARE @.Xml nvarchar(4000)
> DECLARE @.XmlHandle int
> SET @.Xml = N'<Root><Row><ID>1</ID></Row><Row><ID>2</ID></Row></Root>'
> EXEC sp_xml_preparedocument @.XmlHandle OUTPUT, @.Xml
> SELECT TestId
> FROM Test
> WHERE TestId NOT IN (
> SELECT TestId
> FROM OPENXML(@.XmlHandle, '/Root/Row')
> WITH (
> TestId int 'ID'
> )
> )
> ORDER BY TestId
> EXEC sp_xml_removedocument @.XmlHandle
> DROP TABLE [dbo].[Test]
> SET NOCOUNT OFF
>
>
|||We checked it against SQL 2000 SP3a and SP4. First the good news: SP4 seems
to work fine in either case.
When running it under SP3a: we observed:
1) run whole script - gets 198 rows (only one Remote Scan involved)
2) run create/fill 'Test' table first, and then OpenXML part, get 200 rows
(wrong behavior, two Remote Scans involved).
We seem to be able to correct this by setting "set ansi_nulls off".
Can you please check with select @.@.version what version number you are
running? And if you see version 8.00.2039 can you please tell us how you
installed it?
Thanks
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:u6dGBzyZFHA.3568@.TK2MSFTNGP10.phx.gbl...
>I have tried both in SQL Server 2005 and got the same result (198 rows). I
>don't have an SP4 installation on my machine, but asked our test team to
>investigate. It may take a couple of days though until we can get back to
>you...
> Best regards
> Michael
> "Wouter de Boer" <a789nonymous[AT]hotmail.com> wrote in message
> news:277FA17E-7E50-4151-A034-99DF9221E99D@.microsoft.com...
>
|||Before posting I checked @.@.VERSION and since it mentioned SP4 I figured I had
SQL Server 2000 SP4. Only now did I read it a bit better to see we're running
2000 SP3:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
(Sorry, I got fooled by the "Service Pack 4" at the end... :-$ )
The "set ansi_nulls off" indeed seems to correct this issue.
I did find another way of circumventing this behavior: inserting the values
from the xml into a temporary table and using that temporary table in the
subquery.
In any case: I understand that this behavior is corrected in the next SP, so
that's good. And of course thank you for your time and help with this matter.
Regards,
Wouter

Inconsistent Results with TRY - CATCH and Linked Server

Hi
I'm not sure if this is the right Newsgroup for this question, apologies if
it's off topic.
I've got a stored procedure that calls a second stored procedure on a linked
server. My local stored procedure encloses the call to the remote stored
procedure in a try - catch block. I have deliberately introduced an error
into the remote stored procedure to check whether the local try - catch block
handles it. Unfortunately the try - catch block appears to exhibit
inconsistent behaviour under identical conditions.
At the moment it appears a little like black magic - could anyone explain
what might be going on?
SET UP
Here's a snippet of code from the local stored procedure. The code snippet
exhibits the same inconsistent behaviour as the full stored procedure:
LOCAL CODE SNIPPET STARTS
=====================
declare @.DeviceID NVARCHAR(10),
@.RunID NVARCHAR(10),
@.CourierID NVARCHAR(10)
set @.DeviceID = 'TestDev1'
set @.RunID = 'TestRun1'
set @.CourierID = '99999'
DECLARE
@.RetVal INT,
@.StoredProcErrNum INT,
@.SqlErrMsg VARCHAR(200)
SET @.RetVal = 0
SET @.StoredProcErrNum = 0
SET @.SqlErrMsg = N''
DECLARE @.nCID INT
SET @.nCID = CAST(@.CourierID AS INT)
select @.RunID, @.nCID
DECLARE @.fail bit
DECLARE @.Profile TABLE ( profile_desc VARCHAR(40), Street BIT, [Print] BIT,
t3_run_type CHAR(1) )
SET XACT_ABORT ON
SET @.RetVal = NULL
BEGIN TRY
INSERT INTO @.Profile
EXEC @.RetVal = cme.cme.dbo.spd_t3_login_driver_details @.RunID, @.nCID, @.fail
OUTPUT
END TRY
BEGIN CATCH
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
SET @.RetVal = 1
SET @.StoredProcErrNum = ERROR_NUMBER()
SET @.SqlErrMsg = ERROR_MESSAGE()
END CATCH
IF @.RetVal IS NULL SET @.RetVal = 1
select @.RetVal AS RetVal, @.fail AS Fail, @.StoredProcErrNum AS
StoredProcErrNum, @.SqlErrMsg AS SqlErrMsg
select * from @.Profile
================
CODE SNIPPET ENDS
Here is the remote stored procedure that is called by the above code snippet:
REMOTE STORED PROCEDURE CODE STARTS
===============================
ALTER PROCEDURE [dbo].[spd_t3_login_driver_details] ( @.run_no VARCHAR(40),
@.driver_id INT, @.failure BIT OUTPUT )
AS
--select 100/0
IF NOT EXISTS ( SELECT * FROM run r INNER JOIN driver d ON r.default_driver
= d.driver_id
INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
WHERE run_no = @.run_no
AND d.driver_id = @.driver_id )
BEGIN
SELECT @.failure = 1
END
ELSE
BEGIN
SELECT @.failure = 0
SELECT profile_desc, ISNULL(street, 1) AS 'Street', ISNULL(van_print, 1) AS
'Print', ISNULL(t3_run_type, 'M') AS 't3_run_type'
FROM run r INNER JOIN driver d ON r.default_driver = d.driver_id
INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
WHERE run_no = @.run_no
AND d.driver_id = @.driver_id
END
=================
CODE ENDS
The commented line in the remote stored procedure allows me to introduce a
divide by zero error.
The remote stored procedure is pretty simple and I don't expect much trouble
with it. However there are many remote stored procedures running on the
linked server. I just used this one to test whether we could deal with
errors that may arise in any of the remote stored procedures.
Unfortunately we have no control over the remote stored procedures. They
were developed and are maintained by another company, and are running on
their server which we have only limited rights to. We have a test
environment with a duplicate of the production remote server. This is how
I've played around with the remote stored procedure above. However the
production versions of the remote stored procedures are set in stone.
BEHAVIOUR:
When I uncomment the select 100/0 and run the alter procedure script for
spd_t3_login_driver_details on the remote server, then run the code snippet
on the local server, this is the behaviour I expect:
The code snippet runs to the end and returns the following recordsets:
(no column name) (no column name)
-- --
TestRun1 99999
RetVal Fail StoredProcErrNum SqlErrMsg
-- -- -- --
1 NULL 8134 Divide by zero error encountered.
profile_desc Street Print t3_run_type
-- -- -- --
<no rows returned>
However, this desired behaviour happens only about one time in ten. Most of
the time when I run the code snippet it aborts when it tries to call the
remote stored procedure.
The following recordset is returned:
(no column name) (no column name)
-- --
TestRun1 99999
I also get the following messages:
(1 row(s) affected)
(0 row(s) affected)
Msg 1206, Level 18, State 118, Line 28
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the
distributed transaction.
ATTEMPTED INVESTIGATION:
Initially I thought adding the IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION to
the CATCH block solved the problem by rolling back the implicit transaction.
However I soon discovered the problem came back again. I also thought the SET
XACT_ABORT ON might be the problem. I set it to OFF instead. Then the code
snippet seemed to
always run to completion but it was not picking up the error message and
error number from ERROR_MESSAGE() and ERROR_NUMBER().
Can anyone explain the inconsistent behaviour of this code? Why does the try
- catch block work sometimes but not others?
Cheers
Si
I don't know why it would be inconsistent but This may be why the
transaction aborts without going to the catch:
Attentions will terminate a batch even if the batch is within the scope of a
TRY.CATCH construct. This includes an attention sent by the Microsoft
Distributed Transaction Coordinator (MS DTC) when a distributed transaction
fails. MS DTC manages distributed transactions.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"SimonDev" <SimonDev@.discussions.microsoft.com> wrote in message
news:7F48371C-890D-40F9-8E5C-D40C92F4C043@.microsoft.com...
> Hi
> I'm not sure if this is the right Newsgroup for this question, apologies
> if
> it's off topic.
> I've got a stored procedure that calls a second stored procedure on a
> linked
> server. My local stored procedure encloses the call to the remote stored
> procedure in a try - catch block. I have deliberately introduced an error
> into the remote stored procedure to check whether the local try - catch
> block
> handles it. Unfortunately the try - catch block appears to exhibit
> inconsistent behaviour under identical conditions.
> At the moment it appears a little like black magic - could anyone explain
> what might be going on?
> SET UP
> --
> Here's a snippet of code from the local stored procedure. The code snippet
> exhibits the same inconsistent behaviour as the full stored procedure:
> LOCAL CODE SNIPPET STARTS
> =====================
> declare @.DeviceID NVARCHAR(10),
> @.RunID NVARCHAR(10),
> @.CourierID NVARCHAR(10)
> set @.DeviceID = 'TestDev1'
> set @.RunID = 'TestRun1'
> set @.CourierID = '99999'
> DECLARE
> @.RetVal INT,
> @.StoredProcErrNum INT,
> @.SqlErrMsg VARCHAR(200)
> SET @.RetVal = 0
> SET @.StoredProcErrNum = 0
> SET @.SqlErrMsg = N''
> DECLARE @.nCID INT
> SET @.nCID = CAST(@.CourierID AS INT)
> select @.RunID, @.nCID
> DECLARE @.fail bit
> DECLARE @.Profile TABLE ( profile_desc VARCHAR(40), Street BIT, [Print]
> BIT,
> t3_run_type CHAR(1) )
> SET XACT_ABORT ON
> SET @.RetVal = NULL
> BEGIN TRY
> INSERT INTO @.Profile
> EXEC @.RetVal = cme.cme.dbo.spd_t3_login_driver_details @.RunID, @.nCID,
> @.fail
> OUTPUT
> END TRY
> BEGIN CATCH
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> SET @.RetVal = 1
> SET @.StoredProcErrNum = ERROR_NUMBER()
> SET @.SqlErrMsg = ERROR_MESSAGE()
> END CATCH
> IF @.RetVal IS NULL SET @.RetVal = 1
> select @.RetVal AS RetVal, @.fail AS Fail, @.StoredProcErrNum AS
> StoredProcErrNum, @.SqlErrMsg AS SqlErrMsg
> select * from @.Profile
> ================
> CODE SNIPPET ENDS
> Here is the remote stored procedure that is called by the above code
> snippet:
> REMOTE STORED PROCEDURE CODE STARTS
> ===============================
> ALTER PROCEDURE [dbo].[spd_t3_login_driver_details] ( @.run_no VARCHAR(40),
> @.driver_id INT, @.failure BIT OUTPUT )
> AS
> --select 100/0
> IF NOT EXISTS ( SELECT * FROM run r INNER JOIN driver d ON
> r.default_driver
> = d.driver_id
> INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
> WHERE run_no = @.run_no
> AND d.driver_id = @.driver_id )
> BEGIN
> SELECT @.failure = 1
> END
> ELSE
> BEGIN
> SELECT @.failure = 0
>
> SELECT profile_desc, ISNULL(street, 1) AS 'Street', ISNULL(van_print, 1)
> AS
> 'Print', ISNULL(t3_run_type, 'M') AS 't3_run_type'
> FROM run r INNER JOIN driver d ON r.default_driver = d.driver_id
> INNER JOIN run_profiles rp ON r.profile_id = rp.run_profile_id
> WHERE run_no = @.run_no
> AND d.driver_id = @.driver_id
> END
> =================
> CODE ENDS
> The commented line in the remote stored procedure allows me to introduce a
> divide by zero error.
> The remote stored procedure is pretty simple and I don't expect much
> trouble
> with it. However there are many remote stored procedures running on the
> linked server. I just used this one to test whether we could deal with
> errors that may arise in any of the remote stored procedures.
> Unfortunately we have no control over the remote stored procedures. They
> were developed and are maintained by another company, and are running on
> their server which we have only limited rights to. We have a test
> environment with a duplicate of the production remote server. This is
> how
> I've played around with the remote stored procedure above. However the
> production versions of the remote stored procedures are set in stone.
> BEHAVIOUR:
> --
> When I uncomment the select 100/0 and run the alter procedure script for
> spd_t3_login_driver_details on the remote server, then run the code
> snippet
> on the local server, this is the behaviour I expect:
> The code snippet runs to the end and returns the following recordsets:
> (no column name) (no column name)
> -- --
> TestRun1 99999
> RetVal Fail StoredProcErrNum SqlErrMsg
> -- -- -- --
> 1 NULL 8134 Divide by zero error encountered.
> profile_desc Street Print t3_run_type
> -- -- -- --
> <no rows returned>
> However, this desired behaviour happens only about one time in ten. Most
> of
> the time when I run the code snippet it aborts when it tries to call the
> remote stored procedure.
> The following recordset is returned:
> (no column name) (no column name)
> -- --
> TestRun1 99999
> I also get the following messages:
> (1 row(s) affected)
> (0 row(s) affected)
> Msg 1206, Level 18, State 118, Line 28
> The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled
> the
> distributed transaction.
> ATTEMPTED INVESTIGATION:
> --
> Initially I thought adding the IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION to
> the CATCH block solved the problem by rolling back the implicit
> transaction.
> However I soon discovered the problem came back again. I also thought the
> SET
> XACT_ABORT ON might be the problem. I set it to OFF instead. Then the code
> snippet seemed to
> always run to completion but it was not picking up the error message and
> error number from ERROR_MESSAGE() and ERROR_NUMBER().
> Can anyone explain the inconsistent behaviour of this code? Why does the
> try
> - catch block work sometimes but not others?
> Cheers
> Si
|||Thanks for your reply, Roger.
What exactly is an attention? Sorry if this is a newbie sort of question.
I had assumed that since the severity level of the error was less than 20 it
would have been caught by the catch block. What is the difference between an
error and an attention? Books online mentions examples of attentions being
client-interrupt requests and broken client connections but that doesn't
really help me to understand them.
Is there any way of trapping attentions or will they always break T-SQL
code, no matter what the severity level is?
Cheers
Simon
"Roger Wolter[MSFT]" wrote:

> I don't know why it would be inconsistent but This may be why the
> transaction aborts without going to the catch:
> Attentions will terminate a batch even if the batch is within the scope of a
> TRY.CATCH construct. This includes an attention sent by the Microsoft
> Distributed Transaction Coordinator (MS DTC) when a distributed transaction
> fails. MS DTC manages distributed transactions.
|||Our DBA pointed out I have missed one very important piece of information:
Although the local server is SQL Server 2005, the linked server (cme) that it
calls to is SQL Server 2000 SP4.
Si
|||Further progress: I've tried to find information about attention events.
Pretty hard to track down but as I understand them, attention events are just
requests from a client to cancel the currently running query.
I discovered Profiler can track Attention events so ran Profiler traces on
both local and linked servers. There were no attention events raised on
either server when the MS DTC cancelled the distributed transaction. On the
linked server an exception was raised: Error: 8134, Severity: 16, State: 1
(as far as I can recall 8134 is a divide by zero error). On the local server
an exception was raised: Error: 1206, Severity: 18, State: 199. This was
followed by a user error message "The Microsoft Distributed Transaction
Coordinator (MS DTC) has cancelled the distributed transaction.".
Now that it's finally occurred to me to use Profiler I'll continue using it
while playing with SET XACT_ABORT to see if I can make any further progress.
Cheers
Simon