Sql Server Faq

You are able to submit the data in the table using the application like InfoPath 2007 but when you go ahead and add the table in the Merge Replication Publication, the insert into the table using the InfoPath form starts to fail. This issue occurs when we have the application designed to insert data into multiple tables and the tables have master-child relationship with primary key in the tables set for identity value.

The error message you will see is similar to the below,

Error Message:-
InfoPath cannot submit the form.
An error occurred while the form was being submitted.
Cannot insert the value NULL into column ‘<Column_Name>’, table ‘<Table_Name>’; column does not allow nulls. INSERT fails.

When you remove the article from Replication, you will find the insert statement working again.

 

If you take an SQL Profiler during the failure on SQL Server, You can find that the application runs queries similar to the below,
SELECT @@identity

Then it inserts into the child table using the value returned by the above “SELECT @@identity” statement

The query “SELECT @@identity” returns incorrect results from the application perspective and the insert fails. This result is different because the Replication creates Triggers on the table which goes ahead and inserts data into the replication metadata tables during the operation. This increases the identity value and hence the application gets the updated identity value when it tries to insert into the child table. This causes the failure of the insert.

This is a known issue with ADO 2.8. The KB 951937 article provides a hotfix to correct this issue,

On a computer that is running Windows Vista, Windows Server 2008, or Windows XP, an incorrect value is returned when an application queries the identity column value of a newly inserted row in various versions of SQL Server 2005 and of SQL Server 2000

http://support.microsoft.com/?id=951937

 

 

We get the below error message when distribution agent tries to sync,

Error: 14151, Severity: 18, State: 1.
The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

CAUSE:
======
When we check the Publication properties, we find that the options for subscription expiration are marked as “Subscriptions never expire”.

When we check the sql server error log, we see that the following error is reported in the error log,

Message in Errorlog :-
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 scheduled for retry. Query timeout expired
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.
Error: 14151, Severity: 18, State: 1.

Replication-Replication Distribution Subsystem: agent Servername\Instname-Directory-Directory_R-Servername\Instname-8 failed. The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

From the above error log it is clear that the Subscriber was deactivated due to some reason.

So in order to find the cause of deactivation we need to check the Transaction Retention period in the Distribution properties. If it is set to a lower number like 3 hours, then this indicates that if the subscriber does not synchronies in 3 hours, the distribution cleanup agent will truncate the commands tables in distribution and hence the subscriber will be marked as deactivated.

Since the commands are deleted from distributor there is no other option but to reinitialize the subscriber.

Also to find the cause of the Distributor not being able to sync we can check the Distribution history in the below tables on distribution database,

msdistribution_agents
msdistribution_history

 

Minimize the MSI code for setup and use MSI code for what it does the best.

In Sql 2005 entire setup process was coded using MSI code which uses the Windows Installer code and was found to be root cause of many setup failures in sql 2005.

However in sql 2008 setup process has been optimized such that MSI code is mainly used to lay down the support files locally and then spawn the setup process locally. There after depending the different scenario (viz install, repair, uninstall, add node to cluster  …etc) various different workflows are select and the customized code is executed.

 

Same Media can be used for the installation irrespective of the CPU architecture i.e x86, x64 and IA64bit

The initial setup.exe which is an unmanaged code detects the CPU architecture of the system and calls the appropriate setup100.exe from the respective folder (x86, x64, IA64) on the DVD

 

Edition of the Sql Server 2008 is decided by the Product Key Code

In sql server 2008, same media is used for the installation of the entire edition and the edition installed is mainly decided by the Product code

 

Setup.exe can be patched dynamically on the fly

One of the drawbacks of the sql server 2005 setup was that if there was any bug detected in the code of the setup.exe , the patching of the setup.exe was difficult and at times next to impossible.

With Katmai, it is possible to patch the setup.exe on the fly due to the following property of the bootstrapping code (setup.exe).

When the setup.exe is called it checks for the presence of the local copy of the setup.exe, In other words it checks if the support files are already installed or not. If it finds the local copy of the setup.exe of the same or greater version, the local setup.exe is launched.

So there are 2 ways to patch the setup process

1)      If you have a local share of the sql server 2008 installation media on the disk, you can simply replace the old sqlsupport.msi with the new sqlsupport.msi. This can be useful for enterprise customers which uses the single share of the copy of the media for multiple installation

