Updating 4 million records in SQL server using list of record-ids as input

During a migration project, I'm faced with an update of 4 millions records in our SQL Server. The update is very simple ; a boolean field needs to be set to true/1 and the input I have is a list of all the id's for which this field must be filled.(one id per line) I'm not exactly an expert when it comes to sql tasks of this size, so I started out trying 1 UPDATE statement containing a "WHERE xxx IN ( {list of ids, separated by comma} )". First, I tried this with a million records. On a small dataset on a test-server, this worked like a charm, but in the production environment this gave an

2021-07-13 17:15:37    分类:问答    sql   sql-server   sql-server-2008   tsql   data-migration

What version of .Net framework ships with SQL Server 2008?

Does SQL Server 2008 ship with the .NET 3.5 CLR, so that stored procedures written in CLR can use 3.5 features?

2021-07-13 17:15:28    分类:问答    sql-server   sql-server-2008

WHERE IN (SELECT NonExistingColumnName) causes unexpected behaviour

I recently had a situation where I needed to delete some rows from a table and mis-spelt a column name. There was no error thrown and ALL the rows were deleted from the table. Here is a script that reproduces the issue. There is an Order table with four orders with OrderID. There is a LIST_TO_DELETE table with one ItemID. I should have used DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT ItemID FROM #LIST_TO_DELETE ) instead I used DELETE TOP(1) FROM #Orders WHERE OrderID IN (SELECT OrderID FROM #LIST_TO_DELETE ) This caused all rows in the #Orders table to be deleted when I only meant to

2021-07-13 16:24:35    分类:问答    sql-server   sql-server-2008   tsql

Return Value Using SqlCommand

I'm trying to get the result set and return value of a stored proc on SQL 2008 server. When I run the proc in sql management studio, I get the result set and the return value. However, when I try to get the value in C# 4.0, the value of the parameter is null. Here is my C# code: using (ConnectionManager<SqlConnection> cn = ConnectionManager<SqlConnection>.GetManager(CultureInfo.CurrentCulture.Name)) { using (SqlCommand cm = cn.Connection.CreateCommand()) { cm.CommandText = "Name of proc here"; cm.CommandType = CommandType.StoredProcedure; cm.Parameters.AddWithValue("@ApplicationId"

2021-07-13 12:39:23    分类:问答    c#   .net   sql-server-2008   csla

Hyperlink a value in SQL Server query result in SSMS

I am trying to create something for our QA people, where a query that they would run will return a column with hyperlinked values. I am using SQL Server 2008 R2 and SSMS. How will I hyperlink results in SSMS so a linked value opens in a browser? The idea is that the Qa person can simply click on the link and the appropriate ASP.Net app would open in a browser window. The query is as below, which is right now showing an unlinked string under DocLink column. SELECT DocNumber, 'https://www.xyz.com/mypage.aspx?docNumber=' + CAST(DocNumber AS varchar(20)) AS DocLink FROM Docs

2021-07-13 12:18:53    分类:问答    sql   sql-server-2008-r2   sql-server-2008

c# 和 SQL Server 中的 DateTimeOffset 解析(DateTimeOffset resolution in c# and SQL Server)

问题 文档指出,在 .NET 和 SQL 服务器中,分辨率都是 100ns。 DateTimeOffset 值的时间分量以 100 纳秒为单位测量,称为刻度- C#精度 - 100 纳秒- SQL Server 然而 SQL 似乎放弃了最后一位数字(例如,我试图保存 2013-08-15 09:19:07.2459675 -04:00,而 SQL 保存 2013-08-15 09:19:07.2459670 -04:00 - 注意最后一位数字发生变化。) 这发生在同一台机器上,所以它不依赖于硬件。 并不是说我真的需要这个分辨率,而是它使比较日期变得更加困难......我只是很好奇。 回答1 我会说问题出在你的身上......一些代码显示: namespace Test { using System; using System.Data; using System.Data.SqlClient; using System.Globalization; /// <summary> /// /// </summary> public class Program { /// <summary> /// /// </summary> public static void Main() { // Change the connection string to specify your

2021-07-13 04:54:25    分类:技术分享    c#   .net   sql-server-2008   datetimeoffset   time-precision

Windows 7 家庭高级版上的 Visual Studio 2008/2010 和 SQL Server 2008(Visual Studio 2008/2010 & SQL Server 2008 on Windows 7 Home Premium)

