SQL Server Q&A

Symptoms
When you use transactional replication in a republishing topology, the Distribution Agent may experience an Access Violation (AV), which leads to an abnormal termination of the Distribution Agent.
When the Access Violation occurs on a server that is running Microsoft SQL Server 2000 Service Pack 2, the following stack dump is generated:

*******************************************************An exception was caughtin DISTRIB.EXE* Exception Address = 7800209a* Exception Code = c0000005********************************************************Module Name Return Address LocationMSVCRT 00403a45 wcsncpy + 14distrib 0040104c 00403a45distrib 77e8d326 0040104cKERNEL32 00000000 CreateProcessW + 900*******************************************************
Resolution
With transactional replication, the Logreader Agent invokes the sp_MSadd_replcommands27hp stored procedure to insert the log records from the publisher to the distribution database. The Distribution Agent then takes these log records and distributes them to the subscriber.
In a republishing topology, the sp_MSadd_replcommands27hp stored procedure may incorrectly insert an empty command (0x) in the MSRepl_commands system table in the distribution database. As a result, the Distributor Agent may experience the Access Violation mentioned in the “Symptoms” section when it tries to handle the empty command.
Logreader Agent
The Logreader might send a transaction with no command to distribution, with the intent being to update the MSrepl_transactions system table properly so that the next scan does not repeat the same section of the log.
The Distribution Agent does not expect an empty command; therefore, the Logreader checks the length of “command” in the sp_MSadd_repl_commands27hp stored procedure to make sure that the xact_seqno column is inserted in the MSRepl_transactions system table, but not in the MSRepl_commands system table.
The problem is that the “length checking” logic does not take into account a republishing or bi-directional scenario, where the originator srv and the db fields are not empty. It only checks to see if datalength(@data) > 39 to determine if the command is empty. However, it does not test the actual command data length(@cmd_data_len) before it inserts data in the MSRepl_commands system table.
Distribution Agent
The following data was gathered from the command that was causing the Access Violation:

xact_seqnotypearticle_id originator_id command_id commandcmd_data_len———————————- ———– ———- ————- ———- ————————-0×000770C1000000F6000100×00000000 NULL0×00000001 0×0 The Distribution Agent uses the sp_MSget_repl_commands stored procedure to select the commands that it needs to process. The sp_MSget_repl_commands stored procedure only picks up a command with article_id=0 when all of the following conditions are true: There are no inactive subscriptions.Loopback detection is not turned on for any articles.The number of articles in the MSsubscriptions system table is the same as the number of articles in the MSarticles system table for this particular publication. When the record with article_id=0 is picked up, the command (0x) is passed to the Distribution Agent; however, it is not handled gracefully and the Access Violation occurs.
To summarize, the cause of the problem is that:
The Logreader inserts a command of 0x in the MSRepl_commands system table in the distribution database. -and-
The Distribution Agent picks up commands with article_id=0 from the MSRepl_commands system table in the Distribution database.

