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
You can use Microsoft SQL Server’s PRINT statement in stored procedures to return messages as informational errors in ADO, OLE DB, and ODBC applications. However, a Visual Basic client application may not capture such informational messages when it sets up a DataEnvironment command and uses the DataEnvironment.CommandName syntax to run the stored procedure.
Resolution
This problem has its roots in ADO. The InfoMessage event of an ADO Connection object does not fire when its CursorLocation property is set to adUseClient. By default, the CursorLocation property of Visual Basic 6.0 DataEnvironment Connection objects is set to adUseClient. As a result, the InfoMessage event procedure of DataEnvironment Connection objects does not fire when informational messages are returned to the client application.

Symptoms
In Microsoft Visual Studio .NET or Visual Studio 2005, one or more columns in the Results pane appear to be empty when you retrieve data from a Microsoft SQL Server 2000 database table. This problem occurs when the following conditions are true:The database table contains a column of type CHAR, NCHAR, VARCHAR, or NVARCHAR.The column contains data that has more than 900 characters.
Resolution
The cells of the Results pane in Visual Studio .NET or Visual Studio 2005 cannot display data that has more than 900 characters.

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
This article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database.
Resolution
Microsoft SQL Server version 7.0 provides you the ability to perform queries against different databases by using OLE DB providers. You query databases by using: OpenQuery or OpenRowset Transact-SQL functions.
-or-
A query with four-part names including a linked server name. To set up a linked server to access a secured Microsoft Access database, use these steps: Configure the registry (by using the Registry Editor) to use the correct Microsoft Access Workgroup Information file (the .mdw file). Use the Registry Editor to add the full path name of the Workgroup Information file to the following registry entry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB Next, set the value to the path and name of the file, such as:

C:\…\MySystem.mdwTo open the registry editor, navigate to the Start button and click Run. In the Run dialog box, type Regedit, and then press OK.In the registry editor, navigate to this key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Right-click and select New/String Value.Type SystemDb, and then press Enter.Double-click the SystemDb key in the left window pane.In the Value Data text box, type the full path to your .mdw file.Close the registry editor. Execute the sp_addlinkedserver stored procedure to create the linked server. Specify Microsoft.Jet.OLEDB.4.0 as the provider_name, and specify the full pathname of the Microsoft Access .mdb database file as the data_source. The data_source is evaluated on the server, not the client, and the path must be valid on the server.Execute the sp_addlinkedsrvlogin stored procedure to create login mappings from local logins to Microsoft Access logins.
Steps to Query Secured Microsoft Access DatabaseModify the registry key shown in step 1 of the “More Information” section and add the location of your .mdw file.Start Microsoft Visual Basic 6.0 and select a Standard EXE project. Form1 is created by default.On the Project menu, select References, and set a reference to the Microsoft ActiveX Data Objects 2.1 Library or later.Place two command buttons and a DataGrid control on Form1 (named Command1, Command2 and DataGrid1 respectively).Paste the following code into the Declarations section of Form1:
Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

Dim adorst As New ADODB.RecordsetDim adoconn As New ADODB.ConnectionPrivate Sub Command1_Click()Dim strConn As Stringadoconn.Open “Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;”adoconn.Execute “EXEC sp_addlinkedserver ‘SecuredJetLS’, ‘Jet 4.0′, ‘Microsoft.Jet.OLEDB.4.0′, ‘c:\…..\MyDatabase.mdb’”adoconn.Execute “EXEC sp_addlinkedsrvlogin ‘SecuredJetLS’, FALSE, NULL, ‘UserName’, ‘Password’”adoconn.CloseMsgBox “Successful Setup”End SubPrivate Sub Command2_Click()Dim SQL As String’ Using OpenQuery syntax.SQL = ” Select a.* from OPENQUERY(SecuredJetLS, ‘Select * from MyTable’) a”‘ Using OpenRowset syntax.’ SQL = “SELECT * From OpenRowset(‘Microsoft.Jet.OLEDB.4.0′,’c:\….\MyDatabase.mdb’; ‘UserName’;'Password’, MyTable)”‘ Using four-part name syntax.’ SQL = “Select * from SecuredJetLS…MyTable”adoconn.CursorLocation = adUseClientadoconn.Open “Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;”adorst.Open SQL, adoconn, adOpenStatic, adLockReadOnlySet DataGrid1.DataSource = adorstEnd SubPrivate Sub Form_Load()Command1.Caption = “Setup Linked Server”Command2.Caption = “Query Linked Server”End SubPrivate Sub Form_Unload(Cancel As Integer)adorst.CloseSet adorst = Nothingadoconn.CloseSet adoconn = NothingEnd SubRun the project.Click Setup Linked Server. If you modify sp_addlinkedserver and sp_addlinkedsrvlogin in the connection string with the correct parameters, the linked server is created successfully.Click Query Linked Server. If you modify the connection string and the query text to the correct parameters, the DataGrid control is populated with your data.

Symptoms
Identity columns are normally read-only because the server generates the values. However, when you migrate data from one table to another you often want to keep the existing Identity column values rather than use server-generated values. Microsoft SQL Server 7.0 allows you to do this with the SET IDENTITY_INSERT ON statement.
This operates correctly when you use INSERT INTO SQL statements. However, when you insert records through the AddNew method of an ActiveX Data Objects (ADO) Recordset, you receive the following error:

Run-time error ‘-2147217887 (80040e21)’
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
Resolution
The Microsoft SQL Server 7.0 OLE DB provider incorrectly reports to ADO that the Identity column is read-only.