DataBase Tips

DataBaseに関するTipsです。

SQL Server 2005からリンクテーブルでOracleに接続する

SQL Serverにリンクサーバーを設定することで，SQL ServerからOralceにアクセスできるようになります。その手順をまとめてみました。SQL Server は2005です。

サーバーにOracleのクライアントをインストールする

SQL Serverが稼働しているサーバーにOracleのクライアントをインストールします。インストール後SQLサーバーを再起動します。

リンクサーバーの作成

SQL Server上にリンクテーブルを作成します。SQL Server Management Studio(以下SSMS)を開きます。
サーバーオブジェクトのリンクサーバーを右クリックして，コンテキストメニューの新しいリンクサーバーを実行します。

全般ページは次のように設定します。

リンクサーバー

SQL Server側から指定する際の別名です。

サーバーの種類

その他のデータソースを指定します。

プロバイダ

Microsoft OLE DB Provider for Oracleを指定しました。私の環境ではオラクルが提供するOracle Provider for OLE DBを使うとうまく動作しないみたいでした。

次にセキュリティページ

「このセキュリティコンテキストを使用する」を選択して，リモートログインとパスワードを入力します。OKボタンをクリックするとリンクサーバーが作成されます。

リンクサーバーのテーブルを見る

リンクサーバーのテーブルをSELECT文で指定する時には，テーブル名を

<リンクサーバー名>.<カタログ>.<スキーマ>.<オブジェクト名>

のように指定しなければなりません。カタログは指定する必要がないので，次のようになります。

TEST..SCHEMA_A.TABLE

上記は，TESTというリンクサーバーのSCHEMA_AスキーマにあるTABLEテーブルを指定する構文です。この方法で次のようにSELECT文を記述します。

SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM
  TEST..SCHEMA_A.TABLE

注意すべき点は，オブジェクトにOracleのシノニムを指定しても取れないことです。本来はデータを取ってこられるはずですが，リンクテーブルからの場合は，Oracleで定義されたシノニム名ではなく本来のテーブル名で取ってくる必要があるようです。オブジェクトにビューを指定することは可能です。しかし処理速度が遅いように思います。

シノニムの作成

TEST..SCHEMA_A.TABLE　という表記方法はとても長ったらしいと感じます。そこで，SQL Server上でシノニムを作成することができます。

CREATE SYNONYM ORA_TABLE FOR TEST..SCHEMA_A.TABLE

とすると，それ以降そのデータベースではORA_TABLEという名前でリンクテーブルを利用できます。
シノニムを作成するともう一つ良いことがあります。それはA5:SQLにて入力補完機能を利用できるようになることです。リンクテーブルは入力補完の対象ではありませんが，シノニムは対象となりますので，A5:SQLでの開発がとてもやりやすくなります。A5:SQLのVersion 2.3以降のクエリデザイナでも利用できます。

Access2003のADPプロジェクトで参照する

リンクしたOracleのテーブルやシノニムはAccess2003のデータベースウィンドウには表示されません。これらをAccessのADPプロジェクトで参照するには，ビューを作ってやりましょう。

CREATE VIEW viwOraTalbe
AS
SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM
  TEST..SCHEMA_A.TABLE

このようにビューを作ってしまえば，ADPプロジェクトのデータベースウィンドウのクエリの画面に表示されるようになり，Accessからも簡単に利用できます。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

SQL Server 2005のデータベースコピーウィザードを使う

SQL Server 2005のデータベースコピーウィザードは，旧サーバから新サーバへデータを移行するときにとても便利そうだったので，これを使って稼働中のSQL Server 2000から，データベースをコピーしてみたが，最初はどうにもこうにもうまく動かなかった。そのときの記録。

各DBサーバへの接続

エラーログの出力先をテキストファイルにしてじっくり見てみると，
Windows認証でログインする方法でやっていると，それぞれのデータベースにうまくログインできていないようだった。ログインしているユーザ名ではなくコンピュータ名でSQL Serverに接続しようとしているみたいだ。そりゃ繋がらないよと思う。SQLのサービスがローカルシステムアカウントだからこうなるのかな？よくわからない。どうしたら良いのかわからないので，Windows認証ではなくSQLServer認証に切り替えてsaで接続するようにしてみた。（両方とも混在モードを使っているので）

