SQL Server Q&A

As a software engineer, I focus on .NET, especially asp.net, C#, WCF and so on, and I am also very interested in Search Engine Optimization.

Entries Tagged ‘String’

PRB: Error message: “Exception Access Violation 2147221499.Need to run the object to perform this operation” occurs when you run a DTS package in Microsoft Visual Basic code

Symptoms
This article describes one possible reason as to why a Data Transformation Services (DTS) package created in Microsoft Visual Basic code may fail with an access violation (AV) error message.
When you create a DTS package by using the WITHEVENTS keyword, and you then run the DTS package from within Microsoft Visual Basic code, the package fails with an access violation and the following error message occurs:

Microsoft dts package provider generated code execution exception;
exception access violation 2147221499.Need to run the object to
perform this operation.
Resolution
The following five events are associated with a DTS package when the DTS package is created by using the WITHEVENTS clause:

Private Sub <object>_OnError(ByVal EventSource As String, _ByVal ErrorCode As Long, _ByVal Source As String, _ByVal Description As String, _ByVal HelpFile As String, _ByVal HelpContext As Long, _ByVal IDofInterfaceWithError As String, _ByRef pbCancel As Boolean)Private Sub <object>_OnFinish(ByVal EventSource As String)Private Sub <object>_OnProgress(ByVal EventSource As String, _ByVal ProgressDescription As String, _ByVal PercentComplete As Long, _ByVal ProgressCountLow As Long, _ByVal ProgressCountHigh As Long)Private Sub <object>_OnQueryCancel(ByVal EventSource As String, _ByRef pbCancel As Boolean)Private Sub <object>_OnStart(ByVal EventSource As String)If code for these events is not present, the error message described in the “Symptoms” section occurs.
For more information about these events, please see the following article in the Microsoft Knowledge Base:
221193?(http://support.microsoft.com/kb/221193/) How to install Data Transformation Services (DTS) event handlers in Visual Basic

FIX: Stack Overflow Running CHECKDB Against DB With Many Objects

Symptoms
Running CHECKDB against a database with at least 122,000 objects, causes a stack overflow exception. The number of objects is represented by the count of rows in SYSOBJECTS, NOT the number of tables in the database.
The stack overflow exception may cause Microsoft SQL Server to shutdown. If it does the client will receive a message that the connection has been broken. If the exception does not shutdown Microsoft SQL Server, the client will appear to be still running the DBCC.

Resolution
Instead of using CHECKDB, use a script to run DBCC CHECKALLOC as well as a loop that executes DBCC CHECKTABLE for all system and user tables. Following is a script that performs this task, that can be scheduled and run using ISQL:

DBCC CHECKALLOCGOdeclare @tabname sysnamedeclare @exec_string varchar(300)declare tabcr cursor forselect name from sysobjects where type = ‘S’ or type = ‘U’ order by nameopen tabcrfetch tabcr into @tabnameselect @exec_string = “dbcc checktable(‘” + @tabname + “‘)”select @exec_string = rtrim(@exec_string)exec(@exec_string)while (@@fetch_status = 0)begin fetch tabcr into @tabname select @exec_string = “dbcc checktable(‘” + @tabname + “‘)” exec(@exec_string)endclose tabcrdeallocate tabcr

Error message when you use a common language runtime object in SQL Server 2005: “Cannot load dynamically generated serialization assembly”

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.

System.Environment class does not have a method to set the environment variable for the current process

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”)); } }}

FIX: You cannot insert empty string into Memo, Text, nText, or Blob columns

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.

FIX: Error message when you use Microsoft Connector 1.0 for SAP BI in a SQL Server 2008 Integration Services package to extract data from SAP BW: “System.OutOfMemoryException: Exception of type ‘ …

Symptoms
Consider the following scenario:You install SAP Business Intelligence (SAP BI) server on a big-endian system.You use Microsoft Connector 1.0 for SAP BI in a Microsoft SQL Server 2008 Integration Services (SSIS) package to extract data from SAP Business Warehouse (SAP BW).In this scenario, you execute the SSIS package and the following error message may be returned:

[SAP BI Source] Error: System.OutOfMemoryException: Exception of type ‘System.OutOfMemoryException’ was thrown.
at System.String.PadHelper(Int32 totalWidth, Char paddingChar, Boolean isRightPadded)
at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCStructure..ctor(RFCTableColumnCollection Columns)
at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCParameter.RetrieveColumnsByStructure(R3Connection Connection, String StructureName)
at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.AddParametersAndTablesToUndefinedFunctionObject(RFCFunction& Dest, String Name)
at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.CreateFunction(String Name)
at Microsoft.SqlServer.Dts.SapBw.Components.OHSExtractor.CheckMetadata()
at Microsoft.SqlServer.Dts.SapBw.Components.OHSExtractor.ExtractNow(Boolean InRuntimeMode, String GatewayHost, String GatewayService, String ProgramID, String ProcessChain, String DestinationName, String ExecutionMode, String PropertyRequestID, Int32 Timeout)
Resolution
This issue occurs because of a bug that causes the connector to incorrectly recognize whether the server uses a big-endian or little-endian architechture. Therefore, when allocating the memory, the memory size is parsed incorrectly. This leads to an OutOfMemoryException exception. For example, the memory size that should be allocated is 40(0x 00 00 00 28). However, the memory size is recognized incorrectly as 671088640(0x 28 00 00 00).