各レポートでは、すべてが手動で行われたため、控えめに言っても不合理でした。
データベースサーバー側からHTMLを生成し、 sp_send_dbmailコマンドを実行して、 データベースメールを介してメールを生成することが決定されました。
Web上のほとんどの例では、手動のマークアップを作成しました-これはあまり効率的なアプローチではありませんでした。 ただし、任意の構造を持つテーブルを操作できる汎用的なソリューションは見つかりませんでした。
このギャップを埋めるために、私のソリューションを検討することを提案します。
システムビューから、目的のテーブルの列のリストを取得します。
DECLARE @object_name SYSNAME , @object_id INT , @SQL NVARCHAR(MAX) SELECT @object_name = '[dbo].[Products]' , @object_id = OBJECT_ID(@object_name) SELECT @SQL = 'SELECT [header/style/@type] = ''text/css'' , [header/style] = '' table {border-collapse:collapse;} td, table { border:1px solid silver; padding:3px; } th, td { vertical-align: top; font-family: Tahoma; font-size: 8pt; text-align: left; }'' , body = ( SELECT * FROM ( SELECT tr = ( SELECT * FROM ( VALUES ' + STUFF(CAST(( SELECT ', (''' + c.name + ''')' FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, '') + ' ) t (th) FOR XML PATH('''') ) UNION ALL SELECT ( SELECT * FROM ( VALUES' + STUFF(CAST(( SELECT ', ' + CASE WHEN c.is_nullable = 1 THEN '(ISNULL(' ELSE '(' END + CASE WHEN TYPE_NAME(c.system_type_id) NOT IN ('nvarchar', 'nchar', 'varchar', 'char') THEN 'CAST(' + '[' + c.name + '] AS NVARCHAR(MAX))' ELSE '[' + c.name + ']' END + CASE WHEN c.is_nullable = 1 THEN ',''''))' ELSE ')' END FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, ' ') + ' ) t (td) FOR XML PATH(''''), TYPE) FROM ' + @object_name + ' ) t FOR XML PATH(''''), ROOT(''table''), TYPE ) FOR XML PATH(''''), ROOT(''html''), TYPE' PRINT @SQL EXEC sys.sp_executesql @SQL
次に、 XMLを生成する動的SQLクエリを作成します 。
SELECT [header/style/@type] = 'text/css' , [header/style] = 'css style ...' , body = ( SELECT * FROM ( SELECT tr = ( SELECT * FROM ( VALUES ('column_name1', 'column_name2', ...) ) t (th) FOR XML PATH('') ) UNION ALL SELECT ( SELECT * FROM ( VALUES ([column_value1], [column_value2], ...) )t (td) FOR XML PATH(''), TYPE ) FROM [table] ) t FOR XML PATH(''), ROOT('table'), TYPE ) FOR XML PATH(''), ROOT('html'), TYPE
この場合、特定のデータ型（たとえば、 UNIQUEIDENTIFIER ）を含む列は、生成されたレポートに含まれません。
SELECT name FROM sys.types WHERE user_type_id IN ( 34, 36, 98, 128, 129, 130, 165, 173, 189, 241 )
リクエストを実行すると、レターに添付された次のHTMLマークアップを取得します。
<html> <header> <style type="text/css"> ... </style> </header> <body> <table> <tr> <th>column_name1</th> <th>column_name2</th> ... </tr> <tr> <td>column_value1</td> <td>column_value2</td> ... </tr> </table> </body> </html>
このスクリプトを毎週手動で実行しないために、 ジョブがSQLエージェントに追加され、レポートが自動的に生成および送信されました。
ここで示した解決策が、このような問題の解決に役立つことを願っています。
PS：複数行のVALUESコンストラクトはSQL Server 2008でのみ登場しました。したがって、時間を節約するために、同じスクリプトの例を2005年サーバー用に示します。
DECLARE @object_name SYSNAME , @object_id INT , @SQL NVARCHAR(MAX) SELECT @object_name = '[dbo].[Products]' , @object_id = OBJECT_ID(@object_name) SELECT @SQL = 'SELECT [header/style/@type] = ''text/css'' , [header/style] = '' table {border-collapse:collapse;} td, table { border:1px solid silver; padding:3px; } th, td { vertical-align: top; font-family: Tahoma; font-size: 8pt; text-align: left; }'' , body = ( SELECT * FROM ( SELECT tr = ( SELECT * FROM ( ' + STUFF(CAST(( SELECT ' UNION ALL SELECT ''' + c.name + '''' FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT th =') + ' ) t FOR XML PATH('''') ) UNION ALL SELECT ( SELECT * FROM ( ' + STUFF(CAST(( SELECT ' UNION ALL SELECT ' + CASE WHEN c.is_nullable = 1 THEN 'ISNULL(' ELSE '' END + CASE WHEN TYPE_NAME(c.system_type_id) NOT IN ('nvarchar', 'nchar', 'varchar', 'char') THEN 'CAST(' + '[' + c.name + '] AS NVARCHAR(MAX))' ELSE '[' + c.name + ']' END + CASE WHEN c.is_nullable = 1 THEN ','''')' ELSE '' END FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT td =') + ' ) t FOR XML PATH(''''), TYPE) FROM ' + @object_name + ' ) t FOR XML PATH(''''), ROOT(''table''), TYPE ) FOR XML PATH(''''), ROOT(''html''), TYPE' PRINT @SQL EXEC sys.sp_executesql @SQL