ラベル SQLServer の投稿を表示しています。すべての投稿を表示

2021年11月25日木曜日

[SQLServer]エラー「プールから接続を取得する前にタイムアウト期間が過ぎました。プールされた接続がすべて使用中で、プールサイズの制限値に達した可能性があります」

ある時いきなり以下のエラーが出て、その後同じアプリケーションプールを使っているシステムがすべてタイムアウトになった

プールされた接続がすべて使用中で、プールサイズの制限値に達した可能性があります

調べると１つのアプリケーションプールがＤＢに接続する際に使用するプール数ＭＡＸはデフォルト１００。

これを超えた場合、上記のような現象になる。

プール数が異常に溜まる場合は基本的にはプログラムが原因で、

きちんとDBをクローズしないようなプログラムを書いていると処理が終わった後もプールが４分～１０分残り続ける。

その間に溜まりに溜まって100以上になるとこの現象になる。

プログラムの原因は別途調べるとして

ひとまず現状を回復したい場合は web.configにてMaxPool数をデフォルトの100から200等にUPすればよい。

デメリットとしては永遠に増え続けた場合。メモリを大量に使うことになる懸念がある。

現在のプール数を調べるSQL文は以下（sa権限が必要）

--①hostname内訳
SELECT DB_NAME(sP.dbid) AS the_database,hostname
, COUNT(sP.spid) AS total_database_connections
FROM sys.sysprocesses sP
--where DB_NAME(dbid) = 'ＸＸＸＸDB'　--DB名で絞りたい場合
--and hostname in ('webサーバ名で絞りたい場合')
GROUP BY DB_NAME(sP.dbid),hostname
ORDER BY 1;

--②DB別のsleepコネクション数の分布をチェック
SELECT spid,DB_NAME(sP.dbid) AS the_database , hostname AS host, *
FROM sys.sysprocesses sP
--WHERE DB_NAME(sP.dbid) = 'ＸＸＸＸDB'　--DB名で絞りたい場合
--and hostname in ('webサーバ名で絞りたい場合')
WHERE status = 'sleeping'
ORDER BY hostname,login_time;

--③さらに詳細が知りたい場合
Select text,* from master.dbo.sysprocesses
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS qt
where
--spid = *** -- ②で取得したsession_idをここにセット
spid IN (267,
271,
264,
245,
119
)

上記ＳＱＬ文で、溜まっているＳＱＬを割り出し、そこから該当のプログラムを探し出すといいかも

2016年3月4日金曜日

[SQL]SQLServerにて一時保存テーブルを扱う。

一瞬だけ取り込んで加工したい場合…
一時保存テーブルですが
きちんと消えているかはちゃんと確認したほうがいいです。
（念の為一応前後にDROP句をつけています）


---------------------------------------------

BEGIN TRAN TEST;

--ワークの事前削除
IF OBJECT_ID('tempdb..#TMP_取込ワーク') IS NOT NULL DROP TABLE #TMP_取込ワーク;

   --ワークの生成
CREATE TABLE #TMP_取込ワーク
(Folder1 varchar(100),
Folder2 varchar(100),
Folder3 varchar(100),
Folder4 varchar(100),
Folder5 varchar(100),
Folder6 varchar(100),
Level varchar(100),
Size varchar(100),
Percentage varchar(100));

   --ここを取り込みプログラムにしてみたり
INSERT INTO #TMP_取込ワーク VALUES
( '24245' ,'1503AK1502D','仕様書.xls' , NULL, NULL, NULL,NULL,'1',NULL );
INSERT INTO #TMP_取込ワーク VALUES
( '24245' ,'1403A44674D','お見積り.xls' , NULL, NULL, NULL,NULL,'1',NULL );

--できたデータをここで加工したり
  SELECT *
FROM #TMP_取込ワーク

--一応終わったら消す
DROP TABLE #TMP_取込ワーク

--ROLLBACK TRAN TEST;
COMMIT TRAN TEST;

---------------------------------------------

2015年6月17日水曜日

[その他]SQLServerとExcelマクロを使ってリバースER図を作成する。（エンティティのみ）