転送方法の選択

転送方法の選択の画面では，「デタッチ後にアタッチする方法を使用する」というのと「SQL管理オブジェクトの方法を使用する」というのがある。動作中のDBからコピーするので，「SQL管理オブジェクトの方法を使用する」を使ってやってみる。

この方法だと，うまくコピーできるDBとエラーが出るDBがある。ビューが含まれないDBだと成功するみたいだが，ビューがあるとうまくいかないことが多い，あるDBのエラーログを見ていると，どうもビューの依存関係を解析し切れていないような感じがする。サンプルDBのNorthWindではビューがあるのに成功した。ビューを修正してなんとか依存関係エラーをクリアできるかもしれないが，現状はなにがどうなったときに依存関係を把握できなくなるのかが不明。（今後の調査でわかったことがあれば追記する）

実際にすべてのデータをコピーするときには稼働したままということは考えていないので，サービスを止めても大丈夫なDBを「デタッチ後にアタッチする方法を使用する」方式でもコピーしてみる。この方法で成功すれば，DBを止めてもよい時間帯にすべてのDBをコピーすればよい。

ファイル共有の設定

この方法はコピー元でDBをデタッチした後に，DBのファイル自体をWindowsファイル共有でコピー先にコピーしてアタッチするという方法である。だからDBのデータファイルがどこにあるかをUNCで指定しなければならない。作業中はドメインの管理者権限があるアカウントで入っていたので，最初は，C$などの管理共有から開いていって格納先を指定した。指定はできるのだが，実行時にエラーで終了した。ログを見るとアクセス権がないというエラーが記録されていた。そこでデータが格納されているフォルダに新たに共有を作った。アクセス許可はEveryoneフルアクセスにした。するとコピーが成功した。初めての成功である。とはいえEveryoneのフルアクセスの共有というのも，短期間とはいえなんなのでちゃんとしたアクセス権を設定したい。前に SQLへの接続も，コンピュータ名で接続しようとしていたのを思いだし，ひょっとしてここもコンピュータ名で接続しているのかもと思い，Everyone を外してから，コピー先のサーバ名に対してアクセス権を与えてみた。それでもコピーは成功した。

ログインとユーザー

あとでわかったことだが，コピーウィザードでコピーした場合，データベースでのユーザーとSQL Server認証のログインがちゃんとリンクされないようだ。データベースのユーザー自体ははちゃんとコピーされる。しかしそれがSQL Server認証のログインとリンクされない。Windows認証に関してはちゃんとリンクされる。dboのログインがsaの場合でもリンクされない。リンクされないとどうなるかというと，ログインが割り当てられていないユーザーとなってしまう。アプリケーションがSQL Server認証で動作するものの場合は，サーバーと接続できない状態になる。なのでコピーウィザードでコピーが終了したら，これらのユーザーとログインをリンクさせてやる必要がある。

USE [database]       <----- データベースを指定
EXECUTE sp_changedbowner 'sa'     <----- dboのログインをsaにする
EXECUTE sp_change_users_login 'Update_One','db_login','db_login'   <----- ユーザーとログインをリンク

dboのログインを指定するには，sp_changedbownerを使う。ユーザーとログインをリンクさせるには sp_change_users_loginを使う。複数のデータベースを一括してコピーする場合は，元のデータベースを見てユーザーとログインを関連づけるスクリプトを書いておくとよいだろう。

まとめ

他にも成功する方法はいくつもあるだろうが，環境が許せばとりあえず次のようにすればデータベースのコピーは成功する。コピーウィザードが成功しなくて困っている人は試してもらいたい。

転送元，転送先のDBへの接続はSQLServer認証でsaでログインするように指定する。
データのコピーは「デタッチ後にアタッチする方法を使用する」を使う。
転送元のデータファイルのフォルダに共有を設定し，転送先サーバのコンピュータ名アカウントに対して許可を与える。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

データベースダイアグラムでエラー発生

SQL Server 2005で，SQL Server Management Studio(SSMS)からデータベース　ダイアグラムを選択すると次のようなエラーが発生しました。

このデータベースの有効な所有者がいないので、データベースダイアグラムサポートオブジェクトをインストールできません。続行するには、最初に [データベースのプロパティ] ダイアログボックスの [ファイル] ページまたは ALTER AUTHORIZATION ステートメントを使用してデータベースの所有者を有効なログインに設定してから、データベースダイアグラムサポートオブジェクトを追加します。

