Names of custom content types cannot contain slashes

This is a definition of a custom content type generated in Visual Studio. The only thing I changed is the Name attribute.

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <!-- Parent ContentType: Task (0x0108) -->
  <ContentType ID="0x010800096b8767b29a4191929d5cc2d50e094e"
               Name="Yes/No Task"
               Group="Custom Content Types"
               Description="My Content Type"
               Inherits="TRUE"
               Version="0">
    <FieldRefs>
    </FieldRefs>
  </ContentType>
</Elements>

It seems to be perfectly valid. The package can be built and deployed to SharePoint without any errors or warnings. However, the new content type does not show up on the Site content types page (/_layouts/mngctype.aspx).

The problem is caused by the '/' character in content type's name - remove the slash, re-deploy the package and now the new content type will be visible on the Site content types page.

Error "Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'" when applying snapshot to the Subscriber database

When setting up merge replication, you may get the following error:

Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'

while the initial snapshot is applied to the Subscriber database.

How can data that violates no primary key constraints at the Publisher cause such an error at the Subscriber? This can be caused by different collations used by the Publisher and Subscriber (at the database, table or column level).

If the Publisher database has a case-sensitive collation (e.g. SQL_Latin1_General_CP1_CS_AS ), the following data is correct (columns Type and Code are a primary key):

Type  Code  Column1
024   N     ...
024   n     ...
024   g     ...

However, an attempt to insert these rows into a table with case-insensitive collation (e.g. Latin1_General_CI_AI) will cause a primary key violation.

Filter results in SQL Server Management Studio may be case-sensitive

If you are working with a database that has a case-sensitive collation (e.g. Latin1_General_CS_AS), the filter function in SQL Server Management Studio will use this setting and the results will be case-sensitive.

Filter showing tables with Bitmap in the name:

Filter showing tables with bitmap in the name:

If this database had a case-insensitive collation, filter results for Bitmap and bitmap would be identical and would contain all four tables.

Web Parts Maintenance Page - fixing a SharePoint page with faulty Web Parts configuration

If you ever use Site Aggregator Web Part on the default.aspx page of a site and configure it in a way that redirects users to some other website (specified when adding Site Aggregator) every time they try to open the SharePoint site (I have no idea how I managed to do it), you can use the Web Parts Maintenance Page to resolve this problem. Of course, you can use this page to troubleshoot all other kinds of problems caused by Web Parts misconfiguration.

The URL of the Web Parts Maintenance Page is:

http://SITE-URL/_layouts/spcontnt.aspx?&url=%2fPAGE-WITH-WEB-PARTS.aspx

Error "The remote debugger is not an acceptable version"

If you try to debug an ASP.NET application remotely using Remote Debugging Monitor (msvsmon.exe) and you get the following error:

The remote debugger is not an acceptable version

it means that the versions of Visual Studio and Msvsmon are different (e.g. Visual Studio 2005 + Msvsmon 2008).

Missing auto-generated Resources.Designer.cs file

If the Resources.Designer.cs file is missing and there is no Run Custom Tool option in the context menu for the Resources.cs file (or, of course, any other resources file), it probably means that the Custom Tool property of the Resources.cs file is empty:


This is what MSDN says about the Custom Tool property:

Custom tools are components that can be used to transform files from one type to another at design time. For example, a custom tool might be a dataset code generator that reads in an XML Schema (.xsd) file and generates classes in a code file that programmatically exposes its tables and columns. There is a predefined list of custom tools available in the product; this property enables you to see which custom tool is applied to a file. In rare circumstances, you might have to change the value of this property. The value of this property must be either blank or one of the built-in custom tools.

In case of Visual Studio 2008 the default tool generating the *.Designer.cs files is ResXFileCodeGenerator - type it in the property and the problem should be solved:


UPDATE:

I found an easier way to do it:

Error "Unable to connect to SQL Server '(local)'. The step failed."

If you are using SQL Server 2000 and you have a job with a step that fails with the following error message:

Unable to connect to SQL Server '(local)'. The step failed.

