SSDT(SQL Server Data Tools) スキーマの比較でエラー 「比較が制限されています」

SSDTのスキーマ比較でトラブル。エラーの詳細は

 「データベースに対する View Definition アクセス許可がないため、リバース エンジニアリング操作を続行できません。」

解決方法は

データベースの「セキュリティ」→「ログイン」で対象のアカウントで右クリック→「プロパティ」→「セキュリティ保護可能なリソース」で「定義の表示」に許可をチェック

（参考）

SQL Server リンクサーバーを使って ACCESS accdb にデータをコピー

• アクセス側からデータを引っ張る
• SQL Server 側からデータを送る
• アプリケーションでSQL Server からデータを引っ張りアクセスに送る

など色々と方法は考えられるかと思いますが、そのうちの一例「SQL Server から accdb にリンクサーバーで接続し、データを出力する方法」をメモしておきます。

構成

• Windows Server 2019 64bit
• SQL Server Web 2019 64bit
• IIS 10

開発環境は

• Windows 10 Pro 64bit
• SQL Server 2019 Express 64bit
• Visual Studio 2019
• Office 2019 32bit

初めに空のaccdbファイルを作成して必要な場所に配置しておくと良いです。ACCESSのインストールされていない環境でもaccdbファイルを作成することが出来るようですが、詳細は検索してください。

64bit SQL Server からACCESS accdbファイルに接続するには 64bit 版の Microsoft.ACE.OLEDB が必要です。未インストールの場合は以下から入手できます。（ただし、64bit版と32bit版の共存にはトラブルがあるようで、既に32bit版Officeの入っていた私の開発環境ではACCESSが起動しなくなってしまいました。）

Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント

https://www.microsoft.com/ja-JP/download/details.aspx?id=54920

インストールに成功するとSSMSのサーバーオブジェクト→リンクサーバー→プロバイダーから確認できます。

「リンクサーバー」右クリック→「新しいリンクサーバー」でウィザードが表示され設定できます。

以降は以下のような形でSSMSからリンクサーバーにクエリを投げることができます。

EXEC ('CREATE TABLE Sample (
	Id INTEGER NOT NULL,
	Name TEXT(50) NOT NULL,
	Email TEXT(50)
)') AT [LINKSERVERNAME];

表題のSQLサーバからACCESS accdbにデータを出力するためのクエリを作成しますが、ラクをするために事前に出力先のテーブルをSSMSのエクスポートウィザードで作成しておきます。もちろんCREATE文を書いて対応するテーブルを作成してもかまいません。

以下のストアドプロシージャを作成します。