このメッセージの通り，ALTER AUTHORIZATIONステートメントを使ったりSSMSから所有者にログインを割り当ててもいっこうに解決しません。

このデータベースは，SQL Server 2000からデータベースコピーウィザードでコピーしてきたものでした。2005サーバ上で新たに作成したデータベースではこのエラーは発生しませんでした。

結局，

http://msdn.microsoft.com/ja-jp/library/ms186345.aspx

に記載されている方法で解決ができました。

データベースダイアグラムサポートを SQL Server Management Studio にインストールするには、データベースの互換性レベルを SQL Server 2005 に設定しておく必要があります。データベースの互換性レベルが 90 に設定されていない場合、ダイアグラムサポートをインストールしようとすると次のエラーが発生します。

このデータベースの有効な所有者がいないので、データベースダイアグラムサポートオブジェクトをインストールできません。続行するには、最初に [データベースのプロパティ] ダイアログボックスの [ファイル] ページまたは ALTER AUTHORIZATION ステートメントを使用してデータベースの所有者を有効なログインに設定してから、データベースダイアグラムサポートオブジェクトを追加します。

データベース互換性レベルは、ダイアグラムサポートをインストールした後でリセットできます。次の Transact-SQL ステートメントを使用してデータベースの互換性レベルを変更してから、データベースダイアグラムの操作を再度行います。

EXEC sp_dbcmptlevel 'database_name', '90';

この記事の通り，

EXEC sp_dbcmptlevel 'database_name', '90';

を実行したら，エラーは発生せずデータベース　ダイアグラムを操作できるようになりました。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

ER図の論理名をコメントに設定する

