Category Archives: SQL Server

SharePoint, SQL Server, Windows Server

Windows Server 2008 R2 domain controller: Fixing SceCli Event 1202

When running IIS, SQL Server, or SharePoint on a Windows Server 2008 R2 domain controller, you may encounter this error:

Application Event
Event ID: 1202
Source: SceCli
Security Policies were propagated with warning. 0x534 : No mapping between account names and security IDs was done.

I ran into this error when building a virtual machine to run a self contained SharePoint environment, which required that the VM also be a domain controller. This event will show up repeatedly every few minutes in the application event log. The error occurs because the domain controller doesn’t have a concept of “local” accounts and doesn’t know how to resolve some account names that are added by IIS and SQL Server to the domain controller security policy. Microsoft has released a hotfix 977695 to resolve the issue. Continue reading Windows Server 2008 R2 domain controller: Fixing SceCli Event 1202 →

SQL Server

Modifying the “Action If Name Is in Use Field” for a Merge Replication Article

The “Action if name is in use” field controls what happens to an article’s contents, at the subscriber, when a snapshot is reinitialized. The default setting (with merge replication) is to drop the existing object and create a new one. The problem is that all permissions on that object are also dropped. Fortunately, we can choose another option that will truncate(delete) the data in the object without the removing the permissions. There are a few caveats to this solution. It doesn’t work with articles that are stored procedures (keep the object unchanged is the only other supported option), you have to use transact-SQL to preform the change, and it’s a one way trip (meaning you can’t change back to the previous setting) without recreating the publication.

An alternative solution is to leave the default setting and create a script to reapply the permissions on the object after it has been dropped. In our case, we use this alternative for articles that are stored procedures. Continue reading Modifying the “Action If Name Is in Use Field” for a Merge Replication Article →

SQL Server

Union Vs. Union All

The Union and Union All operators combine the results of two SQL queries. Union All merges every row, while Union throws out duplicate rows. It turns out that Union actually performs the equivalent of distinct sort behind the scene.

As a simple test, I ran 3 separate queries: (1) using Union All, (2) using Union, and (3) using Union All and distinct sort to generate the same results as query 2. For query 3, the order by clause is actually not necessary, as select distinct also automatically sorts in ascending order. Below is the SQL Server execution plan for each query.

Queries 2 and 3 are executed identically. As expected, both are much more expensive than query 1. So stick with Union All whenever possible.

SQL Server

Reducing the Size of SQL Server Log Files

I just ran into a problem where we were not able to create new tables in the database.  The CREATE TABLE command would simply hang.  After examining the log files, we saw the following error:

“Autogrow of file ‘<database_name>_log’ in database ‘<database_name>’ was cancelled by user or timed out after … ”

I examined the log files and found that the log file for the database had grown to over 50Gb.

My first attempt to truncate the log file was to use the “Shrink file” option from within Microsoft SQL Server Management Studio.  To get there, right-click on the database name, select “Tasks” -> “Shrink” -> “Files”.  In the dialog box that appears, change the file type from “Data” to “Log”.  I tried several time to shrink the log file this way.  No luck.  I was not able to reduce the size of the log size to less than its allocated 50Gb.

The solution that my co-worker and I found was to use some SQL statements to reduce the log file size.  The SQL we used was as follows: Continue reading Reducing the Size of SQL Server Log Files →

Programming, SQL Server

Importing CSV Data into SQL Server

On a fairly regular basis at work I have to take .csv files with data from a customer’s database and import them into my SQL Server instance. This should be a simple operation, but the design of the native SQL Server Data Import tool makes it much, much more complicated than it needs to be. The biggest pain point by far is having to specify the data types of every single column in the data source by hand. Not only does it assume that everything is a string, forcing me to go through and figure out which types are a date, an int, a float or whatever else I have in the database, but by default changing a column’s type to a decimal uses decimal(18,0), truncating all my floating point values.

It always seemed a bit absurd that I should have to manually specify the types of all my data since I was importing into a database where the data types of the columns are explicitly specified. Once the import tool knew what table I was importing into, shouldn’t it be able to figure out the types for me?

So I wrote a Python script to import data for me. It uses the pymssql module to connect to the database, and the main insert function requires only three arguments: a connection to a database, the name of the table to import into, and an object with a function to return the names of the columns and then be able to iterate over the data. Conveniently, I also wrote a CSVFile class that wraps around a .csv file and provides just that functionality. Continue reading Importing CSV Data into SQL Server →