2)      If the setup has been installed then in that case sql support files are already been installed. So in that case we will have to download an addition patch from Microsoft site and install the support files of the version higher than the version of setup.exe. So when the setup.exe of the RTM version is called, it finds a local copy of the support files of the version higher than RTM and as explained previously, it  launches the setup.exe of the local copy which is the patched setup.exe.

 

 

RULES AND VALIDATION CHECKS

With sql 2005, setup performs System Configuration checks (SCC) however with katmai the SCC checks have been further enhanced, there are various rules defined which needs to be met for the successful installation of the sql server.

There are Global Rules checks which apply to all the scenarios (viz install, upgrade, uninstall  …etc) however there are additional scenario specific rule checks which need to be met for the specific scenario to succeed.

 

Discovery Report

In sql 2005, there were many failure observed since the uninstall of the previous install was not clean and cannot be detected. In Katmai, Discovery report in the setup process can be used to check for the previous failed installation and which should be cleaned up so that they are not the cause of the failure in the new instance. In addition it can also be used to see the existing sql installation on the system and their version.

Generation of the Configuration File

In an enterprise when we need to perform multiple identical installations of the sql server on different system. It can be much beneficial, faster and efficient to use silent quiet installation of the sql server, wherein the entire configuration option are captured in the single configuration file and can be reused multiple time with minimum user intervention.

Each installation of sql server 2008 creates a configuration file which can be used for silent installation in future. In addition we can specifically run the setup process to create the configuration file. The default location of the configuration file which is created is

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<session _ folder>.

 

There are few new changes which I am not able to cover it now but I will try to cover them in my future posts.

 

In order to install, upgrade, uninstall, and patch the sql server 2008 instance on the system we need to call the setup.exe from the installation media.

The setup.exe is an unmanaged code which checks the basic pre-requisites to launch the actual Setup process. The basic pre-requisites check performed by setup.exe is

1)      It checks for the presence of the .Net Framework with SP1. If not present, it will initiate the installation of the .Net Framework with SP1.

 

Note:  The setup during the installation of .Net Framework 3.5 might say “Downloading from Internet”. However it actually loads the setup from local copy and does not connect to Internet.

 

2)      It checks for the presence of the Windows Installer 4.5 and if it is not present it will install it.

After installing these pre-requisites, it asks for reboot. And if there is any pending reboot operation from previous installation, it will ask for reboot and exit with error code 3010.

The Logs for the setup.exe (unmanaged code) is located in %temp%sqlsetup*.log.

After the initial bootstrapping performed by setup.exe it calls the setup100.exe which is managed code which performs the actual setup process

 

While installing security patch KB 948109 on the sql server 2005, the installation of the patch may fail. In order to find the cause of the failure we need to look into the setup bootstrap logs which are located in the following location

C:\Progarm Files\Microsoft Sql Server\90\Setup Bootstrap\LOG\Hotfix\Summary.txt

One of the cause of the failure can be Error 11032 which is reported in the summary.txt as shown

***********************************************************************

Product Installation Status

Product : SQL Server Database Services 2005 (SQLCMSP6)

Product Version (Previous): 3042

Product Version (Final) :

Status : Failure

Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB948109_sqlrun_sql.msp.log

Error Number : 11032

Error Description : Unable to install Windows Installer MSP file

———————————————————————–

Product : SQL Server Analysis Services 2005 (SQLCMSP6)

Product Version (Previous): 3042

Product Version (Final) : 3068

Status : Success

Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\OLAP9_Hotfix_KB948109_sqlrun_as.msp.log

Error Number : 0

Error Description :

———————————————————————————-

Product : SQL Server Reporting Services 2005 (SQLCMSP6)

Product Version (Previous): 3042

Product Version (Final) : 3068

Status : Success

Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\RS9_Hotfix_KB948109_sqlrun_rs.msp.log

Error Number : 0

Error Description :

———————————————————————–

CAUSE:

====================

During the installation of the Hotfix (948109 in my case) the Windows installer looks for the folder FTData in the location c:\Program files\Microsoft sql server\MSSQL.X\MSSQL\FTdata.

