最近のトラックバック

  • event (夢茶爺&苦茶爺のPC奮戦備忘録)

おすすめ本

AccRanking

Ad

カテゴリ「SQL-Server」の13件の記事 Feed

2019年10月 4日 (金)

【SQL-Server】WITH句を使った再帰処理で連番の生成

WITH句 の中で自分自身を読んでやれば再帰的に処理が行われる様です。 以下のSQLでは連番を生成する処理を再帰処理で行っています。

このSQLを実行すると以下の様になります。

With21


但し、このままでは100件までの再帰処理しかできない様で、「SEQNO < 102」と指定すると以下の様にエラーが発生します。
メッセージ 530、レベル 16、状態 1、行 1
ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。

With22

このエラーも OPTION (MAXRECURSION nnn) として指定し「nnn」に101以上を指定すればできることになります。
尚、 MAXRECURSION の範囲は「0」~「32767」で「0」指定は制限が無い様です。 制限無しでもあまりに大きい値は問題有りだとは思います。 楽天市場

【SQL-Server】WITH句を使って同一処理SQLを複数回利用する

SELECT文において副問合せを使うことはよくありますが、 全く同じ副問合せを何度も記述することはSQL的にも非効率です。
そこで WITH句 の登場なのですが、 この句を用いて何度も利用するSELECT文を宣言し、 それをその後に続くSQLの中の FROM句 の中で利用できます。
以下の様なテストテーブル「TEST1」があるとします。 このテーブルに対して WITH句 を使ってみます。

With10

TEST1のCODE2が「3」のデータを全て抽出するSELECTを WITH句 として宣言し、 さらにその WITH句 を2回使用するSQLが以下の様になります。

WITH句 では「CODE1」が「1」と「4」のデータを抽出します。 それ以降の SELECT文 では「CODE2」が「1」と「3」のものを UNION しています。 このSQLを実行すると以下の様になります。

With11

楽天市場

2019年9月25日 (水)

【SQL-Server】Oracleの「CREATE OR REPLACE TABLE」に変わる方法について

SQL-Server には Oracle の様に CREATE OR REPLACE TABLE がありません。
そこで以下の様に OBJECT_ID 関数でテーブルの存在確認をし、存在すればテーブルを削除(DROP TABLE)します。
その後で、テーブルの生成(CREATE TABLE)を行います。
このテーブルでは CODE1CODE2 を主キーとして設定する例を示しています。

楽天市場

2016年3月26日 (土)

VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力

ADO.NET を使用した SQL-Server のテーブルをCSVファイルに出力する簡単な関数を作成してみました。

CSVファイルを作成する関数への引数は、データベース名とデータソース名と、SQL文、それとCSVファイル名とCSV内のカラムヘッダを 出力するかのフラグになります。

CSV出力処理関数内では SQL-Server への接続をオープンし、指定されたSELECT文を実行します。 SELECT文の結果の行が在れば、1行づつデータを取得します。 初回の行の処理の場合には、リーダーオブジェクトからカラム名を取得しヘッダ行を書き込みます。 その後、各データ行をCSVファイルに書き込みます。 CSVの各データは強制的にダブルクォートで囲む様にしていますので、 データ内のダブルクォートはエスケープ処理しています。


尚、今回のテーブルは以下の生成文で生成しました。

結果的にCSVファイル内容は以下の様になります。


■関連記事
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行

デル株式会社

楽天市場

2016年2月17日 (水)

BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について

以前の記事の中で「SQL Server」の「BCP」コマンドを取り上げましたが、 その時のコマンドは以下の様になっていました。 このコマンドは出力されるファイルが文字形式として書き込まれます。 (データベースの中のネイティブ形式ではないということです) このコマンドで出力されるテキストファイルのコードは「Shift-JIS」形式になるはずです。
これに対応するインポートコマンドは同様に「-c」オプションをつけて以下の様になります。 今回はUnicodeでのインポート・エクスポートの必要がありましたので、「-c」オプションを「-w」に変えて行います。
以下はエキスポートコマンドです。 このコマンドで出力されるテキストファイルのコードがUnicodeとなります。
以下はインポートコマンドです。

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行

達人に学ぶDB設計 徹底指南書【電子書籍】[ ミック ]

価格: 2,808円
(2016/09/29 13:53時点 )

感想:1件


楽天市場

2015年12月 2日 (水)

Windows7のIIS(インターネット・インフォメーション・サービス)インストール

仕事で使うことがあって、久々にIISなどというものをWindows7でインストールしてみました。
まず最初に、IISそのものをインストールします。

■IISをコントロールパネルから有効にする

1.コントロールパネルの「プログラム」をクリックします。

Ctrl1_2

2.「Windows の機能の有効化または無効化」をクリックします。

Ctrl2