问题 在 Windows 7 Home Premium 上使用 VS 2008/2010 和 SQL Server 2008 开发 ASP.NET 和 WPF 应用程序是否有任何限制? 我知道您可以在 Home Premium 上运行 IIS 7.5。 我在 Home Premium 和 VS/SQL Server 上进行了 Google 搜索和 Binged,但找不到太多内容。 看起来家庭高级版、域加入、xp 模式等中缺少的功能不应该阻止在 VS/SQL Server 中运行和开发。 更新 我确实发现了 IIS 7.5 的一个限制,我认为它适用于所有版本的 Windows 7。 您无法创建自己的自签名 SSL 证书,并指定机器名称以外的名称。 你被困在你的机器名称上,这没什么大不了的,但它与以前的版本相比有所不同。 更新二 您无法在 Home Premium 上进行远程调试。 msvsmon 根本不会运行。 我什至无法让它在本地进行远程调试。 回答1 Visual Studio 将运行良好。 您将“受限”于 SQL Server Express 或 SQL Server Developer 版本,因为 mssql 安装程序会检查操作系统版本并拒绝在桌面操作系统上安装更高端版本。 需要注意的是,开发者版和高端版完全一样,只是许可证不同,只能用于开发,不能运行真正的数据库。 回答2

2021-07-13 04:25:43    分类:技术分享    visual-studio   sql-server-2008   windows-7

将数据合并到两个目标表中(Merge data into two destination tables)

问题 我需要将捐赠者表中的数据合并到两个目标表中。 结构如下。 如果在跟踪表中找不到 projid,我需要在组件表中创建新组件并使用新的 id 插入到跟踪表中。 此外,对于捐赠者表中不再存在的那些项目,跟踪表的“活动”列应标记为 0。我可以在单个合并语句中实现这一点吗? 捐助表 projid | datestamp | Ownerid ------------------------------------------------- c_abc 1-jan-2013 name1 c_def 2-jan-2013 name3 c_ghi 3-jan-2013 name4 跟踪表 compid |projid |active | ... ----------------------------------------------- 123 c_abc 1 124 c_xyz 1 125 c_def 1 组件表 compid |ownerid ------------------------- 123 name1 124 name2 125 name3 合并后的输出表: 组件表 compid |ownerid ------------------------- 123 name1 124 name2 125 name3 126 name4 跟踪表 compid |projid

2021-07-13 04:17:56    分类:技术分享    sql   sql-server   sql-server-2008   merge

asymmetric key creation over network

I'm attempting to add an assembly to a database in SQL2008 using an asymmetric key. We're adding the assembly using a hex string (adding assemblies to servers through sql queries only) USE [master] GO IF NOT EXISTS (SELECT * from sys.asymmetric_keys where name = 'ManagedAsymmetricKey') BEGIN CREATE ASYMMETRIC KEY ManagedAsymmetricKey FROM FILE = 'C:\Managed.dll' CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY ManagedAsymmetricKey GRANT UNSAFE ASSEMBLY TO CLRLogin END GO USE [$dbName] GO CREATE ASSEMBLY [Managed] AUTHORIZATION [dbo] FROM 0x4D5A.... WITH PERMISSION_SET = UNSAFE GO This will work on a

2021-07-13 04:14:02    分类:问答    sql-server   sql-server-2008   assemblies   public-key

当您在 SQL Server 中创建新登录时,它默认选择 db_owner(When you create a new login in SQL Server it selects db_owner by default)

问题 我在 SQL Server 2008 R2 中创建新 SQL 用户时遇到了一些问题。 当我使用 SQL Server Management Studio 时,它默认检查db_owner角色成员资格。 我只想创建一个具有只读访问权限的新 sql 用户。 即使使用以下原始 SQL,它仍会创建具有 db_owner 级别权限的用户。 <!-- language: lang-sql --> CREATE LOGIN readonlyuser WITH PASSWORD = '12345',CHECK_POLICY = OFF, DEFAULT_DATABASE=mydatabase GO USE mydatabase GO CREATE USER readonlyuser FOR LOGIN readonlyuser GO EXEC sp_addrolemember 'db_datareader', 'readonlyuser' 现在,如果我使用新创建的用户登录 SQL Server Management Studio,我基本上可以访问任何表并以任何我想要的方式修改任何数据。 这正是我不想做的。 我只想被读取数据而不是修改任何数据 奇怪的是,如果我查看数据库的角色, readonlyuser位于db_datareader而不是db_owner 。 那么为什么 SQL

2021-07-13 03:57:55    分类:技术分享    sql   sql-server   sql-server-2008   sql-server-2005