Symptoms
When you use a common language runtime (CLR) object in Microsoft SQL Server 2005, you may receive an error message that is similar to the following:

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate ‘ObjectName’:
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters options, String[] fileNames)
at Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters options, String[] sources)
at Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.CompileAssemblyFromSourceBatch(CompilerParameters options, String[] sources)
at System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerParameters options, String[] s
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GenerateTempAssembly(XmlMapping xmlMapping, Type type, String defaultNamespace)
at System.Xml.Serialization.XmlSerializer..ctor(Type type, String defaultNamespace)
at System.Xml.Serialization.XmlSe…For example, you may receive the error message when you use a CLR object that calls a Web service or performs conversion from user-defined types to XML inside SQL Server.
Resolution
This issue occurs when a CLR object is converted to the XML data type. When this conversion occurs, the Windows Communication Foundation (formerly code-named “Indigo”) tries to do the following: Generate a new XML serialization assembly.Save the assembly to disk.Load the assembly into the current application domain. However, SQL Server does not allow for this kind of disk access in the SQL CLR for security reasons. Therefore, you receive the error message that is mentioned in the “Symptoms” section. Several scenarios may cause the CLR object to be converted to the XML data type.
For more information about the Windows Communication Foundation, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms735119.aspx(http://msdn2.microsoft.com/en-us/library/ms735119.aspx)You may receive the error message that is mentioned in the “Symptoms” section in the following scenarios: The CLR code that implements CLR objects explicitly uses the XmlSerializer class. These CLR objects may include stored procedures, functions, user-defined types, aggregates, and triggers.You use a Web service in the CLR code.You send or receive CLR objects to or from SQL Server by using direct HTTP/SOAP access to SQL Server. The CLR object converts a user-defined type to the XML data type.

Symptoms
When you create a parameterized command against Microsoft SQL Server 7.0, if you call the Prepare method before you add parameters to the command, you receive the following error message:

An unhandled exception of type ‘System.Data.SqlClient.SqlException’ occurred in system.data.dll.
Additional information: System error. This problem does not occur in SQL Server 2000.
Resolution
This problem occurs in SQL Server 7.0 because, by design, you cannot run the Prepare method before you add parameters. This applies to most database systems.
SQL Server 2000 does not generate the above-mentioned exception because it does not run Prepare until the first command is executed. This optimization prevents the overhead of Prepare if no commands are subsequently executed.

Symptoms
The System.Environment class has methods to read the environment variables. However, this class hasno method to set the environment variables for the current process.
Resolution
To work around this problem, use the interop services to set the environment variables. You can set an environment variable by using the Microsoft Platform Software Development Kit (SDK) SetEnvironmentVariable function.
To set an environment variable by calling the Platform SDK SetEnvironmentVariable function, follow these steps: Start Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET. On the File menu, point to New, and then click New Project.In the New Project dialog box, click Visual C# Projects.
Note In Visual Studio 2005, Visual C# Projects is be changed to Visual C#.Under Templates, click Console Application, and then click OK. By default, the Class1.cs file is created.In the code view of the Class1.cs file, specify the using statement to declare the namespaces so that you do not have to qualify the declarations later in the code. Paste the following code in the Class1.cs file:

using System;using System.Runtime.InteropServices;using System.Security;using System.Security.Permissions;Declare the static method and the extern method. Use the DllImport attribute to import the Kernel32.dll file. This declaration indicates that the definition of the function is outside the code. To do this, paste the following code in the Class1.cs file:

// Import the Kernel32 dll file.[DllImport("kernel32.dll",CharSet=CharSet.Auto, SetLastError=true)][return:MarshalAs(UnmanagedType.Bool)]// The declaration is similar to the SDK functionpublic static extern bool SetEnvironmentVariable(string lpName, string lpValue);Paste the following code in the Class1.cs file to add a static method in the class that calls the SetEnvironmentVariable method and that sets the environment variable:

public static bool SetEnvironmentVariableEx(string environmentVariable, string variableValue){ try { // Get the write permission to set the environment variable. EnvironmentPermission environmentPermission = new EnvironmentPermission(EnvironmentPermissionAccess.Write,environmentVariable); environmentPermission.Demand(); return SetEnvironmentVariable(environmentVariable, variableValue); } catch( SecurityException e) { Console.WriteLine(“Exception:” + e.Message); } return false;}Paste the following code in the Main method to set an environment variable:

// Create a sample environment variable and set its value (for the current process).SampleSetEnvironmentVariable.SetEnvironmentVariableEx(“TESTENV”, “TestValue”);Paste the following code to display the environment variable value:

// Verify that environment variable is set correctly.Console.WriteLine(“The value of TESTENV is: ” + Environment.GetEnvironmentVariable(“TESTENV”));Complete Code Sample

using System;using System.Runtime.InteropServices;using System.Security;using System.Security.Permissions;namespace SetEnv{ /// <summary> /// Summary description for Class1. /// </summary> public class SampleSetEnvironmentVariable {// Import the kernel32 dll.[DllImport("kernel32.dll",CharSet=CharSet.Auto, SetLastError=true)][return:MarshalAs(UnmanagedType.Bool)]// The declaration is similar to the SDK functionpublic static extern bool SetEnvironmentVariable(string lpName, string lpValue); public SampleSetEnvironmentVariable() { } public static bool SetEnvironmentVariableEx(string environmentVariable, string variableValue) { try { // Get the write permission to set the environment variable. EnvironmentPermission environmentPermission = new EnvironmentPermission(EnvironmentPermissionAccess.Write,environmentVariable); environmentPermission.Demand(); return SetEnvironmentVariable(environmentVariable, variableValue); } catch( SecurityException e) { Console.WriteLine(“Exception:” + e.Message); } return false; } } class MyClass { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { // Create a sample environment variable and set its value (for the current process). SampleSetEnvironmentVariable.SetEnvironmentVariableEx(“TESTENV”, “TestValue”); // Verify that environment variable is set correctly. Console.WriteLine(“The value of TESTENV is: ” + Environment.GetEnvironmentVariable(“TESTENV”)); } }}

Symptoms
When you bind a DateTimePicker control to a data source, you may receive the following error message:

An unhandled exception of type ‘System.InvalidCastException’ occurred in mscorlib.dll
Additional information: Object cannot be cast from DBNull to other types.
Resolution
This behavior occurs if the field that is bound to the DateTimePicker control contains a null value that is represented by the System.DBNull object. The DateTimePicker control does not support the System.DBNull object.

Symptoms
When you try to insert an empty string into Memo, Text, nText or Blob columns by using the OLE DB .NET data provider, you receive the following exception:

An unhandled exception of type ‘System.InvalidOperationException’ occurred in system.data.dll If you handle this exception within a try-catch block, you receive the following information:

System.InvalidOperationException
System.Data.OleDb.OleDbException: Multiple-Step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Resolution
To work around this problem: For strings, insert a space that has a length greater than zero (such as ” “), instead of using an empty string.If the database has an Allow Nulls setting, select Allow Nulls for the columns in the database, and treat nulls as empty strings.