Incorrect syntax near the keyword 'Insert'.

I'm trying to build a command that gets a linkservername but gets this error
msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
Thnaks.
set @.cmd = Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
run_date))),
case j.enabled
when 0 then 'N'
when 1 then 'Y'
End,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
End,
h.run_duration,
substring(h.message,1,300)
From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
[+@.linkservername+].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From [+@.linkservername+].[msdb].[dbo].
[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1
Message posted via http://www.webservertalk.comNaana,
I believe you'll need to use the EXEC statement and build your SQL statement
dynamically to do this. Google Dynamic SQL.
HTH
Jerry
"Naana via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:549AE24111A74@.webservertalk.com...
> I'm trying to build a command that gets a linkservername but gets this
> error
> msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
> Thnaks.
>
> set @.cmd = Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
> run_date))),
> case j.enabled
> when 0 then 'N'
> when 1 then 'Y'
> End,
> case h.run_status
> when 0 then 'Failed'
> when 1 then 'Successful'
> when 3 then 'Cancelled'
> when 4 then 'In Progress'
> End,
> h.run_duration,
> substring(h.message,1,300)
> From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
> [+@.linkservername+].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From [+@.linkservername+].[msdb].[dbo].
> [sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1
>
> --
> Message posted via http://www.webservertalk.com|||Hi
You are missing the many single quotes within the construction of @.cmd
Try:
set @.cmd = 'Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
case j.enabled
when 0 then ''N''
when 1 then ''Y''
End,
case h.run_status
when 0 then ''Failed''
when 1 then ''Successful''
when 3 then ''Cancelled''
when 4 then ''In Progress''
End,
h.run_duration,
substring(h.message,1,300)
From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From
['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1'
You may want to read:
http://www.sommarskog.se/dynamic_sql.html
John
"Naana via webservertalk.com" wrote:

> I'm trying to build a command that gets a linkservername but gets this err
or
> msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
> Thnaks.
>
> set @.cmd = Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
> run_date))),
> case j.enabled
> when 0 then 'N'
> when 1 then 'Y'
> End,
> case h.run_status
> when 0 then 'Failed'
> when 1 then 'Successful'
> when 3 then 'Cancelled'
> when 4 then 'In Progress'
> End,
> h.run_duration,
> substring(h.message,1,300)
> From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
> [+@.linkservername+].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From [+@.linkservername+].[msdb].[dbo].
> [sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1
>
> --
> Message posted via http://www.webservertalk.com
>|||John Bell wrote:
>Hi
>You are missing the many single quotes within the construction of @.cmd
>Try:
>set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
>['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>You may want to read:
>http://www.sommarskog.se/dynamic_sql.html
>John
>
>[quoted text clipped - 30 lines]
Hi John,
Thanks, the sub_query is working now but I get the following message when
runn the whole query:
1) Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ACCURATE'.
and
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ''.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.
The whole query:
Declare @.linkservername varchar(256)
Declare @.cmd varchar(8000)
Declare @.batchid bigint
-- Set the parms
exec pBatchInsert @.BatchID output
--select @.batchID
-- if exists drop then create table to hold error log results
If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
[Error_Log_Results_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Drop Table [dbo].[Error_Log_Results_Temp]
End
Create Table [dbo].[Error_Log_Results_Temp]
([vchMessage] [varchar] (255),
[ID] [int])
-- if exists drop the create table to hold a list of servers this proc is
accesing.
If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
[Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Drop Table [dbo].[Job_Status]
End
Create Table [dbo].[Job_Status]
(Status varchar(16),
Server varchar(64),
[TimeStamp] datetime)
-- cursur thru the sysservres table to get list of servers linked to this on
e.
Declare linkserver_cursor CURSOR
For Select srvname
From master..sysservers(nolock)
Where srvproduct = 'SQL Server'
and isremote = 1
and srvname not in ('DBPHQOVOM01\OVOPS')
-- and srvname = 'posrep'
Order by srvname
Open linkserver_cursor
Fetch Next From linkserver_cursor into @.linkservername
While ( @.@.fetch_status <> -1 )
Begin
If ( @.@.fetch_status <> -2 )
Begin
-- insert ststus
Insert into Job_Status
Select 'Started', @.linkservername, getdate()
Insert into Servers_Scaned
Select @.BatchID, @.linkservername
-- build command
set @.cmd = 'Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+',
'+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
case j.enabled
when 0 then ''N''
when 1 then ''Y''
End,
case h.run_status
when 0 then ''Failed''
when 1 then ''Successful''
when 3 then ''Cancelled''
when 4 then ''In Progress''
End,
h.run_duration,
substring(h.message,1,300)
From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From
['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1'
-- exec command
Exec (@.cmd)
--select @.cmd
-- Begin loading info from error logs.
Set @.cmd = ' '
Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
[master].[dbo].[xp_readerrorlog]'
--select @.cmd
Exec (@.cmd)
-- insert error log results into temp table and tag it with a server name.
Insert into Error_Log_Results
Select @.batchid, @.linkservername,'0', *
From Error_Log_Results_Temp
Where (vchMessage like '%error:%'
or vchMessage like '%failed%'
or vchMessage like '%invalid%'
or vchMessage like '%full%'
or vchMessage like '%hung%'
or vchMessage like '%kill%'
or vchMessage like '%violation%'
or vchMessage like '%SqlDumpExceptionHandler%'
or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
or vchMessage like '%Stack%'
or vchMessage like '%terminating%'
or vchMessage like '%shutdown%'
or vchMessage like '%fatal%')
-- truncate table for load from the second error log.
Truncate Table Error_Log_Results_Temp
-- clear then build the command
Set @.cmd = ' '
Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
[master].[dbo].[xp_readerrorlog] 1'
--select @.cmd
Exec (@.cmd)
-- Insert results into temp error log table
Insert into Error_Log_Results
Select @.batchid, @.linkservername,'1', *
From Error_Log_Results_Temp
Where (vchMessage like '%error:%'
or vchMessage like '%failed%'
or vchMessage like '%invalid%'
or vchMessage like '%full%'
or vchMessage like '%hung%'
or vchMessage like '%kill%'
or vchMessage like '%violation%'
or vchMessage like '%SqlDumpExceptionHandler%'
or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
or vchMessage like '%Stack%'
or vchMessage like '%terminating%'
or vchMessage like '%shutdown%'
or vchMessage like '%fatal%')
Truncate Table Error_Log_Results_Temp
Insert into Job_Status
Select 'Finished', @.linkservername, getdate()
End
FETCH NEXT From linkserver_cursor into @.linkservername
End
CLOSE linkserver_cursor
DEALLOCATE linkserver_cursor
-- Cleanup
Drop Table [dbo].[Error_Log_Results_Temp]
Drop Table [dbo].[Job_Status]
-- print ' '
-- print '***************************************
***'
-- print '** End of Report: '+convert(varchar(64),getdate())+' **'
-- print '***************************************
***'
-- Return (0)
GO
Message posted via http://www.webservertalk.com|||Hi
The query runs but now get's this message for my server names as invalid
column names.
) Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ACCURATE'.
Thanks.
John Bell wrote:
>Hi
>You are missing the many single quotes within the construction of @.cmd
>Try:
>set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
>['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>You may want to read:
>http://www.sommarskog.se/dynamic_sql.html
>John
>
>[quoted text clipped - 30 lines]
Message posted via http://www.webservertalk.com|||Hi
Accurate must be in your data, therefore you may want to try using PRINT
@.cmd before your EXEC statement to show what the SQL is that you are
executing. You could then 'debug' the output from the PRINT statement so tha
t
it provides what you require and then figure out how to make up the command
in dynamic SQL.
It could be that you are wanting to output a literal value rather than the
value of a column from your variables e.g.
If @.batchid had a value 101 and @.linkservername was DBPHQOVOM01\OVOPSVR
Select distinct '+convert(varchar(10),@.batchid)+','+@.lin
kservername+',...
Would be looking for a column called 101 and DBPHQOVOM01\OVOPSVR (which is
it if a valid column should be escaped with [] such as [DBPHQOVOM01\OVOPSVR]
)when you executed @.cmd.
Therefore:
Select distinct '+convert(varchar(10),@.batchid)+',['+@.li
nkservername+'],...
would give
Select distinct 101, [DBPHQOVOM01\OVOPSVR], ...
If you actually wanted a constants to be output then you need to enquote the
n
Select distinct
'''+convert(varchar(10),@.batchid)+''',''
'+@.linkservername+''',...
this would give
Select distinct '101', 'DBPHQOVOM01\OVOPSVR', ...
HTH
John
"Naana via webservertalk.com" wrote:

> John Bell wrote:
>
> Hi John,
> Thanks, the sub_query is working now but I get the following message when
> runn the whole query:
> 1) Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'ACCURATE'.
> and
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ''.
> Server: Msg 156, Level 15, State 1, Line 27
> Incorrect syntax near the keyword 'and'.
> The whole query:
> Declare @.linkservername varchar(256)
> Declare @.cmd varchar(8000)
> Declare @.batchid bigint
> -- Set the parms
> exec pBatchInsert @.BatchID output
> --select @.batchID
> -- if exists drop then create table to hold error log results
> If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
> [Error_Log_Results_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> Begin
> Drop Table [dbo].[Error_Log_Results_Temp]
> End
> Create Table [dbo].[Error_Log_Results_Temp]
> ([vchMessage] [varchar] (255),
> [ID] [int])
> -- if exists drop the create table to hold a list of servers this proc is
> accesing.
> If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
> [Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> Begin
> Drop Table [dbo].[Job_Status]
> End
> Create Table [dbo].[Job_Status]
> (Status varchar(16),
> Server varchar(64),
> [TimeStamp] datetime)
>
> -- cursur thru the sysservres table to get list of servers linked to this
one.
> Declare linkserver_cursor CURSOR
> For Select srvname
> From master..sysservers(nolock)
> Where srvproduct = 'SQL Server'
> and isremote = 1
> and srvname not in ('DBPHQOVOM01\OVOPS')
> -- and srvname = 'posrep'
> Order by srvname
> Open linkserver_cursor
> Fetch Next From linkserver_cursor into @.linkservername
> While ( @.@.fetch_status <> -1 )
> Begin
> If ( @.@.fetch_status <> -2 )
> Begin
> -- insert ststus
> Insert into Job_Status
> Select 'Started', @.linkservername, getdate()
> Insert into Servers_Scaned
> Select @.BatchID, @.linkservername
> -- build command
> set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+',
> '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
> ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>
> -- exec command
> Exec (@.cmd)
> --select @.cmd
> -- Begin loading info from error logs.
> Set @.cmd = ' '
> Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
> [master].[dbo].[xp_readerrorlog]'
> --select @.cmd
> Exec (@.cmd)
> -- insert error log results into temp table and tag it with a server name.
> Insert into Error_Log_Results
> Select @.batchid, @.linkservername,'0', *
> From Error_Log_Results_Temp
> Where (vchMessage like '%error:%'
> or vchMessage like '%failed%'
> or vchMessage like '%invalid%'
> or vchMessage like '%full%'
> or vchMessage like '%hung%'
> or vchMessage like '%kill%'
> or vchMessage like '%violation%'
> or vchMessage like '%SqlDumpExceptionHandler%'
> or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
> or vchMessage like '%Stack%'
> or vchMessage like '%terminating%'
> or vchMessage like '%shutdown%'
> or vchMessage like '%fatal%')
> -- truncate table for load from the second error log.
> Truncate Table Error_Log_Results_Temp
> -- clear then build the command
> Set @.cmd = ' '
> Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
> [master].[dbo].[xp_readerrorlog] 1'
> --select @.cmd
> Exec (@.cmd)
> -- Insert results into temp error log table
> Insert into Error_Log_Results
> Select @.batchid, @.linkservername,'1', *
> From Error_Log_Results_Temp
> Where (vchMessage like '%error:%'
> or vchMessage like '%failed%'
> or vchMessage like '%invalid%'
> or vchMessage like '%full%'
> or vchMessage like '%hung%'
> or vchMessage like '%kill%'
> or vchMessage like '%violation%'
> or vchMessage like '%SqlDumpExceptionHandler%'
> or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
> or vchMessage like '%Stack%'
> or vchMessage like '%terminating%'
> or vchMessage like '%shutdown%'
> or vchMessage like '%fatal%')
> Truncate Table Error_Log_Results_Temp
> Insert into Job_Status
> Select 'Finished', @.linkservername, getdate()
> End
> FETCH NEXT From linkserver_cursor into @.linkservername
> End
> CLOSE linkserver_cursor
> DEALLOCATE linkserver_cursor
>
> -- Cleanup
> Drop Table [dbo].[Error_Log_Results_Temp]
> Drop Table [dbo].[Job_Status]
> -- print ' '
> -- print '***************************************
***'
> -- print '** End of Report: '+convert(varchar(64),getdate())+' **'
> -- print '***************************************
***'
> -- Return (0)
>
> GO
>
> --
> Message posted via http://www.webservertalk.com
>

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 processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1

Incorrect processing order for views

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:

1) Initial state is that merge replication of table and views is working fine

2) I then alter one view which references a new view in the same publication

3) Synchronization processes the view scripts in the wrong order regardless of the processing order

4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order

Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

Note

1) Dependency information is up to date and accurate on the publisher

2) Creating new view manually on the subscriber allows sync to complete successfully - but this isn't an option for ongoing system updates with large numbers of subscribers

3) I am not clear whether the SQL for creating the new view is ever reaching the subscriber. I will test for this with profiler

