Maxwell Tech Note

ALTER TABLE CA_Blog ADD Region NVARCHAR(100) NULL GO UPDATE CA_Blog SET Region='AU,CA' GO ALTER TABLE CA_Blog ALTER COLUMN Region NVARCHAR(100) NOT NULL GO

USE [DBName] Go SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB, SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name

CREATE TRIGGER TriggerTEST ON [Test] AFTER INSERT,DELETE,UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Insert Into Test_log Select * From Inserted -- Insert statements for trigger here END GO

利用 SQL Server Audit 追蹤資料庫管理動作 參閱以下網址 https://msdn.microsoft.com/zh-tw/communitydocs/sql-server/ta13030702

In Master DB create New Query CREATE LOGIN [userName] WITH PASSWORD=N'password' CREATE user [userName] FOR LOGIN [userName] WITH DEFAULT_SCHEMA=[dbo] ALTER ROLE dbmanager ADD MEMBER [userName] In your taget DB create New Query CREATE user [userName] FOR LOGIN [userName] WITH DEFAULT_SCHEMA=[DBName ] EXEC sp_addrolemember N'db_owner', N'userName'

SELECT * FROM Northwind.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'Customers'

CREATE FUNCTION [dbo].[GetCurrentIP] () RETURNS varchar(255) AS BEGIN     DECLARE @IP_Address varchar(255);     SELECT @IP_Address = client_net_address     FROM sys.dm_exec_connections     WHERE Session_id = @@SPID;     Return @IP_Address; END

Encode Function /* Base64 Encode */ CREATE FUNCTION [dbo].[f_Base64_Encode] ( @var VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN     DECLARE @Base64 VARCHAR(MAX)     DECLARE @bin VARBINARY(MAX)  SET @bin=CONVERT(VARBINARY(MAX),@var)     SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')       RETURN @Base64 END Decode Function /* Base64 Decode */ CREATE FUNCTION [dbo].[f_Base64_Decode] (     @Base64 VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN     DECLARE @Bin VARBINARY(MAX)  DECLare @Var VARCHAR(MAX)  SET @Base64=     CASE       WHEN len(@Base64)%3=2  THEN   @Base64+'=='       WHEN len(@Base64)%3=1 THEN    @Base64+'='     END     SET @Bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')   ...

SQL Express 預設是關閉TCP/IP的連線， 如果要啟用，需要特別去SQL Server Configuration Manager設定 設定方法如下圖

在還原資料庫的時候 可能會於到這樣的錯誤訊息 解決無法取得資料庫 'model' 的獨佔鎖定 這個主要原因是因為資料庫，有其他使用者再使用， 沒辦法讓我們做更高權限的處理 需要先把其他佔用資源的使用者踢除 才能做後續的動作 可以先用以下的SQL 查到目前佔用資料庫的程序有哪幾個 跟他spid select spid,waitresource,waittime , program_name,status,cmd,hostname from  sys.sysprocesses where db_name(dbid) = 'model' 之後可以用Kill的指令 將佔用資料庫的程序給踢除 Kill [spid] 這樣就可以取回資料庫的獨佔權了

asp.net MVC 提供了DonNetOpenAuth的元件，  讓網站要串接社群網站的SSO變的很容易，  但是直接引用DonNetOpenAuth範例是會自動建立一個mdf檔 就無法使用原本要接的SQL server。  這裡是提供修改範例code的方式 讓DotNetOpenAuth照既定程式， 在SQL server中建立相關的table  1.修改web.config 在web.config中加上SQLserver的連接資訊 [add connectionstring="Data Source=server;Initial Catalog=coolstay;Integrated Security=False;user id=userID;password=password" name="Connection" providername="System.Data.SqlClient"] 2.修改InitializeSimpleMembershipAttribute.vb的連接設定 WebSecurity.InitializeDatabaseConnection("Connection", "UserProfile", "UserId", "UserName", autoCreateTables:=True) 3.修改AccountModels.vb連接設定 Public Sub New() MyBase.New("Connection") End Sub 如此一來 執行系統就會在SQLserver上 建立好相關的table了

我們見了兩個table   Parent table : Mall_product   Son table : Mall_Item    Mall_Product---> Mall_Item (1對多)  1.Create ADO.net Entity 2. Create  Association Mappings  /建立關連 3.設定關連    由於在DB設計時就已經把欄位都建好了   所以設定的時候 就不用「將外部索引鍵屬性加入至 Mall_item 實體中」 但要在Mall_Product建立「導覽屬性」。   這樣就建立物件之間的關連了   但我們還沒有設定好外鍵對映的屬性  所以 4. 點選關連    要設定主體跟相依的狀況    主體 Mall_Product    相依 Mall_Item    主體索引鍵 就是Mall_Product PK : Poid    相依的屬性 就是我們在DB設計的 Mall_Item FK: ProductPoid 5.最後在程式中 Dim db As New GalleryMaxEntities Dim p As New Mall_Product p.ProductID = "Product01" Dim i As New Mall_Item i.Price = 50 p.Mall_Items.Add(i) db.Mall_Product.Add(p) db.SaveChanges()