it may mean that this step is configured to use a database that existed when the step was created but has later been dropped.

Moreover, if you don't remember what database this step should use, checking the step definition in Enterprise Manager won't reveal the cause of the problem, because there will be some database selected in the Database combo box (it cannot be empty) but it won't be the same database that the step is really configured to use (this database does not exist so it is not in the combo box).

To fix the problem select a new database for each failing step and save the changes.

Invalid 'Remote table-valued function calls are not allowed' message error

Assuming that [11.99.120.12] is a linked server created on a SQL Server 2005 computer and pointing to a SQL Server 2000 computer, executing a simple query like this:

  SELECT *
  FROM [11.99.120.12].SALES_DB.dbo.ORDERS (nolock)

will fail with the following error message:

  Server: Msg 4122, Level 16, State 1, Line 1
  Remote table-valued function calls are not allowed.

This error message is not correct, since ORDERS is a table and not a function.

This query will run correctly only if the WITH keyword is used before the (nolock) hint:

  SELECT *
  FROM [11.99.120.12].SALES_DB.dbo.ORDERS WITH (nolock)

This problem is also discussed here.

What I recently learned about casting an empty string (T-SQL)

SELECT CAST('' AS money)
-- .0000

SELECT CAST('' AS decimal(19,4))
-- Server: Msg 8114, Level 16, State 5, Line 1
-- Error converting data type varchar to numeric.

Listing users and all roles they're members of (SQL Server 2005)

I admit it's trivial but it took me awhile today to understand what data is shown in the sys.database_role_members catalog view, so I am posting this in case I forget.

SELECT
  DP1.name as ROLE_NAME
, DP2.name as [USER_NAME]
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP1
  ON DRM.role_principal_id = DP1.principal_id
INNER JOIN sys.database_principals DP2
  ON DRM.member_principal_id = DP2.principal_id

Disabling or enabling multiple jobs with a single click

If you navigate to <SQL Server instance>\SQL Server Agent\Jobs in SQL Server Management Studio and then right-click on some job, you'll see a context menu with Enable and Disable commands.

However, when you select more than one job and then right-click on the selection, a different menu shows and, of course [sic], it does not contain these commands.



Luckily there is another way to do it - you can use Job Activity Monitor which uses the same context menu for a single job as well as multiple jobs.

SSIS imports NULLs instead of numeric values from an Excel file

Let's imagine that you have been assigned a task to import some kind of parts catalogue from an Excel file to SQL Server table. You decided to use Integration Services. One of the columns in the Excel file contains catalogue numbers. Most of them look like this XYZ123456 but some of them are plain numeric values.

This is what can happen:

Excel          SQL Server
CAT_NO         CAT_NO
---------      ---------
XYZ121156      XYZ121156
XYZ654321      XYZ654321
ABC120456  =>  ABC120456
CBA183456      CBA183456
ZYX123416      ZYX123416
123            NULL       (!)
ZXV654521      ZXV654521

This article from SQL Server 2005 Books Online explains this weird behaviour:

Missing values
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

Preventing code from being generated in InitializeComponent() method for a property

When you place controls on a form, Visual Studio generates code in the InitializeComponent() method that sets values of their properties.

If you are developing a custom control and you don't want Visual Studio to generate such code for some of its properties, you should apply the DesignerSerializationVisiblityAttribute to such properties and place DesignerSerializationVisiblity.Hidden value on them.

[DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
public DateTime Value
{
    get { /* ... */ }
    set { /* ... */ }
}

More information about customizing the code generated by Visual Studio can be found here.

A SQL CLR user-defined aggregate - notes on creating and debugging

At the end of this post you will find my very first attempt to use one of the new features of SQL Server 2005 - CLR integration. It is a CLR user-defined aggregate that produces a comma-separated (comma plus space to be precise) list of values.

Column1
--------
aaa
bbb       -->   aaa, bbb, ccc
ccc

My code is a modified version of an example that I found here.

Global variables

You may expect (at least I did) that if you have a global variable (e.g. intermediateResult in my example) and you assign it a value in the Init method then you will be able to use this value in the Terminate method. Well, that's not true.

If you need to use a value of some variable in the Terminate method, you have to serialize it in the Write method (to save the needed value) and then deserialize it in the Read method and assign to some variable (to restore the needed value).

You can of course save and restore more than one variable - use some kind of serializable object to store the values.

Debugging

There is an MSDN article that explains exactly how to debug a CLR user-defined aggregate - Walkthrough: Debugging a SQL CLR User-Defined Aggregate - nonetheless, I encountered one problem.

I started with a solution that contained two projects - one project contained the aggregate code and the other was for a SQL test script. It is possible to perform debugging in such configuration (I managed to do it, although I am not sure how), but it is a better idea to have a single SQL Server project with both the aggregate code and SQL test scripts. This is what the IDE expects, so following this advice can spare you lots of hassle.

For example, MSDN says that to debug a CLR user-defined aggregate you need to enable CLR Debugging, but it doesn't say that Application Debugging must also be enabled.



When I worked with a single project, Visual Studio enabled this option automatically when needed, while with the two-project solution I had to do it myself.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable()]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls=true,
    IsInvariantToDuplicates=false,
    IsInvariantToOrder=false,
    MaxByteSize=8000)]
public class Concat : IBinarySerialize
{
    #region Private fields
    private string separator;
    private StringBuilder intermediateResult;
    #endregion

