linq SQL2005のDateTimeでDelete

Linq To SQLは便利だけど、SQL2005でDateTime型を利用時に
DELETEを行ったが、SQL2008のDate型と見なしてエラーになった。

解決方法が良く分からないので、直接ＳＱＬを発行する方法にした。

ExecuteCommandメソッドで削除

void ExecuteCommandメソッドで削除()
{ db.ExecuteCommand( @"delete from Employee where EmployeeId = {0}", 1); }

後日、DataContentのデータ定義が、Date型になっていたのが原因でした。 データ定義をDateTimeに変更したら、linqの削除がうまく実行出来ました。

SQLServer 2005 split 関数 独自版

＊＊＊＊＊＊＊＊＊　以下をそのまま実行　＊＊＊＊＊＊＊＊＊＊ create function [dbo].[split](@str as varchar(1000), @kugiri as char(1), @idx as int) returns varchar(1000) as begin declare @result varchar(1000) declare @loop int set @loop = 0 while (@loop < @idx) begin select @result = case when charindex(@kugiri, rtrim(@str)) > 0 then substring(rtrim(@str), 1, charindex(@kugiri, rtrim(@str)) -1) else rtrim(@str) end select @str = case when charindex(@kugiri, @str) > 0 then substring(@str, charindex(@kugiri, @str)+1, 1000) else null end set @loop = @loop + 1; end return @result end --使用例 -- select dbo.split('aaazbbbzccc', 'z', 2) --結果 -- bbb ＊＊＊＊＊＊＊＊＊　以上、ここまで　＊＊＊＊＊＊＊＊＊＊ 元ネタ http://blog.livedoor.jp/hashikure3/archives/20478.html

SQL階層の実行で、以下のエラーが表示された。

Types don't match between the anchor and the recursive part in column "ColumnName" of recursive
query "CTE_Name"

通常は、項目属性をCASTで統一すればＯＫだが
データベースの参照順位がBIN2になっていなかった為に、SQLは問題ないけど
エラー表示されるようだ。

データベース参照順位を、Japaness90BIN2にしたら、ＯＫになった。

MS-SQLで階層を表現する

LevelPath順に表示したい場合
ItemNo＋LevelのItemNoを追加した
ただし、ItemNoが数字属性なので、すべてにおいてCASTしないと
下記のエラーが表示される
Types don't match between the anchor and the recursive part in column "ColumnName" of recursive
query "CTE_Name"


WITH BOM(Lv1, ItemNO, ChildPN, ChildPartName, Quantity,
ParentPN, LevelPath)
AS
(SELECT 1 AS Lv1, ItemNo, ChildPN, PartName, Quantity, ParentPN, CAST('/' + RIGHT('000' + CAST(ItemNo as varchar(256)),3) as varchar(256)) as LevelPath
FROM UP_BOM
INNER JOIN UP_Item I1 ON i1.PartNo = ChildPN
WHERE ParentPN = 'J073-185 '
UNION ALL
SELECT h.Lv1 + 1, d.ItemNo, d.ChildPN, i2.PartName, d.Quantity, d.ParentPN, CAST((RTRIM(h.LevelPath) + '/' + RIGHT('000' + CAST(d.ItemNo as varchar(256)),3)) as varchar(256)) as levelPath
FROM UP_BOM d
INNER JOIN BOM h ON h.ChildPN = d.ParentPN
INNER JOIN UP_Item I2 ON i2.PartNo = d.ChildPN)
SELECT Lv1, ItemNo, ChildPN, ChildPartName, Quantity, ParentPN, LevelPath
FROM BOM
ORDER By LevelPath

MS-SQLで階層を表現する

with c as (
select '/'+cast(billID as varchar(49)) as path,BillID
from dbo.bill b
where b.parentBillID is null
union all
select cast(c.path+'/'+CAST(b.billID as varchar(4)) as varchar(50)), b.billID
from dbo.bill b join c on b.parentBillID = c.billID)
select c.path+'/',b.descr
from c join dbo.bill b on c.billID = b.billID
order by 1;

MS-SQLの階層にRowNumみたいな順番が無いので、一番最初の項目にlevelを表現している。

SQL Audit

MS-SQLで監査用の履歴を作成する

http://sourceforge.net/projects/sqlaudit/

MS-SQL 接続ユーザーの取得

MS-SQLで接続ユーザーの取得する場合

SELECT SYSTEM_USER

確認Ver SQL2005

MS-SQLで、メタ情報からCREATE TABLEを作成

MS-SQL2005以上で使用出来る

DECLARE @Table_Definition VARCHAR(MAX),
@TableName VARCHAR(25)

SET @TableName = 'Attachments'

SET @Table_Definition = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ' +
QUOTENAME(@TableName,'''') + ')' + CHAR(13) + 'BEGIN'

SET @Table_Definition = COALESCE(@Table_Definition + CHAR(13),'') + 'CREATE TABLE ('

SELECT @Table_Definition = COALESCE(@Table_Definition,'') +
CHAR(13) + '[' + COLUMN_NAME + ']' + SPACE(1) + '[' + DATA_TYPE + ']' +
CASE
WHEN UPPER(DATA_TYPE) IN ('REAL','MONEY','DECIMAL','NUMERIC') THEN
'(' + CONVERT(VARCHAR(3),COALESCE(numeric_precision,'')) + ','+
CONVERT(VARCHAR(3),COALESCE(Numeric_Scale,'')) + ')'
WHEN UPPER(DATA_TYPE) IN ('CHAR','NVARCHAR','VARCHAR','NCHAR') THEN
'(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'max' ELSE CAST(isnull(CHARACTER_MAXIMUM_LENGTH,'') AS VARCHAR) END + ')'
ELSE '' END +
CASE WHEN IS_NULLABLE = 'NO' THEN ' not null' ELSE ' null' END +
CASE WHEN COLUMN_DEFAULT IS NULL THEN ',' ELSE 'default ' + COLUMN_DEFAULT + ',' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName

SET @Table_Definition = STUFF(@Table_Definition,LEN(@Table_Definition),1,CHAR(13) + ')' + CHAR(13) + 'END')

PRINT @Table_Definition

--EXEC (@Table_Definition)

MS-SQL でOracleのUser_tab_columnsみたいなメタ情報を参照するには？

MS-SQL でOracleのUser_tab_columnsみたいなメタ情報を参照するには？
MS-SQL 2005以降だけど、INFORMATION_SCHEMA.COLUMNS　で参照出来ます。

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

select * from INFORMATION_SCHEMA.COLUMNS

MS-SQL2005 DTS 単体実行はＯＫだが、SQL Agentにするとエラーになる

MS-SQL2005のDTSを単体テストでは、ＯＫ。
これをファイルベースで、MS-SQL2005のエージェント(Agent)で実行すると
パスワードの暗号化が解除出来ない為、エラーになる。

解決方法
http://support.microsoft.com/kb/918760/ja
1.パスワードを使用して暗号化するパッケージを変更します。
2.EncryptSensitiveWithUserKey に ProtectionLevel プロパティを変更するのに
オペレーティング システム (cmd Exec) SQL Server エージェント ジョブのステップで
Dtutil.exe ユーティリティを使用します。
　このプロセスは、パスワードを使用して、パッケージを re-encrypting し、
　パッケージを暗号化解除では。
パッケージの暗号化に使用されるユーザー キーは、SQL Server エージェント ジョブの
　ステップを 別のユーザーとして実行 ] 一覧で設定です。

Dtutil.exeを実行して暗号化を解除するそうです。