PageLastUpdate:2017-08-08/today: - /yesterday: -
2008/05/12 検索語：アクセス、DAO、ADO、動かない、実行されない
別にバグでもなんでもないですが、落とし穴としてメモ。
「Accessから更新するとトリガが動いてない！」という報告を受けてリンクテーブルでテストしたけど・・・動いてる。
Accessからだと動かないって理由はないだろう・・・としばらく考えていて、トリガの先頭の
IF UPDATE(msrepl_tran_version) RETURN
以外にありえないと気がついた。
ソースを見てみるとご丁寧にmsrepl_tran_versionも含めて全フィールドに値を書き込んでくれてます。
確かにレプリケート自体レアな仕様だから、まあ気がつかないかもね。
でも書き込む必要のないフィールドに値を入れるのはそもそも冗長なのでダメですね。
こういうのを開発チームに徹底するのって大変。
2008/04/25
同じ列名のローマ字綴りがテーブルによってズレてるのを見つけてへこむ。
間違いやすい綴りについてチェックするために、列名検索を作成。
cyu→chuとかjyu→juとか、20個近くありましたorz
SELECT sysobjects.name AS TableName , syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN sysobjects
ON sysobjects.id=syscolumns.id
WHERE sysobjects.xtype = 'U'
AND syscolumns.name LIKE '%tyo%'
2008/04/25
ユーザーが一時使用するテーブルに対してデータをDELETEではなくTRUNCATE TABLEで消したいとする。
(多量のデータを一括して消したいとき、TRUNCATE TABLEはDELETEより効率がよい。)
このときテーブルにDELETE権限を与えても、ストアドなどにEXECUTE権限を与えてTRUNCATE TABLEしようとしても成功しない。
テーブルをユーザーの所有にすればTRUNCATE TABLEが使えるようになる。
2008/04/25
SQLServerはユーザー名に\を含めることができる。
例えば別ドメインのユーザーとしてDomainName\UserNameのような表記があり得る。
しかしこのテーブルはAcesssからリンクできない。
2008/03/19
AccessからリンクしたテーブルにINSTEAD OFトリガを設定したが、テーブルを結合して更新をかけようとするとエラー。
特定条件に一致する件数のカウントのためにトリガの中でSELECTを書いたのが原因。
件数カウントは必須なので
SELECT ... INTO #DUMMY
FROM ...
IF @@ROWCOUNT > 0
BEGIN ...
のようにしたら解決。えー。
CREATE PROCEDURE [dbo].[Select_LockStates] AS
CREATE TABLE #sp_lock (
spid nvarchar(64)
,dbid nvarchar(64)
,PbjID nvarchar(64)
,IndID nvarchar(64)
,TYPE nvarchar(64)
,Resource nvarchar(64)
,Mode nvarchar(64)
,STATUS nvarchar(64)
)
INSERT INTO #sp_lock EXEC sp_lock
CREATE TABLE #sp_who (
spid nvarchar(64)
,ecid nvarchar(64)
,STATUS nvarchar(64)
,loginname nvarchar(64)
,hostname nvarchar(64)
,blk nvarchar(64)
,dbname nvarchar(64)
,cmd nvarchar(64)
)
INSERT INTO #sp_who EXEC sp_who
SELECT *
FROM #sp_who
INNER JONI #sp_lock
ON #sp_who.spid=#sp_lock.spid
GO
LEFT CONVERT(VARCHAR(2), moji)
RIGHT REVERSE(CONVERT(VARCHAR(4), REVERSE(moji)))
2バイト文字列が分割されるとどうなるのかなぁ。要検証。
検証してみた
DECLARE @moji VARCHAR(30)
DECLARE @one VARCHAR(1)
DECLARE @three VARCHAR(3)
SET @moji ='綺麗'
SELECT @one = CONVERT(VARCHAR(1), @moji)
SELECT @three = CONVERT(VARCHAR(3), @moji)
SELECT '基本文字列' AS TestCase , @moji AS String , DATALENGTH(CONVERT(VARCHAR(30), @moji)) AS Byte
UNION
SELECT '左から1バイト取得',@one,datalength(@one)
UNION
SELECT '左から3バイト取得',@three,datalength(@three)
文字の途中で切れることもなく、綺麗にとってくれます。
| TestCase |
String |
Byte |
| 基本文字列 |
綺麗 |
4 |
| 左から1バイト取得 |
|
0 |
| 左から3バイト取得 |
綺 |
2 |
2007/06/12
レプリケート対象となったテーブルは、自分自身に対してmsrepl_tran_versionへのアップデートをかける。
したがって、トリガを作成する場合は
if update (msrepl_tran_version) return
を入れないと、トリガが複数回起動されるので注意。
ただし、INSTED OF トリガに関してはこの行は不要。
逆に設定すると動作しなくなるので注意。
2007/06/07
CREATE TABLE #temp (
TABLE_NAME sysname,
ROWS sysname,
reserved sysname,
DATA VARCHAR(32),
index_size VARCHAR(254),
unused VARCHAR(100)
)
INSERT INTO #temp EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"
SELECT * FROM #temp ORDER BY TABLE_NAME
ここで使われている sp_MSforeachtable はヘルプに乗っていない。
「
アンドキュメンテッド ストアドプロシージャ」で検索をかけるといろいろ出てきます。
他には sp_MSforeachdb とか便利そう。
別回：件数だけならこれでもいい
SELECT o.name, i.rows
FROM sysindexes AS i, sysobjects AS o
WHERE o.xtype = 'U' AND o.id = i.id AND i.indid < 2;
まあ一番シンプルに書くとこんな感じ。引数はこれ以上は省略できない。
RAISERROR ('ここにメッセージ',0,1)
RETURN
2007/05/22
--こんな風に定義して
CREATE PROCEDURE [dbo].[GetNextID] (@KEY INT,@NEXTID INT OUTPUT) AS
UPDATE T_COUNTER SET F_ID = F_ID +1 WHERE F_KEY = @KEY
SELECT @NEXTID = F_ID FROM T_COUNTER WHERE F_KEY = @KEY
--こんな風に使う。
DECLARE @NEXTID INT
EXEC dbo.GetNextID 22,@NEXTID OUTPUT
PRINT @NEXTID
レコードに対する処理をしない場合はユーザー定義関数のほうが適切。
2007/05/18 検索語：うまくいかない/値がとれない
どちらも間にIFとかPRINTを挟むとクリアされてしまうので注意。
BEGIN TRANSACTION
DECLARE @RC INT
DECLARE @ERR INT
UPDATE ...
SELECT @RC=@@ROWCOUNT,@ERR=@@ERROR --必ず実行した直後に
IF @ERR<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @ERR
END
IF @RC=0
BEGIN
INSERT ...
SELECT @RC=@@ROWCOUNT,@ERR=@@ERROR --必ず実行した直後に
IF @ERR<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @ERR
END
END
COMMIT TRANSACTION
DECLARE @RESULT INT
SELECT @RESULT = myField FROM myTable
PRINT @RESULT
のようにして取り出せる。複数レコードだとどうなるんだろう。あとでテストしよう。
DECLARE @RESULT nvarchar(1024)
EXECUTE sp_executesql N'SELECT @Result = ...', N'@Result nvarchar(1024) OUTPUT', @RESULT OUTPUT
PRINT @RESULT
ユーザー定義関数を文字列で与えて結果を返せるか？
上のsp_executeを利用して、fn_getresult('FunctionName')みたいに、ユーザー定義関数を文字で指定して結果を返すユーザー定義関数がつくれるかもと思ったけど、ユーザー定義関数の中でsp_executeがそもそも利用できませんでした。
もしできるなら誰か教えてください。
DECLARE myCursor CURSOR FOR
SELECT myField FROM myTable
OPEN myCursor
FETCH NEXT FROM myCursor --← これを知らなくてハマった。@@FETCH_STATUSはOPEN直後が0であるとは限らないので、かならず1回実行してからLOOPに入ること。
WHILE @@FETCH_STATUS = 0
BEGIN
--処理
FETCH NEXT FROM myCursor
END
CLOSE myCursor
DEALLOCATE myCursor
所有権の継承の使用
EXECUTE
...権限
ストアド プロシージャの EXECUTE 権限は、特に指定のない限りストアド プロシージャの所有者に与えられます。EXECUTE 文字列内でステートメントを使用する権限は、そのステートメントがストアド プロシージャ内に含まれている場合でも、EXECUTE の実行直前にチェックされます。文字列を実行するストアド プロシージャが実行されるとき、権限は、プロシージャを作成したユーザーのコンテキストではなく、プロシージャを実行しているユーザーのコンテキストでチェックされます。しかし、ユーザーが 2 つのストアド プロシージャを所有しており、最初のプロシージャが 2 番目のプロシージャを呼び出すような場合、2 番目のストアド プロシージャに対して EXECUTE 権限がチェックされることはありません。
2007/05/16
トリガ中のCREATEA TABLEで「NULL可」を明示的に指定しなかったことでエラー。
規定ではNULL可で作成されるはずだが、明示したほうがよい。
comment
このページの記述で聞きたいこととか間違ってることとかありましたらコメントを。
