internal static string GenerateScript(GenerateScriptParams generateScriptParams,
CancellationToken cancellationToken)
{
const string scriptPrologue =
@"IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'AssessmentResult'))
BEGIN
CREATE TABLE [dbo].[AssessmentResult](
[CheckName] [nvarchar](max) NOT NULL,
[CheckId] [nvarchar](max) NOT NULL,
[RulesetName] [nvarchar](max) NOT NULL,
[RulesetVersion] [nvarchar](max) NOT NULL,
[Severity] [nvarchar](max) NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[TargetPath] [nvarchar](max) NOT NULL,
[TargetType] [nvarchar](max) NOT NULL,
[HelpLink] [nvarchar](max) NOT NULL,
[Timestamp] [datetimeoffset](7) NOT NULL
)
END
GO
INSERT INTO [dbo].[AssessmentResult] ([CheckName],[CheckId],[RulesetName],[RulesetVersion],[Severity],[Message],[TargetPath],[TargetType],[HelpLink],[Timestamp])
VALUES";
var sb = new StringBuilder();
if (generateScriptParams.Items != null)
{
sb.Append(scriptPrologue);
foreach (var item in generateScriptParams.Items)
{
cancellationToken.ThrowIfCancellationRequested();
if (item.Kind == AssessmentResultItemKind.Note)
{
sb.Append(
$"\r\n('{CUtils.EscapeStringSQuote(item.DisplayName)}','{CUtils.EscapeStringSQuote(item.CheckId)}','{CUtils.EscapeStringSQuote(item.RulesetName)}','{item.RulesetVersion}','{item.Level}','{CUtils.EscapeStringSQuote(item.Message)}','{CUtils.EscapeStringSQuote(item.TargetName)}','{item.TargetType}','{CUtils.EscapeStringSQuote(item.HelpLink)}','{item.Timestamp:yyyy-MM-dd hh:mm:ss.fff zzz}'),");
}
}
sb.Length -= 1;
}
return(sb.ToString());
}
internal async Task HandleGenerateScriptRequest(
GenerateScriptParams parameters,
RequestContext <ResultStatus> requestContext)
{
GenerateScriptOperation operation = null;
try
{
operation = new GenerateScriptOperation(parameters);
TaskMetadata metadata = new TaskMetadata
{
TaskOperation = operation,
TaskExecutionMode = parameters.TaskExecutionMode,
ServerName = parameters.TargetServerName,
DatabaseName = parameters.TargetDatabaseName,
Name = SR.SqlAssessmentGenerateScriptTaskName
};
var _ = SqlTaskManager.Instance.CreateAndRun <SqlTask>(metadata);
await requestContext.SendResult(new ResultStatus
{
Success = true,
ErrorMessage = operation.ErrorMessage
});
}
catch (Exception e)
{
Logger.Write(TraceEventType.Error, "SQL Assessment: failed to generate a script. Error: " + e);
await requestContext.SendResult(new ResultStatus()
{
Success = false,
ErrorMessage = operation == null ? e.Message : operation.ErrorMessage,
});
}
}
public GenerateScriptOperation(GenerateScriptParams parameters)
{
Validate.IsNotNull(nameof(parameters), parameters);
Parameters = parameters;
}