|||

While this is not a fix for your problem it might help you out for a while.

You can schedule a script to run before the snapshot is extracted onto the subscriber. In the script you could create your troublesome view. That way it will automatically create the view everytime someone is added. Not the solution but maybe a workaround.

Martin

|||

Hi Martin

Thanks for your suggestion. Similarly to what you suggest - I am looking at ways of introducing a generic mechanism in our system to provide a means of running scripts that replication should have run. As some subscribers are SQL Express this can't be an agent job

I did look at using the pre-snapshot script for this purpose - but this script is only run when initializing

The case I have cited is just one instance of the type of ordering problems that may be encountered - so I can't just code for this specific case

Note that I created the new view manually on a test system and profiled the subsequent merge (which was successful). As anticipated all the 'alters' were run first and then the 'creates' came along afterwards. The processing order applied was correct within the 'alters' and correct within the 'creates'. However, the processing order needs to be applied across the whole set of 'alters' and 'creates' together

aero1

|||I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes.|||

Hi Greg

I have tested as per your suggestion - i.e. I created a separate merge publication for all the non schema articles (stored procs, view and functions) - and recreated the other publications without those articles.

All appeared to be going well until I attempted to alter a replicated stored proc (on the publisher). I attempted altering a number of stored procs, but each time the alter hung - and I could see that the alter SPID had clocked up up to 20 minutes of CPU each time. I checked this behaviour on my original configuration and the alters took about 4 seconds.

I like the simplicity of a separate publication, but need to get over this issue to make it workable.

I don't know if it is relevant, but the database had 3 merge publications previously and now has four with the new publication for the non schema articles.

The 3 publications were setup for the following reasons

- To get over the 256 article limit problem

- To use separate publications for subscribers needing filtered and unfiltered data

Any thoughts would be much appreciated

Thanks

aero1

|||

I have found that the problem altering procs is a known issue covered in this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=417921&SiteID=1

I added a dummy table to the publication and am now able to alter published procs successfully

aero1