declare @val1 varchar(15) select @val1 = 'I love Sql' SELECT SUBSTRING(master.dbo.fn_varbintohexstr( HashBytes('MD5', CONVERT(varchar,@val1))),3,32) as MD5_varchar

在SQL查詢的時候 會有需要將多行資料變成一個單一字串 以利於使用 這時候可以藉由For XML ,跟 STUFF()的配合做到這個功能 例如：   SELECT itemNO FROM Item WHERE ItemType='Food' 取得的資料可能為 itemNO ------------------ Apple0001 Banana0002 Orange0003 如果要整合成一行就可下以下的SQL SELECT STUFF((SELECT ''+itemNO FROM Item WHERE ItemType='Food' FOR XML PATH('')),1,1,'') V 這樣的結果是 V ---------------------------------------------- Apple0001,Banana0002,Orange0003   如果配合關連性的表格使用 則可以這樣寫 select ProductName , ItemNO=STUFF((SELECT ''+itemNO FROM Item WHERE ItemType=P.Type FOR XML PATH('')),1,1,'') from Product p where pID='ABC' ProductName | ItemNO -------------+------------- SunShine | Apple0001,Banana0002 For XML STUFF

有時候需要用SQL找出前一筆跟後一筆資料 用SQL的TOP是沒有辦法做到 這個時候就可以這個語法 select * from ( SELECT TOP 1 * FROM [Article] where Poid {CurrentPoid} order by CreateDate ASC) t2 找出目前PK id前一個與後一個的資料（依照想要排序順序） 那如果指示想要一個資料行呈現的話 可以改用下面的SQL語法 讓這兩筆資料join在同一筆 select Pre.*,Nex.* from (SELECT TOP 1 * ,1 tID FROM [dbo].[Article] where Poid {CurrentPoid} order by CreateDate ASC) Nex on Pre.tID=Nex.tID

今天專案要上線的時候發現出現以下的錯誤 The version of SQL Server in use does not support datatype 'datetime2' 客戶用的SQL 2005 不支援datatime2的格式 但是查過系統後， 並沒有發現在系統有設定當datatime2的格式 而是都是用正常的datatime格式 google大神之後 原來使用Entity Framework 會預設成SQL 2008的資料型態 必須去用特別的方法去修該edmx的檔案才能符合SQL2005的格式 修改方式如下 1.用notepad打開.edmx 2.修schema tag中的ProviderManifestToken 由 ProviderManifestToken="2008" 改成 ProviderManifestToken="2005" 然後存檔 就可以了

  在今天再安裝SQL server2008的時候 在檢測安裝程式支援規則時 出現這個錯誤 仔細查了一下MS 有提供了一個解決方法 「如何手動重建效能計數器程式庫值 」 DEMO大也寫個一個處理方法 安裝MS SQL Server時發生 效能計數器登錄區一致性 的錯誤 如果是一般的狀況 DEMO大 寫的方法就可以解決的，但是我的問題卻是另外一個狀況 因為在這邊的系統打開regedit.ext 找到 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib  在Preflib下面有004和009兩個資料夾都是空的 這下麻煩了 狀況完全不一樣怎麼辦，而且更糟糕的是我沒有辦法直接在這兩個資料夾內新增數值 上面的解決方法都無法使用 最後在GOOGLE大神的幫忙下找到一個 windows 的指令 在cmd下 執行 cd %systemroot%/system32 lodctr /r 用lodctr /r強制重建所有的效能計數器 之後就會在register中把數值都建好了如下圖 SQL也就能正常安裝了

要在還原備份的時候， 遇到「備份組包含現有的XXX資料庫以外的資料庫備份」的錯誤 這主要是資料庫的路徑問題 要解決這個問題 可以用以下的方法 1.先執行以下的SQL USE [master] GO restore database #資料庫名稱 from disk = '#備份資料庫檔路徑及檔名' with move '資料庫檔案邏輯名稱' to '資料庫mdf檔案所在位置', move '資料庫記錄檔邏輯名稱' to '資料庫記錄檔ldf檔案所在位置', NoRecovery, Replace go 執行完畢資料庫會呈現「還原中」 2.到要還原的資料庫上按右鍵→[工作]→[還原]→[資料庫]→[來源裝置]→瀏覽備份檔→勾選還原→確定 如此就完工了  

如果想要更改資料庫的結構但是出現訊息[防止儲存需要重新建立資料表的變更] 代表有啟用[防止儲存需要重新建立資料表的變更] 可以用[工具]->[選項]->[設計師]->[資料表和資料庫設計工具]  將此選項取消

ALTER TABLE [TableName] ADD [ColumName] int IDENTITY(1,1) CONSTRAINT [PK_Name] PRIMARY KEY NONCLUSTERED NOT NULL