SQLTeam.com | Weblogs | Forums

Syntax Error


#1

Hi experts,

It seems to be erroring on the line:
when ''I'' then ''differential''

Declare @Servername nvarchar(50);
DECLARE @SQL nvarchar(200);
Set @Servername = 'MyServerName\PRODUCT_PRICE'

SET @SQL = N';with backup_cte as (select
database_name,
backup_type = case type,
when ''D'' then ''database''
when ''L'' then ''log''
when ''I'' then ''differential''
else ''other''
end,
backup_finish_date,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)
from ' + @ServerName + '.msdb.dbo.backupset)'

EXEC sys.sp_executesql @sql;

Gives errors:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 'different'.

Any ideas? Thanks


#2

remove the ending comma from above line


#3

Thanks bitsmed.
I tried that:

Declare @LinkedServer nvarchar(50);
Declare @Servername nvarchar(50);
DECLARE @SQL nvarchar(300);
Set @Servername = 'MyServerName\PRODUCT_PRICE';

SET @SQL = N';with backup_cte as (select
database_name,
backup_type = case backup_type
when 'D' then 'database'
when 'L' then 'log'
when 'I' then 'differential'
else 'other'
end,
backup_finish_date,
rownum =
row_number() over
(
partition by database_name, type
order by backup_finish_date desc
)
from ' + @ServerName + '.msdb.dbo.backupset)'
EXEC sys.sp_executesql @sql;

... but I stlll get this (confusing) error:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'D'.

Any thoughts? Thankjs


#4

Replace with:

SET @SQL = N";with backup_cte as (select
.
.
.
from ’ + @ServerName + '.msdb.dbo.backupset)"