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
>