Visual Basic Q&A

Symptoms
In Microsoft SQL Server 2000 and in MicrosoftSQL Server 2005, you can add, delete, and rename the data files and the transaction log files. This article explains how to consolidate physical files, and how to rename the logical file name in SQL Server 2000 and in SQL Server 2005.
Resolution

Consolidate Physical Files You may have to consolidate files or reduce the number of physical files for a server that is running SQL Server 2000 or SQL Server 2005. To reduce the number of physical files, you can delete the files. To consolidate the files, you can purge inactive transactions.
Deleting FilesDeleting a data or a transaction log file removes the file from the database. However, you cannot remove a file from a database if the file contains data or transaction log information. You can only remove a file if the file is empty. If you have data that you want to keep, you can migrate the data from a data file to other files in the same filegroup. To migrate the data, you can use a DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Then, SQL Server no longer allows data to be inserted in the file, and you can delete the file by using an ALTER DATABASE statement.
You cannot migrate the transaction log data from one log file to another to delete a transaction log file.
Purging Inactive TransactionsTo purge inactive transactions from a transaction log file, you must truncate or back up the transaction log. When a transaction log file no longer contains any active or inactive transactions, you can remove the log file from the database. To remove the log file from the database, use these steps: To shrink data or information in a file and to make the file empty, run the following Transact-SQL statement:

DBCC SHRINKFILE (‘<logical file name>’, EMPTYFILE ) To delete a file from a database, run the following Transact-SQL statement:

ALTER DATABASE <Database name>REMOVE FILE <logical file name>
IMPORTANT After you add or delete files, create a database backup immediately. You must create a full database backup before you create a transaction log backup.
Rename Logical File NameTo modify the logical name of a data file or a log file, specify the logical file name you want to rename by using the Name parameter, and then specify the new logical name for the file by using the NewName parameter. To rename the logical file, run the following Transact-SQL statement:

ALTER DATABASE <Database name>MODIFY FILE( NAME = <current_logical_name>, NEWNAME = <new_logical_name>)

Symptoms
If you try to run Structured Query Language (SQL) against an Oracledatabase through Data Access Objects (DAO), you can encounter the followingerror:

Run-Time Error 3024
Couldn’t find file C:\Program Files\DevStudio\VB\<schema name>.mdb.This error occurs when you try to specify a schema name in your SQLstatement and you use DAO with Oracle. The <schema name> in the errormessage is whatever schema name you specified for the Oracle object youreference.
Resolution
If you add the dbSQLPassThrough option to the OpenRecordset method, thesample code in the MORE INFORMATION section of this article runs withouterror. You can use brackets ([ ]) around the table name in the SQL string toavoid the error as well:

sql = “SELECT * FROM [scott.emp]” Also, you can use an advanced data access technology, such as ActiveX DataObjects (ADO) to avoid this error.

Symptoms
Pressing the ESC key while the cursor is in the SQL text box of theGeneral Tab in the property page of the Remote Data Control causes a blankGeneral tab. When you click the Colors tab and then click the General tabagain, an application error occurs and Visual Basic ends. The SQL text boxcan be empty or contain characters. Clicking the Cancel button on thewindow correctly closes the window. This behavior only occurs under theWindows 95 operating system.To work around this behavior, programmatically set the properties ofremote data control or use the Properties window of the Remote Data Objectto set the properties.
Resolution
Microsoft has confirmed this to be an issue in the Microsoft productslisted at the beginning of this article. Microsoft is researching thisissue and will post new information here in the Microsoft Knowledge Baseas it becomes available.

Symptoms
When you set the RecordSource property of an ADO Data Control to a different SQL SELECT statement and then try to execute the Refresh method, the following error is returned:

Syntax error in FROM clause.which is followed by:

Run-time error ‘-2147217900(80040e14)’:
Method ‘Refresh’ of object ‘IAdodc’ failedor:

Method ‘Refresh’ of object ‘IAdodc’ failed when attempting to refresh an ADODC after setting the recordsource property to another value.The errors occur if the CommandType property of the ADO Data Control is set to adCmdTable.
Resolution
When the CommandType of the ADO Data Control is set to adCmdTable, “SELECT * From” is automatically prepended to the RecordSource value.
Setting the RecordSource to a table name results in a valid SQL statement, such as SELECT * FROM Tablename.
Setting the RecordSource to a SQL SELECT statement, such as Select * From Tablename, results in a SQL statement ofSelect * From Select * From Tablename, which is an invalid SQL statement.

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.