SQL Server Management Studio Scripting Options

This week I had to partition a table for performance improvements.  I scripted the table to create a temporary table for my sliding window. When I wanted to change the script, I noticed that none of my indexes and my partition scheme was scripted… Strange… because I thought that indexes were always scripted together with the table. In the Options menu of the management studio I found the default scripting options. You can find them under Tools –> Options –> SQL Server Object Explorer

When I checked the default setting of the option “Script indexes”, it was indeed false. However, according to the books online, the default setting should be true.So I compared my SQL Management Studio scripting default values with the books online default values and I noticed a few differences, which can cause some confusions, like I had… Maybe it’s worth to mention that I used a standard installation and didn’t change any option :)

Below you can find a list of the SSMS settings and the books online default values

According to me, following settings should be changed after installation of the SSMS, otherwise you will get faulty scripts, which can have a serious impact on your database if you should reuse the scripts.

Script Data Compression Options
Script indexes
Script partition schemes
Script Triggers



SSIS Excel Source: Missing Values

Last week, I was working on a new SSIS package.  I had to perform an extract of an Excel file into a staging table.
After playing around, I suddenly noticed that the value of some of my columns were NULL, although some fields of that column contained data.

When looking into this problem, I found the following:

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.

More info on: http://msdn.microsoft.com/en-us/library/ms141683.aspx

When I checked my Excel file, it turned out that my first 8 rows were empty

How can you fix this problem?

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.

In my case, I had the following connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:UserTempScrambling Requests_Requests_Inventory.xls;Extended Properties=”EXCEL 8.0;HDR=NO;IMEX=1“;

In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text.

After this modification, my values were shown correctly.


Denali Multi-Site Cluster – DNS Latency Issues During Failover – How to change HostRecordTTL

While I was playing around with my Denali Multi-Site Cluster, I noticed that sometimes you can have DNS Latency Issues During a Failover.

When the SQL Server cluster instance fails over to another subnet, the online IP address changes accordingly. Windows failover cluster issues a DNS update immediately after the network name resource name comes online. However the client connections will not reflect the DNS update due to the local DNS cache on the client machines, and possible DNS replication latency if the cluster nodes and the client machines are not using the same DNS server. Therefore, the client machines cannot resolve the network name causing connection failure until the DNS replication is complete and the local DNS cache is timed out.

To minimize the client downtime, Microsoft recommends setting the HostRecordTTL to 60 seconds for most multi-subnet clustering environment.
The default value of the network name resource private property HostRecordTTL is 1200 seconds. Before making the HostRecordTTL value change, please consult your DNS server administrator to make sure your DNS server can handle the increased query request from database client machines.

You can find more details here http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx

Below you can find the procedure how I changed the HostRecordTTL on my Denali Multi-Site Cluster

Step 1: Login on your active cluster node
Open the Failover Cluster Manager.  Below you can see a screenshot of my cluster

Step 2: Retrieve your Network Name Resource.

For me, the Failover Cluster Manager is not very clear, so I looked it up with the following command in a command prompt:

cluster res

After you executed the command, you find a list of all your cluster resources.  Look for the cluster resource that start with “SQL Network Name”.  See screenshot below

Step 3: Retrieve the current value of the HostRecordTTL

To retrieve the HostRecordTTL current setting, I executed the following command:
cluster /cluster:DENALLICLUSTER res “SQL Network Name (DENALISQL)” /priv

Remark: Change the cluster name and resource name according to your installation

After you executed the command, you find a list of all the properties of the resource.  Look for the HostRecordTTL properties and check the value.
Default value is 1200 seconds. See screenshot below.

Step 4: Change the HostRecordTTL value

To change the HostRecordTTL value, execute the following command

cluster /cluster:DENALLICLUSTER res “SQL Network Name (DENALISQL)” /priv HostRecordTTL=60

Remark: Change the cluster name and resource name according to your installation

After you have executed the command, you will get a warning stating that you have to bring your resource off line before your new setting will take affect. See screenshot below.

Step 5: Verify if your new value

To confirm that your new setting will be applied, execute the following command again:

cluster /cluster:DENALLICLUSTER res “SQL Network Name (DENALISQL)” /priv

Remark: Change the cluster name and resource name according to your installation

Step 6: Take your resource offline and online

Take the clustered service or application offline and bring it back online, using the method that you are most familiar with