①まずは以下のSQL文を↓SQLServer上指定のDB上にて実行しましょう

SELECT
object_name(sys.columns.object_id),
sys.columns.column_id,
sys.columns.name,
sys.indexes.name AS INDEX_NAME,
sys.indexes.is_primary_key AS TEST

FROM
sys.columns inner join sys.objects on
sys.columns.object_id = sys.objects.object_id

LEFT JOIN
sys.index_columns
on sys.columns.column_id = sys.index_columns.column_id
and sys.columns.object_id = sys.index_columns.object_id

LEFT JOIN sys.indexes
on sys.indexes.object_id = sys.index_columns.object_id

where

ISNULL(sys.indexes.is_primary_key ,-1) IN (-1,1)
and
sys.objects.type = 'U'

--もしER図に出したくないカラムがある場合はここで指定

--and
--sys.columns.name NOT IN ( --'created',
--'テスト項目',
--'削除フラグ'
--)

--テーブルしぼるならここで！

--and
--LEFT(object_name(sys.columns.object_id),8) IN (
--'T_氏名'
--'T_給与'
--)

order by
object_name(sys.columns.object_id),
column_id

②出力された結果をエクセルに貼り付けて以下の様にします
　（必ずＡ１セルから貼り付けてください）

③貼り付けたシート上で以下のＶＢＡマクロを実行します。

Dim WB As Workbook
Set WB = ActiveWorkbook
Dim WS1 As Worksheet
Set WS1 = WB.ActiveSheet

WS1.Select
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select

'--------------------------------------------------------
'実行速度向上のため画面更新と自動計算を停止
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'-----------------------------------------------------------

'現在選択しているセルの取得
Dim intStartGyo As Integer: intStartGyo = Selection.Row
Dim intLastGyo As Integer: intLastGyo = Selection.Row + Selection.Rows.Count - 1
Dim intStartClm As Integer: intStartClm = Selection.Column
Dim intLastClm As Integer: intLastClm = Selection.Column + Selection.Columns.Count - 1

'※初期置
Dim Rng As Range
Set Rng = WS1.Cells(intStartGyo, intStartClm)

Dim lWidth As Long: lWidth = 150 'エンティティオブジェクトのサイズ
Dim lHeight As Long: lHeight = 14 'エンティティオブジェクトのサイズ

lLeft = Rng.Left + 350 '初期位置
Ltop = Rng.Top + 10 '初期位置

Dim TblCnt As Integer: TblCnt = 0
Dim TblTitle As String
Dim TblStartTop As Integer
Dim ECnt As Integer
Dim strShapes As String
Dim FlgKey As Boolean

For j = intStartGyo To intLastGyo

'テーブルカウント
If TblTitle <> WS1.Cells(j, intStartClm).Text Then
TblCnt = TblCnt + 1
TblTitle = WS1.Cells(j, intStartClm).Text
ECnt = 0
strShapes = ""
Ltop = WS1.Cells(j + 1, intStartClm).Top + 10
TblStartTop = Ltop
FlgKey = True
End If

'エンティティの作成---------------------------------------

Dim myShape1 As Shape

'主キーからそうでなくなった場合、線を足す
If WS1.Cells(j, intStartClm + 4).Text <> "1" Then
If FlgKey = True Then
Dim SShape As Shape
Set SShape = WS1.Shapes.AddConnector(msoConnectorStraight, lLeft, Ltop, lLeft + lWidth, Ltop)
strShapes = strShapes & "," & SShape.Name
FlgKey = False
End If
End If

Dim oShape As Shape
Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, Ltop, lWidth, lHeight)
With oShape
.Fill.Visible = msoFalse
.Fill.Solid 'グラデーション等無し
.Line.Visible = msoFalse
.TextFrame.Characters.Font.Color = vbBlack
If WS1.Cells(j, intStartClm + 4).Text <> "1" Then
.TextFrame.Characters.Text = WS1.Cells(j, intStartClm + 2).Text
Else
.TextFrame.Characters.Text = "■" & WS1.Cells(j, intStartClm + 2).Text
End If
.TextFrame.Characters.Font.Size = 8
.TextFrame.HorizontalAlignment = xlHAlignLeft
End With
strShapes = strShapes & "," & oShape.Name

