Visual Basic Q&A

Symptoms
If more than one connection exists in Microsoft Visual Studio .NET Server Explorer, and if you cannot see Server Explorer in Visual Studio .NET, you may receive the following error message when you click Fill Dataset to preview the data in the Data Adapter Preview window:

SqlDataAdapterName. The data adapter could not return the data from the data source.
The connection string is invalid.Alternatively, when you right-click DataAdapter and then click Generate Dataset, you may receive the following error message:

Retrieving the schema for SqlDataAdapter1 failed.
The connection string is invalid.
Resolution
Connection Manager manages the Microsoft SQL Server connections in Visual Studio .NET. When you click Fill Dataset or Generate Dataset, a Connection object is built and then added to the Connection Manager list of Connection objects. Connection Manager finds the Connection in its list and then prepares to return the Connection with the display name of the Connection. To return the display name, Connection Manager incorrectly opens Server Explorer and then looks for a connection in Server Explorer. Connection Manager generates an error because the connection does not exist in Server Explorer.

Symptoms
When you create a SQL Server local temporary table using the rdoConnection object by calling its .Execute method with default parameters, and then attempt to access the table after the Remote Data Objects (RDO) method has run, you may receive one of the following error messages:

Run-time error ‘40002′: 37000:[Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s)could not be prepared
-or-

Run-time error ‘40002′: S0002:[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid Object Name ‘#<Name of the temporary table>’
Resolution
The creation and use of temporary database tables to facilitate the storage and manipulation of volatile intermediate data is a common programming practice. The default behavior of the SQL Server Open Database Connectivity (ODBC) driver is to create and use temporary stored procedures to run prepared statements. The .Execute method of the rdoConnection object uses the SQLPrepare() and SQLExecute() ODBC application programming interface (API) calls by default to run a SQL statement as a prepared statement. Temporary tables that are created by a stored procedure are automatically dropped when the procedure completes execution. As a result, when you attempt to access a SQL Server temporary table that was created by calling the .Execute method of an rdoConnection object with default parameters, in subsequent statements you receive one of the error messages specified in the “Symptoms” section.

Symptoms
With SQL Server’s quoted_identifier option set to Off, you may receive the following error:

Run-time error ‘-2147217900 (80040e14)’:
Line 1: Syntax error near ‘tablename’ This error occurs when you are using client-side cursors with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB). The error occurs on an ActiveX Data Objects (ADO) recordset’s Update method and may occur on an AddNew method.
Resolution
With ADO client-side cursors, when you invoke an ADO recordset’s AddNew or Update method, the OLE DB Provider prepares a SQL statement to send to SQL Server.
The Microsoft OLE DB Provider for SQL Server automatically quotes identifiers on an ADO recordset’s Update method and may quote identifiers on an AddNew method. Identifiers include table names and field names.
For example, updating the Titles table in the Pubs database with the following code:

MyADORecordet.Update The preceding would be prepared similar to the following:

UPDATE “titles” SET “title”=’Hello World’ WHERE “title_id”=’3′ Note that the table name is in quotes, “titles”, and that each field name is in quotes, “title”, “title_id”, and so on.
If SQL Server’s Quoted_Identifier option is set to Off, SQL Server will not recognize table names and field names enclosed in quotes.
The error “Syntax error near ‘tablename’” occurs.

Symptoms
When using the Distributed Component Object Model (DCOM) ConfigurationUtility (Dcomcnfg.exe), only one class for each ActiveX server appears inthe list of applications. This occurs even though an ActiveX server maycontain several classes, each of which is functioning correctly.
Resolution
DCOM provides server level security. All objects within a server share thesame security settings. Therefore, Dcomcnfg.exe only lists the first object(class) for each ActiveX server registered.

Symptoms
When attempting to delete or update a record in a SQL Server cursor, the delete or update operation succeeds, but the following error returns:

Run-time error 3146 “ODBC call failed”.
Resolution
This behavior occurs when SQL Server does not return a message indicating the number of rows returned by a statement. SQL Server does not return a message indicating the number of rows affected by a statement after the following commands have been executed on SQL Server:

sp_configure “user options”, 512SET NOCOUNT ON

Symptoms
When you try to use Microsoft Decision Support Objects (DSO) from an ASP.NET application to perform administrative tasks on a server that is running Analysis Services, you may receive an error similar to the following:
When connecting to the local computer that is running Analysis Services:

Cannot connect to the Analysis server on computer ‘MyServer’. Connection to the server is lost
When connecting to a remote computer that is running Analysis Services:

Cannot open connection to Analysis server ‘MyRemoteServer’. Error in data [Possible data corruption]
Resolution
This behavior occurs for two separate reasons. One part of the issue involves the start permissions of the DSO ActiveX DLL. By default, the ActiveX DLLs are carried out in the ASP.NET worker process (Aspnet_wp.exe) under the ASPNET account, when called from an ASPX page.
The other part of the issue is, Aspnet_wp.exe uses a Multi-Threaded Apartment (MTA) model, while DSO uses a Single-Threaded Apartment (STA) model. With the ASP.NET application, the impersonation token is on one of the applications MTA threads and the STA COM component is accessed by a different thread (the single thread in its STA). Because the MTA threads impersonation token is not passed to the STA thread, the STA thread carries out under the security token associated with the Aspnet_wp.exe process.