Chui’s counterpoint

Here’s my little mystery of the day that took me an hour to figure out:

I have a table called EQUIPMENT with a column called SITE nvarchar(50). For some strange reason the results I’m getting from ADO.net is a string padded to full 50 characters. Now, only nchar(50) does that. I checked with Query Analyzer:

SELECT SITE, LEN(SITE) FROM EQUIPMENT
RO, 2

A bit of search with Google showed that LEN actually trims off trailing whitespace, so the function above doesn’t tell me anything. I changed my query a bit:

SELECT SITE, LEN(SITE) FROM EQUIPMENT WHERE SITE='RO'
RO, 2

Surely there are no padded strings in the database. I looked up the SQL books online, which confirmed that nvarchar is not padded with spaces. What’s going on here?

One more query:

SELECT SITE + '***', LEN(SITE) FROM EQUIPMENT WHERE SITE='RO'
RO                                ***, 2

What the *&^%#@! SQL Server implements the = comparion by trimming off trailing whitespace for nvarchar. But how did the column get spaces in the first place?

Suddenly it dawned on me the original column was nchar(50), and I had changed it to nvarchar(50) after I had imported the data. So the padding on the original nchar(50) stays.

Use Master
GO
Select owning_principal_name, instance_pipe_name
FROM sys.dm_os_child_instances
GO

HOSTNAME\UserName	\\.\pipe\4104C6A7-5175-45\tsql\query

Connect to
np:\\.\pipe\4104C6A7-5175-45\tsql\query

Visual Studio 2005.net introduced code-less databinding, entirely using drag and drop.

Here’s what happens in the GUI, and what occurs behind the scenes:

GUI - Add a new data source to the project
Code -
Database (mdf) added to project [optional]
Connection strings added to app.config.
TypedDataSet (xsd) created and added to project.
In the TypedDataSet, each database table is represented by property of the same name. (For example, the Document table can be accessed by MyDataSet.Document property). The property returns a DataTable (as seen in ADO.Net 1.0). A TableAdapter is also defined for each table. A TableAdapter is just a struct of SqlDataAdapter, SqlConnection and SqlCommands. The setting-name used for looking up ConnectionString is stored against each TableAdapter.
Here’s a snippet of the XSD file

   <DbSource
     ConnectionRef="MyConnectionString (Settings)"
     DbObjectName="dbo.Document"
     DbObjectType="Table"
     FillMethodModifier="Public"
     FillMethodName="Fill"
     GenerateMethods="Both"
     GenerateShortCommands="True"
     GeneratorGetMethodName="GetData"
     GeneratorSourceName="Fill"
     GetMethodModifier="Public"
     GetMethodName="GetData"
     QueryType="Rowset"
     UseOptimisticConcurrency="True"
     UserGetMethodName="GetData"
     UserSourceName="Fill">

GUI - Add a new control from data source to a form
Code - Adds TypedDataSet to form if not already there.
Adds TableAdapter for the DataTable to form if the TableAdapter is not already there.
Adds BindingSource for the DataTable to form if the BindingSource is not already there.
Adds BindingNavigator for the DataTable to form if the BindingNavigator is not already there.
Adds DataBinding to the control, associating property name with BindingSource.

this.precisTextBox.DataBindings.Add(
  new System.Windows.Forms.Binding(
    "Text", this.documentBindingSource, "Precis", true));

In the Form_load event, the datatable is populated

this.documentTableAdapter.Fill(this.myDataSet.Document);

It’s creepy when Visual Studio adds all this code for you. Although designer-added code is protected from user edits, it still opens code to be brittle. What happens when you rename a control, for instance? I suppose the refactoring browser will have to kick in and fix up all the changes. In particular, observe that the column name is not strongly typed. I don’t know how the refactoring module will cope with that.

Here is a non-exhaustive list of JDBC connection URLs for various databases. Corrections welcome!

Microsoft SQL Server JDBC connection URL

jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=northwind
com.microsoft.jdbc.sqlserver.SQLServerDriver
msbase.jar;mssqlserver.jar;msutil.jar
downloadl

Microsoft SQL Server 2005 JDBC connection URL

jdbc:sqlserver://serverName\instance:port[;user=value][;password=value][;property=value]
com.microsoft.sqlserver.jdbc.SQLServerDriver
sqljdbc.jar
download SQL Server 2005 driver

Microsoft SQL Server (JTurbo) JDBC connection URL

jdbc:JTurbo://<HOST>:<PORT>/<DB>
com.ashna.jturbo.driver.Driver

Microsoft SQL Server (Sprinta) JDBC connection URL

jdbc:inetdae7:<HOST>:<PORT>?database=<DB>
com.inet.tds.TdsDrive
download

(more…)

Deployment notes: Deploying an MDF file to an installation using SQL Server Express 2005 or SQL Server 2005. (The following is useful if you require ODBC)

1. Note down the path to the MDF file, e.g. C:\zope261\3.03.05\DW30305DEMO.mdf
2. Win+Run odbccp32.cpl
3. Assign default database name and database filename to the same one as in step 1 (see picture)

Symptoms:
Error 3007 The backup of the file or filegroup “sysft_Search” is not permitted because it is not online.

Reason: Imported database from SQL Server 2000 may have reference to full text index, which is not supported by SQL Server Express 2005.

I just arrived at a solution through experimentation. Use at your own risk!

sp_help_fulltext_catalogs
... Gets you the catalog name. In my case it was 'Full Text'

sp_help_fulltext_tables
...Gets you table names which refer to full text indexes
...eg Blobfield

sp_fulltext_table @tabname='BlobField', @action='Drop'
sp_fulltext_catalog @ftcat = 'Full Text', @action='Drop'
... Command(s) completed successfully.

backup database DW30305DEMO to DISK = 'C:\DW30305\DW30305.dmp'

If you find this post useful, I’d appreciate a link to this URL to assist people in finding the entry quickly. Thanks!

I was using one database (DB1) and wished to refer to a table in another database on the same server (DB2).
This is accomplished without resorting to sp_addlinkedserver.

USE DB1
GO
SELECT TOP 20 * FROM DB2..TABLE1

Observe that there are two periods between DB2 and TABLE1. This is not a typo.