Cannot evaluate policies that use Executesql() against SQL Server 2000

Jan 24, 2011 at 3:36 AM

I'm not sure whether anyone has noticed this one yet, but I can't find any discussion out on the web regarding it.

When evaluating a Policy where the Condition makes use of the ExecuteSQL() function, the T-SQL generated at the remote instance is in the format :

DECLARE @@UserScript nvarchar(max); SET @@UserScript = N'<T-SQL for the Condition> ;'; EXEC sp_executesql @@UserScript;

Because this generates the script varaible as nvarchar(max), this cannot be executed against a SQL Server 2000 instance.

Has anyone else experienced this?  Has it been there all the time or has this slipped in through a SP or CU?   My SQL 2008 CMS is version 10.0.4000

Oct 4, 2012 at 3:31 PM
Edited Oct 4, 2012 at 3:50 PM

I ran into the same problem.  I need to get the servername and you can't use @@servername so I'm trying it with sysservers but I can't because of the NVARCHAR(MAX) SQL 2000 limitation.  Is anyone aware of a way to get the servername without using the ExecuteSQL() function?

Trace below...

 

DECLARE @@UserScript nvarchar(max); SET @@UserScript = N'IF (CONVERT(INT,''8'')  > CONVERT(INT,''8''))
	BEGIN
	IF EXISTS (SELECT name FROM sys.servers WHERE name in (''ServerNameHere''))
				   BEGIN
					   SELECT 0
				   END
				ELSE
					BEGIN
						 SELECT 1
					END
	END
ELSE
	BEGIN
	IF EXISTS (SELECT srvname FROM sysservers WHERE srvname in (''ServerNameHere'')) 
				   BEGIN
					   SELECT 0
				   END
				ELSE
					BEGIN
						 SELECT 1
					END
	END'; EXEC sp_executesql @@UserScript; 
Developer
Nov 20, 2012 at 10:21 PM

Whereas I can't speak to the executesql issue there is a way to get server name through either the Server facet or the Server Information facet.  I would recommend using the Server Information facet -> NetName or the Server -> ComputerNamePhysicalNetBIOS or Server -> NetName.  One or both of these facets should be able to evaluate against a SQL 2000 instance.

Oct 7, 2014 at 5:14 PM
I know the solution for SQL 2012 is to give either read access to all databases or SYSADMIN to ##MS_PolicyTsqlExecutionLogin##.