3.「Windows の機能」ウィンドウが表示されたら、
  「インターネット インフォメーション サービス」にチェックをつけ、[OK] をクリックします。
  このとき「ASP.NET」の機能が必要だったので、「インターネット インフォメーション サービス」内の
  「World Wide Web サービス」⇒「アプリケーション開発機能」⇒「ASP.NET」にチェックを入れました。
  その他、必要な機能が在れば該当するところにチェックを入れます。
  この機能の有効化には結構時間が掛かりますので、暫く待ちます。

Ctrl3

4.インストールが終わったら、ブラウザを立ち上げてURLに「http://localhost/」と入力します。
  インストールに問題が無ければ、以下のページが表示されるはずです。

Ctrl4

■ASP.NETで作成したプログラムをIISに登録する

1.スタートメニューから「管理ツール」の「インターネット インフォメーション サービス(IIS)マネージャー」をクリックする。

Ctrl5

2.「サイト」フォルダ内の「Default Web Site」上で右クリックし、メニューから「アプリケーションの追加...」をクリックする。

Iis1

3.「アプリケーションの追加ウインドウ」が開くので、エイリアスと物理パスを設定します。

Iis2

4.「Default Web Site」の配下にエイリアスの名前ができていればOKです。

Iis3


■「HTTP エラー 500.19 - Internal Server Error ページに関連する構成データが無効であるため、要求されたページにアクセスできません」のエラー発生
IISの設定は終わったのですが、なぜかHTTPエラーの500.19が発生しました。

あるブログからの引用なのですが、以下の処理でエラーが解消されるようです。

・定義ファイル
%windir%\system32\inetsrv\config\applicationHost.config

・変更内容
<sectionGroup name="system.webServer"> セクションの以下の項目が、デフォルトでは"Deny"になっているので"Allow"に変更する。

  <section name="handlers" overrideModeDefault="Allow" />
  <section name="modules" allowDefinition="MachineToApplication" overrideModeDefault="Allow" />

この変更後、HTTPエラーは出なくなりました。


■MSSQLでのIIS用のユーザを作成する
ここまでの設定で、「http://localhost/TEST/Index.apsx」でログイン画面が表示されたのですが、ログイン処理を行うとデータベースエラーが発生しました。

Log1_2

Log2


MSSQLにIISから接続するユーザが設定されていない様なので、「Microsoft SQL Server Management Studio」を起動し「セキュリティ」の中の「ログイン」で「新しいログインの追加..」をクリックしIIS用のログインを追加します。


Sql1

ログインの追加ウインドウ

Sql2


ログインの追加後はデータベースへのアクセスが成功するはずです。

2015年5月23日 (土)

ADO.NET での DataReader の入れ子エラー

ADO.NETを利用してSQL-Serverをアクセスするプログラムを組む場合に、データ参照のために DataReader を使用します。
一連のデータ処理では DataReader からのデータ取得をループで処理しますが、そのループ内で更にマスタ等のデータを 参照したくなることはよくあります。
この場合、同じコネクションを使いたくなり DataReader の処理中に更に DataReader を入れ子で使うことになったのですが 以下のエラーが発生しました。

System.InvalidOperationException: このコマンドに関連付けられている DataReader が既に開かれています。このコマンドを最初に閉じる必要があります。

この DataReader を入れ子で使えないかと調べたら、接続文字列の中に以下の指定を行えばできることがわかりました。

MultipleActiveResultSets=True

以前、このブログの中で 「ADO.NET を使用した SQL-Server へのアクセス・クラス」を載せていますが、接続文字列は以下の様な感じがいいと思います。

Persist Security Info=false;Integrated Security=SSPI;MultipleActiveResultSets=True;Initial Catalog=[DataBase-Name];Data Source=[DataSource-Name];

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


富士通パソコンFMVの直販サイト富士通 WEB MART


楽天市場

2015年3月18日 (水)

ADO.NET を使用した SQL-Server へのアクセス・クラス

ADO.NET を使用した SQL-Server へのアクセス・クラスの簡単なものを作成してみました。
このクラスを元にして拡張したものを仕事でも使っています。

このクラス「clsSqlServer」には以下のメソッド・プロパティが備わっています。

メソッド・プロパティ 概要
New コンストラクタ(引数にデータベース接続文字列を渡す)
BeginTransaction トランザクション開始
Commit トランザクションコミット
Rollback トランザクションロールバック
CloseConnection コネクションの解除
OpenDataReader SELECT文SQLの実行とDataReaderへの読込
CloseDataReader DataReaderのクローズ
ExeSQL DML-SQL文の実行
CnvReaderToHashtable DataReaderの結果ItemsをHashtableに変換



今回のクラスのテストプログラムを以下に載せますが、SQLサーバへの接続文字列は以下の様にしています。

キーワード 概要(設定値)
Persist Security Info ID やパスワードなどのセキュリティ関連情報の破棄指定(False)
Integrated Security 現在のWindowsアカウントでの認証(SSPI)
Database データベース名(取敢えず「TEST」)
Data Source SQL Serverのインスタンスの名前
(Microsoft SQL Server Management Studio Express:.\SQLEXPRESS)