A5:SQLでER図の論理名をコメントに設定するスクリプトを書いてみました。スクリプトをツールフォルダに保存して，ツールメニューから実行するとSQLウィンドウにSQL文をはき出します。

 // ER図の論理名をコメントに設定(&C)
 
 function isSQLServer(dbName){
     var regini = new RegIni("Software\\mmatsubara\\A5M2");
     var s = regini.read("DataSources",dbName,"");
     return (s.match(/Provider=SQLOLEDB.*/) || s.match(/Provider=SQLNCLI.*/));
 }
 
 function delQuate(s){
     var r = s.replace(/^"/i,"");
     r = r.replace(/"$/,"");
     r = r.replace(/""/g,'"');
     return r;
 }
 
 function tableExists(cnn, sTable){
     var tables = cnn.getTableList();
     var exp = new RegExp(sTable,"i");
     return tables.toString().match(exp);
     //return tables.indexOf(sTable) >= 0;
 }
 
 function fieldExists(cnn, sTable, sField){
     if(tableExists(cnn, sTable)){
         var fields = cnn.getTableColumnList(sTable);
         var fieldNames = new Array(fields.length);
         for(var i=0; i<fields.length; i++){
             fieldNames[i] = fields[i].columnName;
         }
         writeln(fieldNames.toString());
         var exp = new RegExp(sField,"i");
         return fieldNames.toString().match(exp);
     }else{
         return false;
     }
 }
 
 function setDescriptionMSSQL(cnn, sTable, sTDescription, sField, sFDescription){
     var sCmdField, rs, sCmd, sDescription;
     if(isNull(sField)){
         sCmdField = "NULL, NULL";
         sDescription = sTDescription;
     }else{
         sCmdField = "'COLUMN','"+sField+"'";
         sDescription = sFDescription;
     }
     rs = cnn.executeQuery("select value from fn_listextendedproperty(
         'MS_Description', 'SCHEMA', 'dbo', 'TABLE', '"+sTable+"', "+sCmdField+");");
     sCmd = "N'MS_Description', N'"+sDescription+"', N'SCHEMA', N'dbo', N'TABLE', N'"+
         sTable+"', "+sCmdField+";\n";
     if(rs.eof()){
         return "EXECUTE sp_addextendedproperty "+sCmd;
     }else{
         return "EXECUTE sp_updateextendedproperty "+sCmd;
     }
 }
 
 function setDescriptionOra(cnn, sTable, sTDescription, sField, sFDescription){
     if(isNull(sField)){
         return "comment on table "+sTable+" is '"+sTDescription+"';\n";
     }else{
         return "comment on column "+sTable+"."+sField+" is '"+sFDescription+"';\n";
     }
 }
 
 function setDescription(cnn, sSQL, sTable, sTDescription, sField, sFDescription){
     var isComment;
     if(isNull(sField)){
         if(sTDescription.length==0){
             return sSQL;
         }
         isComment = !tableExists(cnn, sTable);
     }else{
         if(sFDescription.length==0){
             return sSQL;
         }
         isComment = !fieldExists(cnn, sTable, sField);
     }
     if(isComment){
         sSQL = sSQL + '-- ';
     }
     var dbName = application.dbTree.getSelectedDatabaseName();
     if(isSQLServer(dbName)){
         return sSQL + setDescriptionMSSQL(cnn, sTable, sTDescription, sField, sFDescription);
     }else{
         return sSQL + setDescriptionOra(cnn, sTable, sTDescription, sField, sFDescription);
     }
 }
 
 function SetLogicalName(){
     var cnn = application.dbTree.getSelectedDatabaseConnection();
     var dbName = application.dbTree.getSelectedDatabaseName();
     var form = application.getActiveWindow();
     var sSQL = '';
     if(isUndefined(dbName)){
         alert('更新対象のデータベースを選択してください');
         return;
     }
     if(form.formType != 'A5EREditor'){
         alert('ERエディタ上で実行してください');
         return;
     }
     var s, A, inEntity=false, sTableP, sTableL, sField, aField;
     var sTableCmd, sFieldCmd;
     for(var I=0; I<form.source.Count; I++){
         s = form.source[I];
         if(s.match(/^\[.*\]$/)){
             inEntity = s.match(/\[Entity\]/);
         }
         if(inEntity){
             if(s.match(/PName=.*/)){
                 A = s.split(/=/);
                 sTableP = A[1];
             }
             if(s.match(/LName=.*/)){
                 A = s.split(/=/);
                 sTableL = A[1];
                 sSQL = sSQL + "-- Table " + sTableP + "\n";
                 sSQL = setDescription(cnn, sSQL, sTableP, sTableL, null, null);
             }
             if(s.match(/Field=.*/)){
                 A = s.split(/=/);
                 sField = A[1];
                 aField = sField.split(/,/);
                 for(var J=0; J<aField.length; J++){
                     aField[J] = delQuate(aField[J]);
                 }
                 sSQL = setDescription(cnn, sSQL, sTableP, sTableL, aField[1], aField[0]);
             }
         }
     }//for
     var frmSQL = application.newSqlEditor(sSQL);
     //frmSQL.runSql();
     //application.dbTree.reload();
 }
 
 
 SetLogicalName();

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

A5:SQLでOracleのビューのソースを得る

A5:SQLでOracleのビューのソースを得て，新しいSQLウィンドウに表示させるスクリプトです。

//	Oracle ビューのソースを開く
var conn = application.dbTree.getSelectedDatabaseConnection();
//	ビューの名前と内容を取り出すSQL
var sObjName = application.dbTree.getSelectedItemName();	//	スキーマ名.テーブル名
var sNames = sObjName.split("\\.");
var sTableName = sNames[1];
var sSql = "select VIEW_NAME, TEXT from USER_VIEWS where VIEW_NAME='" + sTableName + "'";
var rsSource = conn.executeQuery(sSql);
if(rsSource.getRecordCount() > 0){
	var sSql2 = "select * from user_tab_columns where table_name = '" + sTableName + "'";
	var rsFields = conn.executeQuery(sSql2);
	var ssSources = new Strings();
	var sFields;
	var sText = rsSource.getFieldValue("TEXT");	//	ビューの内容
 
	ssSources.add("CREATE OR REPLACE VIEW " + sTableName);
	rsFields.first();
	sFields = rsFields.getFieldValue("COLUMN_NAME");
	rsFields.next();
	while(!rsFields.eof()) {
	    sFields = sFields + "," + rsFields.getFieldValue("COLUMN_NAME");
	    rsFields.next();
	}
	rsFields.close();
	ssSources.add("(" + sFields + ")");
	ssSources.add("AS");
	ssSources.add(sText);
	application.newSqlEditor(ssSources.text);
}else{
	alert("ビューではありません");
}

このスクリプトを script/TreeTBに保存します。データベースツリー上で右クリックからアドオンメニュー > Oracle ビューのソースを開くで実行すると，新しいSQLウィンドウにビューのソース(CREATE OR REPLACE文)が表示されます。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

A5:SQLでINSERT文の雛形を作る

INSERT文の雛形を作るスクリプトを作りました。

//	INSERT文の雛形を作る
function makeSelectSQL() {
	var conn = application.dbTree.getSelectedDatabaseConnection();
	var sTableName = application.dbTree.getSelectedItemName();	//	スキーマ名.テーブル名
	var columnList = conn.getTableColumnList(sTableName);
	var ss = new Strings();
    var sIndent = "    ";
 
    ss.add("INSERT INTO "+sTableName);
    ss.add("(");
	for (var nIdx = 0; nIdx < columnList.length-1; nIdx++) {
		ss.add(sIndent + columnList[nIdx].columnName + ",");
	}
	ss.add(sIndent + columnList[columnList.length-1].columnName);
    ss.add(")");
    ss.add("VALUES (");
    ss.add(")");
	application.newSqlEditor(ss.text);
}
 
makeSelectSQL();

このスクリプトを script/TreeTBに保存します。データベースツリー上で右クリックからアドオンメニュー > INSERT文の雛形を作るで実行すると，新しいSQLウィンドウにINSERT分の雛形が表示されます。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

Oracle のViewのソースを取得する

OracleのViewのソースを取得しようとしたら、ことのほかはまったのでメモです。

いろいろなクライアントから取得しようと苦労したのですが、最終的にSQL*Plusでやりました。

SQL> set long 20000
SQL> set longc 3000
SQL> set linesize 3000
SQL> set pagesize 0
SQL> set trimspool on
SQL> spool D:¥sql¥view.sql
SQL> select dbms_metadata.get_dll('VIEW', view_name) from user_views;
SQL> spool off

結果から言うと、このような手順でできました。ここにたどり着くまでにはソースの妙な位置に改行が入ったりして、なかなかだめでした。

set longはLONG型を読み込むときのバッファの大きさで、これ以上のものはしっぽが切れてしまいます。標準ではかならず尻切れになりますから大きく設定します。
set longc と linesize を大きくしておかないと、ソースに改行が入ります。どれくらいのサイズがいいのかはViewのサイズによると思います。
pagesizeは0にします
trimspool を on にするとうっとおしい空白が消えてくれます。
spool で出力結果をファイルにも出すようにします。
sql文は単純です。user_viewsのtextでもいいのかもしれませんが....
spool off で出力を止めます。

以上で、Viewのソースを取得できます。longc や linesize はもっともっと大きくしてもいいのかもしれません。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

SQL Server で文字列の半角／全角変換

SQL Server 2005 のサーバー側の関数で，文字列の半角／全角変換をしたいとずっと思っていました。ネットでググると，「SQL Server にはその機能がないので，REPLACE関数を羅列して解決するしかない」という記事（データの全角半角変換、、－ Database Expert －＠IT）に当たり，そのコードを実装した記事（SQLServerでの文字列の半角文字から全角文字への変換）に当たりました。確かにT-SQLで実装しようとすると，その方法しかないでしょう。しかし，あまりにあまりな力業(^^;

一方，SQL Server 2005 からはSQLCLRというのが使えるはずです。それを使えばVB.NETで書いた関数を，サーバーサイドの関数にできるはず。AccessのMDBを使っている場合には，クエリの中でAccess VBAの関数を使えるので，StrConv関数を使って簡単に実現できていました。StrConvは.NETでも使えます。SQLCLRを一度やってみたいと思いながら手をつけずにいたので，いっちょ挑戦してみるかという気分になりました。そう思ってさらにググってみると，次の記事を発見しました。

ノラの日々 : ＳＱＬＣＬＲでSTRCONV関数

この記事がまさに私の求めていたものです。さっそくマネマネすることにしました。

SQLCLR構成オプションの有効化

まず，SQLCLRを使えるようにする必要があります。その方法は「SQL Server 自習書開発編 No.3 SQLCLR」に載っていますが，簡単に説明すると次の手順です。

全てのプログラム > Microsoft SQL Server 2005 > 構成ツール > SQL Server セキュリティ構成を実行。
「機能のセキュリティ構成」をクリック。
ツリーの中から「データベースエンジン」を展開して「CLR統合」をクリック。
「CLR統合を有効にする」のチェックを入れて「適用」ボタンをクリック。

DLLの作成

次にVBで書いたプログラムソースを入力します。先ほどのサイトに掲載されていたVBのソース部分です。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
 
Public Class SQLCLR
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function SqlStrConv(ByVal str As SqlString, ByVal mode As SqlInt32) As SqlString
        Return StrConv(str.ToString, mode)
    End Function
End Class

このプログラムをSqlClrStrConv.vbというファイル名で作成します。ここでは C:\CLRLib というフォルダに作成することにします。次にコマンドプロンプトを起動してC:\CLRLibに行きます。なお，ここでの手順ではこれらの作業はサーバー上で行います。

C:\>CD CLRLib

私の環境ではVBコンパイラ(VBC.exe)にパスが通っていませんでしたので，そこにパスを通します。.NET 2.0のコンパイラを使う必要があるようです。

C:\CLRLib>PATH C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\;%PATH%

パスはお使いの環境に合わせてくださいね。これでコンパイルの準備ができましたので，コンパイルします。

C:\CLRLib>vbc /out:SqlClrStrConv.dll /t:library /r:"C:\Program Files\Microsoft S
QL Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll" SqlClrStrConv.vb

エラーなくコンパイルができたら，SqlClrStrConv.dllができていると思います。

ストアド関数の作成

次に，関数を作成するSQL文を実行します。関数を作成したいデータベースに対して実行してください。SQL Server Management Studio の場合は次のように

CREATE ASSEMBLY [SqlClrStrConv]
FROM 'C:\CLRLib\SqlClrStrConv.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION SqlStrConv( @str nvarchar(max),@conversion int ) returns nvarchar(max)
AS EXTERNAL NAME SqlClrStrConv.SQLCLR.SqlStrConv
GO

僕の好きなA5:SQLだと次のような感じで ; コロンで終了して，「すべてを順に」で実行します。

CREATE ASSEMBLY [SqlClrStrConv]
FROM 'C:\CLRLib\SqlClrStrConv.dll'
WITH PERMISSION_SET = SAFE;
CREATE FUNCTION SqlStrConv( @str nvarchar(max),@conversion int ) returns nvarchar(max)
AS EXTERNAL NAME SqlClrStrConv.SQLCLR.SqlStrConv;

同じサーバー上の他のデータベースで利用したい場合は，そのデータベースで上記のSQLを実行すれば使えるようになります。

関数を使う

実際にSQLで変換をするには次のようにします。

半角に変換

SELECT
    dbo.SqlStrConv(NameKana, 8)
FROM
    tblPersons

全角に変換

SELECT
    dbo.SqlStrConv(NameKana, 4)
FROM
    tblPersons

第二引数の値はVbStrConv 列挙型ですが，数値に直すとほぼVBAと同じです。

定数	値	内容
vbUpperCase	1	文字列を大文字に変換します。
vbLowerCase	2	文字列を小文字に変換します。
vbProperCase	3	文字列の各単語の先頭の文字を大文字に変換します。
vbWide*	4*	文字列内の半角文字 (1 バイト) を全角文字 (2 バイト) に変換します。
vbNarrow*	8*	文字列内の全角文字 (2 バイト) を半角文字 (1 バイト) に変換します。
vbKatakana**	16**	文字列内のひらがなをカタカナに変換します。
vbHiragana**	32	文字列内のカタカナをひらがなに変換します。
vbUnicode	64	システムの既定のコードページを使って文字列を Unicode に変換します。Macintosh. では使用できません)
vbFromUnicode	128	文字列を Unicode からシステムの既定のコードページに変換します。Macintosh. では使用できません)

でも.NETのStrConvは，なんだか簡体字／繁体字の変換までできるみたいですね。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

VBAからデータリンクプロパティダイアログを表示する

ExcelやAccessのVBAから，データベースへ接続する際にADODBのConnectionオブジェクトに渡すためのConnectionString（接続文字列）を用意する必要があります。これをGUIを使って得ることができるのが，データリンクプロパティダイアログです。

これを利用するには，拡張子UDLのファイルを作ってダブルクリックするとダイアログが表示されます。設定を完了してOKをクリックした後のファイルをエディタで開くと接続文字列が格納されていますので，それを利用します。このダイアログをVBAのプログラムの中から呼び出せないでしょうか。

ネットをググってみると，UDLファイルを作って，ShellExecuteでそのファイルを開くというようなテクニックが載っていました。そういえば昔その方法で設定ユーティリティを作ったことがありました。でも，このダイアログどっかのDLLの中に入っているに違いないと思って調べてみたら，やっとわかりました。次の手順で利用できます。

参照設定で...

VBEの参照設定で，Microsoft OLE DB Service Componinte 1.0 Type Library を参照するように設定します。これでMSDASCというライブラリが使えるようになります。そこにあるDataLinksオブジェクトでデータリンクプロパティダイアログが利用できます。

DataLinksオブジェクト

DataLinksオブジェクトのPromptNewメソッドは，データリンクプロパティダイアログを表示して，設定された情報を元にConnectionオブジェクトを返します。ダイアログでキャンセルボタンがクリックされた場合は，Nothingを返します。

Public Function ShowDataLinkPropertyDialog() As String
    Dim msd As MSDASC.DataLinks
    Dim con As Connection
 
    Set msd = New MSDASC.DataLinks
    Set con = msd.PromptNew
    If con Is Nothing Then
        ShowDataLinkPropertyDialog = ""
    Else
        ShowDataLinkPropertyDialog = con.ConnectionString
    End If
    Set con = Nothing
End Function

上のコードは，データリンクプロパティダイアログを表示して，接続文字列を返す関数です。

既にあるConnectionの接続文字列を編集する場合はPromptEditメソッドを使います。PromptEditメソッドにはConnectionオブジェクトを渡します。戻り値はOKボタンがクリックされた場合にはTrueが返されます。

Public Function ShowDataLinkPropertyDialog2() As String
    Dim msd As MSDASC.DataLinks
    Dim con As Connection
 
    Set msd = New MSDASC.DataLinks
    Set con = New Connection
    con.ConnectionString = "Provider=SQLNCLI;"
    If msd.PromptEdit(con) Then
        ShowDataLinkPropertyDialog2 = con.ConnectionString
    Else
        ShowDataLinkPropertyDialog2 = ""
    End If
    Set con = Nothing
End Function

上の例は，同様の処理をPromptEditメソッドを使って書いたものです。前の例と動作はほぼ同じですが，違うところがひとつあります。

PromptNewメソッドは新しく接続文字列を作るだけあって，最初に表示されるのはプロバイダのタブですが，

PromptEditの場合は，接続のタブが表示されます。

それぞれのメソッドはこの特徴を踏まえて場面にあわせて使うと良いでしょう。2つめのソースでは，あらかじめプロバイダにSQL Native Client(SQLNCLI)を設定してから，PromptEditを呼び出しています。

トラックバック

この記事のトラックバックURL:

URL から "-MoIyadayo" を削除してトラックバックを送信してください。

トラックバックは承認後に表示されます。

DataBase Tips

SQL Server 2005からリンクテーブルでOracleに接続する

サーバーにOracleのクライアントをインストールする

リンクサーバーの作成

リンクサーバー

サーバーの種類

プロバイダ

リンクサーバーのテーブルを見る

シノニムの作成

Access2003のADPプロジェクトで参照する

トラックバック

SQL Server 2005のデータベースコピーウィザードを使う

各DBサーバへの接続

転送方法の選択

ファイル共有の設定

ログインとユーザー

まとめ

トラックバック

データベースダイアグラムでエラー発生

トラックバック

ER図の論理名をコメントに設定する

トラックバック

A5:SQLでOracleのビューのソースを得る

トラックバック

A5:SQLでINSERT文の雛形を作る

トラックバック

Oracle のViewのソースを取得する

トラックバック

SQL Server で文字列の半角／全角変換

SQLCLR構成オプションの有効化

DLLの作成

ストアド関数の作成

関数を使う

半角に変換

全角に変換

トラックバック

VBAからデータリンク プロパティ ダイアログを表示する

参照設定で...

DataLinksオブジェクト

トラックバック

VBAからデータリンクプロパティダイアログを表示する