    #region IBinarySerialize members
    public void Read(BinaryReader r)
    {
        this.intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
    #endregion

    #region Aggregation contract methods
    public void Init()
    {
        this.separator = ", ";
        this.intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString pValue)
    {
        if (pValue.IsNull)
        {
            return;
        }

        if (this.intermediateResult.Length > 0)
        {
            this.intermediateResult.Append(this.separator);
        }
        this.intermediateResult.Append(pValue.Value);
    }

    public void Merge(Concat pOtherAggregate)
    {
        this.intermediateResult.Append(pOtherAggregate.intermediateResult);
    }

    public SqlString Terminate()
    {
        return this.intermediateResult.ToString();
    }
    #endregion
}

'Alter table failed because unique column IDs have been exhausted for table MY_TABLE' error

Below you'll find a excerpts from my conversation with Peter Yang from Microsoft in microsoft.private.directaccess.sqlserver newsgroup.

Me:

When I try to add a column to MY_TABLE, the ALTER TABLE statement fails with the following error:

Alter table failed because unique column IDs have been exhausted for table 'MY_TABLE'.

1. Can you please explain this exactly means?
2. What is the 'column ID'? (I suppose it is some column in some system table)
3. Is there any way to solve this problem apart from dropping and re-creating the table?

Peter Yang:

Since column id is not reused and the server also defines the maximum number of column id (MAXCOLID == 32000), then trying to alter table add column, alter table drop column until beyond 32000 iteration will be failed. You shall see the error message you encounter under the situation.

For example, using the following script shall reproduce the problem:

CREATE TABLE T1 (C int, C0 varchar)
GO

CREATE CLUSTERED INDEX IDX1 ON T1(C)
GO

DECLARE @i int
SET @i = 1
WHILE @i <= 32770
BEGIN
IF @i % 2 = 1
BEGIN
ALTER TABLE T1 ADD C1 varchar   
ALTER TABLE T1 DROP COLUMN C0    
END    
ELSE   
BEGIN   
ALTER TABLE T1 ADD C0 varchar   
ALTER TABLE T1 DROP COLUMN C1  
END  
SET @i = @i + 1
END 
GO 

DROP TABLE T1
GO

To work around the issue, you may need to create a new table with the columns you want other than alter the table since it has reached maximum ids of the server.

Me:

I just to want to make sure that re-creating the entire table is the only way to solve this problem. There is no way to reset the column id value, is there?

Peter Yang:

Thank you for your reply. Based on my research, there is no method to reset columnID value for a table and columnIds are not reused. This is by design behavior and hard coded right now.

UPDATE:

I am not the only one who encountered this problem. A guy from work showed how to determine the maximum used COLUMN_ID for a chosen table.

-- SQL Server 2000
SELECT 
name AS TABLE_NAME
, info AS MAX_COLUMN_ID_USED
FROM sysobjects
WHERE id = OBJECT_ID('put_the_table_name_here')

-- SQL Server 2005
SELECT 
name AS TABLE_NAME
, max_column_id_used AS MAX_COLUMN_ID_USED
FROM sys.tables
WHERE object_id = OBJECT_ID('put_the_table_name_here')

UPDATE 2:

My suggestion submitted to Microsoft - if you have this problem too, rate it!

Sending mail using System.Net.Mail.SmtpClient and Gmail

This code snippet:

MailMessage message = new MailMessage();
message.From = new MailAddress("somebody@gmail.com");
message.To.Add(new MailAddress("somebody-else@some.server.com"));
message.Subject = "testing...";
message.Body = "This is a test.";

SmtpClient smtp = new SmtpClient();
smtp.EnableSsl = true;
smtp.Send(message);

along with this app.config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.net>
    <mailSettings>
      <smtp>
        <network
          host="smtp.gmail.com"
          port="587"
          userName="somebody@gmail.com"
          password="some-password"
          defaultCredentials="false"
        />
      </smtp>
    </mailSettings>
  </system.net>
</configuration>

allows to send e-mail from .NET code using Gmail as an SMTP server.

It is crucial to remember to:

1. set EnableSsl to true;
2. use port 587 (not 465, clicky).

Converting datetime to YYYY-MM-DD and YYYY-MM-DD hh:mm:ss formats

I know this is trivial, but somehow I keep forgetting this.

-- YYYY-MM-DD
SELECT CONVERT(varchar(10), GETDATE(), 120)

-- YYYY-MM-DD hh:mm:ss
SELECT CONVERT(varchar(19), GETDATE(), 120)

UPDATE:

Depending on the input date format, an additional step may be necessary.

SELECT CONVERT(varchar(10),
  CONVERT(datetime, '20080730 00:00:00.000', 120), 120)

Importing data from DBF files using SSIS - configuring Connection Manager

This is an example Connection Manager configuration:

The most important thing to remember is that the Data Source property should only contain a path to the folder with DBF files (e.g. c:\temp\3\), not a full path to the file (e.g. c:\temp\3\sales.dbf).
The particular file that should be used is selected later in the Name of the table or the view combo box in OLE DB Source configuration.

Importing data from DBF files using SSIS on 64-bit SQL Server 2005 - errors during package execution

If you are trying to import data from DBF files using Integration Services (SSIS) and you are getting errors similar to:

Error: The AcquireConnection method call to the connection manager "DBF files" failed with error code 0xC0202009.

Error: component "DBF file source" (1) failed validation and returned error code 0xC020801C.

Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

check whether you are not running 64-bit version of SQL Server 2005. If so, go to project properties and set the Run64BitRuntime property to false.

I found it here.

UPDATE:

If you are then using dtexec.exe utility to run such a package and encounter similar errors, read this thread and use 32-bit version of dtexec.exe.

HOWTO Inserting a file into image column using only Transact-SQL (SQL Server 2005)

CREATE PROC dbo.InsertBlob
  @id int
, @path varchar(255)
AS
  DECLARE @sql nvarchar(MAX)
 
  CREATE TABLE #BlobData(BlobData varbinary(max))
 
  --insert blob into temp table
  SET @sql =
      N'
      INSERT INTO #BlobData
      SELECT BlobData.*
      FROM OPENROWSET
          (BULK ''' + @path + ''',
          SINGLE_BLOB) BlobData'
  EXEC sp_executesql @sql
 
  --update main table with blob data
  UPDATE dbo.SOME_TABLE
  SET SOME_BLOB_FIELD = (SELECT BlobData FROM #BlobData)
  WHERE SOME_TABLE_ID = @id

  DROP TABLE #BlobData
GO

I found it here and I am happy I can finally stop using textcopy.exe tool.