このテストは、システム日付をSELECTで実行し、SqlDataReaderに取得しています。SqlDataReaderから日付を取り出して その後、クラスのクローズを行っています。

■「clsSqlServer」の使用テストソース1


以下に、clsSqlServerの全体のソースを載せます。バグがあるかもしれませんが悪しからず。

■「clsSqlServer」のソース

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


楽天市場

2013年2月27日 (水)

SQL-Serverの自動採番(IDENTITY値)の取得・リセット

システムに必要なテーブルで、自動的に番号を振っていくものが必要なときがあります。 たとえば、各種の伝票データの伝票番号の様なものです。

プログラム処理上、データを登録した直後に、自動採番された値を取得し何かに使いたいことがよくあります。
SQL-Serverでは現在の自動採番の値を取得する方法が用意されています。

取敢えず自動採番を行える簡単なテーブルの例を示します。(前回ストアド・ファンクションで使用したテーブルに細工します。)



まず、テストテーブルBに1件データを挿入します。 「ID」は自動採番の設定なので登録する値は設定しません。
■自動採番された値を取得
この直後に、設定された「ID」値を取得するには以下のSELECT文を実行します。
結果として「1」という値が返されます。

■自動採番された値をリセット
デバッグ途中でテーブルのデータを全て削除して、さらに自動採番が「1」からにしたい場合はよくあります。 そのときに以下の命令をクエリアナライザ等で実行します。
自動採番の値を「0」にする命令ですが、実際INSERT実行時には+1された値が「ID」に設定されます。

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


楽天市場

2013年2月14日 (木)

SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない

当然といえばそうなのかもしれませんが、SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない。
TRY・・・CATCHも記述できないようです。関数は戻り値のみを返すもので、テーブル等に変更を与えてはいけないのでしょう。

さて、取敢えずストアド・ファンクションの簡単な例を示します。

前回使用したテストテーブルを利用して、ストアド・ファンクションを作成します。テーブルの構造は以下の感じです。
■スカラ値関数

テストテーブルAを利用した簡単な値を返すストアド・関数です。
与えられた検索用IDでテーブルAの「DATA1」カラムの内容を返す簡単な関数です。 SELECT文での実行は以下のようにします。
======================================
SELECT [dbo].[FuncTest2](1)
======================================

■テーブル値関数その1(インラインテーブル値)

テストテーブルAを利用した簡単な値を返すストアド・関数です。
SELECT文のFROM句で実行しますので、以下のようになります。
======================================
SELECT * FROM [dbo].[FuncTest3](1)
======================================

■テーブル値関数その2(複数の行を返すテーブル値)

テストテーブルAを利用した複数の行を返すストアド・関数です。
指定されたID以降のデータを全て返す様な動作を行います。
RETURNS句で指定されたTABLEにINSERTしていくことで、結果をテーブルの値として返します。
INSERTで追加してやればいろんなことができると思います。上の例ではコメント行になっていますが、 強制的にID「0」を作成することも可能です。
この関数の実行も「テーブル値関数その1」で行った方法と同様です。


■スカラ値関数にUPDATE文を記述
SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 15、プロシージャ FuncTest2、行 8
副作用のある演算子または時間に依存する演算子を関数内の 'UPDATE' で使用することはできません。
============================================================

TRY、CATCHを入れてもエラーが表示され使用出来ないようです。
SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 6
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 9
副作用のある演算子または時間に依存する演算子を関数内の 'END TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 10
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN CATCH' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 11
副作用のある演算子または時間に依存する演算子を関数内の 'END CATCH' で使用することはできません。
============================================================


副作用のある処理をファンクションのなかでは記述できないので、 どうしても処理したいのであれば、ストアド・プロシージャとして作成するしかないようです。
そこで、結果として何かを返したいのであれば、引数に出力属性のものを持つしかありません。

また、プロシージャの中からファンクションを実行することはできますが、ファンクションの中から プロシージャを実行することはできないようです。
ファンクションのコールが入れ子になっていて、深いところでUPDATEなどを行いたい場合、 全てのファンクションをプロシージャにしないといけなくなってきます。
このあたりは、ファンクション、プロシージャの切り分けをしっかりしておく必要がありそうです。

■関連記事
VB.NET:ADO.NETを使用したSQL-ServerのテーブルのCSV出力
BCPコマンドでUnicode文字形式を使用したデータのインポート及びエクスポート(SQL Server)について
ADO.NET での DataReader の入れ子エラー
ADO.NET を使用した SQL-Server へのアクセス・クラス
SQL-Serverの自動採番(IDENTITY値)の取得・リセット
SQL-Serverのユーティリティ(BCPコマンド)を利用したテーブルへのインポート・エキスポート
SQL-Serverのプロシージャ生成のバッチ実行
SQL-Serverの関数・プロシージャのVB.NETでの実行


楽天市場