x

Property name not set - Error parsing Executesql

I have received the following error when attempting to set up a condition in policy based management using the Executesql function any help would be greatly appreciated

Error parsing 'ExecuteSql('Numeric','SELECT count(*) FROM sys.databases D INNER JOIN sys.database_mirroring DM On D.database_id = DM.database_id
LEFT JOIN (SELECT BS.[database_name], MAX(BS.backup_finish_date) AS last_log_backup_date FROM msdb.dbo.backupset BS
WHERE BS.type = 'L' GROUP BY BS.database_name) BS1 ON D.name = BS1.database_name WHERE D.database_id > 4 and D.name not like 'reportserver%' and D.recovery_model_desc <> 'SIMPLE' and D.state_desc = 'ONLINE' and DM.mirroring_state is null and (BS1.last_log_backup_date IS NULL or BS1.last_log_backup_date <= getdate()-1))''. Make sure string constants are enclosed in single quotes and facet properties are prefixed with '@' sign.

more ▼

asked Apr 20 at 10:57 AM in Default

avatar image

MNiazi
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It looks like you use some sort of custom implementation of the sp_executesql. If your ExecuteSql (whatever it is) is expecting a string which needs to be a valid SQL statement then every occurrence of the single quote inside of the script definition needs to be replaced with 2 single quotes. Better still, consider creating variable to store the SQL script and then pass into your ExecuteSql (whatever it is), i.e.

 declare @sql varchar(max) = '
 SELECT count(*)
     FROM sys.databases D INNER JOIN sys.database_mirroring DM
         On D.database_id = DM.database_id    
     LEFT JOIN
     (
         SELECT 
             BS.[database_name], MAX(BS.backup_finish_date) AS last_log_backup_date 
             FROM msdb.dbo.backupset BS  
             WHERE BS.type = ''L'' 
             GROUP BY BS.database_name
     ) BS1 
         ON D.name = BS1.database_name 
     WHERE D.database_id > 4 
     and D.name not like ''reportserver%'' 
     and D.recovery_model_desc <> ''SIMPLE''
     and D.state_desc = ''ONLINE''
     and    DM.mirroring_state is null
     and (BS1.last_log_backup_date IS NULL 
     or BS1.last_log_backup_date <= getdate()-1))';
 
 exec  Executesql('Numeric', @sql);
 go

Hope this helps,

Oleg

more ▼

answered Apr 24 at 03:03 PM

avatar image

Oleg
17.8k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x420
x3

asked: Apr 20 at 10:57 AM

Seen: 55 times

Last Updated: Apr 24 at 03:03 PM

Copyright 2017 Redgate Software. Privacy Policy