USE [SampleDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spExportFromSQLtoACCDB 
AS
BEGIN
	SET NOCOUNT ON;

	DELETE FROM [LINKSERVERNAME]...[Sample];
	INSERT INTO [LINKSERVERNAME]...[Sample] SELECT * FROM SampleDB.dbo.Sample
END
GO

このストアドプロシージャを実行すれば SQL Server から accdb へのデータのエクスポートが実行できますが、アプリケーションから実行するにはパーミッションの設定が必要です。 今回の場合は ASP.NET から実行する形でしたので、IISの当該アプリケーションプールIDにaccdb ファイルへの変更権限を付与します。 アプリケーションプールIDはファイルの権限設定ウィンドウに出てこないので「IIS AppPool\DefaultAppPool」のような形で追加します。

以上メモまで。

SQL Server EXPRESS に (local) , localhost で接続できない

参考リンクだけメモ

SQL SERVER でデータベースのコピー

1.SQL Server インポートおよびエクスポートウィザード

SSMS（SQL Server Management System）から利用する場合、
データベースを右クリックし、[タスク] → [データのインポート] または [データのエクスポート] をクリック

MSDN：SQL Server インポートおよびエクスポート ウィザードを実行する

2.bcpユーティリティを使う

MSDN：bcp ユーティリティ

TechNet：bcp ユーティリティ

3.デタッチ→アタッチ

SSMSでデータベース名を右クリック→タスク→デタッチ
該当のデータベースのldfファイルとmdfファイルをコピーし移行先に配置。
移行先のSSMSでデータベースを右クリック→アタッチから配置したmdfファイルを選択

MicrosoftDocs：デタッチとアタッチを使用してデータベースを移動する方法

ちなみにオフラインにしてバックアップするやり方は問題が生じる可能性があるとのことです。
SQL Server でオフライン バックアップおよびオフライン リストアを行うと問題が出ることがある

4.バックアップ→リストア

SSMSでデータベース名を右クリック→タスク→バックアップからバックアップファイルを作成し、移行先に配置。
移行先のSSMSでデータベースを右クリック→「データベースの復元」または「ファイルおよびファイルグループの復元」を選択
配置したファイルを選択し復元する。
なお「オペレーティング システム エラー 5(アクセスが拒否されました。) 」が出てしまう場合
コンピュータの管理→サービス→当該のSQL SERVERを右クリック→プロパティ→ログオンタブでローカルシステムアカウントを選択

下記を参照するとVSS WRITERがローカルシステムアカウントを要求するのが原因っぽいですが、ローカルシステムアカウントのような強い権限を使用するのは推奨されなさそうです。
MSDN：Windows サービス アカウントと権限の構成

5.スクリプト作成→実行

SSMSでデータベース名を右クリック→タスク→スクリプトの生成→ウィザードを進めた先の詳細設定をクリック→
スクリプト作成の詳細オプションのスクリプトを作成するデータの種類を「スキーマのみ」から適宜「スキーマとデータ」に変更→
ファイル、クリップボードなど用途に応じた形式で保存し、コピー先のデータベース上でスクリプトを実行

スクリプトなので改変が容易などのメリットがありますが、データが大きいとスクリプトが実行できない場合があります。



他にも方法があるかもしれません。
用途や環境に応じて検討する必要がありそうです。
以上メモまで。

【Classic ASP】Identityの値を取得【SQL Server】

レコードをINSERTして自動採番されたIdentityを取得することはよくあります。

"INSERT INTO TABLE (NAME) VALUES ('test');  SELECT SCOPE_IDENTITY() AS ID;"

では上手くいきません。

ADODB.Recordset エラー '800a0cc1'

となってしまいます。

頭に"SET NOCOUNT ON;"をつけて

"SET NOCOUNT ON;INSERT INTO TABLE (NAME) VALUES ('test');  SELECT SCOPE_IDENTITY() AS ID;"

としましょう。
（久々にやったので忘れてました）

以上メモまで。

【SQL Server】テーブルとデータの複製【SSMS】

色々と方法はあるかと思いますが、SSMSを使って
データベース右クリック → 「タスク」 → 「スクリプトの生成」から行えます。








上記画面「詳細設定」を開きます。



「スクリプトを生成するデータの種類」で目的のものを選択します。
データを含める場合は「スキーマとデータ」を選択します。


テストデータ含めて別環境にコピーしたい時などは
これを利用すると便利です。

以上メモまで。

SQL Server 月日の取得

何月何日というデータを、例えば７月１日なら'0701'という形式で取得したいことがあります。

SQL Serverで行う場合何通りか方法があるかと思いますが、
いくつか調べた結果をメモしておきます。
（試したバージョンはSQL Server 2012、ロケールは日本）

DECLARE @d datetime
set @d = CONVERT(datetime,  '2014-07-01')

/* DATENAME */
select DATENAME(yyyy, @d )  -- 2014
select DATENAME(yy, @d )    -- 2014
select DATENAME(m, @d )     -- 07
select DATENAME(mm, @d )    -- 07
select DATENAME(d, @d )     -- 1
select DATENAME(dd, @d )    -- 1
select DATENAME(m, @d) + right('0' + DATENAME(d, @d), 2) -- 0701

/* CONVERT */
select CONVERT(nvarchar(10),@d,12)  -- 140701
select CONVERT(nvarchar(10),@d,112) -- 20140701
select CONVERT(nvarchar(10),@d,111) -- 2014/07/01
select right(CONVERT(nvarchar(10),@d,12),4) -- 0701

/* FORMAT */
select FORMAT(@d,'M')    -- 7月1日
select FORMAT(@d,'MM')   -- 07
select FORMAT(@d,'MMM')  -- 7
select FORMAT(@d,'MMMM') -- 7月
select FORMAT(@d,'d')    -- 2014/07/01
select FORMAT(@d,'dd')   -- 01
select FORMAT(@d,'ddd')  -- 火
select FORMAT(@d,'dddd') -- 火曜日
select FORMAT(@d,'MMdd') -- 0701

いかがでしょう。掲載したのはほんの一部ですが、予想と違う結果になっていないでしょうか。
DATENAMEの「m」が２ケタで「d」が１ケタだったり、
DATENAMEの「m」が２ケタで「mm」が２ケタだったり、

FORMATの「M」が7月1日になったり、

FORMATの「d」が2014/07/01になったり。

ちなみに、FORMAT関数は2008 R2以降となっていましたが、updateが当たってないのか

私の環境では2008 R2でもエラーになりました。


DATENAME関数
FORMAT関数
CONVERT関数

SQL Server ストアドプロシージャの結果セットはサブクエリにできない

タイトルの通りです。

ストアドプロシージャの結果セットをそのままサブクエリとして扱うことはできません。

ストアドプロシージャの結果セットに対し抽出したり、JOINをしたりするには
「一時テーブルに結果セットをINSERTしてから用いる」か
ストアドプロシージャの内容と同じものを「ユーザ定義関数」として作成する
などの方法を用いる必要があります。

ASP.NET MVC3 EF Modelからデータベースが作成できない

以下のサイトを参考にASP.NET MVC3 EF Code First を学習しています。

@IT ASP.NET MVC入門【バージョン3対応】

ところがModelからデータベースを作成するところで、以下のようなエラーが出てしまいつまづいてしまいました。

Model compatibility cannot be checked because the database does not contain model metadata. Ensure that IncludeMetadataConvention has been added to the DbModelBuilder conventions.

記事の中で

なお、本稿ではSQL Server Compact 4.0を前提としているので、プロバイダ名は「System.Data.SqlServerCe.4.0」としているが、従来のSQL Serverに接続するならば、「System.Data.SqlClient」とすればよい。

とあったので、

  <connectionStrings>
    <add name="MyMvcContext"
         connectionString="Data Source={servername};
                           Initial Catalog={dbname};
                           Integrated Security=True"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

web.configの記述を上記のようにしたのですが、
この時にデータベース名（上記{dbname}の部分）に
既存のデータベースを指定したのが問題だったようです。
未作成のデータベースを指定することで、解決致しました。

ただし、完全にCodeFirstでデータベースを作成できる場合は良いのですが、
別口からデータベースを操作したり、既存のデータベースとの連携だったり
下記[参考]にあるようにMembership Providerとの連携だったりを考えた場合に
ちゃんと動くのか心配です。
自分はまだ学習段階なのでおいおい解決することを期待・・・。

[参考]
EF 4.1 Code First and Existing Database and .NET Membership
mvc3 code-first error on sqlexpress

AccessmdbのSQLインジェクション対策

１つ前の書き込み「VBScript IsNullとかIsEmptyとかIsNumericとか」で、
「実際にはポストされていないデータをRequestで取得してしまった場合、その値はEmptyとなる」
また、「IsNumeric(Empty)はTrueになる」ということを確認しました。

Access MDB では、数値型を「'(シングルクォート)」で囲むことができないため、
クエリに数値型のパラメータを埋めこまなくてはならない場合、埋め込む値が
数値型であるかどうかを厳密にチェックする必要があります。
VBScriptのIsNumericだけでは意図したチェックにならない可能性があるので、
注意しなくてはなりません。

Webのようなインジェクションの恐れがある環境で、
データベースにmdbを用いないのが本来だとは思うのですが・・・。

SQL Server から CSV データのエクスポート

ハマったのでメモ

単純にテーブルをそのままCSVにエクスポートなら、コマンドプロンプトなりbatファイルなりで
bcpコマンドを叩けば成功した。

参考
bcp ユーティリティ


引数つきのストアドプロシージャの実行結果をエクスポートしようとした時に上手くいかなかった。
また、引数も動的に決定したいのでbcpコマンド自体をストアドに入れてしまって
ジョブで定期実行するなどしたい。

まずはmaster.dbo.xp_cmdshell（master.sys.xp_cmdshell?）を有効にする必要があるので
以下を実行する。

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

実行結果は以下の通り

構成オプション 'show advanced options' が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。
構成オプション 'xp_cmdshell' が 0 から 1 に変更されました。RECONFIGURE ステートメントを実行してインストールしてください。

これで、ストアドプロシージャ内で以下のような感じでbcpコマンドが使えるハズです。

EXECUTE xp_cmdshell 'bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -c'

EXECUTE xp_cmdshell 'bcp "SELECT Name FROM AdventureWorks2008R2.Sales.Currency" queryout Currency.Name.dat -T -c'

SQL Server 日毎、月毎の集計

datetime型のフィールドで集計しようとした時に、
YEAR,MONTH,DAY関数は用意されていますが、
"yyyy/mm/dd" や"yyyy/mm"を返す関数は用意されていないので、

/月毎/
SELECT
 YEAR(日付) AS 年,
 MONTH(日付) AS 月,
 COUNT(*) AS 計数
FROM TABLE
GROUP BY YEAR(日付), MONTH(日付) 

とか

/月毎/
SELECT
 CAST(YEAR(日付) AS VARCHAR) + '/' + CAST(MONTH(日付) AS VARCHAR) AS 年月,
 COUNT(*) AS 計数
FROM TABLE
GROUP BY CAST(YEAR(日付) AS VARCHAR) + '/' + CAST(MONTH(日付) AS VARCHAR)

とかしないといけません。
こんな時はCONVERT関数を使った方がスマートです。

/日毎/
SELECT
 CONVERT(VARCHAR, 日付, 111) AS 年月日,
 COUNT(*) AS 計数
FROM TABLE
GROUP BY CONVERT(VARCHAR, 日付, 111)

/月毎/
SELECT
 SUBSTRING(CONVERT(VARCHAR, 日付, 111),1,7) AS 年月,
 COUNT(*) AS 計数
FROM TABLE
GROUP BY SUBSTRING(CONVERT(VARCHAR, 日付, 111),1,7) AS 年月

参考
CAST および CONVERT
日付と時刻のデータ型および関数
SQL-Sqrverで月ごとにグルーピングして集計したい

SQL Server への CSV データのインポート

CSVファイルのデータをSQL Serverへ取り込むといった要望は結構あるのではないかと思います。
例えば商品データベースをバッチで更新するのにCSVファイルをアップロードするような場合です。

Integration Services を利用する。
bcp コマンドを利用する。
などの方法がありますが、BULK INSERT を用いた方法をメモしておきます。

例として以下のような商品テーブルを考えます。

[T_商品]

製品番号 nvarchar(50)
商品名 nvarchar(50)
価格 int

◆インポートしたいデータがカンマやタブで区切られている場合

[インポートデータ1.csv]

1234-5678,フォーク,120
9876-5432,スプーン,100

以下のような式でインポートすることができます。

BULK INSERT T_商品 FROM 'C:\インポートデータ1.csv'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);