'追加位置を下へずらす
Ltop = Ltop + lHeight
ECnt = ECnt + 1
'-------------------------------------------------------

'次が異なるテーブルor終了行---------------------------------
If Len(strShapes) > 0 Then
If WS1.Cells(j + 1, intStartClm).Text <> TblTitle Or j + 1 > intLastGyo Then

Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, TblStartTop - 20, lWidth, 30 + ECnt * (lHeight + 1))
With oShape
.Fill.Visible = msoFalse
.Fill.Solid 'グラデーション等無し
.Line.Visible = msoTrue
.TextFrame.Characters.Font.Color = vbBlue
.TextFrame.Characters.Text = TblTitle
.TextFrame.Characters.Font.Size = 8
.TextFrame.HorizontalAlignment = xlHAlignLeft
End With
'strShapes(ECnt) = oShape.Name
'ActiveSheet.Shapes.Range(Array("""" & oShape.Name & """" & strShapes)).Select
strShapes = strShapes & "," & oShape.Name

Dim valShapes() As String
valShapes = Split(strShapes, ",")

'oShape.Select
'Selection.ShapeRange.Group.Select
WS1.Shapes.Range(valShapes).Group

'追加位置を右へずらす
'lLeft = lLeft + lWidth + 5
'追加位置を下へずらす
Ltop = Ltop + 30 + ECnt * (lHeight + 1) + 10

End If
End If
'------------------------------------------------------------

Next

'実行速度向上のため画面更新と自動計算を再開---------------
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'-----------------------------------------------------------

MsgBox ("作成成功")

End Sub

④以下の様にER図用のエンティティ図形が出来ます。

2015年6月3日水曜日

[その他]MK-2からコピーしたスキーマ情報からER図用のエンティティオブジェクトを作成するマクロ

Sub MR2ERtoExcelER()

MsgBox "※変換したい情報を全部選択しておく必要があります※"

Dim WB As Workbook
Set WB = ActiveWorkbook
Dim WS1 As Worksheet
Set WS1 = WB.ActiveSheet

'--------------------------------------------------------
'実行速度向上のため画面更新と自動計算を停止
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'-----------------------------------------------------------

'現在選択しているセルの取得
Dim intStartGyo As Integer: intStartGyo = Selection.Row
Dim intLastGyo As Integer: intLastGyo = Selection.Row + Selection.Rows.Count - 1
Dim intStartClm As Integer: intStartClm = Selection.Column
Dim intLastClm As Integer: intLastClm = Selection.Column + Selection.Columns.Count - 1

'※初期置
Dim Rng As Range
Set Rng = WS1.Cells(intStartGyo, intStartClm)

Dim lWidth As Long: lWidth = 150 'エンティティオブジェクトのサイズ
Dim lHeight As Long: lHeight = 14 'エンティティオブジェクトのサイズ

lLeft = Rng.Left + 350 '初期位置
Ltop = Rng.Top + 10 '初期位置

Dim TblCnt As Integer: TblCnt = 0
Dim TblTitle As String
Dim TblStartTop As Integer
Dim ECnt As Integer
Dim strShapes As String
Dim FlgKey As Boolean

For j = intStartGyo To intLastGyo

'テーブルカウント
If WS1.Cells(j, intStartClm).Text = "[Entity]" Then
TblCnt = TblCnt + 1
TblTitle = Replace(WS1.Cells(j + 1, intStartClm).Text, "PName=", "")
ECnt = 0
strShapes = ""
Ltop = WS1.Cells(j + 1, intStartClm).Top + 10
TblStartTop = Ltop
FlgKey = True
End If

'エンティティの作成---------------------------------------
If Left(WS1.Cells(j, intStartClm).Text, 6) = "Field=" Then

Dim myShape1 As Shape
Dim strText() As String
strText = Split(WS1.Cells(j, intStartClm).Text, ",")

