What can I do to resolve a “Row not found or changed” Exception in LINQ to SQL on a SQL Server?

The query generates the following SQL: UPDATE [Sessions] SET [Is_Active] = @p0, [Disconnected] = @p1 WHERE 0 = 1 -- @p0: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False] -- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:12:02 PM] -- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8 The obvious problem is the WHERE 0=1, After the record was loaded, I've confirmed that all the properties in the "deviceSessionRecord" are correct to include the primary key. Also when catching the "ChangeConflictException" there is no additional information about why this failed. I've also confirmed that this exception get's thrown with exactly one record in the database (the record I'm attempting to update) I solved this error by redragging over a table from the server explorer to the designer and re-building. or change nullable value.

SQL資料庫用Windows認證出現:Cannot Generate SSPI Context Error

Cannot Generate SSPI Context Error @ SQL 遇到一個SQL問題。 經驗中，這鐵定是使用NT整合式認證連線SQL資料庫失敗所致。 只要調整時間將Client & Db一致即可. 或者用setspn的方式也可

ASP.net網頁出現URL-encoded form data is not valid的處理方法

HttpException (0x80004005): The URL-encoded form data is not valid 於web.config中加入

<appsettings>
<add key="aspnet:MaxHttpCollectionKeys" value="5000"></add>
</appsettings>

如果還超過則要將該值放大

CDC (Change Data Capture) 範例

EXEC sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases
exec sp_cdc_enable_table 'dbo','tblA',@role_name='sa'
select name, type, type_desc, is_tracked_by_cdc from sys.tables
select * from tbla
insert into tblA values ('6','aga')
select * from cdc.dbo_tblA_CT --__$operation欄位1表示DELETE 2表示INSERT 3表示UPDATE之前 4表示UPDATE之後
Declare @begin_LSN binary(10),@End_LSN binary(10)
select @begin_LSN=sys.fn_cdc_get_min_lsn('dbo_tblA')
select @End_LSN=sys.fn_cdc_get_max_lsn()
select @begin_LSN,@End_LSN
select * from cdc.fn_cdc_get_net_changes_dbo_tblA(@begin_LSN,@End_LSN,'ALL');
select * from cdc.fn_cdc_get_all_changes_dbo_tblA(@begin_LSN,@End_LSN,'ALL');
select * from cdc.lsn_time_mapping --用來查看LSN的時間
exec test.sys.sp_cdc_disable_table @source_schema='dbo',@source_name='tblA',@capture_instance='dbo_tblA'
EXEC sp_cdc_disable_db

四種 EXCEL 讀寫.Net Library without Excel.Application

ExcelPackage http://excelpackage.codeplex.com/
EPPlus http://epplus.codeplex.com/
NPOI http://code.google.com/p/npoi/
ExcelLibrary http://code.google.com/p/excellibrary/

各有優缺點
目前要同時支援xls vs xlsx只有ExcelLibrary
NPOI支援xls
ExcelPackage支援xlsx (office 2007之後)
EPPlus是ExcelPackage改善來的

如何解SQL lock

首先透過 sp_lock 去查詢被 Lock 的資料有哪些,
因為他不會直接列出 Table 名稱, 所以可透過select object_id('TABLE_NAME') 或是 select object_name('OBJECT_ID') 去查出該 Lock 是屬於哪個 Table 的

直接下kill spid...spid為sp_lock查出的第一個欄位
kill xxx

判斷IP是哪個國家的位址

http://www.maxmind.com/app/geoip_country
http://ip-to-country.webhosting.info/downloads/ip-to-country.csv.zip

這兩個網站可以下載到ip對應的國家..
但是
資料要自己從CSV匯入到SQL內

ip="168.95.1.1"
arr=split(ip)
sum = arr(0) * 16777216 + arr(1) * 65536 + arr(2) * 256 + arr(3)
SELECT country, name FROM ip_country WHERE sum BETWEEN begin_num AND end_num