データがタブ区切りの場合「FIELDTERMINATOR = '\t'」を指定します。


◆インポートしたいデータ内に区切り文字が含まれテキスト修飾子がある場合

[インポートデータ2.csv]

"1234-56789","おもちゃA 対象年齢2,3才",1200
"9876-54321","おもちゃB 付属品C,D",1000

このようなデータを前述のようにカンマ区切りで単純にインポートしたのでは
フィールドに含まれるカンマでも区切ってしまいエラーとなります。
この場合、フォーマットファイルというものを別途作成します。

[フォーマットファイル.xml]

<?xml version="1.0"?>
<BCPFORMAT 
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="2" NAME="number" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="3" NAME="name" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="4" NAME="price" xsi:type="SQLINT"/>
  </ROW>
</BCPFORMAT>

<RECORD>に各<FIELD>をどこで区切るかを記述します。
そして<ROW>にはどのデータ（ID）がインポート先のどのフィールドに合致するかを記述します。
ただし<COLUMN>のNAME属性の値はデータベースのフィールド名と一致しなくても構いません。


この場合に記述すべきSQL文は以下のとおりです。

BULK INSERT T_商品
FROM 'C:\インポートデータ2.csv'
WITH (FORMATFILE='C:\フォーマットファイル.xml');

TRUNCATE TABLE T_商品;