However if the FTData folder has been willingly or unknowingly deleted from the location c:\Program files\Microsoft sql server\MSSQL.X\MSSQL\. then the setup of the HotFix may fail with error 11032 and following error will reported in the HotFix.log and SQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

HotFix.log

=====

18/2008 18:08:30.614 Registry: Opened registry key “Software\Policies\Microsoft\Windows\Installer”

08/18/2008 18:08:30.632 Registry: Cannot read registry key value “Debug”

08/18/2008 18:10:02.595 MSP returned 1603: A fatal error occurred during installation.

08/18/2008 18:10:03.057 Registry: Opened registry key “Software\Policies\Microsoft\Windows\Installer”

08/18/2008 18:10:03.075 Registry: Cannot read registry key value “Debug”

08/18/2008 18:10:04.205 Copy Engine: Error, unable to install MSP file: e:\73ee7eecf2f2f0aa67706f3e\HotFixSQL\Files\sqlrun_sql.msp

08/18/2008 18:10:04.222 The following exception occurred: Unable to install Windows Installer MSP file Date: 08/18/2008 18:10:04.222 File: \depot\sqlvault\stable\setupmainl1\setup\sqlse\sqlsedll\copyengine.cpp Line: 807

08/18/2008 18:10:09.275 Watson: Param1 = Unknown

08/18/2008 18:10:09.344 Watson: Param2 = 0×2b18

08/18/2008 18:10:09.378 Watson: Param3 = Unknown

08/18/2008 18:10:09.446 Watson: Param4 = 0×0

08/18/2008 18:10:09.481 Watson: Param5 = copyengine.cpp@807

08/18/2008 18:10:09.498 Watson: Param6 = Unknown

08/18/2008 18:10:09.515 Watson: Param7 = SQL9

08/18/2008 18:10:09.532 Watson: Param8 = @

08/18/2008 18:10:09.566 Watson: Param9 = x86

08/18/2008 18:10:09.583 Watson: Param10 = 3068

08/18/2008 18:10:09.601 Installed product: SQL9

08/18/2008 18:10:09.618 Installing product: OLAP9

08/18/2008 18:10:09.875 Registry: Opened registry key “Software\Microsoft\Windows\CurrentVersion\Uninstall”

08/18/2008 18:10:09.926 Installing instance: SQLCMSP6

08/18/2008 18:10:09.960 Installing target: KDESQLDBOP6

08/18/2008 18:10:09.995 Stopping service: MSOLAP$SQLCMSP6

08/18/2008 18:10:17.103 Stopped service: MSOLAP$SQLCMSP6

08/18/2008 18:10:17.137 Installing file: sqlrun_as.msp

08/18/2008 18:10:17.154 Copy Engine: Creating MSP install log file at: C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\OLAP9_Hotfix_KB948109_sqlrun_as.msp.log

08/18/2008 18:10:17.171 Registry: Opened registry key “Software\Policies\Microsoft\Windows\Installer”

08/18/2008 18:10:17.189 Registry: Cannot read registry key value “Debug”

08/18/2008 18:11:23.909 MSP returned 0: The action completed successfully.

SQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

==================================

MSI (s) (48:50) [18:24:03:651]: Executing op: CustomActionSchedule(Action=CAFTEInstallFTERef.68C6D15C_77E0_11D5_8528_00C04F68155C,ActionType=1025,Source=BinaryData,Target=InstallFTERef,CustomActionData=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\FTERef\|C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\)

MSI (s) (48:9C) [18:24:03:667]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI416.tmp, Entrypoint: InstallFTERef

FTECa.DLL: INFO: FTE: InstallFTERef(), Entering…

FTECa.DLL: INFO: FTE: GetFTERefInstallParams: FTERef : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDataFTECa.DLL: ERROR: FTE: InstallFTERef: Fail to create FTERef file: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseCHS.txt, Err=3

MSI (s) (48:50) [18:24:04:763]: User policy value ‘DisableRollback’ is 0

MSI (s) (48:50) [18:24:04:763]: Machine policy value ‘DisableRollback’ is 0

Action ended 18:24:04: InstallFinalize. Return value 3.

RESOLUTION/WORKAROUND:

====================

We need to create empty FTData folder in the location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData (in my case) and rerun the setup and the setup runs fine without any errors