'主キーからそうでなくなった場合、線を足す
If strText(4) = "" Then
If FlgKey = True Then
Dim SShape As Shape
Set SShape = WS1.Shapes.AddConnector(msoConnectorStraight, lLeft, Ltop, lLeft + lWidth, Ltop)
strShapes = strShapes & "," & SShape.Name
FlgKey = False
End If
End If

Dim oShape As Shape
Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, Ltop, lWidth, lHeight)
With oShape
.Fill.Visible = msoFalse
.Fill.Solid 'グラデーション等無し
.Line.Visible = msoFalse
.TextFrame.Characters.Font.Color = vbBlack
If strText(4) = "" Then
.TextFrame.Characters.Text = Replace(strText(1), """", "")
Else
.TextFrame.Characters.Text = "■" & Replace(strText(1), """", "")
End If
.TextFrame.Characters.Font.Size = 8
.TextFrame.HorizontalAlignment = xlHAlignLeft
End With
strShapes = strShapes & "," & oShape.Name

'追加位置を下へずらす
Ltop = Ltop + lHeight
ECnt = ECnt + 1
End If
'-------------------------------------------------------

'次が異なるテーブルor終了行---------------------------------
If Len(strShapes) > 0 Then
If WS1.Cells(j + 1, intStartClm).Text = "[Entity]" Or j + 1 = intLastGyo Then

Set oShape = WS1.Shapes.AddShape(msoShapeRectangle, lLeft, TblStartTop - 20, lWidth, 30 + ECnt * (lHeight + 1))
With oShape
.Fill.Visible = msoFalse
.Fill.Solid 'グラデーション等無し
.Line.Visible = msoTrue
.TextFrame.Characters.Font.Color = vbBlue
.TextFrame.Characters.Text = TblTitle
.TextFrame.Characters.Font.Size = 8
.TextFrame.HorizontalAlignment = xlHAlignLeft
End With
'strShapes(ECnt) = oShape.Name
'ActiveSheet.Shapes.Range(Array("""" & oShape.Name & """" & strShapes)).Select
strShapes = strShapes & "," & oShape.Name

Dim valShapes() As String
valShapes = Split(strShapes, ",")

'oShape.Select
'Selection.ShapeRange.Group.Select
WS1.Shapes.Range(valShapes).Group

'追加位置を右へずらす
'lLeft = lLeft + lWidth + 5
'追加位置を下へずらす
Ltop = Ltop + 30 + ECnt * (lHeight + 1) + 10

End If
End If
'------------------------------------------------------------

Next

'実行速度向上のため画面更新と自動計算を再開---------------
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'-----------------------------------------------------------

End Sub

2012年8月22日水曜日

[SQL]指定したグループ別に通し番号を振る。

よく忘れるシリーズ。

SQL Serverにて指定したグループで通し番号を振るには
ROW_NUMBER() を使用します。
OVER（）内でグループ単位を指定（複数もＯＫ）、通し順を指定します。

'---------------------------------------------

SELECT
ROW_NUMBER() OVER( PARTITION BY 市, 町 ORDER BY 引っ越して来た日 ) AS SEQ
名前, 市, 町, 引っ越して来た日
FROM
住民台帳
ORDER BY
市, 町, 引っ越して来た日

'---------------------------------------------

結果は

1,田中,広島市,南町,2012/01/01
2,佐藤,広島市,南町,2012/01/03
1,竹田,広島市,中町,2012/01/02
1,田中,福山市,北町,2012/01/02
2,田中,福山市,北町,2012/01/03

となります。

2012年6月21日木曜日

[SQL]SQL文での日付変換あれこれ。

From Evernote:

SQL文での日付変換あれこれ。

●yyyyMMdd（数値型）→Date型

---------------------------------------

SELECT CONVERT(datetime, CAST(CA.数値型 AS VARCHAR), 112) AS 日付型
FROM カレンダーテーブル AS CA

---------------------------------------

●Date型→yy/MM（文字列）

---------------------------------------

SELECT RIGHT(CONVERT(VARCHAR, CA.日付型,111),5) AS 文字列型

FROM カレンダーテーブル AS CA

---------------------------------------

2012年4月17日火曜日

[ACCESS→SQLServer]ACCESS内のリンクをVBAではりかえる(DNSver)。

From Evernote:

[ACCESS→SQLServer]ACCESS内のリンクをVBAではりかえる(DNSver)。

【追記※DNS方式ﾊﾞｰｼﾞｮﾝ】--------------------------------------------

この方法はDNS方式ﾊﾞｰｼﾞｮﾝです。

ローカルＰＣにきちんとODBC設定して行なって使用します。

DNS-LESSﾊﾞｰｼﾞｮﾝはこちら

http://shumitestitblog.blogspot.jp/2012/02/accesssqlserveraccessvba.html

'--------------------------------------------------------

Dim strDSN As String: strDSN = "systest.aa"

Dim strDB As String: strDB = "testDB"

Dim strUser As String: strUser = "user"

Dim strPass As String: strPass = "pwd"

Dim TblName As String: TblName = ""

Dim RemoteTblName As String: RemoteTblName = ""

'確認(パスワード聞くとかするといいかも)---------------

rtn = MsgBox("退避ＯＫ？", vbQuestion + vbYesNo, "確認")

If rtn <> vbYes Then

Exit Sub

End If

'実行--------------------------------------

Dim db As DAO.Database, tb As DAO.TableDef

Set db = CurrentDb

For Each tb In db.TableDefs

If tb.Connect <> "" Then 'リンクテーブルだけを処理

TblName = tb.Name

If Left(tb.Connect, 4) = "Text" Then

Dim tmp As String

Dim tmp2

tmp = tb.SourceTableName

tmp2 = split(tmp, ".")

RemoteTblName = tmp2(0)

Else

RemoteTblName = tb.SourceTableName

End If

'↓チェック=======

Dim stADOConnect As String

stADOConnect = "Driver={SQL Server};server=" & strDSN & _

";database=" & strDB & _

";uid=" & strUser & ";pwd=" & strPass & ";"

Dim ChkFLG As Boolean: ChkFLG = False

Dim adoCON As New ADODB.Connection

Dim adoRS As ADODB.Recordset

Dim mySQL As String

adoCON.Open stADOConnect

mySQL = "SELECT * FROM dbo.sysobjects " & _

" WHERE xtype =N'U' ORDER BY name"

Set adoRS = adoCON.Execute(mySQL)

Do Until adoRS.EOF = True

If RemoteTblName = adoRS!Name Then

ChkFLG = True

Exit Do

End If

adoRS.MoveNext

Loop

adoRS.Close

Set adoRS = Nothing

adoCON.Close

Set adoCON = Nothing

'↑チェック=======

If ChkFLG = True Then

'実行！-----------------------

AttachDSNTable TblName, RemoteTblName, strDSN, strDB, strUser, strPass

Else

'MsgBox("×「" & TblName & "」元の「" & RemoteTblName & _

"」テーブルはSQLServerに存在しません。")

End If

Else

'MsgBox ("「" & tb.Name & "」はリンク元なしなのでスルーしました")

End If

Next tb

RefreshDatabaseWindow

'--------------------------------------------------------

Function AttachDSNTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

On Error GoTo AttachDSNTable_Err

Dim td As TableDef

Dim stConnect As String

'ここにＯＤＢＣ名

stConnect = "ODBC;DSN=ここにＯＤＢＣ名;DATABASE=" & stDatabase & ";"

'現テーブル削除

For Each td In CurrentDb.TableDefs

If td.Name = stLocalTableName Then

CurrentDb.TableDefs.Delete stLocalTableName

End If

'新テーブル作成

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)

CurrentDb.TableDefs.Append td

AttachDSNLessTable = True

Exit Function

AttachDSNTable_Err:

AttachDSNTable = False

MsgBox "AttachDSNTable encountered an unexpected error: " & Err.Description

End Function

'--------------------------------------------------------

ACCESSからSQLServer 変換　移行　VBA マクロ

登録: 投稿 (Atom)