などと組み合わせてストアドプロシージャに記述し、ジョブに登録するなどして
定期的にデータのリフレッシュなどに利用できます。



[参考]
一括インポート操作と一括エクスポート操作について

BULK INSERT (Transact-SQL)

XML フォーマット ファイルのスキーマ構文

SCOPE_IDENTITY()の型はDecimal

表題の通り(C#)

SqlConnection con = new SqlConnection();
SqlCommand cmd
 = new SqlCommand("INSERT INTO ...(略); SELECT SCOPE_IDENTITY();", con);
int i = (int)cmd.ExecuteScalar();
con.Close();

実際にはobject型で返ってくるのだが、内部的にdecimalなのでそのままintにキャストしようとすると
エラーとなってしまう。

こんな感じか。

int i = (int)(decimal)cmd.ExecuteScalar();

App_Data フォルダ & SQL Server Express

Visual Studio 2008 でWebサイトを開発時、
App_Data フォルダに「右クリック」→「新しい項目の追加」で
SQL Server データベースを追加しようとしたところ以下のエラーが出た。

管理者以外のユーザーのためのユーザー インスタンス

App_Data に配置できるのはSQL Server Express だけのようです。
開発環境から本番環境への移行が簡単だっただけに、少し残念。

SQL Server 2005 Express Edition ではジョブの作成ができない

orz...