Sql-server – How to change the target server type for a trace template

I think you need to specify an "/en" option in order to change the encryption level. See this link.

http://support.microsoft.com/kb/906562

I can reproduce that same behavior on SQL Server 2012. However, I am able to get it to work by putting the Linked Server name back into the query. Is there a reason you moved it out into the AT clause?

Try the following:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]');
SET FMTONLY OFF

Well, while the above does work on SQL Server 2012, it was not a good test because it does not work when executing a stored procedure. It might have something to do with it being a different type of call (i.e. the stored procedure call is RPC, and that had to be configured separately).

I even just tried adding WITH RESULT SETS ((...)) and that still didn't work.

Ok, so I found something. It is generally known that FMTONLY ON doesn't actually run the code, it just scans it for SELECT statements. But, that also means that it doesn't know how to handle conditional code. So it returns whatever result sets could be returned, even if no code path would ever return some of them.

Example 1 has two SELECT statements, but only one can ever be returned at a time. Still, when running with FMTONLY ON, both are returned:

CREATE PROCEDURE dbo.FmtOnlyTest1
AS
SET NOCOUNT ON;
  DECLARE @A INT = 5;

  IF (@A <> 6)
  BEGIN
    SELECT TOP 5 * FROM [master].[sys].[objects];
  END;
  ELSE
  BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
    EXEC(@SQL);
END;
GO

Results:

-- Running normally returns one result set of 5 rows from sys.objects:
EXEC Test.dbo.FmtOnlyTest1;

-- With FMTONLY ON, it returns two result sets: one from sys.objects AND one with [IntVal]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest1;
SET FMTONLY OFF

Example 2 shows another consequence of not being able to run the code. If the code produces the result set dynamically, it cannot be determined by FMTONLY ON. This probably explains why it can't follow the EXEC call when the code is remote. I suspect that calling local Stored Procedures works by just doing a lookup in the local system catalog table for that definition.

CREATE PROCEDURE dbo.FmtOnlyTest2
AS
SET NOCOUNT ON;
  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest2;

-- With FMTONLY ON, there are no result sets returned (just like with the remote procedure)
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest2;
SET FMTONLY OFF

Knowing what we now know, we can use the fact that FMTONLY ON will find all result sets while not actually running the code to, even if not ideal, fake a result set to get CodeSmith (and other tools that still work in this fashion) working again.

Example 3 shows that we can effectively hide a dummy result set in a code block that cannot logically execute. Only FMTONLY ON will be able to see this so it won't present a problem for any other code (well, I did try sys.dm_exec_describe_first_result_set and that didn't really like it, but I don't think that is an issue in this case).

CREATE PROCEDURE dbo.FmtOnlyTest3
AS
SET NOCOUNT ON;

  IF (1 = 0)
  BEGIN
    SELECT CONVERT(INT, NULL) AS [FieldName1],
           CONVERT(DATETIME, NULL) AS [FieldName2];
  END;

  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest3;

-- Running with FMTONLY ON returns one result set of [FieldName1], [FieldName2]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest3;
SET FMTONLY OFF

For this test I had the code return a different result set for the IF (1 = 0) block to make the difference in behavior more apparent. But in practice, you would want to return the exact same result set structure in the SELECT as the remote stored procedure returns.

The obvious down-side here is that if you change the result set of that remote procedure you will have to remember to also change the definition in the IF (1 = 0) block, but at least CodeSmith will function.