データベース研究所(DB Lab)

以下のXMLデータをテーブル形式に変換する方法を説明します。 <NewDataSet> <row> <Title>タイトル1</Title> <Memo>内容1</Memo> <UserID>test1</UserID> <Count>1</Count> </row> <row> <Title>タイトル2</Title> <Memo>内容2</Memo> <UserID>test2</UserID> <Count>2</Count> </row> </NewDataSet> 一つ目方法！ このまま実行してみてください。 DECLARE @XmlData xml SET @XmlData = '<NewDataSet><row><Title>タイトル1</Title><Memo>内容1</Memo><UserID>test1</UserID><Count>1</Count></row><row><Title>タイトル2</Title><Memo>内容2</Memo><UserID>test2</UserID><Count>2</Count></row></NewDataSet>' DECLARE @IntDoc int EXEC sp_xml_preparedocument @IntDoc OUTPUT, @XmlData SELECT Title, Memo, UserID, Count FROM OPENXML(@Intdoc, '/NewDataSet/row',2) WITH (Title nvarchar(20), M...

SQL SERVER 2016からはJSONデータをテーブル化することができます。 以下の例文を参考してください。 DECLARE @json NVARCHAR(MAX) SET @json = N'[{"Title":"タイトル1","Memo":"内容1","UserID":"test1","Count":1},{"Title":"タイトル2","Memo":"内容2","UserID":"test2","Count":2}]' SELECT * FROM OPENJSON (@json) WITH (Title nvarchar(20) '$.Title', Memo nvarchar(20) '$.Memo', UserID varchar(20) '$.UserID', Count int '$.Count') 出力結果

指定した日付が含まれる週の日曜日と土曜日の日付を取得が必要な時があります。 Weekly統計を集計する時良く使います。 DECLARE @LogDate date SET @LogDate = '2018-12-20' DECLARE @StartDate date DECLARE @EndDate date --日曜日～土曜日 SET @StartDate = DATEADD(DD,CASE WHEN DATEPART(dw,@LogDate) = 7 THEN -6 ELSE 1-DATEPART(dw,@LogDate) END, @LogDate) SET @EndDate = DATEADD(DD, 6, @StartDate) SELECT @StartDate AS StartDate, @EndDate AS EndDate 出力結果

指定した日付が含まれる週の月曜日と日曜日の日付を取得が必要な時があります。 Weekly統計を集計する時良く使います。 DECLARE @LogDate date SET @LogDate = '2018-12-20' DECLARE @StartDate date DECLARE @EndDate date --月曜日～日曜日 SET @StartDate = DATEADD(DD,CASE WHEN DATEPART(dw,@LogDate) = 1 THEN -6 ELSE 2-DATEPART(dw,@LogDate) END, @LogDate) SET @EndDate = DATEADD(DD, 6, @StartDate) SELECT @StartDate AS StartDate, @EndDate AS EndDate 出力結果

SQL SERVER 2012移行からFORMATを利用して日付をフォーマットされた文字列に変換することができます。 使い方は以下の通りです。 DECLARE @LogDate datetime SET @LogDate = GETDATE() SELECT @LogDate AS LogDate, FORMAT(@LogDate, 'yyyy/MM/dd') AS Str1, FORMAT(@LogDate, 'HH:mm') AS Str2 SELECT @LogDate AS LogDate, FORMAT(@LogDate, 'yyyy年MM月dd日') AS Str1, FORMAT(@LogDate, 'HH時mm分') AS Str2 出力結果

sidを確認するSQLです。 SELECT * FROM sys.server_principals 実行結果 実行結果には「test」DBアカウントのsidは「0x2DC6BE22B74DC549BCAA6E34AE96B33B」です。 では、ミラー サーバーで「test」DBアカウントを作成する時には以下のように記入して実行します。 CREATE LOGIN test WITH PASSWORD = ' testpassword ', SID = 0x2DC6BE22B74DC549BCAA6E34AE96B33B もちろんパスワードはプリンシパル サーバーと同じパスワードを設定してください。 同じSIDでアカウントを作成することでミラーサーバーでのユーザーマッピング問題を解決できます。

Trace Logのtrcファイルの内容を確認する方法です。 以下のように使えます。 SELECT * FROM fn_trace_gettable('c:\temp\mytrace.trc', default); 行番号を追加して取得したいのであれば以下のように使います。 SELECT IDENTITY(int, 1, 1) AS Sn, * FROM fn_trace_gettable('c:\temp\mytrace.trc', default);

■SQL SERVER 2016以上のバージョン SQL SERVER 2016からは「string_split」関数が追加され簡単にカンマ区切りのデータをテーブルに変換することができます。 DECLARE @StrComma varchar(100) SET @StrComma = 'A,B,C,D' SELECT * FROM string_split(@StrComma,',') ■SQL SERVER 2014以下のバージョン SQL SERVER 2014以下のバージョンでは関数を作成して変換することができます。 まず、関数を作成します。 CREATE FUNCTION dbo.fnStringSplit ( @Str VARCHAR(MAX) , @Separator CHAR(1) ) RETURNS @RESULT TABLE(RtnVal VARCHAR(MAX)) AS BEGIN DECLARE @SeparatorPosition int = CHARINDEX(@Separator, @Str ) DECLARE @Value VARCHAR(MAX), @StartPosition int = 1 IF @SeparatorPosition = 0 BEGIN INSERT INTO @RESULT VALUES (@Str) RETURN END SET @Str = @Str + @Separator WHILE @SeparatorPosition > 0 BEGIN SET @Value = SUBSTRING(@Str , @StartPosition, @SeparatorPosition- @StartPosition) IF( @Value <> '' ) INSERT INTO @RESULT VALUES(@Value) SET @StartPosition = @SeparatorPosition + 1 SET @SeparatorPosition = CHARINDEX(@Separato...

「Package.dtsxのコピーに名前を付けて保存」をしたい場合、「SSIS パッケージ ストア」の指定ができなく以下のようにFileSystemに固定になる場合があります。 こういう時は以下のように対応してください。 「プロジェクト」の「パッケージ配置モデルに交換」をクリックします。 この画面が表示されたら「OK」をクリックします。 「OK」をクリックします。 もう一度「Package.dtsxのコピーに名前を付けて保存」をクリックします。 パッケージの場所を指定できるようになります。 パッケージの場所は３つがあります。 ・File System ・SQL Server ・SSIS パッケージ ストア

SSDT for Visual Studio 2017は2017年12月現在、オンライン(Online)バージョンしかありません。 オフライン(Offlne)バージョンをダウンロードする方法について説明します。   まず、以下のサイトからオンラインバージョンをダウンロードしてください。   https://docs.microsoft.com/ja-jp/sql/ssdt/download-sql-server-data-tools-ssdt   その後、コマンドプロンプトでダウンロードフォルダーに移動し、以下のように実行します。   SSDT-Setup-ENU.exe /layout c:\vs2017ssdt   そうするとc:\vs2017ssdt フォルダにSSDT for Visual Studio 2017 offline（オフライン）installerがダウンロードされます！

SQL SERVER→MySQLのリンクサーバーで繋いでデータを取得するSQLを実行した時に以下のようなエラーの対応方法について説明します。 例えば、このようなSQLを実行した時にエラーが発生します。 SELECT * FROM OPENQUERY(LinkedServereMySQL,' SELECT UserID FROM TempData WHERE Sn = 1) OPENQUERYでデータを取得していますが、今まで問題なかったSQLがODBCドライブを変更したら以下のエラーが発生しました。 メッセージ 7357、レベル 16、状態 2、行 1 オブジェクト " SELECT UserID FROM TempData WHERE Sn = 1" を処理できません。リンク サーバー "LinkedServereMySQL" の OLE DB プロバイダー "MSDASQL" では、オブジェクトに列がないか、現在のユーザーがそのオブジェクトに対する権限を持っていないことが示されています。 対応方法は以下の通り修正すると解決できます。 SELECT * FROM OPENQUERY(LinkedServereMySQL,'SELECT UserID FROM TempData WHERE Sn = 1 もしくは SELECT * FROM OPENQUERY(LinkedServereMySQL,'SELECT UserID FROM TempData WHERE Sn = 1) 「SELECT * FROM OPENQUERY(LinkedServereMySQL,'」の後に改行するとエラーになります。